Slowly changing dimensions, without the dogma
A customer changes plan. A product changes category. Do your historical reports use the old value or the new one? That single question is what slowly changing dimensions are really about.
The textbook treatment of slowly changing dimensions (SCD) lists a dozen "types" and turns a practical question into a taxonomy quiz. Strip it back and there are really two choices that matter, and you make them per attribute based on how the business reads history.
Overwrite, or keep history?
For some attributes you only ever care about the current value — a customer's email, say. Overwrite it; history is noise. For others, history is the whole point: which plan a customer was on at the time of each invoice. There you keep versions, each with a validity range, so a report can ask "what was true then?"
-- versioned dimension: one row per state, with a window
customer_id | plan | valid_from | valid_to
1001 | free | 2024-01-01 | 2024-06-30
1001 | pro | 2024-07-01 | NULL -- currentThe mistake that hurts
The expensive error isn't picking the "wrong type" — it's overwriting an attribute that someone later needs the history of, because then the history is simply gone. When unsure, keep versions: storage is cheap and you can always collapse to current, but you can't recover history you discarded.
Decide per attribute, driven by one question: will anyone ever need to know what this was at a past point in time?
Join on the as-of date
The payoff is the as-of join: fact rows match the dimension version that was valid at the fact's timestamp. Get this right and "revenue by plan, historically accurate" is a query, not a reconstruction project.