Home › Data engineering › Gold data modelling › Silver to gold › Rulebook 1: the harness
Silver to gold · The rulebook · 8 min read
The harness rulebook 1: steps, one harness for every fact, troubleshooting
The first third of the rulebook: what the harness does in a run, how one harness builds every kind of fact, and how you see inside it when numbers look wrong. This is one page of Silver to gold.
- Part F. The full rulebook, in plain words
- The harness, step by step
- Can one harness build every kind of fact?
- Troubleshooting: seeing the intermediate results
Part F · the rulebook
Part F. The full rulebook, in plain words
Everything below is reference: the rules the harness follows, in tables. If a word is new, it is in the list under Start here, and Part A shows it happening.
Silver is clean, but shaped like the sources: an orders table, a deliveries table, a dealers table with history. Gold is shaped for questions: a fact of order lines with on-time flags, a monthly sales fact, dimensions with keys. The silver-to-gold hop is the factory between them. A good factory does the same steps for every product, checks every product before it leaves, and only remakes what changed.
Same steps for every gold table; only the build in the middle is written per fact.
1. The harness
The harness, step by step
Metadata (one file per gold table) declares the kind (fact or dimension), the grain, the inputs and how each is bookmarked, the window column and lookback, the dimensions and how to look them up, and the checks. The harness reads it and runs these steps:
- Read each input's watermark from the metadata store and fix a high mark for this run. Changes that arrive during the run wait for the next one.
- Read only the silver changes between the watermark and the high mark (change sequence, file list, or commit version, per input).
- Turn changed rows into changed windows: the window column of each changed row (promised date, sale month, cure hour), widened by the lookback the fact declares.
- Overlay any correction plan due today (records from a corrections table, never edits in the source).
- Run the fact's build for those windows: a small SQL file or PySpark function, pure, reading only the frames it is given.
- Look up surrogate keys as of each row's event time; a missing member gets the unknown key (-1) and is counted.
- Mark each window complete or not: every input has loaded past the window's end, and every feed that owes data has sent it.
- Check before writing: grain unique, rows in equal rows out, totals reconcile with silver, unknown-key rate under its limit. A failure stops here; nothing is written.
- Replace only the windows whose numbers changed. A window published before and now different is logged as a restatement.
- Write the run log and move each watermark to its high mark. Last, and only on success.
1b. One harness for every fact?
Can one harness build every kind of fact?
Yes, for the mechanics: reading changes, windows, keys, completeness, checks, writing, logging. Each fact kind is a declared mode the harness knows. What one harness cannot do is decide business logic: how OTIF is defined, how a header discount is shared out over lines. That stays in a small build per fact, and it should.
| Fact kind | How the harness builds it | In our build |
|---|---|---|
| Transaction fact | Replace changed windows of the event date | Built (order lines, scrap) |
| Periodic snapshot | Rebuild the open period; closed periods frozen; completeness per period | Built (monthly sales) |
| Accumulating snapshot | Upsert by grain key as milestones arrive (the one mode that MERGEs rows) | Owed: a declared fact mode |
| Factless fact | Same as transaction, with no measures | Same code path |
| Aggregate fact | Rebuild the windows its source fact changed | Same code path, input is a gold fact |
| Consolidated fact (plan vs actual) | Each input added up to the common grain in the build | Same code path |
| Dimensions | One shared dimension pipeline, no code per dimension | Built |
| Bridge tables with weights | Built with their dimension; weights checked to add to 1 | Owed |
Is gold too business-specific to generalise?
Partly, and that is the point of the split. Gold is the first layer where business logic cannot be avoided: silver is almost entirely generic (13 standard stages driven by metadata), while gold must know what "on time" means. But the business part is small. In our build the three facts' business logic is 116 lines in total (about 35 to 45 each), the shared harness is about 1,400 lines written once, and the six gold tables' metadata is 140 lines of YAML. So: generalise the mechanics, declare the shape, hand-write only the business rule. Never per-dataset code for the mechanics; that is where every notebook-per-table platform goes wrong.
| Part | Generic or per fact | Where it lives |
|---|---|---|
| Reading changes, watermarks, high marks | Generic | Same for every table |
| Windows, lookback, window replace | Generic, configured | Window column and lookback are metadata |
| Surrogate keys, as-of lookups, unknown and inferred members | Generic, configured | Which dimensions and how: metadata |
| Completeness, restatements, correction plans | Generic, configured | Which inputs owe data per window: metadata |
| Checks, row ledger, run log, upkeep | Generic, configured | Thresholds per table: metadata |
| Dimensions | Generic | One shared pipeline; a new dimension is metadata only |
| Grain, measures, fact kind | Per fact, declared | Metadata, not code |
| The business rule itself (what counts as on time, how a discount is shared out, which orders are open) | Per fact, code | A small SQL file or PySpark function; the only hand-written part |
How to keep the common and the specific apart
| Rule | What it means |
|---|---|
| The build sees only frames and parameters | build(inputs, params) gets the input DataFrames (or SQL views) and settings; it cannot read a table, write, or ask the clock. Everything else is the harness's job. |
| The harness never knows the business | No dealer, OTIF or tyre word in the harness code; a test fails the build if one appears. |
| Shape is metadata, not code | Grain, window, lookback, inputs, lookups, checks: one file per gold table, validated when loaded. |
| Separate folders, separate owners | Harness in the platform package (platform team); builds in models/<domain>/gold/ (domain team). A change to the harness needs the platform's review; a change to a build needs the domain owner's. |
| Separate tests | Harness tests use made-up tables of any shape; build tests use the domain's small tables and expected rows; the synthetic world tests both together. |
| A contract between them | The build declares the columns it returns; the harness checks them against the gold contract before keys are looked up. |
The build: SQL or PySpark DataFrames?
Both, chosen per fact, behind the same build interface. Default to SQL; use PySpark when the logic is genuinely complex. In our build the two sales facts are SQL and the scrap fact (hours, shifts, plant time zones) is PySpark.
| Need | SQL | PySpark DataFrames | Better |
|---|---|---|---|
| Readable by analysts and reviewers | Yes: the logic reads like the question | Harder: chained calls, Python around it | SQL |
| Run a piece of it by hand | Paste a CTE into a SQL editor and look | Needs a notebook and the input frames | SQL |
| Complex logic (sessions, allocations, loops over rules, reuse) | Gets long and repetitive | Functions, parameters, reuse, unit tests per function | PySpark |
| Unit tests with small tables | Possible: register small tables as views, run the SQL | Natural: pass DataFrames in | Both |
| Same code on Spark and DuckDB | Mostly (a common SQL subset) | Yes through SQLFrame, with known gaps | Both, tested on both |
| Static checks before running | SQL linters and parsers | Ruff, mypy and pure-function checks | Both |
| Performance | Same engine underneath; the optimizer sees all of it | Same; watch for Python UDFs and collect() | Even |
1c. Developer friendly
Troubleshooting: seeing the intermediate results
People can fix only what they can see. A harness that hides its steps is worse than a notebook. So every step must be visible: what was read, what each step produced, and where rows came and went.
| Feature | What the developer gets | State |
|---|---|---|
| Each SQL build is a file of named steps (CTEs) | Run any step alone in a SQL editor against the same inputs | Built: fact_otif.sql, fact_sales.sql |
| The harness logs the exact SQL it ran, with the window values filled in | Copy, paste, run | Proposed |
| Inspect mode for one run and a few windows | Every step's output saved to a scratch schema keyed by run id: changed rows, windows, build output before and after key lookup, rows rejected, check results | Proposed: off by default, sampled, auto-dropped after a few days |
| Replay one window locally | The run log holds the input ranges; a command rebuilds that window on a laptop from a copy of those inputs | Proposed |
| Row counts per step in the run log | See where rows were added or lost without rerunning | Built for silver stages and gold totals; per-join counts proposed |
| Checks name their rows | A failed check lists sample keys, not just a count | Partly built |
Where exactly records drop: the row ledger
Every run keeps a ledger of rows per step. When totals are wrong, the ledger shows the step where they changed, without rerunning anything.
| Ledger line | What is counted | Step |
|---|---|---|
| Rows read per input | Rows in the changed range of each input | Reading |
| Filtered | Rows removed by a declared filter (cancelled, test dealers), counted per filter name | Build |
| After each join | Rows out against rows in, per join; a lookup join must never add rows and never lose rows | Build |
| Unknown keys | Rows given -1 because the member is missing, per dimension | Key lookup |
| Rejected | Rows that broke a rule, with the reason, in a rejects table | Checks |
| Written | Rows in the replaced windows | Write |
| The balance | Rows read = filtered + rejected + written (or the aggregate's declared rule); totals of each measure equal silver's for the same windows | Before publish |
Catching many-to-many joins
A join that silently matches several rows multiplies totals. The harness checks for it at four points:
| Where | Check | What it catches |
|---|---|---|
| Before the join | Check the dimension side is unique on the join key (one row per surrogate key, or per business key and version range) | Stops the run and names the duplicate keys |
| At the join | Count rows out against rows in; for a lookup join they must be equal | Fails the build step that grew |
| SCD2 ranges | No overlapping valid_from / valid_to for a member, checked every run | An overlap would match two versions |
| Real many-to-many | Model it with a bridge table and weights that add to 1 | Totals stay right when an order line has two sales reps |
| Grain after the build | The fact is unique on its grain columns | Catches anything that slipped through |