Mastering Slowly Changing Dimensions (SCD Type 2) in SQL

In a standard database, if a user changes their address, you simply UPDATE the row.

UPDATE users SET city = 'New York' WHERE id = 1;

The old address is gone forever. For a transactional app, this is fine. For data analysis, it's a disaster.

What if you need to know where the user lived last year to calculate shipping costs for an old order?

This is where Slowly Changing Dimensions (SCD) come in. Specifically, Type 2, which is the gold standard for tracking history.

The Logic: Start Date, End Date, and Flags

Instead of overwriting the row, we:

1. Expire the old row (set an end_date).
2. Insert a new row with the new data (and a start_date of today).

A table with SCD Type 2 looks like this:

User ID	City	Start Date	End Date	Is Current?
1	San Francisco	2023-01-01	2024-06-01	false
1	New York	2024-06-02	NULL	true

Implementing the Update Query

Updating an SCD Type 2 table involves two steps (often wrapped in a transaction):

Step 1: Close the old record

We find the current active record for the user and set its end_date to yesterday (or today).

UPDATE user_dim
SET 
    end_date = CURRENT_DATE,
    is_current = false
WHERE user_id = 1 
  AND is_current = true;

Step 2: Insert the new record

We insert the new state, with start_date as today and end_date as NULL (or a future date like 9999-12-31).

INSERT INTO user_dim (user_id, city, start_date, end_date, is_current)
VALUES (1, 'New York', CURRENT_DATE, NULL, true);

In a warehouse, these two steps should normally run in the same transaction. Otherwise, a failed insert can leave the old row expired with no new current record.

Designing the Table Correctly

SCD Type 2 works best when the table has a clear split between:

• a surrogate key for the row version
• a natural business key such as customer_id
• the tracked attributes that can change over time
• validity columns such as start_date, end_date, and is_current

Two practical rules matter a lot:

1. there should be only one current row per business key
2. date ranges should not overlap for the same business key

If either rule is broken, point-in-time reporting becomes unreliable very quickly.

Detecting Changes Before You Write

In real ETL jobs, you usually compare a staging table to the current dimension row and only create a new version when tracked attributes actually changed.

The flow looks like this:

1. load today's source snapshot into staging
2. join staging to the current dimension row
3. identify rows where tracked fields differ
4. expire the old rows
5. insert the new versions

That change-detection step is what prevents unnecessary history bloat. If the customer's city did not change, there is no reason to create a new dimension version.

Interactive Using Playground

Let's simulate an address change for a user. We'll start with their original record, then "move" them to a new city while keeping the history.

Querying Point-In-Time Data

The beauty of SCD Type 2 is that you can query the state of the world as it was at any point in time.

To find where Customer 101 lived on Christmas 2023:

SELECT city 
FROM customer_history
WHERE customer_id = 101
  AND '2023-12-25' BETWEEN start_date AND COALESCE(end_date, '9999-12-31');

If you only need the latest state, the query is even simpler:

SELECT *
FROM customer_history
WHERE is_active = 1;

This "current view" versus "historical view" split is why SCD Type 2 is so valuable in analytics. You can support both present-day dashboards and historically accurate reporting from the same dimension table.

Common Implementation Pitfalls

SCD Type 2 is conceptually straightforward, but teams often get the details wrong:

• using the business key as the primary key, which prevents multiple historical versions
• forgetting transactions, so the table is left in a half-updated state
• allowing overlapping date ranges for the same entity
• versioning every attribute, even fields that do not matter for analytics
• joining fact tables to the current dimension instead of the dimension version valid at event time

The last point is especially important. If an order happened in January, you must join it to the customer dimension row that was valid in January, not whatever row is current today.

Conclusion

SCD Type 2 transforms your database from a snapshot of "Now" into a time machine. While it adds complexity to your writes, the ability to reconstruct history is invaluable for accurate reporting and analytics.

Related Articles

• Designing Your First Database Schema for the table design fundamentals behind stable warehouse models.
• Star Schema vs Snowflake Schema for the dimensional modeling context where SCD Type 2 is commonly applied.
• Mastering SQL Transactions for the write-safety patterns needed when expiring and inserting dimension rows together.