Data Engineering Slowly Changing Dimensions (SCDs) 2 — Questions and Answers
Question 1: In a Type 2 SCD, what is the primary purpose of a surrogate key on the dimension table?
- To uniquely identify each historical version of a dimension record (Correct answer)
- To store the natural business key from the source
- To encrypt sensitive customer data
- To enforce referential integrity with the source system
Correct answer: To uniquely identify each historical version of a dimension record
Surrogate keys let multiple rows share one natural key while each version remains uniquely identifiable.
Question 2: A Type 1 SCD update is applied to a customer's address. What happens to the prior value?
- It is overwritten and lost (Correct answer)
- It is moved to a history table automatically
- A new row is inserted with an end date
- A flag marks it inactive
Correct answer: It is overwritten and lost
Type 1 overwrites the existing value, retaining no history of the previous state.
Question 3: Which columns are most commonly added to support a Type 2 dimension?
- effective_date, end_date, and is_current flag (Correct answer)
- created_by and updated_by
- hash_key and load_id only
- primary_key and foreign_key
Correct answer: effective_date, end_date, and is_current flag
Validity dates plus a current-record indicator are the standard Type 2 tracking columns.
Question 4: What does a Type 3 SCD typically store to track change?
- A previous-value column alongside the current value (Correct answer)
- Unlimited historical rows
- Only the most recent value
- A separate audit log per row
Correct answer: A previous-value column alongside the current value
Type 3 adds a column (e.g., previous_address) to hold one prior value, allowing limited history.
Question 5: Why are surrogate keys preferred over natural keys when joining facts to a Type 2 dimension?
- They point a fact to the correct dimension version active at event time (Correct answer)
- They are always smaller integers
- They prevent NULL values in facts
- They avoid the need for indexes
Correct answer: They point a fact to the correct dimension version active at event time
The surrogate key resolves to the specific historical row valid when the fact occurred.
Question 6: A business wants to keep full history but only queries the current value 95% of the time. Which design helps?
- Type 2 with an is_current flag for fast current-state filtering (Correct answer)
- Type 1 only
- Type 0 with no changes allowed
- Storing all versions in a JSON blob
Correct answer: Type 2 with an is_current flag for fast current-state filtering
The current flag lets common queries filter to active rows while full history remains available.
Question 7: What is a Type 0 SCD?
- An attribute that never changes once written (retain original) (Correct answer)
- An attribute overwritten on every load
- An attribute with unlimited history
- An attribute split across two tables
Correct answer: An attribute that never changes once written (retain original)
Type 0 preserves the original value permanently and ignores later source changes.
Data Engineering Slowly Changing Dimensions (SCDs) 2
In a Type 2 SCD, what is the primary purpose of a surrogate key on the dimension table?