Home › Data engineering › Gold data modelling › Silver to gold › Rulebook 2: loading rules

Silver to gold · The rulebook · 8 min read

The harness rulebook 2: incremental loading, watermarks, windows, late data

The middle third of the rulebook: how a run finds what changed, which windows it rebuilds, when a window is complete, and what happens to late data. This is one page of Silver to gold.

2. Incremental loading

How incremental loading works

Incremental means a run does work in proportion to what changed, not to how much history there is. In gold that takes two moves: find the changed rows, then turn them into the windows they touch.

change sequencewatermark (last run)high mark (fixed at start)this run reads only these changeschanged rows touchpromised dates 10, 11 and 14 Nov+ lookbackwindows 10 to 14 Novrebuild only thosewindows from silver

Read a closed range of changes, map them to windows, rebuild only those windows.

Table kindIncremental patternWhy
FactsRebuild changed windows from silverA window is small (a day, a month, an hour), rebuilt whole, so the write is idempotent and needs no row-by-row MERGE
Dimensions (SCD Type 2)Through one shared dimension pipeline; replace the dimension when it changedDimensions are small; history comes from silver's SCD2 rows, keys from the key map stay stable
Very large dimensionsMERGE only changed membersWorth it above tens of millions of members
Aggregate tablesRebuild the windows the atomic fact changedSame windows, same checks
Full rebuildOnly on a declared backfill, with its own run id and the same checksNever as the daily default: cost grows with history, not with change

What gold reads from silver incrementally, and what in full

Silver inputHow gold reads itNote
Transactional tables with a change feed (order lines, deliveries, production)Incremental: rows changed since the watermark, by change sequenceThen the whole changed windows are recomputed from silver's current rows
Event streams (sensor readings)Incremental: by arrival or commit, mapped to event-time windows with a lookbackLate events inside the lookback rebuild their window
File-per-period feeds (a dealer's monthly file)Incremental per feed: each feed's newest file is a mark; a window waits until every owed feed has deliveredRe-sent copies are refused before silver
Small dimensions' sources (dealers, materials, presses)Full read of silver's current and history rows: small, so a full read is cheaper than tracking changesSwitch to incremental above tens of millions of members
Dimension slices during a fact buildOnly the members whose keys appear in the changed rows, as of their event timesBroadcast when small
Full daily copies from the source (a materials copy)Silver turns the copy into changes; gold reads silver like any other tableA short copy is held in bronze, never passed on (mass-delete guard)
Gold facts read by aggregatesIncremental: the windows the atomic fact changed in this runNever the whole fact
Backfill, history load, rule changeFull for a declared date range, through the same harnessLogged, checked, compared before release

3. Watermarks

Watermarks

TopicRule
What it isA bookmark per gold table and per input: the last change this table has already used. Stored in the metadata store's run log, never in a notebook or a file.
What it is measured inFor silver inputs: the table's own commit version (Delta change data feed, Iceberg snapshot), so it does not matter how silver was written (see A8). Elsewhere, a value that only goes up and never ties: an ordered file list, an API cursor. Timestamps tie and go backwards with clock skew; use them only when nothing better exists, with an overlap and a dedupe.
Fixed high markFixed at the start of the run, so the run reads a closed range and a rerun reads the same range.
Moves lastOnly after the write and the checks succeed. A crash before that repeats work; it never leaves a gap.
Never past unsaved dataIf a window was held back (incomplete, failed check), the watermark does not pass it.
Second job: completenessAn input's watermark says how far its data is loaded. A window is complete when every input's watermark is past the window's end.
Reset is an eventMoving a watermark back (to reload) is a logged action with a reason, never a silent edit.

4. Grain and windows

Grain and windows

The fact's grain (one row per order line) says what a row is. The window says how the fact is rebuilt: by the time part of that grain. Declaring both is what lets the harness rebuild a day without touching the rest.

TopicRule
Window columnThe date that decides where a row belongs in gold: promised date for OTIF, sale month for sales, cure hour for scrap.
Window sizeThe grain of time the fact is rebuilt in: day, month or hour. Usually the time part of the fact's grain.
LookbackHow far back a late or changed row may still land (35 days for OTIF, 3 months for sales, 48 hours for scrap). Changes older than that go through a correction plan.
Replace, not mergeThe whole window is recomputed and replaced. Running it twice gives the same table; a deleted source row disappears without a delete rule.
On incompletePublish the window flagged incomplete (readers see it is not final), or hold it until complete. Declared per fact.
RestatementA window that was published and later changes is recorded with its old and new numbers, the reason (late data or a correction plan) and the run.

5. Completeness

Completeness and restatements

A number shown before all its data arrived is a promise that will change. The harness says so on the row (_is_complete) and records the change when it comes.

October window600 dealers owe a fileday 1: 599 filesday 2: dealer 42 lateday 3: re-sent copypublished, flagged incompletecomplete; restatement loggedduplicate skipped; nothing changes

A month owed files by many dealers is complete only when the last owed file is in.

A real lesson from our tests: our test oracle once expected October to complete on the day the late dealer's file arrived. The harness kept October open, because other dealers' files were still owed that day, and it was right. Completeness is "every owed feed has delivered", not "the late one arrived".

6. Late data

Late facts and late dimensions

CaseWhat the harness doesExample
Late fact inside the lookbackIts window is rebuilt; the change is a restatement if the window was already publishedLate sensor readings, a delivery recorded a day late
Late fact outside the lookbackGoes through a correction plan with the windows it may touch, approved and datedA promised date corrected after the month closed
Late feed (a file per dealer)The window stays incomplete until every feed that owes data has sent it, then completes and restatesOne dealer's October file arrives late
Re-sent fileRefused at landing as a duplicate; gold does not change and nothing is restatedThe same file sent twice under a new name
Dimension member arrives after its factsFacts get the unknown key (-1) and are counted as orphans; re-keyed in the run where the member appearsOrders for a new dealer before the dealer list has it
Source deletesSilver marks the row deleted; the window rebuild leaves it out of goldCancelled order lines hard-deleted at source

Inferred members (a placeholder dimension row with a real key, filled in later) are the alternative to the unknown key; both are compared on the gold harness spec.

6b. Loads that are not daily

History load, reloads and hiding a bad batch

The history load

The first load of years of history is just a long backfill: the same harness, period by period, oldest first, with the same checks. What is special is the order, the size and the handover to daily runs.

StepHowWhy
Load silver's history firstYears of source history land in bronze and silver once (snapshots, history tables or a full extract); SCD2 dimensions are built from it with their real valid_from datesFacts can only look up versions that exist
Build gold with the same harness, period by periodA backfill run per month (or quarter), oldest first, with its own run id, the same build and the same checksNo special one-off scripts that skip the rules
Dimensions before factsEvery dimension holds its full history before any fact is looked up against itAs-of lookups land on the right version
Size compute for the load, then shrinkA bigger cluster or more parallel periods only for the history loadHistory is a one-time cost; do not keep paying it
Reconcile with the old systemPer period: counts and totals against the legacy warehouse or finance closeProves the history before anyone reports on it
Quiet the alarmsRestatement and freshness alerts off for backfill runs (still logged)A history load is not an incident
Freeze closed periodsMark periods already closed by finance as frozen once reconciledLater loads cannot move them by accident
Hand over to incrementalSet each input's watermark to the point the history load read up to, in the same logged step, then start the daily runsNo gap and no overlap between history and daily loads (our harness owes a written rule for this step)

Reloads: reload from the nearest correct copy

Pick the reload by where the fault entered, and start from the nearest copy that is still right. Most reloads are small: a few windows rebuilt from silver.

Where the fault enteredReloadReload from
Gold build was wrong (a rule or a bug)Fix the build; backfill the affected windows from silverSilver
Silver was wrong (a stage or a setting)Rebuild silver from bronze for the affected batches, then rebuild the gold windows they touchBronze
A file was landed wrongRe-land it from the landing archive, then silver and goldLanding archive
The source sent wrong dataAsk the source to resend (or re-extract); the new delivery flows through normallySource
Rules for every reloadA logged backfill run with a reason; watermarks moved back only as a logged event; restatements logged; a data diff against current gold before the swap-

Hiding a bad batch

A bad batch that reached readers must disappear from their numbers quickly, and leave a record of why.

OptionHowState or verdict
Stop it before readers see itChecks run before publish: a failing batch writes nothing and the watermark staysBest: built
Void the batchMark the batch void in the metadata store; every row carries its batch key, so silver is rebuilt from bronze without itBuilt in silver (void plus rebuild)
Rebuild the gold windows it touchedLineage from batch to windows says which; the harness rebuilds just those and logs the restatementOwed: the gold rerun after a silver rebuild
Hide it at once, while the rebuild runsViews and metric views filter out rows of void batches, or the windows are flagged 'under review' so readers see the number is not finalProposed
Roll the table back (time travel restore)Fast, but it also undoes every good batch after the bad onePursue with care: emergencies only
Delete the rows by handNo record of why; the next run may bring them backDrop