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

silverclean rows,history keptthe gold harness: same steps for every tablewhatchanged?whichwindows?build(pure)keys asof eventcheckreplacewindowsmetadata says what to build; one small build per fact says how to compute itgold starfacts anddimensions

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:

  1. 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.
  2. Read only the silver changes between the watermark and the high mark (change sequence, file list, or commit version, per input).
  3. 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.
  4. Overlay any correction plan due today (records from a corrections table, never edits in the source).
  5. Run the fact's build for those windows: a small SQL file or PySpark function, pure, reading only the frames it is given.
  6. Look up surrogate keys as of each row's event time; a missing member gets the unknown key (-1) and is counted.
  7. Mark each window complete or not: every input has loaded past the window's end, and every feed that owes data has sent it.
  8. 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.
  9. Replace only the windows whose numbers changed. A window published before and now different is logged as a restatement.
  10. 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 kindHow the harness builds itIn our build
Transaction factReplace changed windows of the event dateBuilt (order lines, scrap)
Periodic snapshotRebuild the open period; closed periods frozen; completeness per periodBuilt (monthly sales)
Accumulating snapshotUpsert by grain key as milestones arrive (the one mode that MERGEs rows)Owed: a declared fact mode
Factless factSame as transaction, with no measuresSame code path
Aggregate factRebuild the windows its source fact changedSame code path, input is a gold fact
Consolidated fact (plan vs actual)Each input added up to the common grain in the buildSame code path
DimensionsOne shared dimension pipeline, no code per dimensionBuilt
Bridge tables with weightsBuilt with their dimension; weights checked to add to 1Owed

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.

PartGeneric or per factWhere it lives
Reading changes, watermarks, high marksGenericSame for every table
Windows, lookback, window replaceGeneric, configuredWindow column and lookback are metadata
Surrogate keys, as-of lookups, unknown and inferred membersGeneric, configuredWhich dimensions and how: metadata
Completeness, restatements, correction plansGeneric, configuredWhich inputs owe data per window: metadata
Checks, row ledger, run log, upkeepGeneric, configuredThresholds per table: metadata
DimensionsGenericOne shared pipeline; a new dimension is metadata only
Grain, measures, fact kindPer fact, declaredMetadata, not code
The business rule itself (what counts as on time, how a discount is shared out, which orders are open)Per fact, codeA small SQL file or PySpark function; the only hand-written part

How to keep the common and the specific apart

RuleWhat it means
The build sees only frames and parametersbuild(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 businessNo dealer, OTIF or tyre word in the harness code; a test fails the build if one appears.
Shape is metadata, not codeGrain, window, lookback, inputs, lookups, checks: one file per gold table, validated when loaded.
Separate folders, separate ownersHarness 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 testsHarness 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 themThe 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.

NeedSQLPySpark DataFramesBetter
Readable by analysts and reviewersYes: the logic reads like the questionHarder: chained calls, Python around itSQL
Run a piece of it by handPaste a CTE into a SQL editor and lookNeeds a notebook and the input framesSQL
Complex logic (sessions, allocations, loops over rules, reuse)Gets long and repetitiveFunctions, parameters, reuse, unit tests per functionPySpark
Unit tests with small tablesPossible: register small tables as views, run the SQLNatural: pass DataFrames inBoth
Same code on Spark and DuckDBMostly (a common SQL subset)Yes through SQLFrame, with known gapsBoth, tested on both
Static checks before runningSQL linters and parsersRuff, mypy and pure-function checksBoth
PerformanceSame engine underneath; the optimizer sees all of itSame; 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.

FeatureWhat the developer getsState
Each SQL build is a file of named steps (CTEs)Run any step alone in a SQL editor against the same inputsBuilt: fact_otif.sql, fact_sales.sql
The harness logs the exact SQL it ran, with the window values filled inCopy, paste, runProposed
Inspect mode for one run and a few windowsEvery 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 resultsProposed: off by default, sampled, auto-dropped after a few days
Replay one window locallyThe run log holds the input ranges; a command rebuilds that window on a laptop from a copy of those inputsProposed
Row counts per step in the run logSee where rows were added or lost without rerunningBuilt for silver stages and gold totals; per-join counts proposed
Checks name their rowsA failed check lists sample keys, not just a countPartly 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 lineWhat is countedStep
Rows read per inputRows in the changed range of each inputReading
FilteredRows removed by a declared filter (cancelled, test dealers), counted per filter nameBuild
After each joinRows out against rows in, per join; a lookup join must never add rows and never lose rowsBuild
Unknown keysRows given -1 because the member is missing, per dimensionKey lookup
RejectedRows that broke a rule, with the reason, in a rejects tableChecks
WrittenRows in the replaced windowsWrite
The balanceRows read = filtered + rejected + written (or the aggregate's declared rule); totals of each measure equal silver's for the same windowsBefore publish

Catching many-to-many joins

A join that silently matches several rows multiplies totals. The harness checks for it at four points:

WhereCheckWhat it catches
Before the joinCheck 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 joinCount rows out against rows in; for a lookup join they must be equalFails the build step that grew
SCD2 rangesNo overlapping valid_from / valid_to for a member, checked every runAn overlap would match two versions
Real many-to-manyModel it with a bridge table and weights that add to 1Totals stay right when an order line has two sales reps
Grain after the buildThe fact is unique on its grain columnsCatches anything that slipped through