Slowly Changing Dimension

A slowly changing dimension is a descriptive attribute in a warehouse that changes occasionally and unpredictably — a customer's plan tier, a sales rep's territory, a product's category, an account's region — and the term names the problem of deciding what history to keep. The decision is not cosmetic. If you overwrite the old value, every past fact is re-attributed to the current state, and last year's revenue by segment changes every time someone is reclassified. If you preserve it, historical reports stay stable but every query must specify which version it wants. The classic responses are conventionally numbered. Type 1 overwrites: simple, no history, correct for fixing genuine data errors. Type 2 inserts a new dimension row each time the attribute changes, with validity dates and a current flag, so a fact joins to the version that was in force when it happened; this is the default for anything used in trend reporting, at the cost of a larger table and joins that must respect the effective date. Type 3 keeps a previous-value column, which is enough only when one prior state matters. Two practical points. The choice belongs to the business question, not the modeller: someone has to state whether last year's numbers should move when a customer is re-segmented, and that answer differs by attribute. And a Type 2 dimension is only as good as the change feed behind it — if updates arrive as overwrites from a source system with no change capture, the warehouse cannot reconstruct history it was never told about.