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".
| Thing | What it is in gold | Why |
|---|---|---|
| Order line | Fact (the grain of fact_otif) | An event with numbers: tyres ordered. One row per line. |
| Order (header) | Degenerate dimension: order_id kept on the fact | Nothing to describe beyond its id; no table of its own needed. |
| Delivery | Fact. Here a silver input folded into fact_otif; it can also be its own gold fact | An event with numbers (tyres, a date). Nobody filters 'by delivery' the way they filter by dealer, so it is not a dimension. |
| Dealer | Dimension, SCD Type 2 | Describes who: name, region. Changes slowly; history matters for as-of lookups. |
| Warehouse, product, date | Dimensions | Describe where, what, when. |
| Delivery file received | Neither: control data in the metadata store | Says 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_id | qty_delivered | otif | dealer_key | recorded_from | recorded_to |
|---|---|---|---|---|---|
| L3 | 6 | 0 | 2 | 2026-11-13 02:00 | 2026-11-15 02:00 |
| L3 | 8 | 0 | 2 | 2026-11-15 02:00 | open (current) |
| L5 | 6 | 1 | -1 | 2026-11-13 02:00 | 2026-11-15 02:00 |
| L5 | 6 | 1 | 4 | 2026-11-15 02:00 | open (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_id | qty_delivered | otif | _run_id | meaning |
|---|---|---|---|---|
| L3 | 6 | 0 | r1 | first published |
| L3 | +2 | 0 | r2 | the 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
| Way | How | Good at | Weak at | Verdict |
|---|---|---|---|---|
| 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 expires | Pursue: 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 filter | More rows; every query must pick current rows, or totals double | Pursue 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 past | Works 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))