Flat reference dimensions — one manifest, generated dims

2026-07-08T02:02:21Z by Showboat 0.6.1

Flat reference dimensions — one manifest, generated dims

Turning Sierra’s cryptic codes (item_status='!', ptype=0) into staff-facing language (ON HOLDSHELF, Unlimited) — without hand-copying the same machinery twenty times.

Our library’s Sierra ILS speaks in short codes. An item’s status is a single character; a patron type is a small integer; an agency is a number. The analytics layer has to show those as words, which means a code→label dimension for each one — a little lookup table that says ! → ON HOLDSHELF.

Sierra exposes about 25 of these flat X_property / X_property_name dictionary tables. They all share one shape, but their column names and types drift from table to table — so the tempting path is to hand-write the same extract-and-conform machinery once per dim, in two repos (the extractor and the transformer). That is the hand-maintained-parallel-list anti-pattern: two lists that must agree, kept in sync by discipline, drifting the moment someone forgets.

This walkthrough shows the approach we took instead. The plain-language version is a label printer with a template: you don’t keep a drawer of hand-typed labels, you keep one template and reprint from it — and a lint step refuses to ship if the printed labels ever stop matching the template.

One manifest (chimpy_lake.reference.CODE_TABLES) is the single source of truth. A generator emits the committed dbt models from it; the extract tenant imports the same manifest to harvest bronze. A --check freshness guard fails the build if the committed output ever drifts. Adding a dim = adding one manifest row + regenerating.

Every beat below runs the real code — the actual manifest, generator, and committed dbt staging SQL + macro — over a small frozen bronze fixture with no patron data. The commands are reproducible (showboat verify re-runs them); the production numbers are called out separately where they can’t be reproduced offline.

1 · The problem — same pattern, different columns

What. The three proving tables (item_status, agency, ptype) are structurally identical — a code table joined to a name table — but every column name and type is different. agency’s code is an integer; ptype is renamed end to end.

Why it matters. That per-table drift is exactly what makes hand-cloning look easy and go wrong. Two lossy mistakes already happened in the hand-built dims: display_order (the curated sort order) was dropped from every one, and dim_itype silently lost three of its four axes.

uv run python docs/demos/flat-reference-dims/_driver.py problem


The problem — same pattern, different columns (a copy-paste trap)
=================================================================
Sierra ships ~25 flat code->label lookup tables. Three prove the family.
Every one is an X_property (the code + curated display_order) joined to an
X_property_name (the English label) — SAME SHAPE, but the column names/types
drift table to table. That drift is exactly what tempts a hand-copy per dim:

  dim          code column          name column    name-table FK
  ------------ -------------------- -------------- -------------
  item_status  code (varchar)       name           item_status_property_id
  agency       code_num (integer)   name           agency_property_id
  ptype        value (smallint)     description    ptype_id

agency's code is an integer; ptype is renamed end to end (value / description /
ptype_id). Hand-cloning ~20 more of these is where drift and lossy drops creep in
(two already happened: display_order dropped everywhere, itype lost 3 of 4 axes).

2 · The one manifest

What. A single frozen dataclass registry in the shared chimpy_lake package — the neutral node both repos already import. Each row pins the per-table variation (which column is the code, its type, which column is the label, the FK). Exclusions are first-class entries, so the manifest is also the honest catalog of what we deliberately don’t model, and why. This walkthrough follows the three proving rows that exercise every field-mapping variant; the manifest now holds many more, each added the same one-row way (see the closer).

Why it matters. One declaration, two consumers. There is no second list to keep in sync — the whole class of drift bugs is designed out, not tested for after the fact.

Term to know: single source of truth; the DRY principle (don’t repeat yourself).

uv run python docs/demos/flat-reference-dims/_driver.py manifest


One manifest — chimpy_lake.reference v0.2.0 (the single source of truth)
========================================================================
A frozen dataclass registry, versioned in the shared package BOTH repos import:
the extract tenant harvests from it; the generator emits dbt models from it.

  name         -> sierra tables                             facet
  item_status  sierra_view.item_status_property(+_name)     true
  agency       sierra_view.agency_property(+_name)          true
  ptype        sierra_view.ptype_property(+_name)           true
                 note: reference dictionary only (non-PII); independent of the GATED patron de-id track

Exclusions are first-class too — the honest catalog of what we DON'T model, and why:
  - fund           fund-master table (fund_master_id, percentages, is_active), not a code->label dictionary
  - external_fund  code_num but no display_order; fund-adjacent footgun
  - firm           zero rows at CHPL
  - gtype          zero rows at CHPL
  - user_defined   consolidated super-table partitioned by user_defined_category_id; Tier-2, deferred
  - order_status   order family footguns — validate the dictionary before adding
  - order_type     order family footguns — validate the dictionary before adding
  - order_note     order family footguns — validate the dictionary before adding

Add a dim = add one CodeTable row + regenerate. No second hand-maintained list.

3 · Bronze — a faithful snapshot, with no transformation

What. The extract tenant lands each Sierra table verbatim: every column as text, append-only, all languages, one snapshot per weekly harvest. Bronze does not type, filter, or deduplicate anything.

Why it matters. The raw truth is always recoverable and reprocessing is cheap — if we later change how we conform the data, we re-derive from bronze without re-reading Sierra. Keeping the Spanish rows, the un-labelled codes, and both harvests of every name is the point: bronze remembers everything; silver decides.

Term to know: medallion architecture (bronze → silver → gold); schema-on-read; SCD Type 0 (a landed snapshot is never edited — history is append-only).

uv run python docs/demos/flat-reference-dims/_driver.py bronze


Bronze — a faithful snapshot: NO transformation happens here
============================================================
The extract tenant lands each X_property / X_property_name verbatim: every
column VARCHAR, append-only, ALL languages, one snapshot per weekly harvest.
Nothing is typed, filtered, or deduplicated at this layer (schema-on-read /
SCD Type 0) — the raw truth is always recoverable.

reference.item_status_property_name_snapshots — 11 raw rows across 2 harvests:
  fk   name               lang  observed_at
  1    AVAILABLE          1     2026-06-29T00:00:00
  1    DISPONIBLE         2     2026-06-29T00:00:00
  2    ON HOLDSHELF       1     2026-06-29T00:00:00
  3    IN TRANSIT         1     2026-06-29T00:00:00
  4    IN TRANSIT LONG    1     2026-06-29T00:00:00
  5    NULL               1     2026-06-29T00:00:00
  1    AVAILABLE          1     2026-07-06T00:00:00
  2    ON HOLDSHELF       1     2026-07-06T00:00:00
  3    NULL               1     2026-07-06T00:00:00
  4    LONG INTRANSIT     1     2026-07-06T00:00:00
  5    NULL               1     2026-07-06T00:00:00

Note what bronze KEEPS, untouched: the Spanish row (lang 2), the un-labelled
code (fk 5, NULL name), BOTH harvests of every name, and a rename in flight
(fk 4: 'IN TRANSIT LONG' then 'LONG INTRANSIT'). Silver decides what to do with
all of it — bronze just remembers.

4 · Generate — one row becomes a full model set

What. chimpy-lake gen-reference-dims reads the manifest and emits, per row: two thin staging models, a one-call dim that delegates to the shared macro, and the spliced _sources.yml / _dims.yml blocks. Every generated file is stamped GENERATED FROM chimpy_lake.reference vX — do not edit (provenance you can grep for).

Why it matters. The dim files carry no logic — the logic lives once, in the macro. And because the output is provably derived, we can ask at any time whether the committed tree still matches the manifest. It does: the run below reports fresh.

Term to know: code generation; provenance; idempotence (regenerating a clean tree changes nothing).

uv run python docs/demos/flat-reference-dims/_driver.py generate


Generate — one manifest row becomes a full, provenance-stamped model set
========================================================================
`chimpy-lake gen-reference-dims` renders, per row: two stg models, a thin dim,
and the spliced _sources.yml / _dims.yml blocks. The dim is a ONE-CALL stub into
the shared macro (the logic lives once):

--- transform/reference_dims/models/dims/dim_item_status.sql (generated) ---
-- GENERATED FROM chimpy_lake.reference v0.2.0 — do not edit
{{ config(materialized='table', alias='item_status') }}
{{ reference_flat_dim(
    stg_property='stg_item_status_property',
    stg_name='stg_item_status_property_name',
    code_column='code',
    name_column='name',
    fk_column='item_status_property_id',
    code_out='item_status_code',
    name_out='item_status_name') }}

--- the managed block spliced into models/staging/_sources.yml ---
# GENERATED FROM chimpy_lake.reference v0.2.0 — do not edit
# flat-dictionary sources (managed block)
      - name: item_status_property_snapshots
      - name: item_status_property_name_snapshots
      - name: agency_property_snapshots
      - name: agency_property_name_snapshots
      - name: ptype_property_snapshots
      - name: ptype_property_name_snapshots
      - name: billing_location_property_snapshots
      - name: billing_location_property_name_snapshots
      - name: temp_location_property_snapshots
      - name: temp_location_property_name_snapshots
      - name: receiving_location_property_snapshots
      - name: receiving_location_property_name_snapshots
      - name: receiving_action_property_snapshots
      - name: receiving_action_property_name_snapshots
      - name: bib_level_property_snapshots
      - name: bib_level_property_name_snapshots
      - name: form_property_snapshots
      - name: form_property_name_snapshots
      - name: mblock_property_snapshots
      - name: mblock_property_name_snapshots
      - name: notification_medium_property_snapshots
      - name: notification_medium_property_name_snapshots
      - name: claim_action_property_snapshots
      - name: claim_action_property_name_snapshots
      - name: acq_type_property_snapshots
      - name: acq_type_property_name_snapshots
      - name: material_property_snapshots
      - name: material_property_name_snapshots
      - name: branch_snapshots
      - name: branch_name_snapshots
      - name: location_snapshots
      - name: location_name_snapshots
      - name: statistic_group_snapshots
      - name: statistic_group_name_snapshots

Idempotence / freshness — does the committed tree still match the manifest?
  reference dims fresh.  (rc=0)

Every generated file carries `-- GENERATED FROM chimpy_lake.reference v0.2.0 — do not edit`.

5 · Silver — the one transformation, run

What. All the conforming logic — typing, the English-only filter (iii_language_id=1), the Type-1 latest-per-code pick, and the append-only name dedup — lives in a single shared dbt macro. Here it runs, unmodified, over the frozen bronze from beat 3, producing the conformed dims — including the payoff this whole walkthrough follows: item_status code t now reads as IN TRANSIT.

Why it matters. One query has to get several subtle things right at once, and the macro does: a code keeps its curated sort order (not alphabetical); a later NULL-name harvest does not clobber a resolved label; a renamed code takes its newest non-null name; an un-labelled code is kept as NULL, never dropped; and the grain holds — exactly one row per code — despite two harvests of every name. The load-bearing bit is a single (name is not null) desc sort key; get it wrong and unlabelled codes silently vanish.

Term to know: conformed dimension; SCD Type 1 (current value per key); grain (one row per code).

uv run python docs/demos/flat-reference-dims/_driver.py silver


Silver — the one transformation, run over the frozen bronze
===========================================================
Typing, English filter (iii_language_id=1), Type-1 latest-per-code, and the
append-only NAME dedup all happen HERE, via the shared macro. Row counts from
the fixture (prod carries far more: 30 / 53 / 45):

  dim                     rows  code type
  reference.item_status      5  varchar
  reference.agency           3  integer
  reference.ptype            3  smallint

reference.item_status (ordered by display_order — the CURATED sort, not A-Z):
  code  name             display_order
  -     AVAILABLE        0
  !     ON HOLDSHELF     1
  t     IN TRANSIT       5
  g     LONG INTRANSIT   6
  n     NULL  <- kept    10

What that one query got right, all from the macro:
  - t  -> IN TRANSIT     a LATER null-name harvest did NOT clobber the resolved name
  - g  -> LONG INTRANSIT a rename kept its newest non-null label
  - n  -> NULL           an unlabelled code is KEPT (left join), never dropped
  - the Spanish 'DISPONIBLE' row was filtered out (English only)
  - grain intact: 0 duplicate codes despite 2 harvests of every English name

reference.agency (integer code) and reference.ptype (smallint / description / ptype_id):
  agency   1 -> Main Library, 4 -> Branch Libraries, 11 -> Outreach Services
  ptype    0 -> Unlimited, 1 -> Adult, 10 -> Staff

6 · The freshness guard bites

What. --check re-derives every artifact from the manifest and fails (rc=1) if any committed file differs. It’s wired into the transform runner’s freshness gate, so a drifted commit fails the nightly build fast, before dbt touches the lake.

Why it matters. This is the crucial distinction from a parity test. A parity test compares two hand-maintained lists after they’ve diverged. This polices one generator against one source — there is no second list. The fix for drift is never a hand-patch; it’s regenerate-and-commit.

Term to know: freshness guard vs parity test; fail-fast.

uv run python docs/demos/flat-reference-dims/_driver.py guard


The freshness guard bites — a drifted commit fails the build fast
=================================================================
`--check` (the same check_all() wired into run_transform.py's freshness gate)
re-derives every artifact from the manifest and fails rc=1 on ANY drift. It
polices ONE generator against ONE source — not two hand-lists reconciled later.

clean tree:
  reference dims fresh.  (rc=0)

after a hand-edit to dim_agency.sql:
  STALE: dims/dim_agency.sql
  reference dims are stale — run `chimpy-lake gen-reference-dims` and commit.
  (rc=1)

The fix is never a hand-patch — it's regenerate-and-commit. The template is law.

7 · The enforced contract

What. Each generated dim ships an enforced dbt contract that pins every column name and type. If a build ever produces a different shape, dbt aborts with data type mismatch, exit 1 — the same guardrail that already caught a real type drift on itype. Grain (not_null + unique on the code) stays as data tests, because DuckLake refuses PRIMARY KEY / UNIQUE DDL; display_order gets its own not_null test as the curated sort key.

Why it matters. The manifest guarantees the models are generated right; the contract guarantees they stay right at build time. Two independent guards — one on the source, one on the output — with no hand-maintained list between them.

Term to know: data contract / contract enforcement; data quality as code.

uv run python docs/demos/flat-reference-dims/_driver.py contract


The enforced contract — the conformed shape is pinned, drift fails loud
=======================================================================
Each generated dim ships an ENFORCED dbt contract (config.contract.enforced:
true) that pins every column name + type. If a build ever produces a different
shape, dbt aborts with `data type mismatch`, exit 1 — the same way itype's
contract already caught a real type drift. The managed _dims.yml block:

# GENERATED FROM chimpy_lake.reference v0.2.0 — do not edit
# flat-dictionary dims (managed block)
  - name: dim_item_status
    description: "Conformed Sierra item_status dimension (one row per item_status_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: item_status_code
        data_type: varchar
        tests: [not_null, unique]
      - name: item_status_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_agency
    description: "Conformed Sierra agency dimension (one row per agency_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: agency_code
        data_type: integer
        tests: [not_null, unique]
      - name: agency_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_ptype
    description: "Conformed Sierra ptype dimension (one row per ptype_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: ptype_code
        data_type: smallint
        tests: [not_null, unique]
      - name: ptype_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_billing_location
    description: "Conformed Sierra billing_location dimension (one row per billing_location_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: billing_location_code
        data_type: varchar
        tests: [not_null, unique]
      - name: billing_location_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_temp_location
    description: "Conformed Sierra temp_location dimension (one row per temp_location_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: temp_location_code
        data_type: varchar
        tests: [not_null, unique]
      - name: temp_location_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_receiving_location
    description: "Conformed Sierra receiving_location dimension (one row per receiving_location_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: receiving_location_code
        data_type: varchar
        tests: [not_null, unique]
      - name: receiving_location_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_receiving_action
    description: "Conformed Sierra receiving_action dimension (one row per receiving_action_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: receiving_action_code
        data_type: varchar
        tests: [not_null, unique]
      - name: receiving_action_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_bib_level
    description: "Conformed Sierra bib_level dimension (one row per bib_level_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: bib_level_code
        data_type: varchar
        tests: [not_null, unique]
      - name: bib_level_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_form
    description: "Conformed Sierra form dimension (one row per form_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: form_code
        data_type: varchar
        tests: [not_null, unique]
      - name: form_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_mblock
    description: "Conformed Sierra mblock dimension (one row per mblock_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: mblock_code
        data_type: varchar
        tests: [not_null, unique]
      - name: mblock_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_notification_medium
    description: "Conformed Sierra notification_medium dimension (one row per notification_medium_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: notification_medium_code
        data_type: varchar
        tests: [not_null, unique]
      - name: notification_medium_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_claim_action
    description: "Conformed Sierra claim_action dimension (one row per claim_action_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: claim_action_code
        data_type: varchar
        tests: [not_null, unique]
      - name: claim_action_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_acq_type
    description: "Conformed Sierra acq_type dimension (one row per acq_type_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: acq_type_code
        data_type: varchar
        tests: [not_null, unique]
      - name: acq_type_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_material
    description: "Conformed Sierra material dimension (one row per material_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: material_code
        data_type: varchar
        tests: [not_null, unique]
      - name: material_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: display_order
        data_type: integer
        tests: [not_null]
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_branch
    description: "Conformed Sierra branch dimension (one row per branch_code_num). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: branch_code_num
        data_type: integer
        tests: [not_null, unique]
      - name: branch_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_location
    description: "Conformed Sierra location dimension (one row per location_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: location_code
        data_type: varchar
        tests: [not_null, unique]
      - name: location_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: branch_code_num
        data_type: integer
      - name: parent_location_code
        data_type: varchar
      - name: is_public
        data_type: boolean
      - name: is_requestable
        data_type: boolean
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar
  - name: dim_statistic_group
    description: "Conformed Sierra statistic_group dimension (one row per statistic_group_code). Type-1 current, latest-per-code-ever-seen."
    config:
      contract:
        enforced: true
    columns:
      - name: statistic_group_code
        data_type: integer
        tests: [not_null, unique]
      - name: statistic_group_name
        data_type: varchar
        description: "English (iii_language_id=1) label; NULL when unresolved (kept)."
      - name: location_code
        data_type: varchar
      - name: observed_at
        data_type: varchar
      - name: provenance
        data_type: varchar

Grain (not_null + unique on the code) stays a dbt DATA test, never a contract
constraint — DuckLake rejects PRIMARY KEY / UNIQUE DDL. display_order gets its
own not_null test: it is the curated sort key and 0-null across every table, so
a null there is a real regression worth failing.

What’s live in the lake right now

Everything above runs offline against a frozen fixture so it reproduces. For the record, here is the production state (verified 2026-07-06 by a read-only query of the live lake — not reproduced here, because live counts change as Sierra changes):

dim	rows	code type	a real label
`reference.item_status`	30	varchar	`!` → ON HOLDSHELF
`reference.agency`	53	integer	`1` → Main Library
`reference.ptype`	45	smallint	`0` → Unlimited

Plus six *_property(_name)_snapshots bronze tables, provenance sierra-db.cincinnatilibrary.org. Both the extract harvest and the transform build run on enabled weekly (Sunday) systemd timers, so the dims stay fresh with no hand-holding. The item_status dim is the join target that lets the long-in-transit worklist show IN TRANSIT instead of a bare t.

Proof

Every command above is reproducible — showboat verify re-runs each one and diffs its output. The thesis invariants (the shared macro’s NULL-kept / no-clobber / rename-wins / grain behaviour, the code-type variants, and the committed tree’s freshness against the manifest) ship as a guard test:

uv run pytest tests/demos/test_flat_reference_dims.py -q 2>&1 | sed -E 's/ in [0-9.]+s//'

...                                                                      [100%]
3 passed

Glossary

Code→label dimension — a lookup table mapping a raw Sierra code to a human name (! → ON HOLDSHELF). (Beats 1, 5)
Single source of truth / DRY — one authoritative declaration that all consumers import, instead of parallel hand-maintained copies. (Beat 2)
Medallion (bronze/silver/gold) — layered refinement from raw to query-ready. Bronze is verbatim; silver conforms; gold aggregates. (Beats 3, 5)
Schema-on-read / SCD Type 0 — store raw now, impose structure at query time; a landed snapshot is never edited. (Beat 3)
Code generation / provenance — the dbt models are emitted from the manifest and stamped with their origin, not written by hand. (Beat 4)
SCD Type 1 / grain — keep the current value per key; the grain is one row per code. (Beat 5)
Freshness guard vs parity test — a guard polices one generator against one source; a parity test reconciles two lists after they diverge. We use the guard. (Beat 6)
Data contract — an enforced declaration of a model’s columns + types; a drifting build fails loud. (Beat 7)

Adding the next dimension is one row

You already watched this happen. Back in beat 4, one manifest row produced an entire model set — two staging models, a one-call dim, and the spliced contract blocks — all provenance-stamped, from a single CodeTable entry. That is the payoff: adding a Sierra code→label dimension is one row plus a regenerate. No new SQL, no second list to update, no per-dim machinery. The macro holds the logic once; the freshness guard keeps every generated file honest against the manifest; the contract keeps every built dim honest against its shape.

And it isn’t hypothetical. This walkthrough follows three tables; since it first shipped, the manifest has grown to seventeen flat rows — bib_level, form, material, the location family, and more — each added as a single CodeTable row, with no new SQL and no second list to keep in sync. The one-row thesis, proven in production.

Where this goes next — backlog, not commitments

Beyond the flat dictionaries, posed as direction rather than done work:

A reference data-quality layer. Referential-integrity, naming-convention, and retired-placeholder detection (the en-dash names, the “DO NOT USE” rows, an orphaned statistic_group) — surfaced in discovery, still ahead.
Richer hierarchy modelling. The branch / location / statistic_group snowflake has name-path rollups and data-quality landmines beyond the flat code→label shape shown here.

The claim isn’t cleverness — it’s the opposite. One manifest, one macro, one guard, and the whole flat family conforms itself. Everything above re-runs on demand.

← all walkthroughs · Rendered from ff1fb5a on 2026-07-08 · showboat verify: reproduces. A living artifact — the version ledger is git.