The Report That Changed Its Mind

Thursday 20 August, 09:41. Nordhavn — one of six brands we hold a commission agreement with — emails our partner manager:

“Your July statement bills us €6,723.02 commission for June. The partner dashboard you gave us access to says €5,322.44 for the same month. Which one do we pay?”

Same warehouse, same table, same SQL. One run 3 July, one run 20 August. In between, on 12 August at 11:20, the category team re-tiered two brands in the admin: Nordhavn Premium → Standard, Bergfeld Value → Standard. Real, approved, effective forward.

dim_brand is a full nightly reload, so the loader ran the equivalent of UPDATE dim_brand SET tier = 'Standard' WHERE brand_id = 1. That is Type 1: the row holds the current value and the prior value is not archived anywhere. Because every fact joins dim_brand on brand_id at query time, that one UPDATE didn’t change August — it changed every month we have ever recorded.

Gross reconciliation break €1,807.68; the net (−€993.48) hides it, because one brand’s loss is another’s gain. Rates: Premium 24%, Standard 19%, Value 15%.

A fact is an event: it happened, at a time, and it is finished. A dimension is a description: it is true right now, and “right now” keeps moving. Joining a finished event to a moving description is how last quarter changes.

Stop updating, start appending. Each version gets a validity interval, a flag, and a surrogate key (brand_sk) that identifies this version of this brand, distinct from the natural key brand_id. Six brands, two changes → 8 rows.

brand_id is no longer unique. That is the point.

Three load-bearing details:

• Half-open intervals valid_from <= d < valid_to. Old row ends on the 12th, new row starts on the 12th. Closing at run_date − 1 invents a rule about “a day” that breaks the first time a change lands at 11:20. Use >= and <, never BETWEEN.
• Far-future sentinel 9999-12-31, not NULL — keeps the comparison a plain range check instead of a special case every consumer must remember.
• is_current is a convenience, not the truth. It is derivable from the dates; never let a load path set the flag without setting the dates.

-- 1. close every open row whose tracked attributes changed
UPDATE dim_brand d SET valid_to = :run_date, is_current = false
  FROM stg_brand s
 WHERE d.brand_id = s.brand_id AND d.is_current
   AND d.attr_hash <> s.attr_hash;          -- md5(tier || name || …)

-- 2. append the new version
INSERT INTO dim_brand (brand_id, brand_name, tier, attr_hash,
                       valid_from, valid_to, is_current)
SELECT s.brand_id, s.brand_name, s.tier, s.attr_hash,
       :run_date, DATE '9999-12-31', true
  FROM stg_brand s
  LEFT JOIN dim_brand d ON d.brand_id = s.brand_id AND d.is_current
 WHERE d.brand_id IS NULL OR d.attr_hash <> s.attr_hash;

-- 3. rows present yesterday and absent today are NOT deleted:
--    close them and set a deleted flag. Deletes destroy history too.

The attribute hash turns “did anything change?” into one comparison and records which columns are tracked. :run_date is Lesson 04’s logical run date, not CURRENT_DATE — re-running the 12 Aug load on the 20th must still start the version on the 12th. A Type 2 loader that reads the wall clock is not idempotent, and its history bends on every backfill.

-- A · current row. WRONG for any past period.
JOIN dim_brand b ON b.brand_id = f.brand_id
-- B · as-of join
JOIN dim_brand b ON b.brand_id = f.brand_id
                AND f.sold_date >= b.valid_from AND f.sold_date < b.valid_to
-- C · surrogate key resolved once at load, frozen onto the fact
JOIN dim_brand b ON b.brand_sk = f.brand_sk

B and C give the same answer; C is the one to build. The as-of join is a range join — slower, easy to write wrong, and every query has to remember it. Resolving the version once in the loader turns every downstream query back into an equality join that is correct by construction. The analyst cannot get it wrong because there is nothing left to get wrong.

Same 1,405 June orders, three joins:

Every bucket moves by more than half and the total is identical to the cent. Revenue monitors, row counts and grand-total reconciliations all stay silent. Only the split moves, and nothing watches the split. (Nordhavn is 61.6% of June’s Premium GMV — with six hundred brands instead of six, the same failure is a permanent smear rather than a visible jump.)

Series rule: compute before claiming invisibility. Here Type 1 fails in both directions, and the second failure is worse. Weekly Premium GMV, each series compared against its own min–max band over the ten weeks before the re-tier:

Under Type 1, Premium GMV is €3,735–€4,430 every week of the quarter, before and after, because the Premium bucket has only ever contained the brands that are Premium today. A top brand left the tier and the tier report did not move by a euro.

Type 1 does not hide a change. It destroys the evidence that there was anything to compare against — then reports past and present as if they had always been the same.

The deciding question is not how often the value changes — it is why:

• always wrong, now fixed → Type 1 (no period existed when it was right)
• was right, now different → Type 2 (overwriting deletes a fact about the world)

So: if this value changes, should a report about last quarter change with it? For anything money, contracts or targets are computed from, the answer is always no.

Where Type 2 is the wrong answer

A nightly-changing attribute is not slowly changing. Three escapes, in order:

1. Put it on the fact. We already store commission_rate_at_sale on fct_sale — which is why the money owed is recoverable even though the tier was overwritten. Values agreed at event time belong to the event.
2. Mini-dimension. Band the volatile attributes into a small dim_seller_profile of a few hundred combinations, referenced from the fact.
3. Periodic snapshot fact. One row per entity per month with attributes as they stood.

Our 8 rows pass all four checks across six keys. Move Nordhavn’s valid_to forward one day and check 2 reports gap/overlap at 2026-08-13|2026-08-12 — a window where Nordhavn is both Premium and Standard and every sale in it counts twice. These belong in the audit step of Lesson 06’s Write–Audit–Publish, gating the swap in Lesson 07’s DAG. An untested Type 2 dimension is more dangerous than a Type 1 one, because it looks like history.

Late-arriving facts. A sale dated 10 Aug arriving on 15 Aug resolves to brand_sk = 1, Premium, 24%. Against the current row it gets Standard, 19% — a €13,000 sale billed €650 short. Same rule as Lesson 04: decide everything from the event’s date.

1. Which dimension columns can change, and which are UPDATEd in place today? (Ask for the loader, not the schema.)
2. If we re-ran last quarter’s board-deck queries this morning, would the numbers match the deck? Has anyone checked?
3. For anything we bill or pay commission on — is the rate stored on the fact row at event time, or looked up from a dimension at query time?
4. Do facts join on natural keys or on surrogate keys resolved at load?
5. Does anything test our dimensions for exactly-one-current, gaps and overlaps?
6. Lesson 08’s re-coded item_condition was this same failure in other clothes. Which enum columns have been re-coded, and can we still read data from before the change?

1. Reproduce. Run seed.py. Confirm 4,303 sales, 1,405 June orders, invoice €20,972.00 vs re-run €19,978.52. No RNG — figures match to the cent.
2. Build both dimensions in SQLite/DuckDB. Write the June report three ways; assert B and C are byte-identical and A is not.
3. Write the loader (detect–close–append, attribute hash, :run_date). Run it twice for the same run date — if the dimension grows, it isn’t idempotent.
4. Write the three tests and prove each one fails.
5. Break it on purpose: backfill 1 July with CURRENT_DATE instead of the logical date.
6. Now do sellers: a segment that changes for ~2% of sellers monthly. Show row growth, then re-solve with a mini-dimension.

Push as de-practice/09-scd-type-2. README: name one real dimension attribute at Buddy & Selly that a past number depends on and that is overwritten today — and what the last change to it did to last quarter’s report.

Facts are immutable; dimensions are not; every warehouse assumes otherwise until a partner reads two different numbers for the same month. Give dimension rows a lifespan instead of a value, resolve the version once at load, and store the surrogate key on the fact so no downstream query can ask the wrong question.

Smallest useful action: take the one dimension attribute a bill, payout or target depends on and stop it being overwritten — store the value on the fact at event time, or give the dimension valid_from / valid_to. Ten minutes of loader, and last quarter stops moving.

• Slowly changing dimension (SCD) — a descriptive attribute that changes occasionally and unpredictably.
• Type 1 / Type 2 — overwrite the row vs close it and append a version.
• Natural key vs surrogate key — brand_id identifies the brand; brand_sk identifies one version of it.
• Validity interval — [valid_from, valid_to), half-open, far-future sentinel on the open row.
• As-of join — resolving a dimension version by the event’s date rather than by “now”.
• Attribute hash — hash over the tracked columns; the cheap change test and a record of what is tracked.
• Late-arriving fact — an event reaching the warehouse after the dimension moved on.
• Mini-dimension — volatile attributes banded into their own small dimension.
• Periodic snapshot fact — one row per entity per period, attributes as they stood.