Slowly Changing Dimensions (SCD) — ETL & Data Warehousing Concepts

7. Slowly Changing Dimensions (SCD)

Intermediate★ Must knowLesson 7 of 12

Type	Behaviour	Example
Type 0	Never changes (retain original)	Date of birth
Type 1	Overwrite — no history	Fix a spelling mistake
Type 2	New row per change with ValidFrom/ValidTo/IsCurrent — full history	Customer moves city; past sales stay with old city
Type 3	Extra column for previous value — limited history	CurrentRegion, PreviousRegion
Type 4	History in a separate table	Mini-dimension for rapidly changing attributes
Type 6	1 + 2 + 3 hybrid	Current value on every historical row + full history

sql

-- SCD Type 2 with T-SQL (expire changed rows, then insert new versions)
UPDATE d SET d.ValidTo = CAST(GETDATE() AS DATE), d.IsCurrent = 0
FROM dw.DimCustomer d
JOIN stg.Customer s ON s.CustomerId = d.CustomerId
WHERE d.IsCurrent = 1 AND (d.City <> s.City OR d.Name <> s.Name);

INSERT INTO dw.DimCustomer (CustomerId, Name, City, ValidFrom, ValidTo, IsCurrent)
SELECT s.CustomerId, s.Name, s.City, CAST(GETDATE() AS DATE), '9999-12-31', 1
FROM stg.Customer s
LEFT JOIN dw.DimCustomer d ON d.CustomerId = s.CustomerId AND d.IsCurrent = 1
WHERE d.CustomerKey IS NULL;      -- new customers + customers whose current row was just expired

Fact loads must look up the surrogate key valid at the transaction date (or the current row for new facts).