Home › Learn › Hard problems by layer › Silver to gold

Hard problems by layer · 6 of 10

Silver to gold: hard problems and anti-patterns

The hard challenges and common anti-patterns of silver to gold in a data platform, with how each is solved and tested. This hop must make several sources agree on keys, grain, time and meaning, without losing or doubling a row.

↑ All layersSource systemsIngestionBronzeBronze to silverSilverSilver to goldGoldGold to semanticSemantic layerSemantic to UIAll challenges

Where we are

Silver to gold

SourcesystemsIngestionBronzeBronze tosilverSilverSilver togoldGoldGold tosemanticSemanticlayerSemantic toUI
The ten layers and hops from source to screen. The dark box is this page.

What this hop must achieve. Make several sources agree on keys, grain, time and meaning, without losing or doubling a row.

This is a hop: the step that moves data from one layer to the next. Handled by: gold harness, metadata store, data expectation services, observability.

One of the two hardest steps. This is the first place several sources meet, so keys, grain, time and meaning have to agree. Most of the work is business decisions, which is why they are declared and tested rather than coded by hand.

The hard part

13 challenges, and how they are solved

12 solvedStatus: 10 building, 2 designed, 1 gap
Solved means the engine does it once the decision is declared, and a test proves it. Assisted means the tool suggests and checks, and a person confirms. People decide means a business decision the tool records and enforces.

Every example comes from the tyre world, a synthetic tyre company whose fourteen simulated days carry planted problems with known right answers, so each fix is tested, not just described.

ChallengeWhat goes wrongTyre-world exampleHow we solve itVerdictStatus
Rows dropped in joins
S2G-01
An inner join to a missing dealer or material silently drops sales.Sales naming a dealer not yet in the dealer table.Left join plus an unknown member; rows in must equal rows out, with unmatched rows and duplicate keys counted separately so they cannot cancel out.

assurance rules LATE-02, PUB-01; decisions ADR-004 Deterministic hash surrogate keys with reserved members -1 and -2.

Shift-left checks that guard it: SL-CLEAN-31.

solvedbuilding
Conforming keys across sources
S2G-02
The same customer has different codes in different systems, so totals split or double.An order-system customer code, a dealer interface id and a name in partner files.Mapping tables kept by a steward, with suggested matches; deterministic hash surrogate keys.

assurance rules KEY-01; decisions ADR-004 Deterministic hash surrogate keys with reserved members -1 and -2.

assisteddesigned
Grain and double counting
S2G-03
Joining one to many multiplies quantities.Order lines joined to deliveries.Grain declared per fact and tested unique; joins aggregated to the fact's grain first; fan-out detected from captured query plans.

assurance rules DES-04.

Shift-left checks that guard it: SL-CLEAN-31, SL-CLEAN-32.

solvedbuilding
Which version of history applies
S2G-04
A sale is joined to today's dealer region instead of the region on the day of the sale.A dealer moving region mid-month.Joins as of the event date on history tables.

assurance rules SCD-04; decisions ADR-005 History dimensions are joined as of the event.

solvedbuilding
Completeness across sources
S2G-05
Four of six inputs have arrived; publishing gives partial numbers that look final.Lab results four hours late; partner files a day late.Inputs declared per fact, a completeness signal per input, and a declared policy per fact (wait, publish provisional, or wait then provisional with a maximum wait); every window carries a complete flag.

assurance rules ORCH-01, SERV-02; decisions ADR-018 A gold window is complete only when every input has caught up.

solvedbuilding
Late facts and restatements
S2G-06
October's numbers change in November, or late rows are ignored.Day 5, dealer D-0042's October file. (tested on day 5)Recompute exactly the windows the new data touched (replace, never append) and log the restatement.

assurance rules LATE-01, GOLD-01; decisions ADR-006 Facts are tables built by window recompute; materialised views only for simple aggregates.

Shift-left checks that guard it: SL-CLEAN-16.

solvedbuilding
Late reference data
S2G-07
A fact arrives before its dealer exists and is dropped or keyed wrong.Days 11 and 12, new dealer D-9001. (tested on day 11)Unknown member first, re-keyed when the dealer arrives.

assurance rules LATE-02.

Shift-left checks that guard it: SL-CLEAN-31.

solvedbuilding
Corrections
S2G-08
The source fixes a past value and old reports silently change, or never change.Day 10, twelve promised dates corrected. (tested on day 10)Corrections live in their own table and reach gold only for the windows their plan names, logged as a restatement.

assurance rules HIST-05; decisions ADR-017 Corrections live in their own table and reach gold through a backfill plan.

solvedbuilding
Deletes reaching gold
S2G-09
A deleted line still counts.The day-4 deleted order lines.Gold reads current, non-deleted rows when windows recompute.

assurance rules DEL-03.

solvedbuilding
Time zones and business calendars
S2G-10
Days cut at the wrong hour; fiscal periods computed differently in each report.Plants in India and Hungary.Business date per plant and one shared calendar, declared once.

assurance rules LATE-04.

Shift-left checks that guard it: SL-CLEAN-16.

solvedgap
Recompute cost
S2G-11
Rebuilding everything every night is slow and costly.Fourteen days of order lines.Only the windows the new data touched are recomputed.

assurance rules GOLD-01.

solvedbuilding
Functional join mistakes in hand-written queries
S2G-12
Inner instead of left, a wrong key, a many-to-many join; the query runs and the numbers are wrong.Two dealers missing and one duplicated, so the net row count looks almost normal.Declared join shapes checked against captured plans and observed counts of unmatched rows and duplicate keys.

assurance rules DES-04.

Shift-left checks that guard it: SL-CLEAN-31, SL-CLEAN-32.

solveddesigned
Proving gold is right
S2G-13
A quiet two percent error goes unnoticed for months.OTIF south November must equal 0.8961.Known answers from the synthetic world in tests; reconciliation from silver to gold in production.

assurance rules REC-01, SIM-01.

Shift-left checks that guard it: SL-CLEAN-30.

solvedbuilding

What not to do

Common anti-patterns

An anti-pattern is a habit that looks reasonable and keeps causing the same damage. These are the ones seen most often at this hop.

Anti-patternWhy it hurtsDo this instead
Inner join from fact to dimensionRows whose dimension has not arrived simply vanish, and totals quietly drop.Left join, give missing matches surrogate key -1, measure the orphan rate and re-key on the next recompute.
Joining incremental to incrementalJoining only the new rows of one input to only the new rows of another misses every match that arrived in a different batch.Detect changed windows incrementally, then read every input in full for those windows.
Appending to gold factsEvery retry or late file double-counts.Replace the whole window, or MERGE on the grain key.
Joining an SCD2 dimension to its current row onlyLast year's sales get this year's customer region, so history is rewritten.Join where event time is between valid_from and valid_to.
One giant SQL notebook mixing IO and logicIt cannot be unit-tested, reused or reviewed in parts.Write a pure build(inputs) function and let the harness do reads and writes.
Publishing before inputs are completeUsers see half a day and make decisions on it.Publish a window only when every input bookmark has passed its end.
Fixing data by hand in goldThe fix is overwritten on the next rebuild and leaves no audit trail.Add a row to the corrections table and run the backfill plan.

FAQ

Questions people ask

What are the hardest problems in the silver to gold step?

Rows dropped in joins; Conforming keys across sources; Grain and double counting; Which version of history applies; Completeness across sources.

What is the most common mistake at silver to gold?

Inner join from fact to dimension. Left join, give missing matches surrogate key -1, measure the orphan rate and re-key on the next recompute.

Next

Where to go next

Next: Gold

↑ All layers · All 69 challenges, filterable · Shift left: fail fast on data code · Medallion architecture