Data Engineering Data Warehouse Modeling 3 — Questions and Answers
Question 1: A Slowly Changing Dimension Type 2 handles attribute changes by:
- Overwriting the old value
- Adding a new row to preserve history (Correct answer)
- Deleting the dimension
- Storing changes in the fact table
Correct answer: Adding a new row to preserve history
SCD Type 2 inserts a new dimension row for each change, preserving full historical context.
Question 2: An SCD Type 1 approach manages a changed attribute by:
- Adding a new row
- Overwriting the existing value with no history (Correct answer)
- Creating a new column
- Archiving to a separate table
Correct answer: Overwriting the existing value with no history
SCD Type 1 simply overwrites the old value, keeping no record of prior states.
Question 3: Which columns are typically added to support an SCD Type 2 dimension?
- Only a surrogate key
- Effective date, expiration date, and a current flag (Correct answer)
- A single timestamp
- A foreign key to the fact table
Correct answer: Effective date, expiration date, and a current flag
SCD Type 2 uses effective/expiration dates and a current-record indicator to track row validity periods.
Question 4: An SCD Type 3 dimension tracks change by:
- Adding a new row per change
- Storing a limited 'previous value' column alongside the current value (Correct answer)
- Overwriting silently
- Using a bridge table
Correct answer: Storing a limited 'previous value' column alongside the current value
SCD Type 3 keeps a previous-value column, allowing comparison between current and one prior state.
Question 5: A rapidly changing attribute (like customer age band) is best handled by splitting it into a:
- Factless fact table
- Mini-dimension (junk or demographic dimension) (Correct answer)
- Surrogate key
- Snapshot table
Correct answer: Mini-dimension (junk or demographic dimension)
Volatile attributes are moved into a mini-dimension to avoid exploding the main dimension's row count.
Question 6: What is a hybrid SCD Type 6 technique a combination of?
- Types 1, 2, and 3 (Correct answer)
- Types 2 and 4 only
- Types 1 and 5
- Types 3 and 7
Correct answer: Types 1, 2, and 3
Type 6 (1+2+3) combines overwrite, new-row history, and previous-value columns for flexible reporting.
Question 7: Why is overwriting a natural key value risky in a dimension table?
- It speeds up queries too much
- It can break historical fact-to-dimension relationships (Correct answer)
- It removes the need for surrogate keys
- It always violates normalization
Correct answer: It can break historical fact-to-dimension relationships
Surrogate keys insulate facts from natural-key changes, which otherwise could corrupt historical joins.
Data Engineering Data Warehouse Modeling 3
A Slowly Changing Dimension Type 2 handles attribute changes by: