---
name: data_dictionary_builder
description: "Build a data dictionary from schema exports and sample rows: extract declared types and comments, profile the samples for inferred types, null rates and observed values, merge curated definitions and owners, and produce dictionary.csv/.md plus a gap list flagging every field no source defines. Use when documenting a database, CSV feed, API payload, or warehouse table, when onboarding onto an undocumented dataset, when a schema changed and documentation needs rebuilding, or when someone asks what a field means, who owns it, or what values it can hold."
---

# Data Dictionary Builder

## Purpose

Turn scattered evidence about a dataset into two files a team can trust: a
dictionary that states what each field means, and a gap list that names every
field where no source states it. The builder merges three kinds of evidence
and keeps them labelled, so a reader can tell a declared fact from an observed
one:

1. Declared: the schema export (types, nullability, database comments).
2. Observed: profiling of real sample rows (inferred type, null rate,
   distinct count, observed values, examples).
3. Curated: a definitions file a person wrote (definitions, owners, allowed
   values, null meanings).

Never invent a definition to fill a hole. A hole is a gap, with a code, a
severity, and a named next step. That rule is the difference between this and
a draft document that reads well and lies quietly.

## Workflow

1. Get the schema export. Run one of the queries in
   `references/extraction-queries.md` (PostgreSQL, MySQL, SQL Server, SQLite)
   or use a JSON Schema file directly. Save the CSV export; do not retype it.
2. Get representative sample rows per table, a few hundred rows when
   available, spread across time. Scrub values, not structure: swap a real
   email for a well-formed fake one instead of blanking the column, or type
   inference and null rates will lie. Read the sampling notes in
   `references/extraction-queries.md` first.
3. Start the curated definitions file. Copy `assets/example_definitions.csv`
   as a template. Fill in what people already know: definitions, owners,
   allowed values, what null means per field. Leave blanks blank; blanks
   become gaps, and gaps get owners later.
4. Build:

       python3 bin/build_dictionary.py \
         --schema schema_export.csv \
         --sample orders=orders_sample.csv \
         --sample customers=customers_sample.csv \
         --definitions definitions.csv \
         --outdir ./dictionary-out

   `--sample` repeats. A bare path with `--table NAME` works for one table.
   With no database access, pass a JSON Schema as `--schema`, or skip
   `--schema` and profile samples alone (every field starts as GAP-01).
5. Read `gaps.md` before `dictionary.md`. Triage with
   `references/gap-rules.md`: error gaps (GAP-01, 02, 03, 05, 06) block any
   claim that the dictionary is complete. Assign each gap a person and a
   decision: define it, fix the data, fix the schema, or record it accepted.
6. Close gaps at the source. Put definitions into database comments or the
   definitions file, then rerun the builder. Never hand-edit dictionary.csv;
   the next build wipes manual edits.
7. Gate the result when it ships with a release:

       python3 bin/check_dictionary.py dictionary-out/dictionary.csv \
         --min-definition 0.9 --min-owner 0.9

   It prints definition and owner coverage and fails on open error gaps.
   Start at 0.8 and ratchet up as the dictionary matures.
8. Publish dictionary.md where the data's consumers actually look: repo,
   wiki, or catalog. If the dictionary feeds a catalog, keep dataset title,
   description, publisher, and contact alongside it, matching the DCAT model
   of catalog, dataset, and distribution.

## Worked example

The bundled example is small and deliberately dirty. One value in total_usd
is the text `not-a-price`, discount_code exists in the data but not in the
schema, and legacy_code exists in the schema but not in the data.

    cd assets/..   # the skill directory
    python3 bin/build_dictionary.py \
      --schema assets/example_schema.csv \
      --sample orders=assets/example_sample_orders.csv \
      --definitions assets/example_definitions.csv \
      --outdir /tmp/dictionary-out

Real output: `wrote 7 fields, 9 gaps`. The gap list includes:

- GAP-03 on discount_code: present in sample rows, absent from the schema.
- GAP-04 on legacy_code: in the schema, absent from every sample.
- GAP-05 on total_usd: declared numeric(12,2), sample infers mixed, because
  5 of 6 values parse as numbers and one does not.
- GAP-02 on the four fields the definitions file does not name an owner for.
- GAP-07 on discount_code: one observed value and no documented allowed set.
  For an open code set like a promotion code, this closes by writing
  "open set, validated against the promotions table" in allowed_values,
  not by listing the values seen so far.

`check_dictionary.py` on this output reports 100 percent definition coverage,
42.9 percent owner coverage, and RESULT: FAIL, which is correct. The example
dictionary is a draft until the owners are named.

## Output Contract

The build writes four files to `--outdir`:

- `dictionary.csv`: one row per field, columns specified in
  `references/dictionary-schema.md`. This is the machine-readable source
  other tools should consume.
- `dictionary.md`: the same content as tables grouped by table, for humans.
- `gaps.csv` and `gaps.md`: one row per gap with code, severity, and the
  evidence behind it.

Every reported run states: fields documented, gap count split by severity,
definition coverage, owner coverage, and where the files went. Report
observed facts as observed ("the sample shows 4 values") and declared facts
as declared ("the schema declares NOT NULL"). Do not merge the two voices.

## Operating Rules

1. A definition states what one value means, with unit, currency, or timezone
   where those apply. If the definition still works after renaming the
   column, it is probably real. If it is the column name in sentence form,
   it is GAP-01 wearing a costume; leave the gap open.
2. Observed allowed values are evidence, not permission. The documented set
   comes from code, config, a lookup table, or an owner's word. When they
   differ, that difference is a finding to report, not a conflict to smooth
   over.
3. Null is not one thing. Unknown, not applicable, withheld, optional, and
   load error all show up as empty. Record which one applies per field in
   null_meaning (GAP-10 tracks fields where nobody has said).
4. Protect sample data. The builder masks example values for columns whose
   names suggest personal data (email, phone, ssn, address, date of birth,
   and similar) unless `--show-pii-examples` is passed. Use that flag only
   on samples already cleared for sharing. The sample files themselves are
   your responsibility; keep live credentials and unredacted personal data
   out of them.
5. Rebuild, do not patch. After every schema migration, rerun the builder
   and compare gap counts with the previous run. New error gaps after a
   release mean the release shipped undocumented fields.
6. Keep the dictionary under version control next to the schema it describes,
   with the definitions file as the human-edited input. Review changes to
   the definitions file like code changes: who changed it, when, and why.
7. Label drafts honestly. A dictionary with open error gaps ships as a
   draft, named as one in its header and wherever it is linked.