Data Engineering Slowly Changing Dimensions (SCDs) 3 — Questions and Answers
Question 1: When loading a Type 2 dimension, a record's tracked attribute changes. What is the correct sequence?
- Expire the current row, then insert a new row marked current (Correct answer)
- Delete the old row, then insert a new one
- Update the old row in place
- Insert a new row and leave the old one current
Correct answer: Expire the current row, then insert a new row marked current
You close the existing version (set end_date, is_current=false) and insert a fresh current row.
Question 2: Which technique efficiently detects whether any tracked column changed during a Type 2 load?
- Comparing a hash of the tracked columns (Correct answer)
- Comparing each column with a separate query
- Reloading the entire table every run
- Using row counts only
Correct answer: Comparing a hash of the tracked columns
Hashing the tracked columns lets a single comparison detect any change quickly.
Question 3: A late-arriving dimension means a fact references a dimension member that:
- Does not yet exist in the dimension when the fact loads (Correct answer)
- Has been deleted from the source
- Is duplicated across versions
- Has a NULL surrogate key by design
Correct answer: Does not yet exist in the dimension when the fact loads
Late-arriving dimensions require inserting a placeholder member so the fact can be loaded.
Question 4: How is a high-end_date often represented for the current Type 2 row?
- A far-future sentinel date like 9999-12-31 (Correct answer)
- NULL only
- The load timestamp
- 0000-00-00
Correct answer: A far-future sentinel date like 9999-12-31
A far-future sentinel keeps BETWEEN range queries simple while marking the row as open.
Question 5: What problem can occur if a non-tracked attribute is mistakenly configured as Type 2?
- Unnecessary version explosion bloating the dimension (Correct answer)
- Loss of all history
- Broken surrogate keys
- Facts failing to load entirely
Correct answer: Unnecessary version explosion bloating the dimension
Treating volatile non-essential columns as Type 2 creates many rows and inflates storage.
Question 6: In a hybrid Type 6 SCD, which behaviors are combined?
- Type 1, 2, and 3 (1+2+3=6) in one dimension (Correct answer)
- Type 2 and a fact table
- Type 0 and Type 1 only
- Two separate dimensions merged
Correct answer: Type 1, 2, and 3 (1+2+3=6) in one dimension
Type 6 blends overwrite, new-row history, and a current-value column for flexible reporting.
Question 7: A mini-dimension is commonly used to:
- Pull rapidly changing attributes out of a large dimension (Correct answer)
- Replace the fact table
- Store only surrogate keys
- Hold deleted records
Correct answer: Pull rapidly changing attributes out of a large dimension
Mini-dimensions isolate fast-changing attributes to prevent version explosion in the main dimension.
Data Engineering Slowly Changing Dimensions (SCDs) 3
When loading a Type 2 dimension, a record's tracked attribute changes.
What is the correct sequence?