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.
- Pure functions, and testing locally with synthetic data
- How the harness answers each problem
- Reliability practices
- Cost practices
- Has the industry solved this layer?
- What is built, and what is owed
- Decisions with verdicts
- Questions people ask
7. Testing
Pure functions, and testing locally with synthetic data
Keep every decision in a pure build; keep reading and writing in adapters; then the build can be tested anywhere.
| Practice | What it means |
|---|---|
| The build is a function | build(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 adapters | Reading 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 engines | The 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 tests | Each case is a few input rows and the expected output rows: new member, late fact, deleted line, duplicate, window straddling a month. |
| A synthetic world | A 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 incidents | The 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 calendar | For the daily loop, every incident is moved into three days, so a short run meets all of them. |
| Compare layer by layer | After 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.
| Layer | What it compares | When | State |
|---|---|---|---|
| Unit tests on each build | Table-driven cases: small tables in, expected rows out, on Spark and DuckDB | Every commit | Built |
| Generated files unchanged unless meant | Metric views, dashboards, bundle files are generated and compared with the committed copies; any drift fails | Every commit | Built |
| Known answers | Fixed questions (OTIF for one region and month) asked of the metric views; the answer must equal the expected number | Every run | Built |
| The synthetic world, every incident | Three compressed days with eleven planted incidents; every layer must equal the world's truth | Every change to the harness or a build; daily on the platform | Built |
| Side-by-side data diff | Run 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 release | Before every release that touches a build or metadata | Proposed |
| Speed and cost | Step times and rows per step from the run log, compared with the last runs; a step twice as slow is flagged | Every run | Partly built (timings logged; comparison proposed) |
| Release gate | A change ships only when all of the above pass; a planted bad change must be blocked | Every release | Built 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.
| Problem | How the harness handles it | Proven by |
|---|---|---|
| Double counting from joins | Keys are looked up once in the build; facts carry surrogate keys; grain uniqueness checked every run | Grain check |
| Duplicates and replays | Duplicates dropped in silver; gold windows are replaced whole, so a rerun is a no-op | Stream replay incident (DUP-04) |
| Re-sent files | Refused at landing; gold unchanged, nothing restated | Re-sent file incident (DUP-01) |
| Late data | Changed windows with a lookback; completeness flags; restatements logged | Late file incident (LATE-01) |
| Late dimension members | Unknown member (-1), counted, re-keyed when the member appears | Late dealer incident (LATE-02) |
| Source deletes | Soft delete in silver, window rebuild in gold | Deleted lines incident (DEL-01) |
| Out-of-order history | Silver orders SCD2 by the source's change sequence; gold looks up as of the event | Out-of-order change incident (SCD-02) |
| Corrections to closed periods | A correction plan touches only its declared windows | Backdated correction incident (HIST-05) |
| A crash halfway | Watermark moves last; window replace is idempotent | Rerun tests |
| Silent loss | Rows in equal rows out; totals reconcile with silver; unknown-key rate limited | Reconciliation checks |
| Cost creep | Only changed windows are rebuilt; work grows with change, not history | Run log timings |
| Every table done differently | One harness, metadata per table, one small build per fact | Code review: the build is the only per-fact code |
9. Reliability
Reliability practices
| Practice | How | What it prevents |
|---|---|---|
| Idempotent writes | Replace whole windows; never append to gold | A retry or rerun never doubles a number |
| Watermark moves last | After write and checks, in the metadata store | A crash repeats work, never skips it |
| Pinned inputs | Fix the high mark per input at run start | A run reads a closed range; reruns match |
| Check before publish | Grain, balance, reconciliation, thresholds before the write | Bad windows never reach readers |
| Completeness on every row | _is_complete per window, visible to readers and metric views | Nobody mistakes a partial month for a final one |
| Run log per run | Inputs and ranges read, windows rebuilt, counts, checks, restatements, time | Every number has evidence |
| One writer per table | A lock or a single job per gold table | No two runs fight over a window |
| Dependency order | Dimensions before facts; facts before aggregates | Lookups always see finished dimensions |
| Backfill is a run | A date range with its own run id, same plan, same checks | No special scripts that skip the rules |
| Tested on a world, not only units | Daily runs on the synthetic world with planted incidents | The whole hop is proven, not just functions |
10. Cost
Cost practices
| Practice | Why it saves |
|---|---|
| Rebuild changed windows only | Work follows the number of changes, not the size of history |
| Right-size the lookback | Each extra day of lookback is rebuilt every run; set it from how late data really arrives |
| Cluster by the window column | A window replace touches a few files, not the whole table |
| Broadcast small dimensions | Key lookups without shuffling the fact |
| Trigger on change, with a minimum interval | No run when silver did not change; no run every minute for a chatty source |
| Regular upkeep | Window replaces leave small files and old snapshots; compact and expire on a schedule (we run it every seven days) |
| Serverless for spiky loads | No idle cluster between runs; classic clusters only for steady heavy loads |
| Tag every run | Domain, table and run id on the job, so the cost of each table is visible |
| Aggregates only where read | A 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 approach | What it solves | What it leaves to you | Verdict |
|---|---|---|---|
| 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 community | As-of key lookups with late members, per-feed completeness, restatement log, a row ledger, correction plans | Pursue with care: a strong base for SQL builds; the harness parts become macros or a wrapper |
| SQLMesh | Time-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 releases | Same gaps as dbt for keys, completeness and restatements | Pursue 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 pipelines | Facts with surrogate keys as of the event, completeness, restatements; works only inside Lakeflow pipelines | Pursue 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 model | Commercial and tool-shaped; testing on a synthetic world; open formats vary | Pursue with care where a team wants a tool, not code |
| Data vault automation (datavault4dbt and others) | Hubs, links, satellites generated from metadata | A vault is an integration layer; gold stars are still built on top | Drop for gold itself |
| Hand-written notebooks per table | Nothing shared | Everything | Drop |
11. Status
What is built, and what is owed
| Part | State | Evidence |
|---|---|---|
| The harness: windows, keys as of event, completeness, checks, restatements, run log | Built | Gold 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 input | Built | Ingestion 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) | Owed | To 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 engines | Built | Same 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 port | Built | Unity Catalog managed Iceberg on Databricks |
| Gold rerun after a silver rebuild | Owed | When a bad batch is voided and silver rebuilt, the affected gold windows must be redone |
| Full data size on Databricks | Owed | Runs so far use 1/10 of the data |
| A written rule for what counts as a restatement | Owed | The harness decides today; the spec must say it |
| Date and plant dimensions | Owed | See the gold modelling page |
12. Decisions
Decisions with verdicts
| Decision | Choice | Verdict |
|---|---|---|
| How gold tables are built | One harness with metadata; one small build per fact | Pursue |
| Fact refresh | Replace changed windows with a lookback | Pursue |
| Row-level MERGE into large facts | Only for accumulating snapshots that update by grain key | Pursue with care |
| Watermark unit | The 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 alone | Pursue |
| Incomplete windows | Publish flagged by default; hold only where readers cannot handle a flag | Pursue |
| Corrections | Correction plans with declared windows, never edits in the source | Pursue |
| Testing | Pure builds, table-driven tests on two engines, a synthetic world with planted incidents every day | Pursue |
| 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.