In this lesson, you will learn how to maintain historical accuracy in your data warehouse by applying Slowly Changing Dimension (SCD) techniques to track attribute modifications over time.
When business entities—like customers or products—change their attributes (e.g., a customer moves to a new city), you must decide how to handle the "old" data. We use three standard SCD techniques to manage these updates.
SCD Type 1: The Overwrite
Type 1 is the simplest method: you simply update the existing record. This is ideal when you do not need to preserve historical context.
UPDATE dim_customer
SET city = 'Austin'
WHERE customer_id = 101;
Effect: The old value (e.g., 'Dallas') is lost permanently.
SCD Type 2: The Historian
Type 2 is the most robust method for analytics. Instead of updating, you expire the old record and insert a new one. You typically use a start_date, end_date, and an is_current flag to differentiate versions.
-- Expire the old record
UPDATE dim_customer
SET end_date = CURRENT_DATE, is_current = FALSE
WHERE customer_id = 101 AND is_current = TRUE;
-- Insert the new record
INSERT INTO dim_customer (customer_id, city, start_date, end_date, is_current)
VALUES (101, 'Austin', CURRENT_DATE, NULL, TRUE);
Effect: You retain the full history, allowing you to run reports exactly as they looked at any point in the past.
SCD Type 3: The Snapshot
Type 3 tracks only the previous value and the current value in the same record using additional columns (e.g., previous_city and current_city).
UPDATE dim_customer
SET previous_city = city, city = 'Austin'
WHERE customer_id = 101;
Effect: This provides a limited view of history—enough to see the last change, but insufficient for a full audit trail. It is storage-efficient but less granular than Type 2.
Choosing the right technique depends on your business requirements: do you need an audit log (Type 2), a lightweight comparison (Type 3), or just the most recent state (Type 1)?
Key Takeaway: Managing SCDs is a trade-off between storage costs and analytical depth, with Type 2 serving as the gold standard for full historical traceability.