Home › Data engineering › Gold data modelling › Silver to gold › Fact or dimension?

Silver to gold · Decide · 4 min read

Is a delivery a fact or a dimension? And what if facts are versioned?

A short page on two modelling questions: what a delivery is, and what to do when facts themselves have versions. This is one page of Silver to gold.

Part E · 1

In this example, is a delivery a fact, a dimension, or neither?

A delivery is a fact. It is an event with numbers (6 tyres, on 11 Nov). In our example it is not a gold table of its own. It is a silver input that the OTIF build adds up per line, because the question is about order lines. If people start asking delivery questions (how many trucks per warehouse, how many split deliveries), add fact_delivery at one row per delivery, with keys to the line, warehouse and date. It is never a dimension. Dimensions describe the who, what and where you filter and group by, and nobody filters "by delivery".

ThingWhat it is in goldWhy
Order lineFact (the grain of fact_otif)An event with numbers: tyres ordered. One row per line.
Order (header)Degenerate dimension: order_id kept on the factNothing to describe beyond its id; no table of its own needed.
DeliveryFact. Here a silver input folded into fact_otif; it can also be its own gold factAn event with numbers (tyres, a date). Nobody filters 'by delivery' the way they filter by dealer, so it is not a dimension.
DealerDimension, SCD Type 2Describes who: name, region. Changes slowly; history matters for as-of lookups.
Warehouse, product, dateDimensionsDescribe where, what, when.
Delivery file receivedNeither: control data in the metadata storeSays whether a day is complete. It describes the pipeline, not the business.

A quick test: does it have numbers you add up, and does it happen at a moment? Then it is a fact. Does it describe something you filter or group by, and change slowly? Then it is a dimension. Does it describe the pipeline rather than the business? Then it belongs in the metadata store.

Part E · 2

What if facts are versioned too?

Dimensions keep versions because descriptions change: D7 moved region. Facts change too: L3 went from 6 to 8 tyres delivered, and L5's dealer went from unknown to D9. There are three ways to keep a fact's history. Here are the real rows from our two nights, done each way.

Way 2: versioned fact rows

Same idea as SCD Type 2, but on the fact's grain and dated by when we recorded it. "What did the dashboard show on 14 Nov?" becomes one filter: recorded_from on or before 14 Nov, and recorded_to empty or after it.

line_idqty_deliveredotifdealer_keyrecorded_fromrecorded_to
L36022026-11-13 02:002026-11-15 02:00
L38022026-11-15 02:00open (current)
L561-12026-11-13 02:002026-11-15 02:00
L56142026-11-15 02:00open (current)

Note L5: its version 1 has key -1 and its version 2 has key 4. Both use the dealer version true on the promised date. Two different dates are at work: the business date picks the dimension version, and the record date picks the fact version. Keeping both is called bitemporal.

Way 3: adjustment rows

line_idqty_deliveredotif_run_idmeaning
L360r1first published
L3+20r2the late 2 tyres, added as a new row

Sum the rows and you get 8, today's truth. Filter to run r1 and you get 6, what was first published. Nothing is ever updated. But it only works for numbers you can add: there is no sensible "+1 on time".

Which to choose

WayHowGood atWeak atVerdict
1. Current state + restatement log (our default)Gold shows today's truth. Each changed published number is logged with old and new value. Table versions (time travel) cover recent history.Simple queries; one row per grain'What did we show on 14 Nov?' needs the log or time travel, and time travel expiresPursue: the default
2. Versioned fact rows (SCD Type 2 on the fact's grain)Every change closes the old row (recorded_to) and adds a new one (recorded_from).Exact 'as we reported it on any day' with one filterMore rows; every query must pick current rows, or totals doublePursue where auditors or regulators ask 'what did you report then?'
3. Adjustment rows (accounting style)Rows are never changed; a correction adds the difference as a new row (+2 tyres).Append-only; summing gives today's truth; filtering by run gives the pastWorks only for numbers you can add; flags and ratios cannot be 'adjusted'Pursue for money ledgers; Drop for flags like on-time

A useful rule: if silver keeps the full history of its sources (it does in our platform), you can always rebuild any past state of gold. So you need versioned gold facts only when you must show "exactly what we published then" quickly and provably, for example to an auditor. In our harness, both writer modes are written and tested (below) but not yet wired in as fact modes. That is owed.

The two writer modes (builds.py), with table-driven tests in test_builds.py
def plan_fact_versions(current: DataFrame, new: DataFrame, keys: list, measures: list, recorded_at) -> tuple:
    """current = open versions in gold; new = rows just built. Returns (keys to close, rows to insert)."""
    cur = current.select(*keys, *[F.col(m).alias(f"_old_{m}") for m in measures])
    j = new.join(cur, keys, "left")
    changed = j.where(" OR ".join(f"NOT (`{m}` <=> `_old_{m}`)" for m in measures))
    to_close = changed.where(F.col(f"_old_{measures[0]}").isNotNull()).select(*keys)
    to_insert = (changed.drop(*[f"_old_{m}" for m in measures])
                 .withColumn("recorded_from", F.lit(recorded_at)).withColumn("recorded_to", F.lit(None).cast("timestamp")))
    return to_close, to_insert


def plan_adjustments(published_totals: DataFrame, new: DataFrame, keys: list, measures: list, run_id: str) -> DataFrame:
    old = published_totals.select(*keys, *[F.col(m).alias(f"_old_{m}") for m in measures])
    j = new.join(old, keys, "left")
    diffs = [(F.col(m) - F.coalesce(F.col(f"_old_{m}"), F.lit(0))).alias(m) for m in measures]
    out = j.select(*keys, *diffs).withColumn("_run_id", F.lit(run_id))
    return out.where(" OR ".join(f"{m} != 0" for m in measures))