Slowly Changing Dimension

The question this answers is easy to state and expensive to get wrong: when a customer moves from one sales region to another, does last year's report still show their revenue in the old region?

Type 1 overwrites, keeping only the current value. History is lost and prior reports change retrospectively, which is fine for correcting a misspelled name and wrong for anything reported on. Type 2 adds a new row with validity dates and a current-record flag, preserving the full history and letting facts join to the attribute value as it was at the time. Type 3 keeps a previous-value column, which handles exactly one change and is rarely the right answer.

Type 2 is the workhorse and its cost is complexity: the dimension grows, every fact must join on the surrogate key rather than the natural key, and queries must filter on the current flag or on the effective date range — forgetting that filter is the single most common cause of inflated numbers in a warehouse.

The architectural point that generalises beyond warehousing: this is the same problem as bitemporality, and any system reporting on the past must decide whether it reports what it knows now or what it knew then. Deciding by accident is how two dashboards end up disagreeing.