Home › Data engineering › Gold data modelling › Silver to gold › Building each kind of fact
Silver to gold · Learn it · 8 min read
Building each kind of fact: reader, processor, writer
The three parts of every build, then each of the six kinds with its reader, processor and writer, the traps, and the tested code. This is one page of Silver to gold.
Part B · 2
Reader, processor, writer: the three parts of every build
Reader and writer are shared; the processor is the business rule.
Reader. Its job is to answer: what changed since my bookmark, and which windows does that touch? Then it reads every row of those windows, plus small dimension tables. It never decides business meaning. It is generic: the settings say which inputs, which bookmark column and which window column.
Processor. A pure function, build(inputs, params). It gets DataFrames and returns DataFrames. It holds the one thing only your business knows: what "on time" means, how stock carries forward, how a plan meets actuals. Because it reads and writes nothing, you can test it with three rows on a laptop. In our examples the six builds are 4 to 15 lines each.
Writer. Its job is to put the rows into gold safely. For five kinds that means replacing whole windows, which is idempotent: twice is the same as once. For the accumulating snapshot it means a MERGE by key that never blanks a known milestone. Only after a successful write does the bookmark move.
Around all three, the harness adds what no single build should have to remember: keys as of the date, completeness, checks, the row ledger, restatements and the run log.
Part B · 3
The six kinds, each with an example and its reader, processor and writer
All six examples were run. The tables show their real output.
1. Transaction fact
One row per event. This is the OTIF fact from Part A. It is the most common kind and the default.
The tyre question: What happened to each order line? Grain: one row per order line
Rows: the gold table in Part A.
Traps: joining child rows (deliveries) without adding them up first; looking up the dealer's current row instead of the row true on the date.
2. Periodic snapshot
A photo of a level taken at the end of every period: stock, balances, headcount. The point is that every period gets a row, even when nothing moved, so a chart of stock over time has no gaps.
The tyre question: How many R15 tyres were in WH-A at the end of each day? Grain: one row per warehouse, product and day
| day | movements that day | closing stock (from the run) |
|---|---|---|
| 10 Nov | +100 in, -10 out | 90 |
| 11 Nov | none (quiet day) | 90 |
| 12 Nov | -6 out | 84 |
| 13 Nov | +50 in | 134 |
A change to one day's movement ripples into every later day.
def build_daily_stock(movements: DataFrame, days: DataFrame, params: dict) -> DataFrame:
pairs = movements.select("warehouse", "product").distinct()
grid = pairs.crossJoin(days.select(F.col("day").alias("snapshot_date"))) # a row per pair per day
m = movements.select("warehouse", "product", "move_date", "qty")
j = grid.join(m, ["warehouse", "product"], "left")
upto = F.when(F.col("move_date") <= F.col("snapshot_date"), F.col("qty")).otherwise(0) # count, never drop, the day
return (j.groupBy("warehouse", "product", "snapshot_date")
.agg(F.sum(upto).cast("int").alias("stock_on_hand")))
Traps: never add stock up across days (100 on Monday plus 100 on Tuesday is not 200 tyres): it adds up across warehouses, not across time. Use the last day or the average instead. The bug our test caught lived here: a day before the first movement was dropped instead of showing 0.
3. Accumulating snapshot
One row for each process instance (an order line), with a date column per milestone. The row is updated as the order moves. It answers 'where is everything right now, and how long do the steps take?'
The tyre question: Where is line L3 in its journey, and how many days did delivery take? Grain: one row per order line
| after | ordered_at | shipped_at | delivered_at | invoiced_at | days_to_deliver |
|---|---|---|---|---|---|
| day 1 | 8 Nov | 10 Nov | |||
| day 2 | 8 Nov | 10 Nov | 14 Nov | 6 | |
| day 3 | 8 Nov | 10 Nov | 14 Nov | 16 Nov | 6 |
Still one row after three days (rows in the table: 1). Each day filled in one more column.
MILESTONES = ["ordered", "shipped", "delivered", "invoiced"]
def build_line_lifecycle(events: DataFrame, params: dict) -> DataFrame:
p = events.groupBy("line_id").pivot("milestone", MILESTONES).agg(F.min("at"))
for m in MILESTONES:
p = p.withColumnRenamed(m, f"{m}_at")
return p.withColumn("days_to_deliver", F.datediff("delivered_at", "ordered_at"))def merge_by_key(spark, df: DataFrame, path: str, key: str, cols: list):
"""Only for accumulating snapshots: update the row for a key, never blank out a milestone already known."""
if not DeltaTable.isDeltaTable(spark, path):
df.write.format("delta").save(path)
return
t = DeltaTable.forPath(spark, path)
sets = {c: F.coalesce(F.col(f"s.{c}"), F.col(f"t.{c}")) for c in cols}
(t.alias("t").merge(df.alias("s"), f"t.{key} = s.{key}")
.whenMatchedUpdate(set=sets).whenNotMatchedInsertAll().execute())
Traps: this is the only fact kind where rows change after they are written, so every update is effectively a restatement; and a line that never reaches 'delivered' needs an 'open' flag, or averages hide it.
4. Factless fact
A fact with no numbers. The row itself says something happened: a visit, a training, an enrolment. You answer questions by counting rows, or by finding what is missing.
The tyre question: Which dealers did our reps visit, and which did nobody visit? Grain: one row per rep, dealer and day
| rep | dealer | visit_date | note |
|---|---|---|---|
| R-Asha | D1 | 10 Nov | kept |
| R-Asha | D1 | 10 Nov | same visit sent twice: dropped |
| R-Vik | D7 | 11 Nov | kept |
Visits counted: 2. Dealers nobody visited (dimension minus visits): D9.
Traps: 'what did NOT happen' cannot be read from the fact alone: you compare it with the dimension (all dealers) or keep a coverage fact (which dealers were in scope).
5. Aggregate fact
A small summary of a big fact, built so the busiest question is fast. It is built from gold, never straight from silver, so its numbers always agree with the detail.
The tyre question: OTIF per region per day, for the home dashboard. Grain: one row per day and region
| promised_date | region | lines | otif_lines | otif_pct |
|---|---|---|---|---|
| 5 Nov | North | 1 | 1 | 100.0 |
| 10 Nov | North | 2 | 1 | 50.0 |
| 11 Nov | North | 1 | 0 | 0.0 |
| 13 Nov | South | 1 | 1 | 100.0 |
| 13 Nov | West | 1 | 1 | 100.0 |
Traps: store the counts (lines, otif_lines), not only the percent: percents cannot be added up across regions or days. A summary nobody reads is pure cost.
6. Consolidated fact
Two or more sources about the same business, brought to one shared grain and joined, so they can be compared on one row.
The tyre question: Did each dealer order what we planned for November? Grain: one row per dealer and month
| dealer | month | plan_qty | actual_qty | gap |
|---|---|---|---|---|
| D1 | 2026-11 | 20 | 18 | -2 |
| D7 | 2026-11 | 10 | 12 | 2 |
| D9 | 2026-11 | 5 | 6 | 1 |
D1: 3 + 10 + 5 = 18 tyres against a plan of 20. D7: 8 + 4 = 12 against 10. TEST01 is filtered out.
def build_plan_vs_actual(plan: DataFrame, lines: DataFrame, params: dict) -> DataFrame:
actual = (lines.withColumn("month", F.date_format("promised_date", "yyyy-MM"))
.groupBy("dealer_id", "month").agg(F.sum("qty_ordered").cast("int").alias("actual_qty")))
j = plan.join(actual, ["dealer_id", "month"], "full_outer")
return (j.select("dealer_id", "month",
F.coalesce("plan_qty", F.lit(0)).alias("plan_qty"),
F.coalesce("actual_qty", F.lit(0)).alias("actual_qty"))
.withColumn("gap", F.col("actual_qty") - F.col("plan_qty")))
Traps: joining at different grains (a monthly plan to daily orders) repeats the plan on every day and multiplies it; an inner join hides dealers that appear on one side only.
All six on one page
| Kind | Reader finds windows by | Processor | Writer | Verdict |
|---|---|---|---|---|
| Transaction | changed rows to their event days | row rule | replace windows | Pursue: the default |
| Periodic snapshot | changed rows to that day and every later day | running level per period | replace from first changed period | Pursue for levels and balances |
| Accumulating snapshot | changed milestone events | pivot milestones into one row | MERGE by key | Pursue for multi-step processes |
| Factless | changed events to their days | dedupe events | replace windows | Pursue for coverage questions |
| Aggregate | windows the source fact rebuilt | group by | replace windows | Pursue only where a question is slow and frequent |
| Consolidated | changed periods of each source | bring each to one grain, full outer join | replace windows | Pursue for plan vs actual, budget vs spend |