Slowly Changing Dimensions (SCD Type 2)

Last Updated: July 2026 | 10 min read

📚 Lesson in the Data Modeling & Warehousing track of our free Learn Data Engineering course. Builds directly on dimensional modeling & the star schema.

Quick Answer: A slowly changing dimension (SCD) is a dimension whose attributes change over time — a customer moves city, a product changes category. SCD Type 2 handles this by preserving history: instead of overwriting the old value, it inserts a new row for the changed record, each version carrying a surrogate key and a validity window (valid_from, valid_to, is_current). The old row is closed and the new one becomes current — so you can always see what the dimension looked like at any point in the past.

In the star schema lesson we treated dimensions as if they never change. Reality is messier: customers move, products get recategorized, sales reps switch regions. How you handle those changes determines whether your historical reports stay correct. This lesson covers the SCD types, why Type 2 is the workhorse, and exactly how to implement it in SQL.

What is a slowly changing dimension?

A slowly changing dimension is a dimension table whose descriptive attributes change occasionally over time — and the "SCD" strategies are how you decide what to do when they do. The word "slowly" matters: these aren't high-frequency updates, they're the gradual drift of real-world entities.

The core question is: when an attribute changes, do you keep the old value or throw it away?

• Throw it away → Type 1 (overwrite).
• Keep it as history → Type 2 (new row per version).

The answer depends entirely on whether your reports need to reflect history as it was or only the present.

Why history matters: a concrete example

If you overwrite a customer's city, every past sale silently moves to the new city — corrupting history. Say customer 42 bought from Delhi for two years, then moved to Mumbai.

• With Type 1 (overwrite): the customer's city becomes Mumbai. Now your "2024 sales by city" report attributes two years of Delhi purchases to Mumbai. The past has been rewritten.
• With Type 2 (new row): the Delhi version stays, closed off with a valid_to date; a new Mumbai version is added. Sales made before the move still join to the Delhi version. History is intact.

This is the whole reason SCD Type 2 exists: facts must join to the version of the dimension that was current when the event happened, not to whatever the value is today.

The SCD types compared

There are several SCD types, but three cover almost every case in practice. Here's the comparison:

Type 2 is the default for anything analytical where history matters. Type 1 is for corrections (fixing a misspelled name). Type 3 is rare — used when you only ever care about the immediately previous value.

How SCD Type 2 works

SCD Type 2 gives every version of a record its own row, uniquely identified by a surrogate key and bounded by a validity window. Three columns make it work:

• surrogate_key — a warehouse-generated unique id per version (not the business customer_id, which repeats across versions).
• valid_from / valid_to — the date window this version was active. The current version's valid_to is a far-future sentinel like 9999-12-31.
• is_current — a boolean flag for quickly selecting the live version.

Why the surrogate key? Because once customer 42 has two rows, customer_id is no longer unique. The surrogate key lets a fact table point at the exact version that was current on the transaction date.

Implementing SCD Type 2 in SQL

Each change is two operations: close the old row, then insert the new one. When customer 42 moves to Mumbai on 2026-03-15:

-- 1. Close the existing current version
UPDATE dim_customer
SET valid_to   = DATE '2026-03-15',
    is_current = FALSE
WHERE customer_id = 42
  AND is_current  = TRUE;

-- 2. Insert the new current version
INSERT INTO dim_customer
    (surrogate_key, customer_id, city, valid_from, valid_to, is_current)
VALUES
    (1002, 42, 'Mumbai', DATE '2026-03-15', DATE '9999-12-31', TRUE);

Querying is then flexible. Get the current state:

SELECT * FROM dim_customer WHERE is_current = TRUE;

Or reconstruct the point-in-time state for a historical join:

-- which customer version was active on a given sale date?
SELECT c.*
FROM   dim_customer c
JOIN   fct_sales    s
  ON   s.customer_id = c.customer_id
 AND   s.sale_date  >= c.valid_from
 AND   s.sale_date  <  c.valid_to;

In practice, you rarely hand-write this. dbt provides snapshots that implement SCD Type 2 automatically — you declare the unique key and the columns to track, and dbt manages the valid_from/valid_to/is_current bookkeeping on every run.

Try it yourself — reason about SCD

A dim_employee table tracks which department each employee belongs to.

1. An employee transfers from Sales to Marketing. Under Type 2, how many rows now exist for that employee, and what changes on the old row?
2. You want a report of "revenue by the employee's department at the time of each sale." Which SCD type do you need?
3. HR fixes a typo in an employee's name. Which SCD type fits, and why?

Common mistakes with SCD Type 2

• Using the natural key as the primary key. Once versions exist, customer_id repeats. You need a surrogate key per version.
• Forgetting to close the old row. Insert a new row but leave two rows is_current = true, and every join double-counts.
• Overwriting when you needed history (Type 1 by accident). Silently corrupts historical reports — the most damaging SCD mistake.
• Type 2-ing everything. History has a cost in rows and complexity. Use Type 1 for corrections and attributes nobody analyzes over time.
• Gaps or overlaps in validity windows. valid_to of one version must equal valid_from of the next, or point-in-time joins break.

Frequently Asked Questions

What is a slowly changing dimension?

A slowly changing dimension (SCD) is a dimension table whose attributes change over time — like a customer moving city or a product changing category. The term names the challenge of handling those changes: overwrite the old value, or preserve history? SCD techniques (Type 1, 2, 3, and more) are the standard strategies for managing them in a data warehouse.

What is SCD Type 2?

SCD Type 2 preserves full history by adding a new row each time a dimension attribute changes, instead of overwriting. Each version gets its own surrogate key and a validity window — valid_from, valid_to, and is_current. The old row is closed (valid_to set, is_current false) and a new row inserted, so you can see what a dimension looked like at any past point.

What is the difference between SCD Type 1 and Type 2?

Type 1 overwrites the old value, keeping no history — the dimension always shows the current state. Type 2 keeps history by inserting a new row per change and closing the old one. Use Type 1 when history doesn't matter (fixing a typo); use Type 2 when you must analyze data as it was at the time of each event.

Why do you need a surrogate key for SCD Type 2?

Because the natural key (like customer_id) stops being unique once a record has multiple historical versions. A surrogate key — a warehouse-generated id per version — uniquely identifies each version so fact tables can point to the exact version that was current when an event happened.

How do you implement SCD Type 2 in SQL?

Two steps per change. First, update the current row: set valid_to to the change date and is_current to false. Second, insert a new row with a fresh surrogate key, the new values, valid_from = change date, valid_to = a far-future date, is_current true. dbt snapshots automate this pattern so you don't hand-write it.

Conclusion

SCD Type 2 is how a data warehouse remembers the past: instead of overwriting a changed dimension, it versions it — new row, surrogate key, validity window, current flag. That's what keeps historical reports honest, so a sale made in Delhi stays in Delhi even after the customer moves. Use Type 1 for corrections, Type 2 whenever point-in-time history matters, and lean on dbt snapshots to implement it cleanly.

That rounds out the core of the Data Modeling track in the free Learn Data Engineering course. Next you'll scale these ideas up with Spark for data too big for one machine. Building a warehouse that needs reliable history? Get matched with a vetted data engineer on SolutionGigs — it's free to post a project.

Mohammed Yaseen

Founder, SolutionGigs

Mohammed builds warehouse dimension pipelines with SCD Type 2 history in production and teaches the data modeling track of the free Learn Data Engineering course. LinkedIn →