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

every gold table, every nightREADERwhat changed since my bookmark?which windows does it touch?read all rows of those windowsPROCESSORbuild(inputs, params) -> rowsthe business rule onlyno reads, writes or clockWRITERreplace whole windows(or merge by key, for one kind)then move the bookmarkgeneric: settings say whatwritten per fact: the only hand-written partgeneric: the fact kind says howaround all three: keys as of the event, checks, the row ledger, completeness, restatements, the run log

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.

ReaderRows changed since the bookmark, turned into the days (promised dates) they touch.
ProcessorAdd deliveries up per line, apply the on-time and in-full rule.
WriterReplace those days whole. Re-running gives the same table.

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

daymovements that dayclosing stock (from the run)
10 Nov+100 in, -10 out90
11 Novnone (quiet day)90
12 Nov-6 out84
13 Nov+50 in134
10 Novclosing stock11 Novclosing stock12 Novclosing stock13 Novclosing stockA movement corrected on 10 Nov changes the closing stock of 10 Nov AND every day after it.So the reader turns a change into 'from that day to today', not just 'that day'.

A change to one day's movement ripples into every later day.

ReaderMovements changed since the bookmark. Unlike a transaction fact, a change on 10 Nov touches every day from 10 Nov to today, because stock is carried forward.
ProcessorFor each warehouse, product and day, add up every movement up to the end of that day. Quiet days carry the previous level.
WriterReplace the days from the earliest changed day to today. Periods closed by finance are frozen and only change through a correction plan.
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

afterordered_atshipped_atdelivered_atinvoiced_atdays_to_deliver
day 18 Nov10 Nov
day 28 Nov10 Nov14 Nov6
day 38 Nov10 Nov14 Nov16 Nov6

Still one row after three days (rows in the table: 1). Each day filled in one more column.

ReaderMilestone events changed since the bookmark (ordered, shipped, delivered, invoiced).
ProcessorTurn events into one row per line with a column per milestone; work out days between them.
WriterMERGE by line_id, the one kind that updates rows in place. A milestone already known is never blanked out by a later event that does not mention it.
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

repdealervisit_datenote
R-AshaD110 Novkept
R-AshaD110 Novsame visit sent twice: dropped
R-VikD711 Novkept

Visits counted: 2. Dealers nobody visited (dimension minus visits): D9.

ReaderVisit records changed since the bookmark, turned into visit days.
ProcessorKeep rep, dealer and day; drop exact repeats so a re-sent visit counts once.
WriterReplace those days whole.

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_dateregionlinesotif_linesotif_pct
5 NovNorth11100.0
10 NovNorth2150.0
11 NovNorth100.0
13 NovSouth11100.0
13 NovWest11100.0
ReaderThe days the atomic fact (fact_otif) rebuilt in this run, from its run log. Nothing else.
ProcessorJoin the fact to the dimension by its key (already looked up as of the date), group by day and region.
WriterReplace those days. Runs after the fact, never before (dependency order).

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

dealermonthplan_qtyactual_qtygap
D12026-112018-2
D72026-1110122
D92026-11561

D1: 3 + 10 + 5 = 18 tyres against a plan of 20. D7: 8 + 4 = 12 against 10. TEST01 is filtered out.

ReaderThe plan (small, read in full) and the order lines of the months that changed.
ProcessorAdd actual orders up to dealer and month first, then a full outer join with the plan, so a dealer with a plan but no orders (or orders but no plan) still shows.
WriterReplace the changed months.
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

KindReader finds windows byProcessorWriterVerdict
Transactionchanged rows to their event daysrow rulereplace windowsPursue: the default
Periodic snapshotchanged rows to that day and every later dayrunning level per periodreplace from first changed periodPursue for levels and balances
Accumulating snapshotchanged milestone eventspivot milestones into one rowMERGE by keyPursue for multi-step processes
Factlesschanged events to their daysdedupe eventsreplace windowsPursue for coverage questions
Aggregatewindows the source fact rebuiltgroup byreplace windowsPursue only where a question is slow and frequent
Consolidatedchanged periods of each sourcebring each to one grain, full outer joinreplace windowsPursue for plan vs actual, budget vs spend