Metadata-Version: 2.4
Name: schemascope
Version: 0.2.0
Summary: Profile a data source (files, SQLite, or any SQL database) against a schema across JSON/YAML/XML/TXT formats.
Author: schemascope maintainers
License: MIT
Requires-Python: >=3.8
Description-Content-Type: text/markdown
Requires-Dist: PyYAML>=5.1
Requires-Dist: SQLAlchemy>=1.4
Provides-Extra: postgres
Requires-Dist: psycopg[binary]>=3; extra == "postgres"
Provides-Extra: mysql
Requires-Dist: PyMySQL>=1; extra == "mysql"
Provides-Extra: mssql
Requires-Dist: pyodbc>=4; extra == "mssql"
Provides-Extra: oracle
Requires-Dist: oracledb>=1; extra == "oracle"
Provides-Extra: mariadb
Requires-Dist: PyMySQL>=1; extra == "mariadb"
Provides-Extra: cockroach
Requires-Dist: sqlalchemy-cockroachdb>=1; extra == "cockroach"
Requires-Dist: psycopg2-binary>=2.9; extra == "cockroach"
Provides-Extra: redshift
Requires-Dist: sqlalchemy-redshift>=0.8; extra == "redshift"
Requires-Dist: redshift-connector>=2; extra == "redshift"
Provides-Extra: snowflake
Requires-Dist: snowflake-sqlalchemy>=1.5; extra == "snowflake"
Provides-Extra: bigquery
Requires-Dist: sqlalchemy-bigquery>=1.9; extra == "bigquery"
Provides-Extra: databricks
Requires-Dist: databricks-sqlalchemy>=1; extra == "databricks"
Provides-Extra: db2
Requires-Dist: ibm-db-sa>=0.4; extra == "db2"
Provides-Extra: trino
Requires-Dist: trino[sqlalchemy]>=0.328; extra == "trino"
Provides-Extra: clickhouse
Requires-Dist: clickhouse-sqlalchemy>=0.3; extra == "clickhouse"
Provides-Extra: duckdb
Requires-Dist: duckdb-engine>=0.13; extra == "duckdb"

# 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](#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](#the-two-things-schemascope-needs)
- [Install](#install)
- [Your first run in 5 minutes](#your-first-run-in-5-minutes)
- [The core idea](#the-core-idea)
- [Step 1 — Give it your schema](#step-1--give-it-your-schema)
- [Step 2 — Point it at your data](#step-2--point-it-at-your-data)
- [Concepts and glossary](#concepts-and-glossary)
- [Schema Model](#schema-model)
- [Schema Formats](#schema-formats)
- [Type Names](#type-names)
- [Data Sources](#data-sources)
- [Output Reference](#output-reference)
- [Type Inference](#type-inference)
- [Python API](#python-api)
- [Format Detection](#format-detection)
- [Troubleshooting](#troubleshooting)
- [Limitations](#limitations)
- [Requirements](#requirements)
- [Development](#development)
- [License](#license)
- [Appendix A: Generating a schemascope schema from your database](#appendix-a-generating-a-schemascope-schema-from-your-database)
- [Appendix B: Type-mapping cheat sheet](#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:

```text
id,email,age,active,deleted,signup_date
1,alice@example.com,31,true,0,2021-03-05
2,bob@example.com,,false,0,2021-07-19
3,carol@example.com,27,true,1,2022-01-02
4,dave@example.com,44,true,0,2022-11-30
5,erin@example.com,,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](#appendix-a-generating-a-schemascope-schema-from-your-database)) 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:

```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):

```text
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](#appendix-a-generating-a-schemascope-schema-from-your-database) has the exact commands per engine. |
| **Someone handed you a schema file** (`.json` / `.yaml` / `.xml` / `.txt`) | You are ready. Go to [Install](#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](#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](#appendix-a-generating-a-schemascope-schema-from-your-database).

---

## Install

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

```bash
python3 --version
```

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

```bash
pip install schemascope
```

Confirm it landed on your `PATH`:

```bash
schemascope --version
```

You should see a line like `schemascope 0.1.0`. If instead you get "command not found", jump to [Troubleshooting](#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:

```bash
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`:

```text
id,email,age,active,deleted,signup_date
1,alice@example.com,31,true,0,2021-03-05
2,bob@example.com,,false,0,2021-07-19
3,carol@example.com,27,true,1,2022-01-02
4,dave@example.com,44,true,0,2022-11-30
5,erin@example.com,,false,0,2023-05-14
```

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

```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"}
      ]
    }
  ]
}
```

### 2. Run the command

```bash
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:

```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:

```bash
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`):

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

```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:

```bash
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:

```bash
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](#step-2--point-it-at-your-data). |
| Your data is in a database | Read its structure and translate the types into a small schema. [Appendix A](#appendix-a-generating-a-schemascope-schema-from-your-database) 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**:

```json
{
  "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`:

```json
{
  "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](#schema-model) and [Schema Formats](#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-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:

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

is equivalent to this JSON file:

```json
{
  "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](#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`:

```json
{
  "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`:

```bash
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](#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:

```text
data/
  users.csv
  orders.csv
```

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

```bash
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.

```bash
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](#appendix-a-generating-a-schemascope-schema-from-your-database).

### 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](#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`](#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

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

### YAML

```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>`.

```xml
<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:

```text
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:

```yaml
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:

```xml
<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](#appendix-b-type-mapping-cheat-sheet).

> **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:

```text
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:

```python
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:

```bash
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:

```bash
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:

```json
{
  "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](#appendix-a-generating-a-schemascope-schema-from-your-database)).

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:

```python
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:

```python
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:

```bash
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|yaml|xml|txt`, or give the file a recognized extension.
`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](#appendix-b-type-mapping-cheat-sheet).
- **`"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](#sql-database-any-sqlalchemy-url).

---

## Development

Run tests:

```bash
python3 -m pytest -q
```

Build local artifacts:

```bash
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](#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](#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](#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:

```sql
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:

```sql
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:

```bash
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:

```bash
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`:

```bash
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:

```sql
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:

```bash
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:

```bash
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:

```sql
-- 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:

```sql
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:

```bash
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):

```bash
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:

```sql
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:

```sql
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:

```sql
-- 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:

```sql
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:

```bash
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:**

```bash
schemascope schema.json warehouse.sqlite
```

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

```bash
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:

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

Or capture DDL with the `db2look` tool:

```bash
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):

```sql
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](#a-universal-starting-point-information_schemacolumns). Type mapping is the same as [A1. PostgreSQL](#a1-postgresql).

**Map the types.** Same as [A1. PostgreSQL](#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:

```bash
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:

```sql
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:

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

Or query the catalog:

```sql
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:

```sql
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`):

```bash
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:

```bash
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:

```sql
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:

```bash
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`:

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

```bash
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`):

```sql
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):

```sql
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`:

```bash
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):

```python
(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:

```javascript
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:

```bash
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:

```sql
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:

```sql
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):**

```bash
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:

```bash
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`:

```bash
# 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):**

```bash
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):

```bash
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:

```sql
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`:

```sql
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:

```text
 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](#master-type-mapping-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`:

```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:

```bash
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:

```text
id,email,age,active,deleted,signup_date
1,alice@example.com,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:**

```bash
schemascope schema.json data
```

**Step 5 — read the result.** You get the same report structure as the [walkthrough](#your-first-run-in-5-minutes): `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:

```bash
schemascope schema.json data/
```

- If you get a `schema error: ...` on stderr, the schema file itself is wrong — fix it (see [Troubleshooting](#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](#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.
</content>
</invoke>
