Slowly-Changing Dimensions & History

The problem: dimension attributes change over time

In the last chapter you built a star schema: a fact_rentals table surrounded by dimensions like dim_gpu and dim_customer. Dimensions described who and what. The unspoken assumption was that a dimension row is a stable fact about the world. It isn't.

Consider one column: the hourly price of an A100 GPU. In January it rents for $2.00/hr. In March you raise it to $2.50. In May a price war drops it to $1.80. The GPU is the same physical card — same gpu_id — but one of its attributes has a history.

Now suppose your dim_gpu table holds a single price_per_hour column and you simply overwrite it each time the price changes. Today it says $1.80. Someone asks an entirely reasonable question:

"How much revenue did we earn from A100 rentals in February?"

If your report multiplies February's rental hours by the current price, you get a number computed at $1.80 — but in February the price was $2.00. The report is wrong, and worse, it's silently wrong: nothing errors, the query runs, a confident-looking number appears. Last month the same query returned a different answer. Trust evaporates.

The fix is to stop treating a dimension as "the current state" and start treating it as "the state over time." How much machinery you add to do that is exactly what the SCD types describe.

Type 1: overwrite the value

Type 1 is the no-history approach: when an attribute changes, you UPDATE the row in place. The old value is gone forever.

-- A Type 1 update: the old price simply disappears
UPDATE dim_gpu
SET    price_per_hour = 2.50
WHERE  gpu_id = 'A100';

This is simple, cheap, and sometimes exactly right. Type 1 is acceptable when the attribute is a correction rather than a real change, or when nobody will ever need the prior value:

• Fixing a typo in a customer's name ("Jhon" → "John") — the old spelling was never meaningfully true.
• A field nobody reports on historically (an internal note, a display colour).
• Late-binding reference data where only "now" matters.

The test is a single question: "Would anyone ever ask what this value was at some past moment?" If yes, Type 1 is the wrong choice — and for a price, the answer is emphatically yes.

Type 2: add a new row for each version

Type 2 is the workhorse, and the one worth truly understanding. Instead of overwriting, you add a new row every time an attribute changes, and you stamp each row with the window of time during which it was the truth. The business key (gpu_id) now appears on several rows — one per version — so you introduce a surrogate key that is unique per version.

The standard columns are:

Here is the A100's price history as a Type 2 dimension. The same gpu_id spans three rows; each owns a slice of time:

Notice how the windows abut: each valid_to equals the next row's valid_from. The live row's valid_to is a sentinel far in the future (9999-12-31) so range checks work without special-casing NULL. The is_current flag is redundant with that sentinel, but it makes "give me today's price" a trivial filter.

Now the crucial part: a fact joins to the version that was current at the event's timestamp, not to the latest version. A rental that started on 2026-02-14 falls inside row 101's window, so it joins to surrogate key 101 and is priced at $2.00 — correct, forever, no matter how many future price changes happen.

Type 3: keep a "previous value" column

Type 3 is a middle ground you'll rarely use. Instead of new rows, you add a second column to hold the prior value:

-- One extra column remembers exactly one previous value
UPDATE dim_gpu
SET    previous_price = price_per_hour,   -- shuffle current → previous
       price_per_hour = 2.50              -- set the new current
WHERE  gpu_id = 'A100';

This keeps history exactly one step deep. After a second change, the original $2.00 is lost — previous_price only ever holds the most recent former value. It's occasionally handy for "show this quarter vs. last quarter's territory assignment" where only the immediately prior value matters and you want it on the same row. For anything needing full history — which is almost everything in analytics — Type 2 is the answer. Here is the comparison at a glance:

Point-in-time correctness: the "as-of" join

Everything above converges on one technique. To price a fact correctly you join it to the dimension version that was valid as of the fact's own timestamp. That's the as-of join, and it's the backbone of trustworthy history:

SELECT r.rental_id, r.hours, g.price_per_hour
FROM   fact_rentals r
JOIN   dim_gpu g
  ON   g.gpu_id = r.gpu_id
 AND   r.started_at >= g.valid_from     -- the event happened after this
 AND   r.started_at <  g.valid_to;      -- version began, and before it ended

The two range conditions are the magic. For any given started_at, exactly one version satisfies both — because the validity windows partition the timeline with no gaps or overlaps. Join on the business key for identity, then on the time range for the right version. (The >=/< asymmetry matters: it makes the boundary timestamp belong to exactly one window, never both.)

Hands-on: an SCD2 dimension for GPU prices

Let's build it for real in DuckDB. We'll create a Type 2 price dimension, apply a price change with proper merge logic, record some rentals, and then compute revenue using the price that was active at each rental's moment.

Step 1 — create the dimension and seed two versions. The A100 starts at $2.00, then rises to $2.50 on March 1st.

CREATE TABLE dim_gpu (
  gpu_sk         INTEGER,
  gpu_id         VARCHAR,
  price_per_hour DECIMAL(6,2),
  valid_from     DATE,
  valid_to       DATE,
  is_current     BOOLEAN
);

INSERT INTO dim_gpu VALUES
  (101, 'A100', 2.00, DATE '2026-01-01', DATE '2026-03-01', false),
  (102, 'A100', 2.50, DATE '2026-03-01', DATE '9999-12-31', true );

Step 2 — a new price arrives. On May 1st the price drops to $1.80. An SCD2 update is two moves: close the currently-live row, then insert the new live row. Never overwrite.

-- (a) close the old current version: set its valid_to and unflag it
UPDATE dim_gpu
SET    valid_to   = DATE '2026-05-01',
       is_current = false
WHERE  gpu_id = 'A100'
  AND  is_current = true;

-- (b) insert the new current version, open-ended into the future
INSERT INTO dim_gpu VALUES
  (103, 'A100', 1.80, DATE '2026-05-01', DATE '9999-12-31', true);

-- inspect the full history
SELECT * FROM dim_gpu ORDER BY valid_from;

Step 3 — record some rentals at different times, then price them with an as-of join. Two rentals land in February (price $2.00), one in April ($2.50), one in June ($1.80).

CREATE TABLE fact_rentals (
  rental_id  INTEGER,
  gpu_id     VARCHAR,
  started_at DATE,
  hours      INTEGER
);

INSERT INTO fact_rentals VALUES
  (1, 'A100', DATE '2026-02-10', 5),   -- price era: $2.00
  (2, 'A100', DATE '2026-02-20', 3),   -- price era: $2.00
  (3, 'A100', DATE '2026-04-15', 4),   -- price era: $2.50
  (4, 'A100', DATE '2026-06-05', 2);   -- price era: $1.80

-- revenue using the price that was ACTIVE at each rental's time
SELECT  r.rental_id,
        r.started_at,
        r.hours,
        g.price_per_hour                 AS price_at_rental_time,
        r.hours * g.price_per_hour       AS revenue
FROM    fact_rentals r
JOIN    dim_gpu g
  ON    g.gpu_id = r.gpu_id
 AND    r.started_at >= g.valid_from
 AND    r.started_at <  g.valid_to
ORDER BY r.started_at;

✓ Check yourself

Answer from memory before opening each one.

1. The "February A100 revenue" report returned one figure when it was run in April and a lower one when it was run in June. No code changed and no February rentals were added or removed. What design produces that, and why did nothing fail?

   Show answer

   dim_gpu holds a single price that is overwritten (Type 1), and the report multiplies February's hours by whatever the price is today — $2.50 in April, $1.80 in June. Overwriting doesn't only lose history, it rewrites the past: every historical report that joins to the dimension uses today's value as if it had always been true, and the query still runs cleanly. See The problem.

2. A rental's fact row stores gpu_sk = 101. Over the next year the A100's price changes three more times. What has to be updated on that fact row, and why could the business key gpu_id not have done the same job?

   Show answer

   Nothing. The surrogate key is unique per version, so 101 pins the rental to the $2.00 row forever and the fact is immutable. gpu_id = 'A100' repeats on every version, so on its own it can't say which price applied. See Type 2.

3. Add a rental that starts on exactly 2026-03-01 to the hands-on data. Which version does the as-of join give it — and what would happen to that rental if someone "tidied" the two range conditions into started_at BETWEEN valid_from AND valid_to?

   Show answer

   It joins to sk 102 ($2.50), because >= valid_from and < valid_to make a boundary date belong to exactly one window. BETWEEN is inclusive at both ends, so the rental would match sk 101 and 102, appear twice, and have its revenue counted twice. See the as-of join.

4. When the May price arrives, a teammate runs step (b) — the INSERT of sk 103 — but forgets step (a). Nothing errors. What does the revenue query now return for rental 4 (June 5th), and what happens to the total?

   Show answer

   Rental 4 comes back twice — once at $2.50 (5.00) and once at $1.80 (3.60) — because sk 102 is still open to 9999-12-31 and both windows contain June 5th. The total becomes 34.60 instead of 29.60. "Exactly one match" depends on the windows having no gaps and no overlaps, which is why an SCD2 update is always close-then-insert. See the hands-on, step 2.

5. In the same week, one customer's misspelled name is fixed and another customer upgrades from free to pro. Which SCD type do you use for each change, what single question decides it, and what are you paying for when you choose Type 2?

   Show answer

   Type 1 for the name, Type 2 for the tier. The test is "would anyone ever ask what this value was at some past moment?" — the misspelling was never meaningfully true, but "rentals while on the free tier" is a real question. Type 2 costs extra rows, validity columns and an as-of join on every query, so Type 1 wins for corrections and for fields nobody reports on historically. See Type 1 and the comparison table.

6. Later in the curriculum you build a training set with one row per past rental, and add the customer's tier as a feature by joining to dim_customer WHERE is_current = true. The model looks excellent offline and disappoints in production. What went wrong, and what is the fix?

   Show answer

   The feature leaks the future: each historical rental was given the tier the customer has today, which wasn't known at the time of the rental and won't be available in the same way when predicting. Use the value that was valid at event time — the same as-of join on valid_from / valid_to. See Point-in-time correctness.

-- ── SCD1: overwrite. One row, history destroyed. ──
-- dim_customer after the upgrade:
--   customer_id | tier
--   C7          | gold     ◄─ the 'silver' period is GONE
--
-- Asking "rentals while silver?" is now impossible — every C7
-- rental, including the June 3rd one, looks like it was 'gold'.

-- ── SCD2: a new row per version. Full history. ──
CREATE TABLE dim_customer (
  cust_sk INTEGER, customer_id VARCHAR, tier VARCHAR,
  valid_from DATE, valid_to DATE, is_current BOOLEAN
);
INSERT INTO dim_customer VALUES
  (1, 'C7', 'silver', DATE '2026-01-01', DATE '2026-06-15', false),
  (2, 'C7', 'gold',   DATE '2026-06-15', DATE '9999-12-31', true );

CREATE TABLE rentals (rental_id INT, customer_id VARCHAR, started_at DATE);
INSERT INTO rentals VALUES
  (10, 'C7', DATE '2026-06-03'),   -- silver era
  (11, 'C7', DATE '2026-06-20');   -- gold era

-- as-of join attaches the tier that was true AT rental time
SELECT  d.tier, count(*) AS rentals
FROM    rentals r
JOIN    dim_customer d
  ON    d.customer_id = r.customer_id
 AND    r.started_at >= d.valid_from
 AND    r.started_at <  d.valid_to
GROUP BY d.tier;
-- → silver | 1   (the June 3rd rental)
--   gold   | 1   (the June 20th rental)

Takeaway

• Overwriting a dimension value rewrites the past: old reports change with no code change and no error.
• Type 1 fits corrections and fields nobody asks about historically; if anyone will ask what a value was at some past moment, it is the wrong choice.
• Type 2 adds a row per version with valid_from and valid_to, and the fact table stores the version's surrogate key, so a fact stays pinned to the truth it was loaded with.
• Version windows must abut with no gaps or overlaps, and a half-open range (>= start, < end) makes exactly one version match any timestamp.
• Type 3 keeps one previous value only, so it is a rare fit when you need more than one step of history.
• Join a fact to the version valid at the event's own time, never the latest; the same point-in-time rule prevents label leakage when you build machine-learning features.

You can now keep one dimension's history correct, so the next chapter organizes every table, raw to business-ready, into layers. → The Medallion Architecture

Further reading

• Dimensional Modeling Techniques — Kimball Group. Defines the slowly changing dimension types, one section per type.
• DuckDB: ASOF joins — DuckDB docs, accessed 2026. A built-in join for the as-of lookup this chapter writes with two range conditions.
• dbt: Snapshots — dbt Labs docs, accessed 2026. How dbt runs the close-old-row, insert-new-row step for you on every build.