For scenario 1 (create a new version of the row), which Slowly Changing Dimension type is appropriate?

• A. Type 0
• B. Type 1
• C. Type 2
• D. Type 3

Answer: (C)

When you want to preserve how a dimension row changes over time, you use a versioning approach that keeps every state. Slow Changing Dimension Type 2 does exactly that: it creates a new row to represent the new state while keeping the old row intact but marked as historical. This lets you retain the full history of changes, so queries can show both past and current values.

Key idea: you insert a new row for the updated attributes and assign it a new surrogate key, with a start date (or effective_from) that marks when that version becomes valid. The existing row gets its end date (or effective_to) updated to the day before the new version starts, and it’s treated as historical. With this setup, current records are those with no end date (or flagged as current), and historical records remain accessible for reporting.

Why this fits “create a new version of the row” better than the other types: Type 1 would overwrite the old value, losing history. Type 0 doesn’t change the data at all. Type 3 stores only a limited, single-value history (like a previous value in one column) rather than a full row versioning history. Type 2 is the option that keeps every version as its own row, enabling complete historical traceability.