Slowly Changing Dimensions (SCD 0–7)

A dimension is a table that describes things, such as customers. A fact table records events, such as orders.

Each fact row points to one dimension row. It does this with a surrogate key. That is an id the warehouse makes, with no business meaning.

Dimension values change. A customer moves to a new country.

A slowly changing dimension (SCD) is a dimension built to handle this. It is “slowly” because changes are rare compared to orders.

The question is what to do with the old value. Each answer is a numbered type.

This chapter teaches types 0 to 3 in full. Types 4 to 7 get a short note.

What you will learn

1. Type 0 and Type 1: keep the first value, or overwrite it.
2. Type 2: add a new row for each change.
3. How a fact row finds the right version.
4. Type 3, and a short note on types 4 to 7.
5. Three rules for good history, and what to do when a row arrives late.
6. How to choose a type for each column.

Asha lives in India (IN). On 2026-01-08 she moves to Germany (DE). She has two orders: 101 on 2026-01-03, and 102 on 2026-01-10.

Here is the customers dimension, before Asha moves. The table is called dim_customer. The prefix dim_ means dimension.

The customer_key column is the surrogate key. The customer_id column is the id from the shop, called the business key.

One question will test every type. How much paid revenue does each country have?

The key question is order 101. Asha lived in IN then. Should it count under IN or under DE?

First, the fact table. It has one row per order. The customer_key column points at the customer row.

Both of Asha’s orders point at row 1001.

Now the question that tests every type. The query joins each order to its customer, keeps paid orders, and adds the amounts for each country. Run it before the move:

SELECT c.country, SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE f.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;

IN has 65.00. That is Asha’s two paid orders, 40.00 plus 25.00.

Now Asha moves. What should the dimension do?

Type 0: never change

Type 0 keeps the first value forever. The move is ignored. Use it for values that must never change, such as a signup date.

For Asha, nothing changes. The dim_customer table stays as it is above.

The revenue query gives the same result as before the move. IN has 65.00, and DE does not exist.

Type 1: overwrite

Type 1 replaces the old value with the new one. The old value is gone.

UPDATE dim_customer
SET country = 'DE'
WHERE customer_id = 1;

SELECT c.country, SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE f.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;

Type 2 keeps the old row and adds a new row. Each row is one version of the customer. A version is true for a period of time.

Type 2 needs three new columns, and a new surrogate key for each version:

• valid_from: the first day this version is true.
• valid_to: the day this version stops being true. This day is not included.
• is_current: true for the version that is true now.

The not-included rule is a half-open range. The old row ends on 2026-01-08. The new row starts on 2026-01-08.

So every day belongs to exactly one row. There is no gap and no overlap.

A row that is still true gets a far-future end date, 9999-12-31. When Asha moves, you do two steps.

First close the old row. Then add the new row:

UPDATE dim_customer
SET valid_to = DATE '2026-01-08',
    is_current = false
WHERE customer_key = 1001;

INSERT INTO dim_customer
  (customer_key, customer_id, name, country,
   valid_from, valid_to, is_current)
VALUES
  (1006, 1, 'Asha', 'DE',
   DATE '2026-01-08', DATE '9999-12-31', true);

Now the fact rows. Each order must point at the version that was true on its order date.

Order 101 (2026-01-03) points at 1001. Order 102 (2026-01-10) points at 1006.

Run the same revenue query again. The query did not change. Only the keys in the fact table are different:

SELECT c.country, SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE f.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;

Type 2 has a cost. The dimension grows, because each change adds a row.

You also need a way to give each fact row the right key. The next section shows that.

A Type 2 dimension can answer two questions. What is true now? What was true on a past date?

For the current value, filter on is_current. For a past date, you check the date against the half-open range. This query asks about Asha on 2026-01-05, and then about now:

-- What was true on 2026-01-05?
SELECT country
FROM dim_customer
WHERE customer_id = 1
  AND DATE '2026-01-05' >= valid_from
  AND DATE '2026-01-05' <  valid_to;

-- What is true now?
SELECT country
FROM dim_customer
WHERE customer_id = 1
  AND is_current;

Look at the boundary day, 2026-01-08. The old row has valid_to = 2026-01-08, which is not included. So only the new row matches that day.

Each fact row must store the key of the right version. You find it when you load the fact row. Loading means copying new rows into the warehouse.

The lookup is a point-in-time join. It looks up the version that was true on a given date. You join on the business key and the order date.

The result is the surrogate key to store in the fact row.

SELECT o.order_id, o.order_date, d.customer_key
FROM orders AS o
JOIN dim_customer AS d
  ON d.customer_id = o.customer_id
 AND o.order_date >= d.valid_from
 AND o.order_date <  d.valid_to
WHERE o.customer_id = 1
ORDER BY o.order_id;

After this load, reports need no date logic. They join on customer_key, as in the Type 2 section. The fact row carries the history.

Now see the common mistake. Join on customer_id, not customer_key.

The fact_orders table has no customer_id, so this query borrows it from orders. Each Asha order matches two rows:

SELECT c.country, SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN orders AS o ON o.order_id = f.order_id
JOIN dim_customer AS c ON c.customer_id = o.customer_id
WHERE f.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC, c.country;

Type 3 adds a column for the previous value. The row stays one row. You can see the old value and the new value side by side.

ALTER TABLE dim_customer
ADD COLUMN previous_country text;

UPDATE dim_customer
SET previous_country = country,
    country = 'DE'
WHERE customer_key = 1001;

Revenue by country gives the same result as Type 1: DE has 65.00. The new thing is that you can ask “what was her country before?”.

Type 3 keeps only one old value. If Asha moves again to France, previous_country becomes DE. IN is lost.

Use Type 3 only when one step back is enough.

Types 4 to 7 (optional)

These types are less common. Read them once, and come back when you need them.

• Type 4: the main table holds only current rows. A separate history table holds the old rows.
• Type 5: move fast-changing columns, such as an age group, into a small second dimension called a mini-dimension. Without it, Type 2 adds a new row for every small change.
• Type 6: Type 2 rows, plus a column that always shows the current value. The name comes from 1 + 2 + 3 = 6.
• Type 7: the fact row stores two keys. One is the version key. The other is the customer_id, the same for every version.

A Type 2 dimension is only useful if its rows follow three rules:

1. No gap. Every day belongs to a version. If the old row ends on 2026-01-07 and the new row starts on 2026-01-08, an order on 2026-01-07 matches nothing.
2. No overlap. No day belongs to two versions. If the old row ends on 2026-01-09 and the new row starts on 2026-01-08, an order on 2026-01-08 matches both.
3. One current row. Each customer_id has exactly one row with is_current = true.

Half-open ranges keep rules 1 and 2 simple. Set the old valid_to equal to the new valid_from. Then no day is lost and no day is counted twice.

The third rule breaks when you forget to close the old row. Asha then has two current rows.

This query finds every customer with more than one. HAVING keeps only the groups that pass the test:

SELECT customer_id, COUNT(*) AS current_rows
FROM dim_customer
WHERE is_current
GROUP BY customer_id
HAVING COUNT(*) > 1;

Run a check like this after every load. A broken range gives no error. It gives wrong revenue.

You do not have to write the close-and-add steps yourself. dbt is a tool that runs SQL in your warehouse. Its snapshots can build Type 2 rows for you.

Sometimes an order arrives before its customer row. This is a late-arriving dimension. You cannot wait, and you cannot drop the order.

The fix is an inferred member. It is a placeholder row with the business key and the flag is_inferred = true. The fact row points at it.

Later you fill in the real values.

First add one column to the dimension: is_inferred, true for a placeholder (a stand-in row). The example does this in step 1.

New customer 6, Priya, makes order 107 on 2026-01-22 for 30.00. The customer file has not arrived yet. These rows are new, only for this example.

-- 1. Add the flag. Existing rows get false.
ALTER TABLE dim_customer
ADD COLUMN is_inferred boolean DEFAULT false;

-- 2. Order 107 arrives. Customer 6 is not in the dimension.
INSERT INTO dim_customer
  (customer_key, customer_id, name, country,
   valid_from, valid_to, is_current, is_inferred)
VALUES
  (1007, 6, 'Unknown', 'Unknown',
   DATE '2026-01-22', DATE '9999-12-31', true, true);

INSERT INTO fact_orders
  (order_id, customer_key, order_date, status, amount)
VALUES (107, 1007, DATE '2026-01-22', 'paid', 30.00);

-- 3. Later, the real customer row arrives.
UPDATE dim_customer
SET name = 'Priya', country = 'GB', is_inferred = false
WHERE customer_key = 1007;

You update the placeholder in place, and you do not add a new version. The placeholder was never a true state. It only said “we do not know yet”.

You choose a type for each column, not for the whole table. Ask one question: if this value changes, must old reports keep the old value?

Type 2 costs the most. It adds rows, and every fact load needs the point-in-time join. Use it only for columns where history changes the answer.

Do not use Type 2 for a column that changes every day. Move it to a mini-dimension, or keep it in the fact table.

Here is what each type does to Asha’s two orders. The shape of the question is the same each time. Only the dimension (and, for Type 2, the fact keys) changes.

Test yourself. Cover the page and answer each question:

• What is a slowly changing dimension?
• Which type gives IN 40.00 and DE 25.00? Why?
• Why does valid_to use a half-open range?
• How does order 102 get key 1006?
• What breaks if you join facts on customer_id?

Each exercise opens a drawing board. You draw the tables, and the review checks them against sample rows.

Start with “Addresses that stay true to the past”. It is Type 2 with real edge cases.