Slowly Changing Dimensions (SCD)
Dimension attributes change over time — customers move, products get re-categorized. Slowly Changing Dimension techniques define how a warehouse preserves or overwrites history. Master Type 1, Type 2, and Type 3.
The Problem
Suppose a customer moves from Mumbai to Berlin. If you simply overwrite the city, all of their past orders will appear to have come from Berlin and historical reports become wrong. Slowly Changing Dimensions (SCDs) are patterns for handling such changes deliberately.
SCD Types
The most common approaches:
- Type 0 — Never changes; the original value is retained.
- Type 1 — Overwrite the old value. Simple, but history is lost.
- Type 2 — Insert a new row for each change with validity dates. Full history preserved.
- Type 3 — Add a column for the previous value. Keeps limited (one-step) history.
Implementing SCD Type 2
Type 2 adds a new versioned row and closes the previous one using effective dates and a current-flag. Facts join to the version that was active at the time of the event.
CREATE TABLE dim_customer_scd2 (
customer_key BIGINT PRIMARY KEY, -- surrogate key (one per version)
customer_id VARCHAR(30), -- natural key
city VARCHAR(60),
valid_from DATE,
valid_to DATE, -- '9999-12-31' for current row
is_current BOOLEAN
);
-- Step 1: close out changed rows
UPDATE dim_customer_scd2 d
SET valid_to = CURRENT_DATE - 1,
is_current = FALSE
FROM staging.customers s
WHERE d.customer_id = s.customer_id
AND d.is_current = TRUE
AND d.city <> s.city;
-- Step 2: insert the new version
INSERT INTO dim_customer_scd2
(customer_id, city, valid_from, valid_to, is_current)
SELECT s.customer_id, s.city, CURRENT_DATE, '9999-12-31', TRUE
FROM staging.customers s
LEFT JOIN dim_customer_scd2 d
ON d.customer_id = s.customer_id AND d.is_current = TRUE
WHERE d.customer_id IS NULL OR d.city <> s.city;Choosing the Right Type
Match the technique to the business need:
- Use Type 1 for corrections (typos) where history has no value.
- Use Type 2 when reports must reflect attributes as they were at the time of the event.
- Use Type 3 rarely — when only "current vs previous" comparison is needed.
- Many modern stacks implement Type 2 with dbt snapshots rather than hand-written SQL.
Key Takeaways
- Overwriting dimension attributes can silently corrupt historical reports.
- SCD Type 2 preserves full history using versioned rows and validity dates.
- Facts should reference the surrogate key of the version active at event time.
- Pick the SCD type based on whether history matters for the attribute.
Contact Us
Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com