Slowly Changing Dimensions

How to handle dimension data that changes over time — three strategies, three trade-offs.

Slowly Changing Dimensions (SCDs) deal with the question: what do we do when a dimension attribute changes?

A customer moves to a new city. An employee changes departments. A product gets a new category. How do we update the dimension table? Do we overwrite? Keep history? It depends on what the business needs to know.

The three classic types

Type 1 — Overwrite

Simply replace the old value with the new one. No history retained.

Before update EmployeeID │ Name │ Department 101 │ John │ HR After update (department changes to Finance) EmployeeID │ Name │ Department 101 │ John │ Finance ← "HR" is lost forever

Pros: Simplest. Minimal storage. Always shows current value.
Cons: No history. Past reports become incorrect (historical sales now attributed to wrong department).
Use when: Corrections (fixing typos), attributes where history doesn't matter.

Type 2 — Add new row

Add a new row for the changed record, with effective date ranges. Keeps full history.

EmployeeDim — Type 2 SCD EmpSK │ EmployeeID │ Name │ Department │ StartDate │ EndDate │ IsCurrent 501 │ 101 │ John │ HR │ 2020-01-01 │ 2023-12-31 │ N 502 │ 101 │ John │ Finance │ 2024-01-01 │ 9999-12-31 │ Y EmpSK (surrogate key) makes each "version" of the employee unique. Old fact records still join to EmpSK 501; new ones join to EmpSK 502.

Pros: Complete history. Past reports remain accurate. Standard for data warehouses.
Cons: Table grows over time. More complex queries (need date filtering).
Use when: History matters — most analytics scenarios.

Querying Type 2 dimensions

SQL — find current employees

SELECT Name, Department FROM EmployeeDim WHERE IsCurrent = 'Y'; -- Or, to look up history at a specific point: SELECT Name, Department FROM EmployeeDim WHERE EmployeeID = 101 AND '2023-06-15' BETWEEN StartDate AND EndDate;

Type 3 — Add new column

Add a new column to store the previous value. Limited history — only what fits in the columns you add.

EmployeeDim — Type 3 SCD EmployeeID │ Name │ CurrentDept │ PreviousDept │ DeptChangeDate 101 │ John │ Finance │ HR │ 2024-01-01

Pros: Limited history without row explosion. Both values queryable in same row.
Cons: Only tracks the previous value (not the one before that). Schema changes if you want more history.
Use when: You only need "current vs previous" — rare in practice.

Side-by-side comparison

Aspect	Type 1	Type 2	Type 3
Approach	Overwrite	Add new row	Add new column
History	None	Full	Limited
Storage	Smallest	Grows over time	Wider rows
Query complexity	Simplest	Need date logic	Simple
Past report accuracy	Broken	Preserved	Preserved (last change)
Real-world frequency	Sometimes	Most common	Rare

Other SCD types (mentioned for completeness)

Type 0 — Never change. Use for date of birth, original signup date.
Type 4 — Maintain a separate history table.
Type 6 (hybrid) — Combination of Type 1, 2, and 3. Used when you want "current" and "as-of-transaction" simultaneously.

The interview answer "Type 1 overwrites — no history. Type 2 adds a new row with date ranges — full history, this is what most warehouses use. Type 3 adds a column for the previous value — limited history. Choice depends on whether the business needs to see historical state."

← Star vs Snowflake Schema Surrogate vs Natural Keys →