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

• Why does overwriting a dimension value make past reports wrong, not just lose history?
• In an SCD2 table, what does the fact table store — the business key or the surrogate key — and why does that make the fact immutable?
• Write the two range conditions of an as-of join from memory. Why is one >= and the other <?
• How does point-in-time correctness here connect to feature leakage in machine learning?

-- ── 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)

Next

You can now keep history honest in a single dimension. Next we organise all your tables — raw, cleaned, and business-ready — into disciplined layers that make a whole platform legible. → The Medallion Architecture