Home › Data engineering › Gold data modelling › Silver to gold › Rulebook 3: testing and decisions

Silver to gold · The rulebook · 13 min read

The harness rulebook 3: testing, reliability, cost, decisions and FAQ

The last third of the rulebook: testing without a cluster, reliability and cost, what exists already, what is built, the decisions, and the questions people ask. This is one page of Silver to gold.

7. Testing

Pure functions, and testing locally with synthetic data

adapter readssilver framesbuild(inputs, params)-> rowsno reads, no writes,no clock, no randomadapter writesgold windowstests: small tables in,expected rows outsame tests on Spark and DuckDB,a laptop, in seconds

Keep every decision in a pure build; keep reading and writing in adapters; then the build can be tested anywhere.

PracticeWhat it means
The build is a functionbuild(inputs, params) takes the input frames and returns the fact's rows. It reads nothing, writes nothing, never asks the clock or a random source.
I/O lives in adaptersReading silver and writing gold windows sit behind ports (reader, table writer, metadata store). Swapping Delta for Iceberg or Spark for DuckDB is a new adapter, not new logic.
Same code, several enginesThe same stage and build tests run on Spark and on DuckDB (through SQLFrame): DuckDB on every push in about two minutes with no Java, Spark nightly and on Databricks.
Table-driven testsEach case is a few input rows and the expected output rows: new member, late fact, deleted line, duplicate, window straddling a month.
A synthetic worldA seeded generator plays a whole company (orders, deliveries, dealer files, presses, lab results) and keeps its own truth (the oracle): what gold must hold on each day.
Planted incidentsThe world plants problems on a calendar: late files, re-sent files, deletes, a dealer that appears late, an out-of-order change, a backdated correction. Each names the check that must catch it.
Compressed calendarFor the daily loop, every incident is moved into three days, so a short run meets all of them.
Compare layer by layerAfter each simulated day, bronze, silver and gold are compared with the oracle; a difference fails the run and names the layer.

More on testing data code without a cluster: Shift left. The synthetic world idea: DataTwin.

Regression: proving a change broke nothing

A change to one build, one setting or the harness itself can move numbers far away. Regression testing is layered, from seconds to a full simulated run, and the slowest layer is the one that catches the surprises.

LayerWhat it comparesWhenState
Unit tests on each buildTable-driven cases: small tables in, expected rows out, on Spark and DuckDBEvery commitBuilt
Generated files unchanged unless meantMetric views, dashboards, bundle files are generated and compared with the committed copies; any drift failsEvery commitBuilt
Known answersFixed questions (OTIF for one region and month) asked of the metric views; the answer must equal the expected numberEvery runBuilt
The synthetic world, every incidentThree compressed days with eleven planted incidents; every layer must equal the world's truthEvery change to the harness or a build; daily on the platformBuilt
Side-by-side data diffRun the new version on the same inputs into a scratch schema; compare with today's gold per window: row counts, keys, measure totals; any difference must be explained before releaseBefore every release that touches a build or metadataProposed
Speed and costStep times and rows per step from the run log, compared with the last runs; a step twice as slow is flaggedEvery runPartly built (timings logged; comparison proposed)
Release gateA change ships only when all of the above pass; a planted bad change must be blockedEvery releaseBuilt for the observability gate; extend to gold builds

8. Why a harness

How the harness answers each problem

Each problem on the gold modelling page needs a rule. Written once in the harness, the rule holds for every table, and a planted incident proves it every day.

ProblemHow the harness handles itProven by
Double counting from joinsKeys are looked up once in the build; facts carry surrogate keys; grain uniqueness checked every runGrain check
Duplicates and replaysDuplicates dropped in silver; gold windows are replaced whole, so a rerun is a no-opStream replay incident (DUP-04)
Re-sent filesRefused at landing; gold unchanged, nothing restatedRe-sent file incident (DUP-01)
Late dataChanged windows with a lookback; completeness flags; restatements loggedLate file incident (LATE-01)
Late dimension membersUnknown member (-1), counted, re-keyed when the member appearsLate dealer incident (LATE-02)
Source deletesSoft delete in silver, window rebuild in goldDeleted lines incident (DEL-01)
Out-of-order historySilver orders SCD2 by the source's change sequence; gold looks up as of the eventOut-of-order change incident (SCD-02)
Corrections to closed periodsA correction plan touches only its declared windowsBackdated correction incident (HIST-05)
A crash halfwayWatermark moves last; window replace is idempotentRerun tests
Silent lossRows in equal rows out; totals reconcile with silver; unknown-key rate limitedReconciliation checks
Cost creepOnly changed windows are rebuilt; work grows with change, not historyRun log timings
Every table done differentlyOne harness, metadata per table, one small build per factCode review: the build is the only per-fact code

9. Reliability

Reliability practices

PracticeHowWhat it prevents
Idempotent writesReplace whole windows; never append to goldA retry or rerun never doubles a number
Watermark moves lastAfter write and checks, in the metadata storeA crash repeats work, never skips it
Pinned inputsFix the high mark per input at run startA run reads a closed range; reruns match
Check before publishGrain, balance, reconciliation, thresholds before the writeBad windows never reach readers
Completeness on every row_is_complete per window, visible to readers and metric viewsNobody mistakes a partial month for a final one
Run log per runInputs and ranges read, windows rebuilt, counts, checks, restatements, timeEvery number has evidence
One writer per tableA lock or a single job per gold tableNo two runs fight over a window
Dependency orderDimensions before facts; facts before aggregatesLookups always see finished dimensions
Backfill is a runA date range with its own run id, same plan, same checksNo special scripts that skip the rules
Tested on a world, not only unitsDaily runs on the synthetic world with planted incidentsThe whole hop is proven, not just functions

10. Cost

Cost practices

PracticeWhy it saves
Rebuild changed windows onlyWork follows the number of changes, not the size of history
Right-size the lookbackEach extra day of lookback is rebuilt every run; set it from how late data really arrives
Cluster by the window columnA window replace touches a few files, not the whole table
Broadcast small dimensionsKey lookups without shuffling the fact
Trigger on change, with a minimum intervalNo run when silver did not change; no run every minute for a chatty source
Regular upkeepWindow replaces leave small files and old snapshots; compact and expire on a schedule (we run it every seven days)
Serverless for spiky loadsNo idle cluster between runs; classic clusters only for steady heavy loads
Tag every runDomain, table and run id on the job, so the cost of each table is visible
Aggregates only where readA materialized view or aggregate nobody queries is refresh cost with no benefit

10b. Reuse first

Has the industry solved this layer?

Partly. The common mechanics (incremental by time window, SCD2 dimensions, tests, safe releases) are well solved by open tools. What no tool we know gives out of the box is the gold-specific part: keys looked up as of the event with late members, a window being complete only when every owed feed has delivered, a logged restatement when a published number changes, correction plans, and a row ledger. Our recommendation is to build only that part, and to put it on top of an open tool where a team already uses one, not to rebuild what dbt or SQLMesh already do well.

Tool or approachWhat it solvesWhat it leaves to youVerdict
dbt (incremental and microbatch models, snapshots, tests)Incremental loads by time with a lookback (microbatch), SCD2 dimensions from snapshots, generic tests, a surrogate-key macro (a hash), a huge communityAs-of key lookups with late members, per-feed completeness, restatement log, a row ledger, correction plansPursue with care: a strong base for SQL builds; the harness parts become macros or a wrapper
SQLMeshTime-range incremental models with lookback (the same idea as our windows), audits, a plan that shows what will change, table diffs, virtual environments for safe releasesSame gaps as dbt for keys, completeness and restatementsPursue with care: the closest match to this harness's window model and regression diff
Databricks Lakeflow (AUTO CDC, materialized views)SCD Type 1 and 2 dimensions from change feeds, incremental refresh of materialized views, managed pipelinesFacts with surrogate keys as of the event, completeness, restatements; works only inside Lakeflow pipelinesPursue with care: AUTO CDC for dimensions only where silver keeps no history; materialized views for some aggregates. Not for facts. See the SCD2 page
Kimball automation tools (WhereScape and others)Generate star schemas, SCD handling, surrogate keys and load code from a modelCommercial and tool-shaped; testing on a synthetic world; open formats varyPursue with care where a team wants a tool, not code
Data vault automation (datavault4dbt and others)Hubs, links, satellites generated from metadataA vault is an integration layer; gold stars are still built on topDrop for gold itself
Hand-written notebooks per tableNothing sharedEverythingDrop

11. Status

What is built, and what is owed

PartStateEvidence
The harness: windows, keys as of event, completeness, checks, restatements, run logBuiltGold equal to the simulated truth every day: 14 days on a laptop (1/10 data size); 3 days on Databricks Free Edition with eleven planted incidents (13 Nov 2026, gold step about 200 seconds)
Incremental reads with watermarks per inputBuiltIngestion bookmarks: change sequence for database feeds, file lists for drops, cursor for the API. Gold uses each input's watermark for completeness and recomputes every window in the lookback range, replacing only windows whose numbers changed
A change-log reader for gold (Delta change data feed, Iceberg changelog)OwedTo skip unchanged runs, shrink the recompute to changed windows, and raise changes older than the lookback. Tested 14 Nov 2026: the change log caught every write style; column bookmarks missed deletes, overwrites and out-of-order commits
Pure builds tested on two enginesBuiltSame stage and build tests on Spark and DuckDB; a 3-day local run on DuckDB takes about 2 minutes
Iceberg and Delta through one table portBuiltUnity Catalog managed Iceberg on Databricks
Gold rerun after a silver rebuildOwedWhen a bad batch is voided and silver rebuilt, the affected gold windows must be redone
Full data size on DatabricksOwedRuns so far use 1/10 of the data
A written rule for what counts as a restatementOwedThe harness decides today; the spec must say it
Date and plant dimensionsOwedSee the gold modelling page

12. Decisions

Decisions with verdicts

DecisionChoiceVerdict
How gold tables are builtOne harness with metadata; one small build per factPursue
Fact refreshReplace changed windows with a lookbackPursue
Row-level MERGE into large factsOnly for accumulating snapshots that update by grain keyPursue with care
Watermark unitThe silver table's commit version, read through its change log; a column bookmark only where there is no change log; never a wall-clock timestamp alonePursue
Incomplete windowsPublish flagged by default; hold only where readers cannot handle a flagPursue
CorrectionsCorrection plans with declared windows, never edits in the sourcePursue
TestingPure builds, table-driven tests on two engines, a synthetic world with planted incidents every dayPursue
Notebook per gold table-Drop
Full rebuild every night-Drop except for small tables and declared backfills

FAQ

Questions people ask

How does incremental loading from silver to gold work?

Each gold table keeps a watermark per input. A run fixes a high mark, reads only the changes between the two, turns the changed rows into the windows they touch (plus a lookback), rebuilds only those windows from silver, checks them, replaces them in gold, and moves the watermark last.

What is a watermark in data engineering?

A bookmark of the last change a table has already processed, stored per table and per input in a metadata store. It should be a value that only goes up and never ties, such as a change sequence or a commit version, and it moves only after a run has written and checked its output.

Why rebuild whole windows instead of merging rows?

A window (a day, a month, an hour) recomputed and replaced whole is idempotent, removes deleted rows without delete rules, and is cheap because only changed windows are touched. Row-level MERGE is kept for accumulating snapshots that update by key.

How do I test a silver-to-gold pipeline locally?

Keep each fact's build a pure function of its input frames, put reading and writing in adapters, and test the build with small tables in and expected rows out. Then run the whole hop on a synthetic world with planted incidents, comparing gold with the world's own truth after every day, on a laptop with DuckDB or local Spark.

How does gold handle late-arriving data?

Late facts inside the lookback rebuild their window and are logged as restatements; older ones go through a correction plan. A window owed data by several feeds stays flagged incomplete until all have arrived. Facts whose dimension member is missing get the unknown key and are re-keyed when it appears.

Why are there so many types of fact table?

Because business questions come in different shapes. Events that happened once need a row per event (transaction). Levels like stock need a row per period even on quiet days (periodic snapshot). Processes with several steps need one row updated as it moves (accumulating snapshot). Events with nothing to measure need rows without numbers (factless). Busy questions need a summary (aggregate), and comparing sources needs one shared grain (consolidated). The kind decides how a pipeline reads changes and how it writes.

Can the silver to gold layer be built as a Databricks declarative pipeline?

It can: AUTO CDC builds SCD Type 2 dimensions, and materialized views build facts and aggregates, with expectations for quality. But it does not give control over which days are rebuilt, a restatement log, correction plans, a full row ledger, checks across rows or stable integer keys. We recommend a harness for gold, run as a Databricks job, with materialized views only as an option for some aggregates.

What are reader, processor and writer in a data pipeline?

The reader finds what changed since the last run and reads the rows of the windows it touches. The processor is a pure function holding the business rule, which takes data in and returns data out. The writer puts the result into the target safely, by replacing whole windows or merging by key, and only then is the bookmark moved.

Can a fact table be written in SQL?

Yes. The business rule of a fact is usually a few CTEs: add child rows up per parent, apply the rule, select the grain. The harness or dbt does the rest: picks the days, looks up keys, checks and writes. Our SQL and PySpark builds return the same rows.

How does a custom gold harness compare with dbt?

For the business rule they are equal: the same SQL. dbt is better at lineage, docs, adapters and community. A harness is better at choosing which days to rebuild, checking before writing (dbt's data tests run after the table is written), completeness, restatements and stable integer keys. Teams on dbt can add those as macros and a write-audit-publish step.

Is a delivery a fact or a dimension?

A fact: it is an event with numbers. It can be an input to another fact (order lines with OTIF flags) or its own fact at one row per delivery. Dimensions describe what you filter and group by, such as dealer, warehouse and product.

What is a versioned fact table?

A fact that keeps every version of a row, dated by when it was recorded, like SCD Type 2 on the fact's grain. It answers 'what did we report on that day?'. The alternatives are current-state facts with a restatement log, or append-only adjustment rows.

What are the types of fact tables?

Six: transaction (a row per event), periodic snapshot (a row per thing per period, quiet periods included), accumulating snapshot (one row per process, updated as it moves), factless (rows with no numbers, counted), aggregate (a summary built from another fact) and consolidated (two sources brought to one grain to compare).

What is the difference between a periodic and an accumulating snapshot?

A periodic snapshot adds a new row for every thing every period, like a daily account balance; old rows never change. An accumulating snapshot keeps one row per process, like a loan application, and updates it as each step happens.

Can incremental loading from silver to gold miss records?

Yes, if gold bookmarks by a column the writers fill in: a number set only on insert misses updates; a source change number misses hard deletes, full overwrites and out-of-order commits; even a silver batch number misses deletes and out-of-order commits. Reading the table's own change log (Delta change data feed or Iceberg changelog), bookmarked by commit version, caught every case in our test.

What happens to Delta change data feed on a full overwrite?

Every old row is recorded as a delete and every new row as an insert, so a downstream reader sees the whole table as changed. It misses nothing but rebuilds everything. Writing only the differences with a MERGE keeps the change feed small.