how-to-data-dictionary

Input - a warehouse, and three documents that describe it

The star schema from the Infrastructure flagship is already live and already described twice: a data contract that the pipeline enforces, and a metric registry the agent is grounded on. The dictionary is the third document about the same 39 columns, which is exactly how a platform ends up with four versions of the truth. So the job is not only to fix the dictionary. It is to make it point at what the other two already own.

Sample data - a legacy document with 19 planted defects

There is no honest way to generate a human's prose from a seed, so the legacy dictionary is synthesized rather than sampled - and every single departure from the live warehouse is deliberate and listed in the generator docstring. That turns Step 5 into a recall test: which of the 19 does the audit find, and which does it miss? The answer at the bottom of Step 5 includes one it misses.

  D8  wrong uniqueness    order_id documented as the primary key; 2.16 rows each
  D9  stale value set     status listed as Delivered|Shipped|Cancelled - wrong
                          case, and 'paid' is missing entirely
  D15 wrong label         net_amount described as revenue. The metric registry
                          defines platform revenue as net_amount x take rate
  D17 wrong grain         fact_orders documented as one row per order; it is one
                          row per order LINE
  D18 wrong timezone      order_ts documented as Asia/Singapore local; the
                          contract says UTC. Prose against prose - EXPECTED MISS

Objective - the question an analyst actually arrives with

Nobody opens a data dictionary to admire it. They open it at 4pm with one question, take the first answer they find, and put it in a deck.

Find-skills - the toolbox for a document you can test

Everything here exists to move facts out of a human's hands and into a query, and to make the result fail a build rather than sit in a wiki.

Build - four detectors, then a document that regenerates itself

One script runs the whole thing against the live warehouse: profile it, audit the legacy document, fail the gate, generate the replacement, pass the gate, then move the schema for real on a copy and watch the gate fail again. Every number on this page comes out of that run.

The finding that decides everything else

Coverage is the number every catalog reports and the only one a document can reach 100 percent on while being wrong about a third of its rows.

Four detectors, because a document fails in four unrelated ways

Every finding carries a proof field with three values. measured means a query settled it (27 of 67). screen means text or a distribution was read and a human must confirm (40 of 67). undecided means the check could not run at all - and that third value only exists because the review panel found that its absence was the whole design's blind spot. See Step 6.

Answering the question the analyst actually asked

Left: one column, two live definitions plus one wrong one. Delivered GMV and platform revenue are 9.1x apart - that is the take rate - and counting non-delivered lines adds a further 29%. The full traded window is shown, so these are not the registry's signed-off figures: it binds platform revenue to November 2025 only. The formula is the registry's, the window is this chart's. Right: the grain sentence at the top of the table, which multiplies every order count by 2.16 and leaves nothing in the data looking wrong.

  [registry_label_conflict] fact_orders.net_amount
      claimed:  described as "Net revenue recognised on the order, in USD."
      measured: metric registry defines platform_net_revenue as a SCALED
                formula over net_amount, not the column itself

  [grain_mismatch] fact_orders
      claimed:  one row per order
      measured: 107,841 rows over 50,000 distinct order_id = 2.16 rows per key

What each wrong claim actually costs

An empty result, not an error. The doc lists three statuses in title case. The column is lower case and holds a fourth, so status = 'Delivered' returns 0 rows and looks like a quiet month.

Two columns are nullable by design. The doc says "always populated", so an inner join quietly removes 2,880 delivered lines carrying $169,556 of GMV - $18,651 of platform revenue at the registry take rate. Value omitted from a report, not cash lost.

A factor of 100. Documented as "percent, 0 to 100". Every stored value is a fraction, so anyone converting it lands two orders of magnitude out.

The documented ceiling is not a ceiling. unit_price is written as 1 to 1000 and reaches 5,379.20; 186 rows sit above the line a validation rule would have been built on.

The schema moved and the doc did not. Five columns arrived after it was written, two it still describes are gone. Nothing in the document knows either happened.

Nothing to rely on. Not one of the 39 live columns survives the legacy document with a true claim, an owner and a unit all present.

The rebuild: measure everything measurable, write only what cannot be

The replacement dictionary is two files. One is generated on every build from the live warehouse. The other is about 200 lines of judgement a person writes once.

semantics.yaml is 5 owners, 5 grain sentences, 5 classifications, 39 definitions and 10 gotchas. That is the whole hand-written cost, and it is an afternoon at this size - 39 columns across 5 tables. A 2,000-column estate is not an afternoon, and nothing here makes it one.

net_amount:
  definition: >-
    Merchandise value of the line after discount. Summed over delivered
    lines this is GMV, what buyers paid merchants. It is NOT platform
    revenue.
  unit: USD
  gotcha: >-
    Platform revenue is net_amount times the take rate, per the metric
    registry. Reporting net_amount as revenue overstates the P&L line by
    about 9x.

Proving the detectors fire at all

The generated dictionary passes its own detectors because it is derived from the same profile they query. That is arithmetic, not evidence. So the document gets broken one field at a time, and each mutant has to produce its specific finding by name.

On its first run this suite scored 11 of 12. The miss was not in a detector - it was in the harness: the audit was reading the grain sentence from the semantic layer instead of from the claims being audited, so mutating a claim's grain changed nothing. Nothing else on this page would have found that. Fixing it also surfaced a second bug, a range parser reading 1-10 as (1, -10), which had been quietly firing a false out-of-range finding on qty.

Proving the gate is alive, not decorative

A gate that only ever passes teaches nothing. So the build copies the warehouse, adds a column and retypes another for real, and runs again.

================================================================
DATA DICTIONARY GATE
================================================================
coverage           97.5% of live columns have a dictionary row
claims tested      75
findings           2 major, 3 minor
schema drift       BREAKING
  BREAKING  type changed     dim_date.year
  WARN      added column     fact_orders.refund_amount

RESULT: FAIL - a breaking schema change since the recorded baseline
================================================================

Drift is tiered, and the tiering is the difference between a gate and a log line. A retyped column falsifies a claim the document already makes, so it fails. An added column falsifies nothing - it only leaves the document incomplete - so it warns. A gate that reddens the main branch every time somebody adds a column is a gate that gets switched off in week three.

The artifact: a dictionary somebody will actually open

One self-contained HTML file. Searchable, filterable, no build step, no CDN, opens from a file path. Every structural value on it was measured minutes before it was written.

Recall: 18 of 19 planted defects, and the one that got away

The miss is structural, not a bug. No detector here compares one document's prose against another's, and building one would be a language model guessing rather than a query proving. It is planted, published and left unfixed so the boundary of the method is on the page rather than in a footnote. Six further findings appeared that were never planted: the same wrong claims re-detected as disagreements with the data contract, plus two owner conflicts nobody had noticed - the legacy doc assigns net_amount to finance and status to ops, where the contract assigns both to data-platform.

Expert review - the panel found the same hole this page was built to expose

Five senior reviewer agents - 11 to 15 years each in warehouse modelling, governance, analytics engineering, data platform CI and commercial insight - were pointed at the code and the executed outputs. They did not describe defects; they ran them. Three of the five arrived independently at one sentence: a detector that cannot decide returns nothing, and nothing is indistinguishable from clean. This page had been written to argue that a document can look complete because nobody checked it. The audit had exactly that defect.

Before and after, on one of the five

# v1: printed whenever the exit code was 0 - including with 3 major findings
#     and real schema drift present but not gated.
lines.append("RESULT: PASS - every documented claim held, schema unchanged")

# v2: assembled from what was actually established.
held = ["0 blocking findings"]
if non_block:  held.append(f"{non_block} non-blocking")
if undecided:  held.append(f"{len(undecided)} NOT CHECKED")
if drift is None:        held.append("schema not compared")
elif drift.breaking: held.append("breaking drift present but not gated")
#   -> "PASS - 0 blocking findings, 4 non-blocking, schema unchanged"

Every reproduction the panel executed is committed as test_panel_regressions.py and runs green at 39 of 39. A fix without a test is a fix until the next refactor.