Slowly Changing Dimensions (SCD)

• Choose SCD type by the business need for history
• Implement SCD-2 with surrogate keys and effective dates
• Avoid the common type-2 traps: grain drift, orphan rows, double-counting

• Analysts need to see sales attributed to the customer's region AT THAT TIME, not today's.
• Overwriting (type 1) is tempting for simplicity but silently rewrites history for everyone.
• Type 2 gives full history at the cost of surrogate keys, active flags and grain tests.
• Report behavior changes with the type — the choice shapes daily queries.

Type 1: correct the typo, forget the old name. Type 2: keep 'the drummer who joined in 2015' as its own era with start/end dates. Type 3: keep only the previous band member's name in a 'previous keyboardist' column. Each record of history differs — and so does what you can later say.

• Type 1: UPDATE in place; the old value is gone; history reports change retroactively.
• Type 2: on change, close the current row's effective_to, open a new row with a new surrogate key and active=1.
• Type 3: retain previous value in a column and roll the current one; only last change survives.
• Facts reference the surrogate key of the version active at their event time.
• Invariants: one active row per natural key; contiguous effective dates (no gaps/overlap).

• A telecom models customer plans SCD-2; revenue by plan today vs plan-at-signup both compute correctly.
• An e-commerce company tracks product category changes type 1 (past doesn't matter) and customer tier type 2 (history drives retention analysis).
• A finance dim type 3 for 'previous manager' supports a compare-last-role report.

• Ask the question that must survive: 'sales by tier AT sale time' needs type 2; 'fix a typo' needs type 1.
• For type 2 add: surrogate key, business key, effective_from/to, current flag, and tests.
• Close periods to 'end of last month' where reports compare snapshots, to avoid mid-month flips.
• Document the type per attribute — a mixed dimension needs an attention note.

• Mixing type 1 and 2 by updating rows 'to be safe', losing both history and simplicity.
• Forgetting effective dates align, causing overlapping or gapped history.
• Two active rows per natural key after a partial load breaks every join.
• Reports counting dimension versions instead of fact members.

• History 'jumps' in reports? Check the SCD type — overwrite (T1) rewrites the past.
• Customer appearing twice in a group-by? Two active SCD-2 rows for one business key.
• Date slices inconsistent? Verify effective_to boundaries and the as-of rule.