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.

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.

1. sources2. silver3. the nightly run4. gold5. the answerorder systemorder_linestwo warehousesWH-A and WH-Bdeliveriesdealer listdealers(with history)the harnessreader: what changed?processor: the OTIF rulewriter: replace windowsfact_otifone row per linedim_dealerone row per versionon timeand in full,% per regionper day

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_iddealer_idqty_orderedpromised_date_seq (change number)
L0D135 Nov1001
L1D11010 Nov1041
L2D1510 Nov1042
L3D7811 Nov1043
L4D7413 Nov1044
L5D9613 Nov1045
L6TEST01113 Nov1046

silver.deliveries

delivery_idline_idwarehouseqtydelivered_date_seq
X0L0WH-A35 Nov2101
X1L1WH-A1010 Nov2201
X2L2WH-A511 Nov2202
X3L3WH-B611 Nov2203
X4L4WH-A413 Nov2204
X5L5WH-A613 Nov2205

silver.dealers (history kept)

dealer_idnameregionvalid_fromvalid_to
D1Sharma TyresNorth1 Novopen
D7Patel MotorsNorth1 Nov12 Nov
D7Patel MotorsWest12 Novopen

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:

lineordereddeliveredlast delivery vs promisedon time?in full?OTIF
L1101010 Nov vs 10 Novyesyes1
L25511 Nov vs 10 Nov (a day late)noyes0
L38611 Nov vs 11 Novyesno (2 short)0
L44413 Nov vs 13 Novyesyes1
L56613 Nov vs 13 Novyesyes1

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.

1bookmarks:where did I stop?2changed rowssince then3which windows(days) they touch4build: thebusiness rule5keys as ofthe event date6check andcount rows7replace windows,move bookmarkREADER (generic)PROCESSOR (per fact)HARNESS RULES (generic)WRITER (generic)

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.

change number (_seq)1001 (L0)watermark = 10011041 (L1)1042 (L2)1043 (L3)1044 (L4)1045 (L5)1046 (L6)high mark = 1046, fixed at start1047arrives during the run:waits for the next nightrun r1 reads only the rows between the two lines: 1002 to 1046

Read a closed range: after the watermark, up to the high mark. Anything newer waits.

inputwatermark (from last run)high mark (fixed at start)rows read
order_lines10011046L1 to L6 (6 rows)
deliveries21012205X1 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.

5 Novbuilt by r0,untouched6 Nov7 Nov8 Nov9 Nov10 NovL1, L2X1, X211 NovL3, X312 Nov13 NovL4, L5, L6X4, X514 NovDelivery X2 happened on 11 Nov but lands in the 10 Nov window: a delivery belongs to its order line's promised date.Rebuild only the green days, whole. Every other day of history is left alone.

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.

D7D9key 2: Patel Motors, Northkey 3: Patel Motors, Westnight 1: not in the dealer list yet; night 2: arrives as key 4, South8 Nov9 Nov10 Nov11 Nov12 Nov13 Nov14 Nov15 NovL3 promised 11 Nov: key 2L4 promised 13 Nov: key 3L5 on night 1: no version found, key -1 (unknown)

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:

linepromisedOTIFregion as of that date (right)region today (wrong)
L110 Nov1NorthNorth
L210 Nov0NorthNorth
L311 Nov0NorthWest
L413 Nov1WestWest

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.

6 lines read(windows 10, 11, 13 Nov)1 filteredtest dealer TEST015 after the joinmust equal 6 - 1 = 51 unknown dealerkey -1, limit 25%5 writtenthe balance: 6 read = 1 filtered + 0 rejected + 5 written. If it does not balance, nothing is written.

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_iddealer_idpromised_dateqty_orderedqty_deliveredon_timein_fullotifdealer_key_is_complete_run_id
L0D15 Nov331111Truer0
L1D110 Nov10101111Truer1
L2D110 Nov550101Truer1
L3D711 Nov861002Truer1
L4D713 Nov441113Falser1
L5D913 Nov66111-1Falser1

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_iddealer_idpromised_dateqty_orderedqty_deliveredon_timein_fullotifdealer_key_is_complete_run_id
L0D15 Nov331111Truer0
L1D110 Nov10101111Truer1
L2D110 Nov550101Truer1
L3D711 Nov880102Truer2
L4D713 Nov441113Truer2
L5D913 Nov661114Truer2

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.

windowrunwasnowwhy
11 Novr2{"qty_delivered": 6}{"qty_delivered": 8}late delivery for L3
13 Novr2{"unknown_keys": 1}{"unknown_keys": 0}dealer D9 arrived: L5 re-keyed

The dimension after night 2:

dealer_keydealer_idregionvalid_fromvalid_to
1D1North2024-01-01open
2D7North2024-03-0112 Nov
3D7West12 Novopen
4D9South1 Novopen