Slowly Changing Dimensions — Type 1 vs Type 2

The dimension that changes — and what to do about it

Customers move countries. Products change categories. Tier assignments shift. Dimension attributes change over time, and the question "what was their tier when this order was placed?" is suddenly more interesting than it looks. Kimball's answer is the family of slowly changing dimension (SCD) types. Two of them — Type 1 and Type 2 — cover almost every real case.

SCD Type 1 — overwrite

The dimension always reflects the current state. When a customer moves from 'KR' to 'US', you overwrite the country column. Past facts now appear to have always been associated with a US customer. Simple, low storage, no history.

Use when: the historical attribute value doesn't matter (correcting typos, updating profile photos, fixing data-entry errors). "As-of" reporting against the dimension is not needed.

SCD Type 2 — versioned history

Every change to a tracked attribute creates a new row in the dimension table, with effective-from / effective-to timestamps and a current-flag. The fact table joins on customer_key at the time the fact happened, so historical reports show the customer's then-current attributes.

Use when: the historical value matters. Revenue by country needs to attribute past orders to where the customer actually was at the time, not where they are now.

Code

Same customer change, expressed as SCD Type 1 vs SCD Type 2·sql

-- Type 1: just overwrite
UPDATE dim_customers
SET    country = 'US'
WHERE  customer_id = 'C100';
-- Past orders for C100 now look like they were US orders. History is gone.

-- Type 2: close the old row, open a new one
UPDATE dim_customers_v2
SET    valid_to = NOW(), is_current = FALSE
WHERE  customer_id = 'C100' AND is_current = TRUE;

INSERT INTO dim_customers_v2 (customer_id, country, tier, valid_from, valid_to, is_current)
VALUES ('C100', 'US', 'gold', NOW(), '9999-12-31', TRUE);

-- Schema for Type 2:
CREATE TABLE dim_customers_v2 (
    customer_key   INTEGER PRIMARY KEY,        -- surrogate, new every change
    customer_id    TEXT NOT NULL,              -- natural, repeated across rows
    name           TEXT,
    country        TEXT,
    tier           TEXT,
    valid_from     TIMESTAMP NOT NULL,
    valid_to       TIMESTAMP NOT NULL DEFAULT '9999-12-31',
    is_current     BOOLEAN NOT NULL DEFAULT TRUE
);

Joining a Type 2 dimension to a fact table at the right point in history·sql

-- Revenue by *historical* country — what country was the customer in *when the order happened*
SELECT d.country,
       SUM(f.amount_usd) AS revenue
FROM   fact_orders f
JOIN   dim_customers_v2 d
  ON   f.customer_id = d.customer_id
  AND  f.order_date >= d.valid_from
  AND  f.order_date <  d.valid_to
GROUP BY d.country
ORDER BY revenue DESC;

Slowly Changing Dimensions — Type 1 vs Type 2

The dimension that changes — and what to do about it

SCD Type 1 — overwrite

SCD Type 2 — versioned history

Code

External links

Exercise

Progress

Comments 0