Explain slowly changing dimensions Type 1, Type 2, and Type 3. Which statement accurately describes Type 2 behavior?

• A. Type 2 overwrites old data with new values.
• B. Type 2 preserves history by creating new rows.
• C. Type 2 stores limited history in separate attributes.
• D. Type 2 deletes previous values when updates occur.

Answer: (B)

Type 2 keeps full history by adding a new row when a dimensional attribute changes. Instead of replacing the old information, a new version of the dimension member is inserted with its own surrogate key and the updated values. The old row remains in the warehouse, marked as no longer current (often with a valid-to date or a current flag), so you can see what the dimension looked like at any point in time.

For example, in a customer dimension, when a customer moves from one city to another, you create a new row for that same business key with the new address, and you mark the previous row as ended or not current. This way, queries can reconstruct the customer’s attributes as of a particular date and analyze changes over time.

This is different from overwriting the old data (which is Type 1 and loses history) and from storing only a small amount of history in a single attribute (which resembles Type 3, where you keep limited past data in a separate field). It’s also not about deleting previous values, which would discard historical information. Type 2’s strength is preserving a complete history of changes for accurate temporal analysis.