Generate a Data Dictionary Humans Then Verify

Tier: Foundation · Workflow area: Data Quality & Profiling · ~30 minutes

An undocumented schema taxes every analyst, every new hire and every AI feature you will ever build on top of it — NL-to-SQL and RAG both stand on column descriptions. AI turns dictionary-writing from a quarter-long chore into an afternoon: the model drafts descriptions from evidence you feed it, flags its own confidence, and humans verify only the rows that matter before anything publishes.

When to use

• An inherited or acquired database400 tables, no docs, the original developers gone. AI drafting plus targeted human review is the only economically sane path to a catalog.
• Before an NL-to-SQL or RAG rollouttext-to-SQL accuracy rises sharply with good column descriptions (see pb-33). The dictionary is the prerequisite, not the afterthought.
• Onboarding and audit pressurenew team members ask the same twenty questions; auditors want to know what flag3 means. A verified dictionary answers both once.

When NOT to use

• As a publish-without-review shortcutan unverified AI dictionary is worse than none, because it looks authoritative. Wrong descriptions propagate into reports and downstream AI features with a stamp of confidence.
• When sampled values would expose regulated datacolumn names and types are usually safe to share; sampled rows from a salaries or diagnosis table often are not. Profile those columns with aggregates only, or use a tenant-approved model.
• For semantics only the business holdsstatus code meanings, legal definitions, fiscal-calendar quirks. The model will guess; only the data owner knows. Route those rows straight to humans.

The recipe

1. 1.

   Assemble the evidence pack — names alone are not enough. Pull structure, constraints, comments and row counts in one pass:

   Copy

   SELECT c.table_name, c.column_name, c.data_type, c.is_nullable,
          c.column_default,
          pgd.description AS existing_comment
   FROM information_schema.columns c
   LEFT JOIN pg_catalog.pg_statio_all_tables st
     ON st.relname = c.table_name
   LEFT JOIN pg_catalog.pg_description pgd
     ON pgd.objoid = st.relid AND pgd.objsubid = c.ordinal_position
   WHERE c.table_schema = 'public'
   ORDER BY c.table_name, c.ordinal_position;
   

   Add foreign keys (information_schema.table_constraints + key_column_usage) — relationships are the strongest meaning signal a model gets.

2. 2.

   Sample values safely — distributions, not rows. For each column, top values and null rates beat raw rows and leak far less:

   Copy

   SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC LIMIT 10;
   SELECT count(*) FILTER (WHERE region IS NULL)::float / count(*) AS null_rate FROM customers;
   

3. 3.

   Grep the application code for usage references. How the app names and uses a column is evidence the schema cannot give you:

   Copy

   grep -rn "flag3\|churn_score\|settlement_dt" src/ --include='*.py' -l | head
   

4. 4.

   Prompt for drafts with confidence labels and evidence citations. The prompt structure decides whether you get a review surface or a fiction:

   Copy

   You are documenting a PostgreSQL schema for a data catalog. Below:
   DDL with constraints, top-10 value distributions per column, and
   application-code snippets that reference these columns.
   
   For every table and column produce:
   - description: one sentence, business language, no speculation words
   - evidence: which input (constraint, values, code ref) supports it
   - confidence: HIGH (constraint- or code-backed) / MEDIUM (value
     pattern) / LOW (name-only guess)
   - open_question: for LOW rows, the exact question to ask the data owner
   
   Never present a guess as fact. A wrong description is worse than
   "unknown". Output as a markdown table.
   
   [paste evidence pack]
   

   Sample output row: orders.settlement_dt | Date the payment settled with the acquirer | code ref: billing/settle.py:88 | HIGH | — versus customers.flag3 | Possibly a marketing opt-out indicator | name only | LOW | Ask CRM owner: what does flag3=2 mean?

5. 5.

   Route by confidence — humans verify meaning, not grammar. HIGH rows get a skim; MEDIUM rows get a five-minute check against the app; every LOW row goes to the named data owner with the model's open question attached. Track sign-off per table: reviewer, date, rows overturned.

6. 6.

   Publish into the engine and the catalog — not a wiki page that rots. Have the model emit the verified rows as COMMENT ON statements so the dictionary lives where queries live:

   Copy

   COMMENT ON COLUMN orders.settlement_dt IS
     'Date the payment settled with the acquirer. Verified: R.Iyer 2026-08-06.';
   

   Then sync comments into your catalog tool. Schema-change hooks (a CI check that fails when a new column lacks a comment) keep it alive.

Make it yours

• SQL Serverevidence comes from sys.columns, sys.extended_properties and sys.foreign_keys; publish with sp_addextendedproperty @name = 'MS_Description'. Data Discovery & Classification output makes a strong extra evidence input.
• Oraclepull ALL_TAB_COLUMNS and ALL_COL_COMMENTS, publish with COMMENT ON COLUMN. In 23ai, these comments also feed Select AI's schema understanding — the dictionary pays for itself twice.
• ERP schemas (SAP, Dynamics)do not let the model guess at BSEG or MSEG fields; feed it the vendor data dictionary (SE11 exports, Dataverse metadata) as evidence and have it translate to your org's business language, flagging Z-fields and custom columns as LOW by default.

Quality check

• Every published row has an evidence citationa description with no supporting constraint, value pattern or code reference is a guess wearing a suit. Guesses stay in the LOW queue.
• LOW-confidence rows were answered by owners, not polished by the modelrewording a guess more fluently does not verify it. The open questions must come back with human answers.
• Spot-check ten HIGH rows against realitypick ten, verify each against data or code yourself. More than one wrong means the confidence labels are miscalibrated; re-review the batch.
• The dictionary has an owner and a staleness rulea verified-date per row and a CI gate on new columns. An unowned dictionary is six months from being folklore again.