Which pattern preserves historical values in a dimensional model during changes over time?

Which pattern preserves historical values in a dimensional model during changes over time?

• A. Type 2 Slowly Changing Dimension
• B. Type 1
• C. Type 3
• D. Type 0

Answer: (A)

When you want to preserve historical values as data changes over time, you record each change as a new version of the dimension row. The Type 2 Slowly Changing Dimension does this by creating a new row with a new surrogate key whenever a dimension attribute changes, while leaving the old row intact. You typically see validity fields like start_date, end_date (and often a current flag) to indicate when that version was valid. This allows you to reconstruct the exact state of the dimension at any point in time and perform accurate time-based analyses. Other patterns either overwrite existing data (losing history), store only a limited number of previous values in the same row (limited history), or don’t track changes at all (no history). So, the pattern that preserves historical values during changes over time is the Type 2 Slowly Changing Dimension.

When you want to preserve historical values as data changes over time, you record each change as a new version of the dimension row. The Type 2 Slowly Changing Dimension does this by creating a new row with a new surrogate key whenever a dimension attribute changes, while leaving the old row intact. You typically see validity fields like start_date, end_date (and often a current flag) to indicate when that version was valid. This allows you to reconstruct the exact state of the dimension at any point in time and perform accurate time-based analyses. Other patterns either overwrite existing data (losing history), store only a limited number of previous values in the same row (limited history), or don’t track changes at all (no history). So, the pattern that preserves historical values during changes over time is the Type 2 Slowly Changing Dimension.