Home › Data engineering › Gold data modelling › Silver to gold › The worked example
Silver to gold · Learn it · 11 min read
Silver to gold, worked example: a tyre company over two nights
Read this page first if the rest feels like too much. Every idea in the silver-to-gold layer happens here to a handful of real rows, in a picture and a table. The next page continues with a crash, a careless join and the tests. This is one page of Silver to gold.
- The story: a tyre company and one question
- What one nightly run does, in seven steps
- Night 1 (run r1, 13 Nov), step by step
- Night 2 (run r2, 15 Nov): late data and a late dealer
Part A · 1
The story: a tyre company and one question
A tyre maker sells to dealers. A sales director asks one question every morning: what percent of order lines did we deliver on time and in full (OTIF), per region, per day? "On time" means the last delivery came on or before the promised date. "In full" means all the tyres ordered were delivered. A line counts towards OTIF only if both are true.
Silver already holds clean data from three sources. Gold must turn it into a fact and a dimension that answer the question. A program called the harness does that every night.
Sources land in silver; each night the harness turns silver into gold; gold answers the question.
Here is silver just before the night of 13 November. These are the actual rows the code ran on.
silver.order_lines
| line_id | dealer_id | qty_ordered | promised_date | _seq (change number) |
|---|---|---|---|---|
| L0 | D1 | 3 | 5 Nov | 1001 |
| L1 | D1 | 10 | 10 Nov | 1041 |
| L2 | D1 | 5 | 10 Nov | 1042 |
| L3 | D7 | 8 | 11 Nov | 1043 |
| L4 | D7 | 4 | 13 Nov | 1044 |
| L5 | D9 | 6 | 13 Nov | 1045 |
| L6 | TEST01 | 1 | 13 Nov | 1046 |
silver.deliveries
| delivery_id | line_id | warehouse | qty | delivered_date | _seq |
|---|---|---|---|---|---|
| X0 | L0 | WH-A | 3 | 5 Nov | 2101 |
| X1 | L1 | WH-A | 10 | 10 Nov | 2201 |
| X2 | L2 | WH-A | 5 | 11 Nov | 2202 |
| X3 | L3 | WH-B | 6 | 11 Nov | 2203 |
| X4 | L4 | WH-A | 4 | 13 Nov | 2204 |
| X5 | L5 | WH-A | 6 | 13 Nov | 2205 |
silver.dealers (history kept)
| dealer_id | name | region | valid_from | valid_to |
|---|---|---|---|---|
| D1 | Sharma Tyres | North | 1 Nov | open |
| D7 | Patel Motors | North | 1 Nov | 12 Nov |
| D7 | Patel Motors | West | 12 Nov | open |
Grey rows were already used by an earlier run (r0, on 5 Nov). Yellow rows are new since then. TEST01 is a test dealer that must never reach gold. Dealer D9 has placed an order (L5) but is not in the dealer list yet: that happens in real life all the time.
The OTIF rule for one line, worked by hand, so you can check the computer later:
| line | ordered | delivered | last delivery vs promised | on time? | in full? | OTIF |
|---|---|---|---|---|---|---|
| L1 | 10 | 10 | 10 Nov vs 10 Nov | yes | yes | 1 |
| L2 | 5 | 5 | 11 Nov vs 10 Nov (a day late) | no | yes | 0 |
| L3 | 8 | 6 | 11 Nov vs 11 Nov | yes | no (2 short) | 0 |
| L4 | 4 | 4 | 13 Nov vs 13 Nov | yes | yes | 1 |
| L5 | 6 | 6 | 13 Nov vs 13 Nov | yes | yes | 1 |
Part A · 2
What one nightly run does, in seven steps
Every gold table goes through the same seven steps, every night. Only step 4, the business rule, is written for each table. The rest is shared code (the harness) driven by a short settings file. Keep this picture in mind for the rest of the page.
Reader, processor, harness rules, writer. Only the green box is written per fact.
The settings file for fact_otif, as the code used it. This is all the harness needs to know about this table:
FACT_OTIF = {
"name": "fact_otif",
"grain": ["line_id"],
"window_col": "promised_date",
"lookback_days": 35,
"driver": "order_lines", # the input that carries the window column
"inputs": {"order_lines": {"bookmark": "_seq"}, # each input and its bookmark column;
"deliveries": {"bookmark": "_seq", "window_via": "line_id"}}, # a delivery's window is its line's
"filters": {"test dealers": "dealer_id NOT IN ('TEST01')"}, # declared filters, counted by name
"measures": ["qty_ordered", "qty_delivered", "otif"],
"dimension": {"table": "dim_dealer", "business_key": "dealer_id", "key": "dealer_key", "as_of": "promised_date"},
"owed_feeds": ["WH-A", "WH-B"], # who must send a file for each day
"max_unknown_rate": 0.25,
"params": {},
}
Part A · 3
Night 1 (run r1, 13 Nov), step by step
Steps 1 and 2: where did I stop, and what changed since?
The metadata store says the last run used order lines up to change 1001 and deliveries up to change 2101. Those are the watermarks. At the start, the run notes the newest change numbers that exist right now, 1046 and 2205. Those are the high marks, and they do not move during the run. The run reads only the rows in between.
Read a closed range: after the watermark, up to the high mark. Anything newer waits.
| input | watermark (from last run) | high mark (fixed at start) | rows read |
|---|---|---|---|
| order_lines | 1001 | 1046 | L1 to L6 (6 rows) |
| deliveries | 2101 | 2205 | X1 to X5 (5 rows) |
Who makes these change numbers, and can gold miss a change? In this story they are a simple column. In a real platform, read A8: a column bookmark can miss changes, because silver is written in many ways. Our own build avoids the problem differently: it recomputes every window in the lookback range each night and replaces only those whose numbers changed.
Why change numbers and not times? Two rows can have the same time, and clocks on different servers disagree. A change number only goes up and never repeats, so nothing is skipped or read twice.
Step 3: which windows do the changes touch?
The fact is rebuilt one day of promised date at a time. Each changed row points at one day. A delivery has no promised date of its own, so it points at the day of its order line.
Changes become a short list of days. Only those days are rebuilt.
Result: run r1 rebuilds 10 Nov, 11 Nov, 13 Nov. The 5 Nov window is never read again. That is why the run's cost follows the number of changes, not the size of history. With years of history, a night still rebuilds only a few days.
The run then reads every silver row of those three days, not just the changed ones. A day is rebuilt whole, so it must be built from the full set of its rows.
Step 4: the build (processor)
The build is the only code written for this fact. It gets the order lines and deliveries for the three days and returns one row per line with the OTIF flags. Note the first thing it does: add up deliveries per line before joining. Section 6 below shows what happens without that.
def build_otif(lines: DataFrame, deliveries: DataFrame, params: dict) -> DataFrame:
per_line = (deliveries.groupBy("line_id") # one row per line FIRST,
.agg(F.sum("qty").alias("qty_delivered"), # so the join below can
F.max("delivered_date").alias("last_delivered"))) # never multiply lines
j = lines.join(per_line, "line_id", "left")
qty_del = F.coalesce("qty_delivered", F.lit(0))
on_time = F.coalesce(F.col("last_delivered") <= F.col("promised_date"), F.lit(False)).cast("int")
in_full = (qty_del >= F.col("qty_ordered")).cast("int")
return j.select("line_id", "dealer_id", "promised_date", "qty_ordered",
qty_del.alias("qty_delivered"), on_time.alias("on_time"), in_full.alias("in_full"),
(on_time * in_full).alias("otif"))
Step 5: keys as of the event date
Dealer D7 moved from North to West on 12 Nov. Which region should L3 count under? The region that was true on the line's date. L3 was promised on 11 Nov, when D7 was North. The harness finds the dimension version whose valid_from to valid_to range holds the promised date and takes its key.
Each line gets the dealer version that was true on its own date.
The wrong way is to join each line to the dealer's current row. These rows come from the run. Compare the last two columns:
| line | promised | OTIF | region as of that date (right) | region today (wrong) |
|---|---|---|---|---|
| L1 | 10 Nov | 1 | North | North |
| L2 | 10 Nov | 0 | North | North |
| L3 | 11 Nov | 0 | North | West |
| L4 | 13 Nov | 1 | West | West |
With the right lookup, North's OTIF on these lines is 1 of 3 and West's is 1 of 1. With the wrong one, L3 jumps to West: North becomes 1 of 2 and West 1 of 2. Every past report would change each time a dealer moves. That is exactly what SCD Type 2 and as-of lookups prevent.
L5's dealer, D9, is not in the dimension at all. The line still goes into gold, but with key -1 ("unknown dealer"), and it is counted. It is not dropped: dropping it would quietly lose 6 tyres from every total. The code:
def lookup_keys(fact: DataFrame, dim: DataFrame, bk: str, key: str, as_of: str) -> DataFrame:
"""Find the dimension version that was true ON THE EVENT DATE. Missing member -> -1 (unknown)."""
d = dim.select(F.col(bk).alias("_bk"), key, "valid_from", "valid_to")
on = (F.col(bk) == F.col("_bk")) & (F.col(as_of) >= F.col("valid_from")) & (F.col(as_of) < F.col("valid_to"))
out = fact.join(F.broadcast(d), on, "left").drop("_bk", "valid_from", "valid_to")
return out.withColumn(key, F.coalesce(F.col(key), F.lit(-1)))
Step 6: complete or not, check, and count
Both warehouses owe a delivery file every day. On the night of 13 Nov, WH-B's file for 13 Nov has not come. So the 13 Nov window is written with _is_complete = false. Readers and dashboards can show "not final yet" instead of a number that will change. 10 and 11 Nov have both files, so they are complete.
Before writing anything, the harness counts rows at each step (the row ledger) and checks: one row per line, the ledger balances, the join added and lost nothing, and unknown dealers stay under their limit.
Every row is accounted for. A missing or extra row stops the run before it writes.
Step 7: replace the windows, then move the bookmark
The writer replaces the three days whole. It does not add rows on top: run it twice and you get the same table. Only after the write succeeds does the run log its work and move the watermarks to 1046 and 2205. If anything fails before that, the bookmark stays put and the next run simply does the work again.
def replace_windows(spark, df: DataFrame, path: str, window_col: str, windows: list):
"""Replace whole windows. Running it twice gives the same table; rows deleted at source simply vanish."""
if not DeltaTable.isDeltaTable(spark, path):
df.write.format("delta").save(path)
return
cond = f"{window_col} IN ({', '.join(repr(str(w)) for w in windows)})"
df.write.format("delta").mode("overwrite").option("replaceWhere", cond).save(path)
Gold after night 1 (yellow rows were written by r1):
| line_id | dealer_id | promised_date | qty_ordered | qty_delivered | on_time | in_full | otif | dealer_key | _is_complete | _run_id |
|---|---|---|---|---|---|---|---|---|---|---|
| L0 | D1 | 5 Nov | 3 | 3 | 1 | 1 | 1 | 1 | True | r0 |
| L1 | D1 | 10 Nov | 10 | 10 | 1 | 1 | 1 | 1 | True | r1 |
| L2 | D1 | 10 Nov | 5 | 5 | 0 | 1 | 0 | 1 | True | r1 |
| L3 | D7 | 11 Nov | 8 | 6 | 1 | 0 | 0 | 2 | True | r1 |
| L4 | D7 | 13 Nov | 4 | 4 | 1 | 1 | 1 | 3 | False | r1 |
| L5 | D9 | 13 Nov | 6 | 6 | 1 | 1 | 1 | -1 | False | r1 |
Look at L5: dealer_key -1 and not complete. Both get fixed on night 2, by themselves.
Part A · 4
Night 2 (run r2, 15 Nov): late data and a late dealer
Three things arrived in silver since night 1:
- Delivery X6: the last 2 tyres of L3, delivered on 14 Nov (change 2206).
- Dealer D9 appears in the dealer list (South), and becomes dimension key 4.
- WH-B's missing file for 13 Nov arrives.
The reader finds the windows: X6 belongs to L3, so 11 Nov. 13 Nov holds an unknown dealer that now exists, and it is still incomplete, so 13 Nov too. The run rebuilds 11 Nov, 13 Nov, and leaves 5 and 10 Nov alone.
| line_id | dealer_id | promised_date | qty_ordered | qty_delivered | on_time | in_full | otif | dealer_key | _is_complete | _run_id |
|---|---|---|---|---|---|---|---|---|---|---|
| L0 | D1 | 5 Nov | 3 | 3 | 1 | 1 | 1 | 1 | True | r0 |
| L1 | D1 | 10 Nov | 10 | 10 | 1 | 1 | 1 | 1 | True | r1 |
| L2 | D1 | 10 Nov | 5 | 5 | 0 | 1 | 0 | 1 | True | r1 |
| L3 | D7 | 11 Nov | 8 | 8 | 0 | 1 | 0 | 2 | True | r2 |
| L4 | D7 | 13 Nov | 4 | 4 | 1 | 1 | 1 | 3 | True | r2 |
| L5 | D9 | 13 Nov | 6 | 6 | 1 | 1 | 1 | 4 | True | r2 |
What changed: L3 now has 8 tyres delivered (in full), but the last one came on 14 Nov, after the promise, so it is still not OTIF. L5 now has key 4 instead of -1, so its tyres now count under South. 13 Nov is complete.
Restatements: numbers that changed after they were shown
11 Nov and 13 Nov were already published on night 1. Their numbers moved, so the harness logs each change: which window, what it was, what it is now, and which run. A finance reader can then see why a past number changed, instead of being surprised.
| window | run | was | now | why |
|---|---|---|---|---|
| 11 Nov | r2 | {"qty_delivered": 6} | {"qty_delivered": 8} | late delivery for L3 |
| 13 Nov | r2 | {"unknown_keys": 1} | {"unknown_keys": 0} | dealer D9 arrived: L5 re-keyed |
The dimension after night 2:
| dealer_key | dealer_id | region | valid_from | valid_to |
|---|---|---|---|---|
| 1 | D1 | North | 2024-01-01 | open |
| 2 | D7 | North | 2024-03-01 | 12 Nov |
| 3 | D7 | West | 12 Nov | open |
| 4 | D9 | South | 1 Nov | open |