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.
Where we are
Silver to gold
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
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.
| Challenge | What goes wrong | Tyre-world example | How we solve it | Verdict | Status |
|---|---|---|---|---|---|
| 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. | solved | building |
| 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. | assisted | designed |
| 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. | solved | building |
| 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. | solved | building |
| 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. | solved | building |
| 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. | solved | building |
| 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. | solved | building |
| 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. | solved | building |
| 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. | solved | building |
| 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. | solved | gap |
| 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. | solved | building |
| 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. | solved | designed |
| 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. | solved | building |
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-pattern | Why it hurts | Do this instead |
|---|---|---|
| Inner join from fact to dimension | Rows 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 incremental | Joining 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 facts | Every retry or late file double-counts. | Replace the whole window, or MERGE on the grain key. |
| Joining an SCD2 dimension to its current row only | Last 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 logic | It 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 complete | Users 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 gold | The 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