What is the purpose of a type 2 slowly changing dimension in a data warehouse?

• A. To enforce data encryption
• B. To retain full history of values
• C. To optimize query performance by partitioning
• D. To minimize storage by deduplication

Answer: (B)

Type 2 slowly changing dimensions preserve the full history of dimension attribute values by creating a new row for each change rather than overwriting the existing one. In practice, when a property of a dimension changes (like a customer address or an employee role), a new row is added with the new value and its own effective period, while the previous row remains with the period during which that old value was valid. This lets you query the data as it existed at any point in time and analyze trends across historical states. It’s different from simply overwriting the old value (which loses history) or only keeping a single previous value, and it’s more robust for long-term trend analysis than strategies that aim to minimize storage or that reorganize data through partitioning or encryption. The tradeoff is increased storage and additional logic to manage the validity windows, but that storage cost is the price for complete historical insight.