schemascope 0.2.0

Profile a data source (files, SQLite, or any SQL database) against a schema across JSON/YAML/XML/TXT formats.

schemascope

schemascope profiles tabular data against a schema you already have, so you can catch schema drift before it bites you.

In plainer words: you give it the shape your data is supposed to have, and the data itself, and it tells you — per table and per column — what actually showed up. Is every expected table and column present? Where are the nulls? How many distinct values? What type do the real values look like, and does that still match the type you declared? That is the whole job. schemascope is a read-only profiler and drift detector: it does not modify data, create tables, enforce constraints, or validate every row against a rich rule language.

To do that, schemascope needs two things from you: your data, and a schema.

This manual is written to be read top to bottom with nobody to ask; every command can be copied and pasted as-is. If a term is unfamiliar (entity, field, drift, inferred type), the Concepts and glossary section defines each one in a sentence — but you can also just start reading and pick them up as you go.

---

Table of Contents

• The two things schemascope needs
• Install
• Your first run in 5 minutes
• The core idea
• Step 1 — Give it your schema
• Step 2 — Point it at your data
• Concepts and glossary
• Schema Model
• Schema Formats
• Type Names
• Data Sources
• Output Reference
• Type Inference
• Python API
• Format Detection
• Troubleshooting
• Limitations
• Requirements
• Development
• License
• Appendix A: Generating a schemascope schema from your database
• Appendix B: Type-mapping cheat sheet

---

The two things schemascope needs

schemascope only ever looks at two inputs. Get these two things right and everything else follows.

1. Your data — the rows

This is the actual content: the customers, the orders, the users. A tiny example, five rows of a users table:

id,email,age,active,deleted,signup_date
1,[e-mail],31,true,0,2021-03-05
2,[e-mail],,false,0,2021-07-19
3,[e-mail],27,true,1,2022-01-02
4,[e-mail],44,true,0,2022-11-30
5,[e-mail],,false,0,2023-05-14

The single most important fact in this manual: schemascope reads three kinds of data source — a directory of CSV files (one .csv per table), a single SQLite file (.db, .sqlite, .sqlite3), or a live database reached through a SQLAlchemy URL (PostgreSQL, MySQL/MariaDB, SQL Server/Azure/Fabric, Oracle, SQLite, and any other engine SQLAlchemy speaks). Running it straight against your database — schemascope schema.json "postgresql+psycopg://user@host/db" — is the main way to use it; you install the small driver for your engine (see Appendix A) and pass the URL. You always give it a schema too; the easiest way to get one is straight out of the same database (also in Appendix A).

2. A schema — the shape, not the rows

A schema is a small plain-text file that describes what your data is supposed to look like: which entities (tables) exist, which fields (columns) each has, and what type each field should be. It is not SQL. schemascope reads schemas written as JSON, YAML, XML, or a small TXT DSL — never a CREATE TABLE statement.

Here is a schema for the data above. In JSON:

{
  "entities": [
    {
      "name": "users",
      "fields": [
        {"name": "id", "type": "integer", "primary_key": true},
        {"name": "email", "type": "string"},
        {"name": "age", "type": "integer", "nullable": true},
        {"name": "active", "type": "boolean"},
        {"name": "deleted", "type": "integer", "nullable": false},
        {"name": "signup_date", "type": "date"}
      ]
    }
  ]
}

The exact same shape in the TXT DSL, which is the least fiddly to hand-write (no braces, no quotes):

entity users
  id: integer pk
  email: string
  age: integer null
  active: boolean
  deleted: integer not null
  signup_date: date

That file — in any of the four formats — is the schema. You hand it to schemascope directly.

Where does the schema come from?

You are in one of three situations. Find yours.

Your situation	What you do
Your data is in a database	The database already knows its own structure. Read it (its DDL, its information_schema, or an introspection command) and translate the column types into a small schemascope schema. Appendix A has the exact commands per engine.
Someone handed you a schema file (.json / .yaml / .xml / .txt)	You are ready. Go to Install.
You have only data and no schema	Write one by hand — it is tiny (one entity, a few fields). schemascope does not infer a schema from your data, so this step is yours. See Step 1 — Give it your schema.

Short version: if your data lives in a real database, you do two things — read its structure into a small schemascope schema, and export its rows to CSV or SQLite. Both halves are spelled out per platform in Appendix A.

---

Install

First, check your Python version. schemascope needs Python 3.8 or newer (this is the floor declared in pyproject.toml):

python3 --version

If that prints Python 3.8.x or higher, you are good. Then install:

pip install schemascope

Confirm it landed on your PATH:

schemascope --version

You should see a line like schemascope 0.1.0. If instead you get "command not found", jump to Troubleshooting — the usual fix is to run it as python -m schemascope instead.

PyYAML is installed automatically with the package; there are no other runtime dependencies.

To work on schemascope from a source checkout instead:

python -m pip install --upgrade pip
python -m pip install -e .

(A current pip is recommended — older versions may not support editable installs for pyproject.toml projects.)

Note: everywhere this manual writes schemascope ..., you can equally write python -m schemascope .... They are the same program; use whichever is convenient. The python -m form is handy when the console script is not on your PATH.

---

Your first run in 5 minutes

The repository ships a complete, tiny example, so you can see schemascope work before touching your own data. From the root of a source checkout, follow these five steps.

1. Know your two inputs

The command below profiles the schema examples/schema.json against the data directory examples/data. The directory holds one file, examples/data/users.csv:

id,email,age,active,deleted,signup_date
1,[e-mail],31,true,0,2021-03-05
2,[e-mail],,false,0,2021-07-19
3,[e-mail],27,true,1,2022-01-02
4,[e-mail],44,true,0,2022-11-30
5,[e-mail],,false,0,2023-05-14

and the schema, examples/schema.json, declares one entity, users:

{
  "entities": [
    {
      "name": "users",
      "fields": [
        {"name": "id", "type": "integer", "primary_key": true},
        {"name": "email", "type": "string"},
        {"name": "age", "type": "integer", "nullable": true},
        {"name": "active", "type": "boolean"},
        {"name": "deleted", "type": "integer", "nullable": false},
        {"name": "signup_date", "type": "date"}
      ]
    }
  ]
}

2. Run the command

schemascope examples/schema.json examples/data

The first argument is the schema; the second is the data source (here, a directory).

3. Read the report

schemascope prints the report to standard output. It does not write any files — the report is the output. Running the command above prints this exact JSON:

{
  "entities": [
    {
      "name": "users",
      "source": "users",
      "present": true,
      "row_count": 5,
      "fields": [
        {
          "name": "id",
          "declared_type": "integer",
          "column": "id",
          "present": true,
          "row_count": 5,
          "null_count": 0,
          "null_fraction": 0.0,
          "distinct_count": 5,
          "inferred_type": "integer",
          "type_ok": true
        },
        {
          "name": "email",
          "declared_type": "string",
          "column": "email",
          "present": true,
          "row_count": 5,
          "null_count": 0,
          "null_fraction": 0.0,
          "distinct_count": 5,
          "inferred_type": "string",
          "type_ok": true
        },
        {
          "name": "age",
          "declared_type": "integer",
          "column": "age",
          "present": true,
          "row_count": 5,
          "null_count": 2,
          "null_fraction": 0.4,
          "distinct_count": 3,
          "inferred_type": "integer",
          "type_ok": true
        },
        {
          "name": "active",
          "declared_type": "boolean",
          "column": "active",
          "present": true,
          "row_count": 5,
          "null_count": 0,
          "null_fraction": 0.0,
          "distinct_count": 2,
          "inferred_type": "boolean",
          "type_ok": true
        },
        {
          "name": "deleted",
          "declared_type": "integer",
          "column": "deleted",
          "present": true,
          "row_count": 5,
          "null_count": 0,
          "null_fraction": 0.0,
          "distinct_count": 2,
          "inferred_type": "boolean",
          "type_ok": true
        },
        {
          "name": "signup_date",
          "declared_type": "date",
          "column": "signup_date",
          "present": true,
          "row_count": 5,
          "null_count": 0,
          "null_fraction": 0.0,
          "distinct_count": 5,
          "inferred_type": "date",
          "type_ok": true
        }
      ]
    }
  ]
}

4. Understand it field by field

This is where schemascope earns its keep, so read it slowly against the five rows above.

At the entity level: "present": true means users.csv was found, and "row_count": 5 means it has 5 data rows (the header row does not count). "source": "users" is the file stem schemascope read (users → users.csv).

Now walk each field:

• id — Values 1,2,3,4,5. All five are whole numbers, none empty, all different. So null_count is 0, distinct_count is 5, and the inferred type is integer. You declared integer, so type_ok is true. (It is not inferred as boolean because the values are not all 0/1.)

• email — Five different addresses, none empty. null_count 0, distinct_count 5. The values are not numbers, dates, or booleans, so inference falls back to string. Declared string, so type_ok is true.

• age — Values 31, (empty), 27, 44, (empty). Two rows (bob and erin) have an empty cell, which counts as null: null_count is 2 and null_fraction is 2 / 5 = 0.4. The three remaining non-null values (31, 27, 44) are all different, so distinct_count is 3. schemascope only ever infers a type from non-null values, and all three are whole numbers, so the inferred type is integer. Declared integer; type_ok is true. This is a healthy result: nulls are fine here because the field is declared nullable: true.

• active — Values true, false, true, true, false. Only two distinct values, so distinct_count is 2. true/false are recognized boolean tokens, so the inferred type is boolean, matching the declared boolean.

• deleted — Values 0, 0, 1, 0, 0. Here is the interesting one. You declared it integer, but schemascope infers boolean — because every value is either 0 or 1, and boolean is the most specific type that fits an all-0/1 column. distinct_count is 2 (the values 0 and 1). Even though declared and inferred differ, type_ok is still true. That is deliberate: schemascope knows an all-0/1 column often reads as boolean but is a perfectly valid integer, so a declared integer accepts an inferred boolean without complaint. This is not drift.

• signup_date — Five different YYYY-MM-DD dates. distinct_count is 5. They match schemascope's strict date format, so the inferred type is date, matching the declared date.

Every type_ok is true and nothing is missing, so this data is clean against this schema. If, say, users.csv disappeared, the users entity would come back "present": false; if the age column were dropped, its field would come back "present": false; if someone put the word "unknown" in the age column, its inferred type would flip to string and type_ok would become false. That is what drift looks like in the output.

5. Try the variations

The example schema ships in all four supported formats. They normalize to the same model and produce the same profile — run any of them:

schemascope examples/schema.json examples/data
schemascope examples/schema.yaml examples/data
schemascope examples/schema.xml  examples/data
schemascope examples/schema.txt  examples/data

And to get the report as YAML instead of JSON, add -o yaml (or --output yaml):

schemascope examples/schema.json examples/data -o yaml

entities:
- name: users
  source: users
  present: true
  row_count: 5
  fields:
  - name: id
    declared_type: integer
    column: id
    present: true
    row_count: 5
    null_count: 0
    null_fraction: 0.0
    distinct_count: 5
    inferred_type: integer
    type_ok: true
  # ... one block per field, same numbers as the JSON above

That is the whole tool. Everything below is detail and reference.

---

The core idea

Hold on to one mental model:

schema + data → report. You supply the schema (what the data should look like) and the data (what it actually looks like). schemascope compares them and prints a report. There is one command, no subcommands, no state left behind on disk.

The command is always:

schemascope SCHEMA DATA [--output json|yaml] [--schema-format json|yaml|xml|txt]

Arguments and options at a glance

Token	Kind	Meaning	Default
SCHEMA	argument (required)	Path to a JSON / YAML / XML / TXT schema file.	—
DATA	argument (required)	A directory of CSV files, a SQLite file (.db/.sqlite/.sqlite3), or a SQLAlchemy database URL (postgresql+psycopg://…, mysql+pymysql://…, mssql+pyodbc://…, oracle+oracledb://…, sqlite:////…).	—
-o, --output	option	Report format: json or yaml.	json
--schema-format	option	Force the schema format (json/yaml/xml/txt) instead of auto-detecting.	auto-detect
--db-schema	option	Database schema/namespace to read tables from when DATA is a URL (Postgres public, SQL Server dbo, …).	(engine default)
--version	flag	Print the package version and exit.	—
--help	flag	Print CLI help and exit.	—

python -m schemascope ... behaves identically to the schemascope console script:

python -m schemascope examples/schema.json examples/data --output yaml

Exit codes

Keep these two in mind when scripting schemascope into a pipeline:

• 0 — success. The report was printed to stdout.
• 2 — something was wrong with your inputs: a schema error, a data-source error, or bad CLI arguments. The one-line message goes to stderr (schema error: ... or data source error: ...). argparse also uses exit code 2 for its own usage errors, such as a missing argument.

Rule to remember: the report goes to stdout, error messages go to stderr. You can redirect them separately (schemascope schema.json data/ > report.json 2> errors.log). A present: false or type_ok: false inside a successful report is not an error — it is drift, and the exit code is still 0.

---

Step 1 — Give it your schema

A schema is a small text file listing your entities and their fields. You write it (or generate it) yourself — schemascope never invents one from your data.

Decision guide: which situation are you in?

Your situation	What to do
Someone gave you a schema file already	Skip ahead — you have Step 1 done. Go to Step 2.
Your data is in a database	Read its structure and translate the types into a small schema. Appendix A has the per-engine commands.
You have only data, no schema	Hand-write one. Start from the smallest valid schema below and add fields.

The smallest valid schema, then build up

The minimum schema is one entity with one field:

{
  "entities": [
    {
      "name": "users",
      "fields": [
        {"name": "id"}
      ]
    }
  ]
}

That is valid. A field does not even require a type — omit it and the declared type normalizes to unknown, which is compatible with anything (so it never triggers a mismatch). But you usually want to declare a type so drift is caught.

Add one object to fields per column, and give each a type:

{
  "entities": [
    {
      "name": "users",
      "fields": [
        {"name": "id",    "type": "integer", "primary_key": true},
        {"name": "email", "type": "string"},
        {"name": "age",   "type": "integer", "nullable": true}
      ]
    }
  ]
}

• "primary_key": true marks the field that identifies each row. A primary key is treated as not nullable unless you say otherwise, so {"name": "id", "type": "integer", "primary_key": true} normalizes to an integer field with nullable: false.
• "nullable": true marks a field that is allowed to be empty (age is a classic example). schemascope reports nulls no matter what; the nullable flag documents your intent so a human reading the schema knows an empty age is expected and an empty email is not. (In this MVP the profiler does not turn nullable: false into a type_ok: false failure by itself; it surfaces null_count/null_fraction so you can decide.)

The full model — validation rules, source, descriptions, metadata — is in Schema Model and Schema Formats.

Accepted schema formats

You can write the same schema in any of four formats. schemascope picks the parser from the file extension (see Format Detection).

Format	Extensions	One-line note
JSON	.json	Curly-brace objects. Good for tooling and generated schemas.
YAML	.yaml, .yml	Indented, less punctuation than JSON. PyYAML is bundled.
XML	.xml	Attribute-based; root element must be <schema>. A default namespace is allowed and ignored.
TXT DSL	.txt, .dsl, .schema	The least fiddly to hand-write — no braces, no quotes. Cannot express schema name/version, entity source, or descriptions.

For quick hand-authoring, the TXT DSL is easiest. This TXT file:

entity users
  id: integer pk
  email: string
  age: integer null

is equivalent to this JSON file:

{
  "entities": [
    {
      "name": "users",
      "fields": [
        {"name": "id",    "type": "integer", "primary_key": true},
        {"name": "email", "type": "string"},
        {"name": "age",   "type": "integer", "nullable": true}
      ]
    }
  ]
}

In the DSL, pk marks the primary key and null marks a nullable field. The full DSL rules are in Schema Formats.

source vs name

Every entity has a name. It may also have a source. The difference:

• name is what the entity is called in your schema and in the report.
• source is the backing store schemascope actually reads: the CSV file stem or the SQLite table name.

If you set no source, schemascope uses the name. So an entity named users reads users.csv (or a SQLite table named users). But if your export produced a file named app_users.csv while you want the entity called users, set a source:

{
  "name": "users",
  "source": "app_users",
  "fields": [ {"name": "id", "type": "integer", "primary_key": true} ]
}

Now the entity is reported as users but the data is read from app_users.csv (or a SQLite table app_users). Note: the TXT DSL cannot express source; use JSON, YAML, or XML if you need it.

Forcing the format

If your schema file has a misleading or missing extension, force the parser with --schema-format:

schemascope schemafile data/ --schema-format yaml

Accepted values are json, yaml, xml, and txt.

Step 1 is done when schemascope <schema> <data> runs without a schema error: and you see your entities and fields listed in the report — even if some are present: false. That means schemascope understood your schema.

Next: if your schema needs to come out of a real database, see Appendix A — Generating a schemascope schema from your database. It gives the exact read-the-structure command for every major engine.

---

Step 2 — Point it at your data

The DATA argument is one of three things.

1. A directory of CSV files — one .csv per entity:

data/
  users.csv
  orders.csv

2. A single SQLite file — one table per entity:

schemascope schema.yaml warehouse.sqlite

3. A live database — any SQLAlchemy URL, one table per entity. This is the main way to use schemascope: point it straight at your database.

schemascope schema.json "postgresql+psycopg://user:pw@host:5432/shop"
schemascope schema.json "mysql+pymysql://user:pw@host:3306/shop"
schemascope schema.json "mssql+pyodbc://user:pw@host/shop?driver=ODBC+Driver+18+for+SQL+Server"
schemascope schema.json "oracle+oracledb://user:pw@host:1521/?service_name=XEPDB1"

Install the driver for your engine first — pip install "schemascope[postgres]" (or [mysql] / [mssql] / [oracle]); SQLite needs none. schemascope recognizes a database source by the :// in the URL. Add --db-schema public (or dbo, …) to target a specific namespace. Per-engine URLs and drivers are in Appendix A.

The filename / entity matching rule

schemascope matches an entity to its backing store by the entity's source-or-name:

• entity users → file users.csv (CSV) or table users (SQLite);
• if the entity has source: app_users, it reads app_users.csv or table app_users instead;
• if the backing file/table is not found, the entity is reported with present: false rather than silently dropped.

Fields are matched to columns by name: exact match first, then a case-insensitive fallback (so Email in the schema matches email in the data); if nothing matches, the field is reported present: false.

Important: for CSV, the DATA argument must be the directory, not a .csv file. Pointing schemascope at data/users.csv fails with a data-source error; point it at data/.

The full read behavior (BOM handling, null cells, duplicate headers, short rows, SQLite native types) is in Data Sources.

---

Concepts and glossary

A few terms are used throughout this manual. Each is one or two sentences.

• Entity — one table. In a CSV data source, one entity maps to one CSV file (users entity → users.csv). In a SQLite data source, one entity maps to one table.
• Field — one column of an entity (for example, email or age).
• Source — the backing store name schemascope actually reads for an entity: the CSV file stem or the SQLite table name. It defaults to the entity's name but can be overridden with a source value (see source vs name).
• Declared type — the type you wrote in your schema for a field (for example, integer). schemascope normalizes it to one of seven canonical types.
• Inferred type — the type schemascope deduces from the actual data values it scans in the CSV/SQLite source. Declared and inferred can differ; comparing them is the point of the tool.
• Null — a missing value. In CSV, an empty cell (or a whitespace-only cell) counts as null. In SQLite, a real NULL counts as null.
• null_fraction — null_count / row_count for a field: the share of rows where that field was null. 0.4 means 40% of rows were null.
• Distinct count — the number of different non-null values seen in a field. A column of 0,0,1,0,0 has a distinct count of 2 (the values 0 and 1).
• Schema drift — when the data no longer matches what the schema expects: a table went missing, a column disappeared, nulls appeared where they should not, or a column's values stopped looking like the declared type. Detecting drift is what schemascope is for.
• Connector — schemascope's internal reader for a data source. There are two: a CSV-directory connector and a SQLite connector. You never construct these by hand from the CLI; the tool picks one for you based on what you point it at.
• Present — whether a thing was actually found. An entity is present: true if its backing file/table exists; a field is present: true if a matching column exists. When something is missing, schemascope keeps it in the report with present: false instead of dropping it silently — that is how drift stays visible.

---

Schema Model

Every schema format is normalized into the same model:

• A schema has optional name and version metadata.
• A schema contains one or more entities.
• Each entity has a name, optional source, optional description, and one or more fields.
• Each field has a name, a canonical type, nullable, primary_key, and optional description.

The profiler reads data from entity.source when it is set; otherwise it uses entity.name. For CSV data, that means <source>.csv. For SQLite data, that means a table named <source>.

Validation rules (violating any of these is a schema error, exit code 2):

• The schema must define at least one entity.
• Each entity must define at least one field.
• Entity names must be unique.
• Field names must be unique within each entity.
• Empty entity names and empty field names are rejected.

A primary key is treated as nullable: false unless nullable is stated explicitly.

---

Schema Formats

These schemas all describe the same common model.

JSON

{
  "entities": [
    {
      "name": "users",
      "fields": [
        {"name": "id", "type": "integer", "primary_key": true},
        {"name": "email", "type": "string"},
        {"name": "age", "type": "integer", "nullable": true}
      ]
    }
  ]
}

YAML

entities:
  - name: users
    fields:
      - {name: id, type: integer, primary_key: true}
      - {name: email, type: string}
      - {name: age, type: integer, nullable: true}

XML

XML is attribute-based. A default XML namespace is allowed and ignored during parsing. The root element must be <schema>.

<schema>
  <entity name="users">
    <field name="id" type="integer" primary_key="true"/>
    <field name="email" type="string"/>
    <field name="age" type="integer" nullable="true"/>
  </entity>
</schema>

TXT DSL

The TXT format is intentionally small:

entity users
  id: integer pk
  email: string
  age: integer null

TXT rules:

• Blank lines and # comments are ignored.
• Entity lines are entity <name> or entity <name>:.
• Field lines are <field>: <type> [flags...].
• Supported flags are pk, primary_key, primary key (mark a primary key); null, nullable (mark nullable); and not null, notnull, required (mark not-nullable).
• unique is accepted in the field text but is currently ignored.
• Indentation is cosmetic.
• Flags are case-insensitive and order-free.

TXT does not represent schema-level name or version, entity source, or descriptions. For strict whole-model equality across JSON, YAML, XML, and TXT, use only the subset of metadata the TXT DSL can express.

Richer JSON/YAML/XML metadata

JSON and YAML support this fuller shape:

name: customer_exports
version: "2026-07"
entities:
  - name: users
    source: app_users
    description: User account export
    fields:
      - name: id
        type: integer
        primary_key: true
        description: Internal user id
      - name: email
        type: varchar
      - name: created_at
        type: timestamp
        nullable: false

XML supports the same metadata as attributes:

<schema name="customer_exports" version="2026-07">
  <entity name="users" source="app_users" description="User account export">
    <field name="id" type="integer" primary_key="true" description="Internal user id"/>
    <field name="email" type="varchar"/>
    <field name="created_at" type="timestamp" nullable="false"/>
  </entity>
</schema>

---

Type Names

Declared type names are normalized before profiling. Matching is case-insensitive, ignores surrounding whitespace, and strips any trailing (size[,scale]) / (max) parameter — so VARCHAR(255), numeric(10, 2), and double precision all resolve. A wide range of vendor/dialect spellings is recognized, so in most cases you can paste your database's own type names verbatim.

Canonical type	Accepted aliases (case-insensitive; (…) parameters stripped)
string	str, string, char, character, nchar, varchar, varchar2, nvarchar, character varying, text, ntext, tinytext/mediumtext/longtext, clob, citext, uuid, guid, uniqueidentifier, enum, set, json, jsonb, xml, hstore, variant, object, array, struct, map, bytea, blob, binary, varbinary, bytes, image, time, interval, year, inet, cidr, geometry/geography, objectid
integer	int, integer, int2/int4/int8, int64, bigint, smallint, tinyint, mediumint, serial/bigserial/smallserial, long, varint, counter
float	float, float4/float8/float64, double, double precision, real, decimal, numeric, number, money, smallmoney, bignumeric, decimal128
boolean	bool, boolean, bit
date	date
datetime	datetime, datetime2, smalldatetime, timestamp, timestamptz, timestamp with/without time zone, datetimeoffset, timestamp_ntz/ltz/tz
unknown	empty, missing, non-string, array notation (int[]), or any spelling not covered above

The seven canonical types are string, integer, float, boolean, date, datetime, and unknown — those are the only type names schemascope reasons about internally. Every alias above simply normalizes to one of them.

Exotic types (json, jsonb, blob, bytea, geometry, array, …) map to string on purpose: exported to CSV/JSONL/SQLite their values arrive as serialized/hex/base64 text and infer as string, so a declared string accepts them. Only a spelling nothing above covers falls through to unknown — not a crash, but you lose the drift check for that one field (a declared unknown is compatible with any inferred type). The full per-database reference is Appendix B.

Rule to remember: a primary key is treated as not nullable unless nullable is explicitly set. For example, {"name": "id", "type": "int", "primary_key": true} normalizes to an integer field with nullable: false.

---

Data Sources

CSV directory

Pass a directory that contains one CSV file per entity:

data/
  users.csv
  orders.csv

If the schema entity is named users, schemascope looks for users.csv. If the entity has source: app_users, it looks for app_users.csv.

CSV behavior:

• The first row is the header.
• Files are read as utf-8-sig, so a UTF-8 BOM (common in Excel/Windows exports) is handled automatically.
• Duplicate header names are rejected (a data-source error).
• Empty cells count as nulls.
• Whitespace-only cells also count as nulls.
• Extra cells beyond the header are ignored.
• Short rows fill missing cells as nulls.

The CLI uses only the default CSV null token: an empty string after stripping. From Python, you can opt into more null spellings:

from schemascope import CsvConnector, load_schema, profile

schema = load_schema("schema.json")
connector = CsvConnector("data", null_tokens={"", "NULL", "NA", "N/A"})
try:
    report = profile(schema, connector)
finally:
    connector.close()

Important: the DATA argument must be the directory, not a .csv file. Pointing schemascope at data/users.csv fails; point it at data/.

SQLite database

Pass a .db, .sqlite, or .sqlite3 file:

schemascope schema.yaml warehouse.sqlite

Each entity maps to a table named by entity.source or entity.name. SQLite values are read with their native Python types where SQLite provides them. A file that is not actually a SQLite database fails cleanly as a data-source error.

SQL database (any SQLAlchemy URL)

Pass a SQLAlchemy database URL as DATA and schemascope profiles the live database directly — this is the primary way to run it:

schemascope schema.json "postgresql+psycopg://user:pw@host:5432/shop"
schemascope schema.json "mysql+pymysql://user:pw@host:3306/shop"
schemascope schema.json "mssql+pyodbc://user:pw@host/shop?driver=ODBC+Driver+18+for+SQL+Server"
schemascope schema.json "oracle+oracledb://user:pw@host:1521/?service_name=XEPDB1"
schemascope schema.json "sqlite:////abs/path/app.db"

Works with any engine SQLAlchemy has a dialect for. The connector is fully generic — it uses only SQLAlchemy reflection plus a dialect-quoted SELECT, so every engine works the same way. Install the driver for yours and pass its URL:

Engine	URL prefix	Install
PostgreSQL (RDS/Aurora/Cloud SQL/Supabase/Neon)	postgresql+psycopg://	pip install "schemascope[postgres]"
MySQL / MariaDB	mysql+pymysql://	pip install "schemascope[mysql]"
SQL Server / Azure SQL / Fabric	mssql+pyodbc://…?driver=ODBC+Driver+18+for+SQL+Server	pip install "schemascope[mssql]"
Oracle	oracle+oracledb://	pip install "schemascope[oracle]"
SQLite	sqlite:///…	built-in
DuckDB	duckdb:///…	pip install "schemascope[duckdb]"
CockroachDB	cockroachdb://	pip install "schemascope[cockroach]"
Amazon Redshift	redshift+redshift_connector://	pip install "schemascope[redshift]"
Snowflake	snowflake://	pip install "schemascope[snowflake]"
Google BigQuery	bigquery://	pip install "schemascope[bigquery]"
Databricks	databricks://	pip install "schemascope[databricks]"
IBM Db2	db2+ibm_db://	pip install "schemascope[db2]"
Trino / Presto	trino://	pip install "schemascope[trino]"
ClickHouse	clickhouse+native://	pip install "schemascope[clickhouse]"
Any other engine	dialect+driver://	that dialect's SQLAlchemy package

Behavior:

• A source is treated as a database when the DATA string contains ://. SQLAlchemy ships with schemascope; only the per-engine driver is separate (SQLite needs none).
• Each entity maps to a table named by entity.source or entity.name, resolved case-insensitively to the database's real table name. Identifiers are quoted through the dialect's own preparer, so reserved-word and spaced names are safe.
• Add --db-schema public (or dbo, a Fabric/Snowflake schema, …) to read from a specific namespace.
• Tables are read, never written, and rows stream, so a large table never loads fully into memory.
• A table (or column) the schema names but the database does not have is reported present: false — schemascope profiles what exists and flags the rest as drift, rather than failing.

Column matching

Fields are matched to source columns by name:

1. Exact column name match.
2. Case-insensitive fallback (so Email in the schema matches email in the data, and vice versa).
3. If no column matches, the field is reported with present: false.

Entity/table/file matching uses the entity source or name. Missing entities are reported with present: false rather than silently dropped.

---

Output Reference

The top-level report is:

{
  "entities": []
}

Each entity report contains:

Field	Meaning
name	Schema entity name
source	CSV file stem or SQLite table name used for this entity
present	Whether the backing CSV file or SQLite table exists
row_count	Number of rows scanned for this entity
fields	Per-field profile objects

Each field report contains:

Field	Meaning
name	Schema field name
declared_type	Canonical schema type after normalization
column	Actual matched source column, or null if absent
present	Whether the column was found
row_count	Number of rows scanned for this field when present
null_count	Number of null values
null_fraction	null_count / row_count, rounded to 6 decimals in serialized output
distinct_count	Count of distinct non-null values
inferred_type	Type inferred from observed non-null values
type_ok	Whether inferred_type is compatible with declared_type

Missing entities and missing columns stay in the report with present: false (and their numeric fields at 0, inferred_type: unknown, type_ok: true). That makes drift visible instead of dropping absent objects from the output.

---

Type Inference

schemascope infers one type per field from observed non-null values.

Inference checks every non-null value for each field, in a single streaming pass. A type is chosen only when every value matches that type, so a column that drifts to a non-conforming value anywhere in the file — not just in the first few rows — is reported as a mismatch. If no specific type matches, the inferred type is string. If there are no non-null values at all, the inferred type is unknown.

Inference order (most specific first):

1. boolean
2. integer
3. float
4. date
5. datetime
6. string fallback

Recognized values:

• Boolean: real booleans, or true, false, 1, 0, yes, no, t, f, y, n case-insensitively.
• Integer: real integers or ASCII integer strings such as 1, 0, -12, +42. Real booleans are not integers. Values with a decimal point (3.0) are not integers.
• Float: real integers/floats or strings that parse as finite floats. nan, inf, and infinity are rejected.
• Date: strict YYYY-MM-DD calendar dates.
• Datetime: YYYY-MM-DD followed by a space or T and an HH:MM or HH:MM:SS time. Fractional seconds and a trailing Z are accepted.

Watch out: because inference is strict, values that look like a type but do not match the exact format fall through to string. A timestamp with a numeric zone offset such as 2021-03-05 10:00:00+00 does not match the datetime pattern (only a trailing Z is stripped), so a column of such values infers as string. A time-of-day like 10:30:00 is not a recognized type and also infers as string. If you know a column will hold these, either declare it string, or reformat on export (see Appendix A).

Compatibility is intentionally lenient. type_ok is true when:

• Declared and inferred types are equal.
• Declared string accepts any inferred type.
• Declared float accepts inferred integer.
• Declared integer accepts inferred boolean (an all-0/1 column often infers as boolean but is still valid integer data — this is the deleted field in the walkthrough).
• unknown on either side is treated as compatible.

Everything else is a type mismatch (type_ok: false). Note the asymmetry: a declared boolean whose data infers integer (values outside 0/1) is flagged.

---

Python API

The main API is available from the top-level package:

import schemascope

schema = schemascope.load_schema("examples/schema.json")
connector = schemascope.open_connector("examples/data")

try:
    report = schemascope.profile(schema, connector)
finally:
    connector.close()

for entity in report.entities:
    print(entity.name, entity.present, entity.row_count)
    for field in entity.fields:
        print(
            field.name,
            field.present,
            field.inferred_type,
            field.null_fraction,
            field.type_ok,
        )

print(report.to_dict())
print(schemascope.__version__)

Common imports:

from schemascope import (
    CsvConnector,
    SqliteConnector,
    load_schema,
    open_connector,
    profile,
)

open_connector(path) chooses a connector automatically:

• Directory → CsvConnector
• .db, .sqlite, .sqlite3 file → SqliteConnector

The caller owns connector lifecycle — close connectors when finished, ideally in a try/finally.

Also exported from the top-level package: Schema, Entity, Field, SchemaError, ConnectorError, normalize_type, infer_type, type_compatible, detect_format, store_name, __version__, and CANONICAL_TYPES.

---

Format Detection

Known file extensions are authoritative:

Extension	Format
.json	JSON
.yaml, .yml	YAML
.xml	XML
.txt, .dsl, .schema	TXT DSL

For unknown extensions, content is sniffed:

• Leading < → XML
• Leading { or [ → JSON
• YAML mapping with an entities key → YAML
• Anything else → TXT DSL
• An empty file is an error.

Use --schema-format when a file extension is misleading or absent:

schemascope schemafile data/ --schema-format yaml

---

Troubleshooting

Almost every failure exits with code 2 and prints a one-line message to stderr. The report itself (when the run succeeds) goes to stdout, so you can redirect them separately. Here are the real failure modes and their fixes.

Symptom (stderr message)	What it means	Fix
data source error: cannot determine a connector for ... (expected a CSV directory or a .db/.sqlite/.sqlite3 file)	You pointed DATA at something that is neither a directory nor a SQLite file — most commonly a single .csv file.	Point at the directory that contains your CSVs (data/, not data/users.csv), or rename/verify your SQLite file ends in .db/.sqlite/.sqlite3.
data source error: CSV source is not a directory: ...	(Python API only, via CsvConnector) you passed a file path where a directory was expected.	Pass the CSV directory.
data source error: CSV file for 'users' has a duplicate column: 'id'	A CSV header lists the same column name twice. schemascope refuses to guess which one you meant.	De-duplicate the header row in that CSV; each column name must be unique.
data source error: cannot open SQLite database ...: file is not a database	The .db/.sqlite file you passed is not actually a SQLite database (wrong file, corrupt, or a text file renamed).	Rebuild the SQLite file, or point at the correct one. Verify with sqlite3 file.db ".tables".
data source error: SQLite database not found: ...	The SQLite path does not exist.	Check the path.
schema error: schema is missing the 'entities' key	Your JSON/YAML parsed fine but has no top-level entities.	Add an entities: list; every schema needs at least one entity.
schema error: schema defines no entities	The entities list is present but empty.	Add at least one entity with at least one field.
schema error: <path>: empty schema file	The schema file is empty (for a file whose format had to be sniffed).	Put a real schema in the file. Note: an empty .json file instead reports invalid JSON schema: Expecting value... because the .json extension forces the JSON parser.
schema error: invalid JSON schema: ... / invalid YAML schema: ... / invalid XML schema: ...	The file is malformed for its format.	Fix the syntax. If the format was auto-detected wrongly, force it with --schema-format.
schema error: duplicate entity name: 'users'	Two entities share a name.	Rename one; entity names must be unique.
schema error: entity 'users': duplicate field name: 'id'	Two fields in one entity share a name.	Rename one; field names must be unique within an entity.
Wrong format auto-detected (e.g. a DSL file read as YAML)	The file has no recognized extension and the content sniffer guessed wrong.	Pass `--schema-format json
schemascope: command not found	The console script is not on your PATH.	Run it as python -m schemascope ..., or reinstall so the script lands on PATH.

Reading the report itself (not errors)

These are not crashes — they are the profile telling you something.

• "present": false on an entity — the backing CSV file or SQLite table was not found. Check the file name matches <source>.csv (or the table name), and that the file is in the directory you pointed at. This is drift, not an error; exit code is still 0.
• "present": false on a field — no column matched that field name (after the case-insensitive fallback). The column may have been renamed or dropped in the export. Also drift, not an error.
• A field's declared_type is unknown — the type you wrote matched no recognized alias. Most vendor spellings are recognized (jsonb, timestamptz, serial, int4, varchar(255), …); the usual culprits are array notation (int[]), a bespoke domain type, or a typo. It will not fail (an unknown declared type is compatible with anything), but you lose the drift check. Replace it with a canonical name (string, integer, datetime, ...); see Appendix B.
• "type_ok": false — the type inferred from the data is not compatible with the declared type. Example: you declared age as integer but a row contains "unknown", so the whole column infers as string, and integer does not accept string. Either fix the data, or reconsider the declared type. This is the core drift signal.

---

Limitations

• This is not a full data validation engine. It profiles presence, nulls, distinct counts, inferred types, and type compatibility. It does not flag a nullable: false field just because nulls appear — it reports the counts and leaves the judgment to you.
• It does not enforce foreign keys, uniqueness, ranges, regexes, or custom constraints.
• Type inference scans every non-null value in one pass (O(1) memory per field), so drift anywhere in the file is caught — at the cost of running the type predicates over the full column rather than a sample.
• distinct_count tracks all distinct non-null values for each profiled field, which is simple and exact but not approximate-memory analytics.
• TXT schemas do not support metadata such as schema name, version, source, or descriptions.
• The CLI exposes the default CSV null handling only. Use the Python API for custom CSV null tokens.
• To read a live database you install the driver for that engine (psycopg/psycopg2, PyMySQL/mysqlclient, pyodbc, oracledb, …); SQLAlchemy itself ships with schemascope. schemascope only ever reads — it never writes to your database.

---

Requirements

• Python 3.8 or newer (the floor declared in pyproject.toml).
• PyYAML and SQLAlchemy — installed automatically with the package.
• A database driver — only if you profile a live database, one per engine. schemascope is generic across any SQLAlchemy dialect (PostgreSQL, MySQL/MariaDB, SQL Server/Azure/Fabric, Oracle, CockroachDB, Redshift, Snowflake, BigQuery, Databricks, Db2, Trino, ClickHouse, DuckDB, …); SQLite needs none. Install the matching extra, e.g. pip install "schemascope[postgres]" — the full list is in Data Sources → SQL database.

---

Development

Run tests:

python3 -m pytest -q

Build local artifacts:

python3 -m build --sdist --wheel --outdir dist

The source distribution includes the examples used in this README.

---

License

schemascope is released under the MIT License (see pyproject.toml).

---

Next: see Appendix A — Generating a schemascope schema from your database for copy-paste recipes per engine — the driver + SQLAlchemy URL to profile your database live, how to read its structure into a schema file, and (when you can't connect) how to export it to CSV/SQLite.

---

Appendix A: Generating a schemascope schema from your database

To profile a database you give schemascope two things — a schema (what the data should look like) and the data itself — and there are two ways to supply the data:

1. Get the schema. Read the real table structure — from the platform's DDL, its information_schema catalog, or an introspection command below — and translate each column type into one of schemascope's seven canonical types (string, integer, float, boolean, date, datetime, unknown). Write that as a small JSON/YAML/XML/TXT schema. (schemascope reads a schema; it does not invent one, so this step is yours.)

2. Point schemascope at the data — two options:

   • Connect live (recommended): install the driver for your engine and pass its SQLAlchemy URL as DATA; schemascope reads the tables directly. Each engine section gives the exact pip install and URL.
   • Or export: if schemascope can't reach the database from where it runs, export each table to a <table>.csv (header row, all in one directory) or into a single SQLite file, and point it at that.

Either way both halves are required — a schema with no data, or data with no schema, leaves you stuck.

Which situation are you in?

Your situation	What to do
Your data is in a SQL database (PostgreSQL, MySQL/MariaDB, SQL Server, Oracle, Db2, CockroachDB, BigQuery, Snowflake, Redshift, Databricks)	Read its catalog/DDL into a schema (Step 1), then connect schemascope live with its SQLAlchemy URL — or export to CSV/SQLite if you can't connect (Step 2). Find your engine's numbered section below.
Your data is in a NoSQL / document store (MongoDB, DynamoDB, Elasticsearch, Cassandra)	Same two steps, but first sample documents/items to discover fields and their real types — these stores are schemaless. See your engine's section.
Your data is already in a SQLite file	You are done with the export half — schemascope opens .db/.sqlite/.sqlite3 directly. Just read its .schema and write a schemascope schema. See A5. SQLite.
Your data is already in flat files (CSV, Parquet, JSON)	Read the types from the file itself, and if it is not CSV yet, convert it. See A17. Schema from flat files.

You can connect directly. For a SQL database, passing its SQLAlchemy URL as DATA (with the engine's driver installed) is the primary path — schemascope reads the tables live. The export-to-CSV/SQLite recipes are the fallback for when schemascope can't reach the database (air-gapped, VPN-only, or a dump someone emailed you). The introspection commands are useful either way, to build the schema file.

How schemascope uses what you give it

• SCHEMA is a file you write — JSON, YAML, XML, or TXT — listing your entities, fields, and their canonical types. schemascope reads it; it does not generate it for you.
• DATA is a SQLAlchemy database URL (schemascope connects and reads live), a directory of CSV files, or a single SQLite file.
• A URL carries the host, port, database, and credentials for the live connection. The export commands below are an alternative that produce CSV/SQLite instead.

Before you start — placeholders

The commands below use placeholders. Replace them with your real values:

Placeholder	Replace with
HOST / PORT	Your database server's hostname and port — needed only by the export/introspection client, never by schemascope itself.
DBNAME / mydb	The database (or schema) you are reading.
USER / PASSWORD	Credentials for that database.
users, orders, TABLE	The table (or collection) you are profiling. Each becomes one entity and one <table>.csv.
data/	A local directory you create to hold the exported CSVs (one file per table).

Rule for every export: the CSV must have a header row (schemascope reads the first row as the column names). Several tools omit the header by default — where a recipe notes this, add the header line yourself.

A universal starting point: information_schema.columns

Most SQL engines implement the ANSI information_schema. This query lists a table's columns and types and works, with minor variation, on PostgreSQL, MySQL/MariaDB, SQL Server, Snowflake, Redshift, BigQuery, CockroachDB, Databricks, and others:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

Take each data_type it returns and map it to a canonical schemascope type using the master table below (and the per-platform notes that follow).

Master type-mapping table

Write the canonical schemascope type in the middle column into your schema. The left column groups the database types you are likely to see.

Database column type (any platform)	Write this schemascope type	Notes
char, varchar, nvarchar, text, clob, string, character varying	string	Plain text.
uuid, guid, uniqueidentifier	string	uuid is a recognized alias, but writing string is clearer. Exported as text it infers string.
enum, set	string	enum is a recognized alias; values export as text.
json, jsonb, xml, hstore, variant, object, array, geometry, geography	string	Recognized — all map to string. Exported as serialized text they also infer string, so type_ok holds. (Array notation like int[] is not covered → unknown.)
bytea, blob, binary, varbinary, bytes, image	string	Binary. Exports as hex/base64 text → infers string. (Consider excluding huge binary columns from the export.)
smallint, int, integer, bigint, int2, int4, int8, serial, bigserial, tinyint, mediumint, long	integer	All recognized, including int2/int4/int8, serial/bigserial, tinyint/mediumint. (Oracle NUMBER(p,0) resolves via number → float, not integer — the scale is stripped with the parameter, and float safely accepts integer data.)
decimal, numeric, float, double, double precision, real, money, number(p,s)	float	money/number may export with currency symbols or thousands separators; if so it infers string — strip formatting on export or declare string.
boolean, bool, bit	boolean	A single-bit or tinyint(1) flag column of 0/1 infers boolean; declaring integer also passes (integer accepts boolean).
date	date	Must export as YYYY-MM-DD.
timestamp, datetime, datetime2, smalldatetime, timestamptz, timestamp with time zone	datetime	All recognized as datetime (a (precision) parameter and with/without time zone wording are handled). Export as YYYY-MM-DD HH:MM:SS. A trailing numeric zone offset (+00) makes the data infer string; strip the zone or store UTC without offset.
time, time with time zone, interval, year	string	Recognized — all map to string (they export as text and infer string).

Rule of thumb: if you are unsure, declare string. A declared string accepts any inferred type, so it never produces a false type_ok: false. Use the more specific types when you actually want drift detection on that column.

---

A1. PostgreSQL

Read the structure. In psql, \d users prints the column list and types. For a machine-readable version, use the catalog query:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users' AND table_schema = 'public'
ORDER BY ordinal_position;

Or dump DDL only (no data) for all tables:

pg_dump --schema-only --no-owner mydb > schema.sql

Map the types. text/varchar/char → string; uuid → string; smallint/integer/bigint/serial/bigserial/int2/int4/int8 → integer; numeric/decimal/real/double precision/money → float; boolean → boolean; date → date; timestamp/timestamptz → datetime (export without a +00 offset — see note above); json/jsonb/bytea/ARRAY/interval → string.

Export the data (CSV or SQLite). Use \copy for CSV (it runs client-side, no server file permissions needed), one file per table:

psql -d mydb -c "\copy (SELECT * FROM users)  TO 'data/users.csv'  WITH (FORMAT csv, HEADER)"
psql -d mydb -c "\copy (SELECT * FROM orders) TO 'data/orders.csv' WITH (FORMAT csv, HEADER)"

Then run schemascope schema.json data/.

Alternatively, export into SQLite in one command with pgloader:

pgloader postgresql://user@localhost/mydb sqlite://./warehouse.sqlite
# then: schemascope schema.json warehouse.sqlite

---

A2. MySQL / MariaDB

Read the structure. SHOW CREATE TABLE users; prints the full DDL. Or use the catalog:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'mydb' AND table_name = 'users'
ORDER BY ordinal_position;

Schema-only dump of all tables:

mysqldump --no-data mydb > schema.sql

Map the types. char/varchar/text → string; tinyint/smallint/mediumint/int/bigint → integer; tinyint(1) is MySQL's boolean and infers boolean (declare boolean or integer); decimal/float/double → float; date → date; datetime/timestamp → datetime; time/year → string; json/blob/enum → string (enum is a recognized alias, but the data exports as text either way).

Export the data (CSV or SQLite). The most portable way is the batch client, which emits tab-separated output; convert tabs to commas:

mysql --batch --raw -e "SELECT * FROM users" mydb \
  | sed 's/\t/,/g' > data/users.csv

--batch gives one header row plus tab-separated rows; the sed turns tabs into commas. (This is fine when your text fields contain no commas or tabs; for messy text with embedded delimiters, prefer a GUI export such as MySQL Workbench's "Export a Result Set" wizard, which writes a proper quoted CSV.)

The server-side SELECT ... INTO OUTFILE writes a file on the database server and is restricted by the secure_file_priv setting:

-- Check where the server is allowed to write:
SHOW VARIABLES LIKE 'secure_file_priv';

SELECT * FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

INTO OUTFILE does not write a header row, and the file lands on the server — so --batch above is usually easier for schemascope. If you use INTO OUTFILE, add the header line yourself.

---

A3. Microsoft SQL Server / Azure SQL

Read the structure. EXEC sp_help 'dbo.users'; or the catalog:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

Map the types. char/varchar/nvarchar/text/ntext → string; uniqueidentifier → string; tinyint/smallint/int/bigint → integer; decimal/numeric/float/real/money/smallmoney → float; bit → boolean; date → date; datetime/datetime2/smalldatetime/datetimeoffset → datetime (all four are recognized); time → string; varbinary/image/xml → string.

Export the data (CSV or SQLite). Use sqlcmd (which can emit a header) — bcp is faster but does not easily produce headers:

sqlcmd -S server -d mydb -Q "SET NOCOUNT ON; SELECT * FROM dbo.users" \
  -s "," -W -o data/users.csv

-s "," sets the comma separator and -W trims trailing spaces. You will get a dashed separator line under the header that you should delete. For large tables, bcp is the workhorse (no header, so add one):

bcp "SELECT * FROM mydb.dbo.users" queryout data/users.csv -c -t, -S server -T

The SSMS Import and Export Wizard (right-click the database → Tasks → Export Data) is the GUI route and lets you write a flat file with column headers.

---

A4. Oracle Database

Read the structure. Query the data dictionary:

SELECT column_name, data_type, nullable
FROM user_tab_columns          -- or all_tab_columns for another schema
WHERE table_name = 'USERS'
ORDER BY column_id;

Full DDL for a table:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'USERS') FROM dual;

Map the types. VARCHAR2/CHAR/NVARCHAR2/CLOB → string; NUMBER(p,0) → integer, NUMBER(p,s)/FLOAT/BINARY_FLOAT/BINARY_DOUBLE → float; TIMESTAMP → datetime; RAW/BLOB → string. Note that Oracle's DATE actually carries a time component, so it commonly exports as a full timestamp — declare it datetime (or date if you export just the date part). There is no native boolean in table columns; a 0/1 NUMBER(1) flag infers boolean.

Export the data (CSV or SQLite). With SQLcl (the modern command-line client), which has a built-in CSV format:

-- in sqlcl, connected to your DB:
SET SQLFORMAT csv
SPOOL data/users.csv
SELECT * FROM users;
SPOOL OFF

Or with SQL*Plus 12.2+, which added CSV markup:

SET MARKUP CSV ON
SET HEADING ON
SET FEEDBACK OFF
SPOOL data/users.csv
SELECT * FROM users;
SPOOL OFF

Both write a header row by default. (Older SQL*Plus without MARKUP CSV requires concatenating columns by hand — prefer SQLcl.)

---

A5. SQLite

SQLite is the easy case: schemascope opens a .db/.sqlite/.sqlite3 file directly, so you do not need to export data at all.

Read the structure. In the sqlite3 shell:

sqlite3 warehouse.sqlite ".schema users"

Map the types. SQLite uses type affinities: INTEGER → integer; REAL/FLOAT/DOUBLE → float; TEXT/VARCHAR/CHAR → string; NUMERIC/DECIMAL → float; BLOB → string; DATE/DATETIME are stored as text or numbers, so declare date/datetime and confirm the stored format is YYYY-MM-DD(T/space time). SQLite has no dedicated boolean; 0/1 columns infer boolean.

Run it directly:

schemascope schema.json warehouse.sqlite

If you still want CSVs (for example, to hand to a different tool), the shell can produce them:

sqlite3 warehouse.sqlite <<'EOF'
.headers on
.mode csv
.output data/users.csv
SELECT * FROM users;
.output data/orders.csv
SELECT * FROM orders;
EOF

---

A6. IBM Db2

Read the structure. Query the catalog:

SELECT colname, typename, nulls
FROM syscat.columns
WHERE tabname = 'USERS'
ORDER BY colno;

Or capture DDL with the db2look tool:

db2look -d MYDB -e -t USERS > schema.sql

Map the types. CHAR/VARCHAR/CLOB/GRAPHIC → string; SMALLINT/INTEGER/BIGINT → integer; DECIMAL/DECFLOAT/REAL/DOUBLE → float; BOOLEAN → boolean; DATE → date; TIMESTAMP → datetime; TIME → string; BLOB/XML → string.

Export the data (CSV or SQLite). Use the EXPORT command (delimited format):

db2 "EXPORT TO data/users.csv OF DEL MODIFIED BY NOCHARDEL SELECT * FROM users"

OF DEL produces comma-delimited output. Db2 EXPORT does not write a header row, so add one yourself (for example, sed -i '1i id,email,age,active,deleted,signup_date' data/users.csv with your real column names), or list columns explicitly in the SELECT so you know the order.

---

A7. CockroachDB

CockroachDB speaks the PostgreSQL wire protocol, so its introspection is Postgres-compatible.

Read the structure. SHOW CREATE TABLE users; prints the DDL, or use information_schema.columns as in the universal query. Type mapping is the same as A1. PostgreSQL.

Map the types. Same as A1. PostgreSQL.

Export the data (CSV or SQLite). The simplest client-side route is COPY ... TO STDOUT via the cockroach sql shell, redirected to a file:

cockroach sql --url "$CONN" \
  -e "COPY (SELECT * FROM users) TO STDOUT WITH CSV HEADER" > data/users.csv

For large tables, EXPORT writes CSV files to cloud/nodelocal storage in parallel:

EXPORT INTO CSV 'nodelocal://1/users' WITH nullas = '' FROM TABLE users;

(Then collect the files from the storage location. For schemascope, the COPY ... TO STDOUT form is usually simplest because it gives you one local CSV with a header.)

---

A8. Google BigQuery

Read the structure. Print a table's schema with the bq CLI:

bq show --schema --format=prettyjson mydataset.users

Or query the catalog:

SELECT column_name, data_type, is_nullable
FROM `myproject.mydataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'users';

Map the types. STRING → string; INT64/INTEGER → integer; NUMERIC/BIGNUMERIC/FLOAT64 → float; BOOL → boolean; DATE → date; DATETIME/TIMESTAMP → datetime (export in YYYY-MM-DD HH:MM:SS form); TIME → string; BYTES/JSON/GEOGRAPHY/ARRAY/STRUCT → string.

Export the data (CSV or SQLite). EXPORT DATA writes CSV directly to Cloud Storage with a header:

EXPORT DATA OPTIONS(
  uri = 'gs://my-bucket/users-*.csv',
  format = 'CSV',
  overwrite = true,
  header = true
) AS SELECT * FROM mydataset.users;

Or the bq extract CLI (header on by default via --print_header):

bq extract --destination_format=CSV --print_header=true \
  mydataset.users gs://my-bucket/users.csv
gsutil cp gs://my-bucket/users.csv data/users.csv

For small results you can skip GCS entirely:

bq query --use_legacy_sql=false --format=csv \
  'SELECT * FROM mydataset.users' > data/users.csv

---

A9. Snowflake

Read the structure. DESCRIBE TABLE users; lists columns and types, or:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'USERS';

Map the types. VARCHAR/STRING/TEXT/CHAR → string; NUMBER(p,0)/INT/INTEGER/BIGINT → integer, NUMBER(p,s)/FLOAT/DOUBLE/REAL → float; BOOLEAN → boolean; DATE → date; DATETIME/TIMESTAMP_NTZ/TIMESTAMP_LTZ/TIMESTAMP_TZ → datetime (strip the zone offset if present); TIME → string; VARIANT/OBJECT/ARRAY/BINARY/GEOGRAPHY → string.

Export the data (CSV or SQLite). The simplest CLI route uses snowsql output options:

snowsql -o output_format=csv -o header=true -o friendly=false -o timing=false \
  -q "SELECT * FROM users" -o output_file=data/users.csv

The scalable route unloads to a stage with COPY INTO, then downloads with GET:

COPY INTO @~/users
FROM users
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' COMPRESSION = NONE)
HEADER = TRUE
OVERWRITE = TRUE
SINGLE = TRUE;

snowsql -q "GET @~/users file://./data/"

(HEADER = TRUE writes the column names as the first row.)

---

A10. Amazon Redshift

Read the structure. Redshift exposes SVV_COLUMNS and the legacy PG_TABLE_DEF (remember to set search_path):

SELECT column_name, data_type, is_nullable
FROM svv_columns
WHERE table_name = 'users';

Map the types. CHAR/VARCHAR/TEXT → string; SMALLINT/INT2/INTEGER/INT4/BIGINT/INT8 → integer; DECIMAL/NUMERIC/REAL/FLOAT4/DOUBLE PRECISION/FLOAT8 → float; BOOLEAN → boolean; DATE → date; TIMESTAMP/TIMESTAMPTZ → datetime (strip the zone); TIME/TIMETZ/SUPER/VARBYTE → string.

Export the data (CSV or SQLite). Use UNLOAD (writes to S3, with a header):

UNLOAD ('SELECT * FROM users')
TO 's3://my-bucket/users/'
IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole'
FORMAT AS CSV
HEADER
PARALLEL OFF
ALLOWOVERWRITE;

PARALLEL OFF makes a single file; HEADER adds the column names. Then copy the object down from S3 and rename it to data/users.csv:

aws s3 cp s3://my-bucket/users/000 data/users.csv

---

A11. Databricks / Spark SQL

Read the structure. DESCRIBE TABLE users; (or DESCRIBE TABLE EXTENDED users) lists columns and types; information_schema.columns is available in Unity Catalog.

Map the types. STRING → string; TINYINT/SMALLINT/INT/BIGINT → integer; FLOAT/DOUBLE/DECIMAL → float; BOOLEAN → boolean; DATE → date; TIMESTAMP/TIMESTAMP_NTZ → datetime; BINARY/ARRAY/MAP/STRUCT → string.

Export the data (CSV or SQLite). Use the DataFrame writer (write a single file with a header):

(spark.table("users")
      .coalesce(1)                       # one output file
      .write.option("header", "true")
      .mode("overwrite")
      .csv("/tmp/users_csv"))

Spark writes a directory of part files; grab the single part-*.csv inside and rename it to data/users.csv. (On Databricks you can then dbutils.fs.cp it to where you need it, or download via the workspace.)

---

A12. MongoDB

MongoDB is schemaless — documents in a collection need not share the same fields or types. So you decide which fields to profile, then sample the data to learn their real types.

Read the structure (discover fields and types). In mongosh, sample documents:

db.orders.aggregate([{ $sample: { size: 100 } }])

MongoDB Compass has a built-in Schema tab that analyzes a collection and reports each field's observed types and how often they appear. The community variety.js script does the same from the shell. Use whichever to pick your fields and their dominant types.

Map the types. BSON String → string; Int32/Int64/Long → integer; Double/Decimal128 → float; Boolean → boolean; Date → datetime (Mongo dates carry a time; mongoexport writes ISO-8601 like 2021-03-05T10:00:00.000Z — the trailing Z is fine for schemascope's datetime inference); ObjectId/UUID → string; embedded documents/arrays → string.

Export the data (CSV or SQLite). Use mongoexport, which requires you to list the fields for CSV:

mongoexport --uri "mongodb://localhost:27017" --db mydb --collection orders \
  --type=csv --fields "orderId,customerId,status,total,createdAt" \
  --out data/orders.csv

By default the listed field names become the header row (use --noHeaderLine to omit it — but schemascope needs the header, so keep it). Nested fields use dot notation, e.g. --fields "orderId,customer.name".

---

A13. Cassandra / ScyllaDB

Read the structure. In cqlsh, DESCRIBE TABLE users; prints the DDL, or query the catalog:

SELECT column_name, type FROM system_schema.columns
WHERE keyspace_name = 'myks' AND table_name = 'users';

Map the types. text/varchar/ascii → string; tinyint/smallint/int/bigint/varint/counter → integer; decimal/float/double → float; boolean → boolean; date → date; timestamp → datetime; time → string; uuid/timeuuid/inet → string; blob/list/set/map → string.

Export the data (CSV or SQLite). Use cqlsh COPY ... TO, which supports a header:

COPY myks.users TO 'data/users.csv' WITH HEADER = TRUE;

You can restrict/order columns: COPY myks.users (id, email, age) TO 'data/users.csv' WITH HEADER = TRUE;. (COPY is fine for moderate tables; for very large ones use a bulk unloader such as DSBulk.)

---

A14. Amazon DynamoDB

DynamoDB is schemaless apart from its key schema. describe-table tells you only the partition/sort keys and their types — not the other attributes — so you must sample items to learn the rest.

Read the structure (key schema and sample attributes):

aws dynamodb describe-table --table-name Orders \
  --query "Table.{Keys:KeySchema, Attrs:AttributeDefinitions}"

# sample some items to see the other attributes:
aws dynamodb scan --table-name Orders --max-items 25

Map the types. DynamoDB attribute types: S (string) → string; N (number) → integer or float depending on the values; BOOL → boolean; B (binary) → string; M/L (map/list) → string; SS/NS/BS (sets) → string. Because attributes are per-item, pick the fields you care about and declare them from what the sample shows.

Export the data (CSV or SQLite). The native export to S3 (point-in-time) writes DynamoDB JSON / Ion / Parquet — not CSV — so it is not directly usable by schemascope without a conversion step:

aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:us-east-1:123456789012:table/Orders \
  --s3-bucket my-bucket --s3-prefix orders/ --export-format DYNAMODB_JSON

For a small/medium table, the pragmatic route to CSV is to scan and flatten with jq:

# header row:
echo "orderId,customerId,status,total" > data/orders.csv
# rows (adjust the attribute names to your table):
aws dynamodb scan --table-name Orders --output json \
  | jq -r '.Items[] | [.orderId.S, .customerId.S, .status.S, .total.N] | @csv' \
  >> data/orders.csv

(For large tables use AWS Glue or a proper export-then-transform pipeline; the scan + jq approach is best for modest volumes.)

---

A15. Elasticsearch

Elasticsearch is document-oriented; each index has a mapping that plays the role of a schema.

Read the structure (the mapping):

curl -s "http://localhost:9200/orders/_mapping?pretty"

Map the types. text/keyword → string; integer/long/short/byte → integer; float/double/half_float/scaled_float → float; boolean → boolean; date → datetime (Elasticsearch dates are usually full timestamps); ip/geo_point/object/nested → string.

Export the data (CSV or SQLite). Use the community elasticdump tool (or a Logstash csv output):

elasticdump --input=http://localhost:9200/orders \
  --output=data/orders.csv --type=data --csvConfigs='{"headers":true}'

(Keep this one simple: elasticdump and Logstash both flatten documents to CSV; you choose which fields become columns.)

---

A16. Schema from code and tooling

Sometimes the truest schema lives in your application, not the database. You can read the field types there and translate them the same way. You still export the data to CSV/SQLite as above — these only give you the schema half.

• Django — python manage.py inspectdb > models.py reverse-engineers models from an existing database; or read your existing model fields. CharField/TextField/UUIDField/SlugField/EmailField → string; IntegerField/BigIntegerField/SmallIntegerField/AutoField → integer; FloatField/DecimalField → float; BooleanField → boolean; DateField → date; DateTimeField → datetime; JSONField/BinaryField → string.
• SQLAlchemy — reflect an existing table (Table('users', metadata, autoload_with=engine)) or read your models. String/Text/Unicode → string; Integer/BigInteger/SmallInteger → integer; Float/Numeric → float; Boolean → boolean; Date → date; DateTime → datetime; JSON/LargeBinary → string.
• Ruby on Rails — db/schema.rb lists every column. t.string/t.text → string; t.integer/t.bigint → integer; t.float/t.decimal → float; t.boolean → boolean; t.date → date; t.datetime/t.timestamp → datetime; t.json/t.jsonb/t.binary → string.
• Prisma — schema.prisma model fields. String → string; Int/BigInt → integer; Float/Decimal → float; Boolean → boolean; DateTime → datetime; Json/Bytes → string. (Prisma has no bare date type; a date-only column is still DateTime.)
• dbt — column types live in each model's schema.yml (and, if you run dbt docs generate, in target/catalog.json, which carries the warehouse's real types). Map those warehouse types with the platform tables above.

---

A17. Schema from flat files

If your data is already in files, you can read both the types and get CSV in one place.

• CSV — csvkit's csvstat data/users.csv reports each column's inferred type, null count, and distinct count (a nice cross-check against schemascope). Its guesses map cleanly: Number → integer/float, Boolean → boolean, Date → date, DateTime → datetime, Text → string. Or in pandas, pandas.read_csv('users.csv').dtypes: int64 → integer, float64 → float, bool → boolean, datetime64 → datetime, object → string.
• Parquet / Arrow — pyarrow.parquet.read_schema('users.parquet') prints the column types. string/large_string → string; int8/16/32/64 → integer; float/double/decimal → float; bool → boolean; date32/date64 → date; timestamp → datetime; binary/list/struct → string. Then convert to CSV: pyarrow.csv.write_csv(pyarrow.parquet.read_table('users.parquet'), 'data/users.csv') (it writes a header row).
• JSON — inspect the object keys to choose fields, and map each value's JSON type: string → string; whole-number → integer; fractional number → float; true/false → boolean; date-looking strings → date/datetime if they match the strict formats, else string. Flatten to CSV with jq -r (see the DynamoDB example) or pandas json_normalize.

---

A18. Worked end-to-end example: from a PostgreSQL users table to a schemascope report

Suppose you have this table in PostgreSQL:

CREATE TABLE users (
    id          bigserial PRIMARY KEY,
    email       varchar(255) NOT NULL,
    age         integer,
    active      boolean NOT NULL,
    deleted     integer NOT NULL DEFAULT 0,
    signup_date date NOT NULL
);

Step 1 — read the structure. In psql:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users' AND table_schema = 'public'
ORDER BY ordinal_position;

which returns:

 column_name | data_type         | is_nullable
-------------+-------------------+-------------
 id          | bigint            | NO
 email       | character varying | NO
 age         | integer           | YES
 active      | boolean           | NO
 deleted     | integer           | NO
 signup_date | date              | NO

Step 2 — translate to canonical types and hand-write the schema. Using the master table: bigint → integer, character varying → string, integer → integer, boolean → boolean, date → date. The primary key becomes primary_key: true (not nullable); age is nullable. Save this as schema.json:

{
  "name": "customer_exports",
  "version": "2026-07",
  "entities": [
    {
      "name": "users",
      "fields": [
        {"name": "id", "type": "integer", "primary_key": true},
        {"name": "email", "type": "string"},
        {"name": "age", "type": "integer", "nullable": true},
        {"name": "active", "type": "boolean"},
        {"name": "deleted", "type": "integer", "nullable": false},
        {"name": "signup_date", "type": "date"}
      ]
    }
  ]
}

(This is exactly the shape of the bundled examples/schema.json.)

Step 3 — export the data. One CSV, header row, into a data/ directory:

mkdir -p data
psql -d mydb -c "\copy (SELECT * FROM users) TO 'data/users.csv' WITH (FORMAT csv, HEADER)"

Your data/users.csv now looks like:

id,email,age,active,deleted,signup_date
1,[e-mail],31,t,0,2021-03-05
...

Note: Postgres exports booleans as t/f, which schemascope recognizes as boolean tokens — so active still infers boolean.

Alternative Step 3 — export into SQLite instead. If you would rather bridge through SQLite, load the same rows into a file (for example with pgloader postgresql://user@localhost/mydb sqlite://./warehouse.sqlite, or by piping the CSV into sqlite3), and point schemascope at warehouse.sqlite.

Step 4 — run schemascope:

schemascope schema.json data

Step 5 — read the result. You get the same report structure as the walkthrough: users is present: true, each field is present: true, age shows whatever null_fraction your real data has, deleted infers boolean but type_ok stays true (integer accepts boolean), and every other field's type_ok is true if the data matches. Any present: false or type_ok: false in that output is drift worth investigating.

How do I check my schema file worked?

schemascope has no separate "inspect" or "validate" command — running the tool is the check. Point it at your schema and your exported data:

schemascope schema.json data/

• If you get a schema error: ... on stderr, the schema file itself is wrong — fix it (see Troubleshooting).
• If it runs and every entity and field you declared appears in the report, schemascope understood your schema. present: true on an entity means its CSV/table was found; present: true on a field means a matching column was found.
• A present: false entity or field, or a type_ok: false, means your schema is fine but the data does not match it — that is drift, not a schema problem, and the exit code is still 0.

---

Appendix B: Type-mapping cheat sheet

A consolidated reference: given a database column type, the schemascope type in the right-hand column is what it normalizes to. All the spellings below are recognized aliases — including parameterized (varchar(255)) and multi-word (double precision) forms — so you can usually paste your database's own type verbatim. Full rules are in Type Names; only a spelling that appears nowhere below falls through to unknown.

Canonical schemascope type	Database types that map to it
string	char, varchar, nvarchar, text, clob, character varying, uuid, guid, uniqueidentifier, enum, set, json, jsonb, xml, hstore, variant, object, array, struct, map, bytea, blob, binary, varbinary, bytes, image, time, interval, year, inet, geometry/geography, ip, ObjectId
integer	int, integer, bigint, smallint, tinyint, mediumint, int2/int4/int8, serial/bigserial, long, int64, varint, counter
float	float, double, double precision, real, decimal, numeric, number (any NUMBER(p,s) or NUMBER(p,0) — the scale is stripped, so Oracle integers land here too; harmless, since float accepts integer data), money, smallmoney, float4/float8, float64, decimal128, BIGNUMERIC
boolean	boolean, bool, bit. (A MySQL tinyint(1) normalizes to integer — the (1) is stripped to tinyint — but a 0/1 column infers boolean from its data and integer accepts that, so it still passes.)
date	date
datetime	datetime, datetime2, smalldatetime, timestamp, timestamptz, timestamp with/without time zone, TIMESTAMP_NTZ/LTZ/TZ, datetimeoffset (strip any zone offset so it exports as YYYY-MM-DD HH:MM:SS)
unknown	Only a spelling that appears nowhere above (e.g. array notation int[], a bespoke domain type, or a genuine typo), a non-string, or an empty/missing type. A declared unknown is compatible with any inferred type, so its type_ok is always true — you simply get no drift check on that field.

Reminders that catch people out:

• Vendor spellings are recognized now — json, jsonb, blob, bytea, array, money, interval, time, year, serial, int4, nvarchar, datetime2, timestamptz, varchar(255), double precision, and the rest of the table above all resolve. You generally don't need to hand-translate types.
• A native UUID column is fine once exported: it comes out as text, infers string, and a declared string (or uuid) accepts it.
• When unsure, declare string — it accepts any inferred type, so it never produces a false mismatch; use specific types where you want real drift detection.