A customer's address changes. The salesperson responsible for an account is replaced. A product moves category. The question that separates a good data model from a weak one is: do we keep only the current value, or preserve the history? This is what Slowly Changing Dimensions (SCD) solve.
The problem of lost history
Imagine a customer moves from North to South. If you simply update the address, all old sales now appear made in the South — and your analysis by region becomes wrong for the past. History was rewritten. SCDs are the disciplined way to handle these changes.
SCD Type 1: overwrite
The simplest type: when the value changes, the old one is replaced. Fast and light, but history is lost. It works when the past does not matter — fixing a typo in a name, for example — but it is dangerous for data affecting historical analysis.
SCD Type 2: keep the history
The most used in BI: instead of overwriting, a new row is created for the new value, keeping the old one. Each version has start and end dates and a "current" flag. This way, old sales stay linked to North and new ones to South — history stays intact.
How to tell them apart in practice
- Type 1: only the current value matters — use when the past does not count.
- Type 2: you need to analyze "as it was at the date" — the right choice for most business dimensions.
Why this matters for trust
Without well-thought-out SCDs, historical reports change on their own when reference data changes — and nobody understands why. With them, analysis of the past stays faithful to what it really was. It is the difference between a serious data warehouse and one that misleads unintentionally.
In practice
When designing a dimension, always ask: if this value changes, do I want to see history as it was, or only the current one? The answer defines the SCD type. This care, invisible to the end user, is what makes your reports tell the truth over time. Do your dimensions preserve history, or rewrite the past on every change?
