Slowly Changing Dimensions
Slowly changing dimension techniques preserve historical attribute values in a warehouse — type 2 adds versioned rows with validity ranges — so reports reproduce the past as it was.
Definition
SCD handling defines how dimension attribute changes are stored. Type 1 overwrites and loses history; type 2 inserts a new row with effective and expiry dates plus a surrogate key, keeping every version addressable; type 3 keeps alternate columns for limited before-and-after; type 6 combines variants. Fact rows bind to the version that was current when they occurred.
Context and scope
Applied when historical reporting must slice by attribute values at the time of the fact: customer segment then, product category then, rep territory then.
Common misconceptions
- Type 2 is a data type — it is a row versioning strategy the pipeline must implement, with keys and dates designed deliberately
- Facts join to the dimension by any key — they must join to the surrogate key of the version current at fact time, or history collapses
- SCD type 2 captures changes forever — late-arriving dimensions and current-row flags need explicit handling, or pipelines misattribute facts
Anti-patterns
- Joining facts to dimensions on the natural key and collapsing all history into the latest attributes
- Updating attributes in place for a dimension promised as type 2 and silently rewriting history for every report
- Missing effective-date overlap or gap validation, creating rows with ambiguous or impossible validity ranges
Related concepts
Related examples
Authoritative source
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
What it is
Business attributes drift: customers move segments, products reclassify. Reports about last quarter must see last quarter values. SCD type 2 makes each value change a new dimension row with its own surrogate key and validity window, so a fact recorded in March joins to the March version.
How it works
The pipeline detects attribute changes, expires the current row, and inserts a successor with a new effective timestamp. Current-row flags or max-date markers ease latest-value queries. Late-arriving facts join by validity window rather than load time; late-arriving dimension changes force retroactive re-versioning.
When to use it
Any regulatory or analytical requirement to reproduce historical groupings: revenue by segment as reported then, churn by territory at signup. Type 1 suffices when history of the attribute has no business meaning.
Pitfalls
Grain mistakes: exploding a dimension when only one attribute changes forces full-row duplication; consider mini-dimensions for high-churn attributes. Row counts inflate over years, and surrogate key generation must stay stable across backfills.