Explanation

In data warehousing, Slowly Changing Dimensions (SCDs) are strategies for managing changes to dimension data over time. The question specifically describes a scenario where:

• Dimension attribute values need to be tracked over time
• Full history must be preserved
• New rows are added when data changes

This perfectly matches Type 2 SCD characteristics:

Why Type 2 (Option C) is Correct:

• Preserves Complete History: Type 2 SCD maintains a full historical record by adding new rows for each change, rather than overwriting existing data
• Row-Based Tracking: Each version of a dimension member gets its own row with effective date ranges (start_date and end_date columns)
• Historical Analysis: Enables time-based analysis and reporting by maintaining all historical states of dimension attributes
• Common Practice: This is the most widely used SCD type for tracking historical changes in data warehousing

Why Other Options Are Not Suitable:

Type 0 (Option A): Retains original values and never changes - does not track any historical changes

Type 1 (Option B): Overwrites old data with new values - loses historical information entirely

Type 3 (Option D): Adds new columns for previous values but only tracks limited history (typically just the previous value) - does not preserve complete history through new rows

Type 2 SCD is the optimal choice when business requirements demand maintaining a complete historical record of dimension attribute changes, which aligns precisely with the scenario described in the question.