A customer's attribute changes. Which slowly-changing-dimension approach should be used, and what decides it?

What decides it

Whether historical facts should reflect the attribute value at the time of the event, or the current value.

That is a business question, not a technical one, and it frequently differs by attribute within the same dimension.

The approaches

Overwrite (type 1). Keep only the current value. Historical reports change retrospectively when an attribute changes. Correct for genuine corrections — a misspelled name — and wrong for anything where history matters.

New row per change (type 2). Each change creates a new row with validity dates and a surrogate key; facts reference the row that was current at the time. The default for anything where historical accuracy matters, and it is what allows "sales by region as it was then" to be answered correctly.

Add a column (type 3). Keep current and previous values only. Limited and occasionally exactly right — "the previous owner" as a business concept.

Hybrid. Type 2 rows plus a current-value column on the fact, so both "as it was" and "as it is now" can be reported without re-joining. Common in practice and worth the extra column.

The question that resolves most cases

"When the sales director changes region, should last year's sales move with them?"

For territory performance analysis, no — the sales belong to the region that made them. For current accountability, yes — the director owns their history. Both answers are legitimate, which is why the hybrid is common and why the decision belongs to whoever owns the reporting.

The implementation properties that matter

• Surrogate keys, so facts reference a specific version rather than a business key whose meaning changed.
• Validity ranges without gaps or overlaps, which is a correctness property worth testing explicitly.
• A current-record flag for the common query.
• A defined behaviour for late-arriving facts — a fact arriving with a timestamp before a dimension change must be joined to the version that was current then, which requires point-in-time join logic that is easy to get subtly wrong.

That last case is where most SCD defects live, and it is not exercised by testing with current data.