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.
- How incremental loading works
- Watermarks
- Grain and windows
- Completeness and restatements
- Late facts and late dimensions
- History load, reloads and hiding a bad batch
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.
Read a closed range of changes, map them to windows, rebuild only those windows.
| Table kind | Incremental pattern | Why |
|---|---|---|
| Facts | Rebuild changed windows from silver | A 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 changed | Dimensions are small; history comes from silver's SCD2 rows, keys from the key map stay stable |
| Very large dimensions | MERGE only changed members | Worth it above tens of millions of members |
| Aggregate tables | Rebuild the windows the atomic fact changed | Same windows, same checks |
| Full rebuild | Only on a declared backfill, with its own run id and the same checks | Never as the daily default: cost grows with history, not with change |
What gold reads from silver incrementally, and what in full
| Silver input | How gold reads it | Note |
|---|---|---|
| Transactional tables with a change feed (order lines, deliveries, production) | Incremental: rows changed since the watermark, by change sequence | Then 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 lookback | Late 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 delivered | Re-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 changes | Switch to incremental above tens of millions of members |
| Dimension slices during a fact build | Only the members whose keys appear in the changed rows, as of their event times | Broadcast when small |
| Full daily copies from the source (a materials copy) | Silver turns the copy into changes; gold reads silver like any other table | A short copy is held in bronze, never passed on (mass-delete guard) |
| Gold facts read by aggregates | Incremental: the windows the atomic fact changed in this run | Never the whole fact |
| Backfill, history load, rule change | Full for a declared date range, through the same harness | Logged, checked, compared before release |
3. Watermarks
Watermarks
| Topic | Rule |
|---|---|
| What it is | A 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 in | For 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 mark | Fixed at the start of the run, so the run reads a closed range and a rerun reads the same range. |
| Moves last | Only after the write and the checks succeed. A crash before that repeats work; it never leaves a gap. |
| Never past unsaved data | If a window was held back (incomplete, failed check), the watermark does not pass it. |
| Second job: completeness | An 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 event | Moving 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.
| Topic | Rule |
|---|---|
| Window column | The date that decides where a row belongs in gold: promised date for OTIF, sale month for sales, cure hour for scrap. |
| Window size | The grain of time the fact is rebuilt in: day, month or hour. Usually the time part of the fact's grain. |
| Lookback | How 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 merge | The whole window is recomputed and replaced. Running it twice gives the same table; a deleted source row disappears without a delete rule. |
| On incomplete | Publish the window flagged incomplete (readers see it is not final), or hold it until complete. Declared per fact. |
| Restatement | A 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.
A month owed files by many dealers is complete only when the last owed file is in.
6. Late data
Late facts and late dimensions
| Case | What the harness does | Example |
|---|---|---|
| Late fact inside the lookback | Its window is rebuilt; the change is a restatement if the window was already published | Late sensor readings, a delivery recorded a day late |
| Late fact outside the lookback | Goes through a correction plan with the windows it may touch, approved and dated | A 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 restates | One dealer's October file arrives late |
| Re-sent file | Refused at landing as a duplicate; gold does not change and nothing is restated | The same file sent twice under a new name |
| Dimension member arrives after its facts | Facts get the unknown key (-1) and are counted as orphans; re-keyed in the run where the member appears | Orders for a new dealer before the dealer list has it |
| Source deletes | Silver marks the row deleted; the window rebuild leaves it out of gold | Cancelled 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.
| Step | How | Why |
|---|---|---|
| Load silver's history first | Years 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 dates | Facts can only look up versions that exist |
| Build gold with the same harness, period by period | A backfill run per month (or quarter), oldest first, with its own run id, the same build and the same checks | No special one-off scripts that skip the rules |
| Dimensions before facts | Every dimension holds its full history before any fact is looked up against it | As-of lookups land on the right version |
| Size compute for the load, then shrink | A bigger cluster or more parallel periods only for the history load | History is a one-time cost; do not keep paying it |
| Reconcile with the old system | Per period: counts and totals against the legacy warehouse or finance close | Proves the history before anyone reports on it |
| Quiet the alarms | Restatement and freshness alerts off for backfill runs (still logged) | A history load is not an incident |
| Freeze closed periods | Mark periods already closed by finance as frozen once reconciled | Later loads cannot move them by accident |
| Hand over to incremental | Set each input's watermark to the point the history load read up to, in the same logged step, then start the daily runs | No 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 entered | Reload | Reload from |
|---|---|---|
| Gold build was wrong (a rule or a bug) | Fix the build; backfill the affected windows from silver | Silver |
| Silver was wrong (a stage or a setting) | Rebuild silver from bronze for the affected batches, then rebuild the gold windows they touch | Bronze |
| A file was landed wrong | Re-land it from the landing archive, then silver and gold | Landing archive |
| The source sent wrong data | Ask the source to resend (or re-extract); the new delivery flows through normally | Source |
| Rules for every reload | A 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.
| Option | How | State or verdict |
|---|---|---|
| Stop it before readers see it | Checks run before publish: a failing batch writes nothing and the watermark stays | Best: built |
| Void the batch | Mark the batch void in the metadata store; every row carries its batch key, so silver is rebuilt from bronze without it | Built in silver (void plus rebuild) |
| Rebuild the gold windows it touched | Lineage from batch to windows says which; the harness rebuilds just those and logs the restatement | Owed: the gold rerun after a silver rebuild |
| Hide it at once, while the rebuild runs | Views and metric views filter out rows of void batches, or the windows are flagged 'under review' so readers see the number is not final | Proposed |
| Roll the table back (time travel restore) | Fast, but it also undoes every good batch after the bad one | Pursue with care: emergencies only |
| Delete the rows by hand | No record of why; the next run may bring them back | Drop |