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:
- Expire the old row (set an
end_date). - Insert a new row with the new data (and a
start_dateof 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);
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');
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.