data_dictionary_builder v1.0.0

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

1. 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.
2. 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.
3. 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.

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