Slowly changing dimensions, in plain English
Dimension attributes change. A customer relocates, a product gets recategorized, a rep moves to a new region. The question is deceptively simple: when an attribute changes, what happens to the history attached to its old value?
That's the whole of "slowly changing dimensions." The jargon makes it sound like a certification topic. It's really one business question wearing a technical costume, and the answer changes your numbers more than almost anything else in the model.
A concrete example
A customer in Texas buys $50k of product in 2024. In early 2025 they move to New York. Now you run "2024 revenue by state." Should that $50k show up under Texas or New York?
Both answers are defensible — and that's exactly the point. The model has to make a choice, and if you don't make it deliberately, the tool makes it for you.
Type 1: overwrite (keep only the present)
The simplest approach: when the attribute changes, overwrite it. The customer's state becomes New York, full stop. Run the 2024 report and the $50k now shows under New York, because as far as the model is concerned the customer was always in New York.
Type 1 is right when you only ever care about the current value — correcting a typo, or an attribute where history is meaningless. It's the default most tools reach for, and that's the trap: it quietly rewrites the past every time something changes.
Type 2: new row (keep the history)
The richer approach: when the attribute changes, you don't overwrite — you close out the old version and add a new row. The customer now has two dimension records: Texas, valid through the move date; New York, valid after. Each fact joins to whichever version was true at the time. The 2024 sale stays under Texas. The 2025 sale lands under New York.
Type 2 costs more — you carry effective dates and a surrogate key per version — but it's the only way to answer "what was true then" honestly. Any metric that compares periods needs it.
Which to pick
The decision rule is a single question: should this metric reflect how things were then, or how they are now?
- As-was (point-in-time truth — "revenue by the region we sold it to") → Type 2.
- As-is (current state — "revenue by where the customer is today") → Type 1, or a current-value attribute layered on top of a Type 2 history.
The expensive mistake is never choosing — defaulting to Type 1 because it's easier, then wondering why last year's regional numbers shift every time someone moves. The history didn't change. Your model just forgot it.
Decide it on purpose
You don't need Type 2 everywhere — that's its own kind of over-engineering. You need it exactly where someone will one day ask a point-in-time question and trust the answer. Identify those attributes up front, track their history deliberately, and let the rest overwrite. A model that remembers the right things is what lets metrics survive the next reorg — which is where this goes next.