Question 38
Domain 10: Data ModellingA product dimension must preserve historical attribute changes while keeping fact tables stable for reporting over time. Which design is best?
Correct answer: D
Explanation
A surrogate key with SCD Type 2 rows preserves history by creating a new dimension row for each attribute change, so prior versions remain available for past reporting. Facts stay stable because they reference the surrogate key for the dimension version in effect at the time, matching the rule to "preserve historical attribute changes" while keeping fact tables unchanged.
Why each option is right or wrong
A. Use `product_name` as the natural key everywhere
Natural keys like product_name can change or be non-unique, so they are poor historical join keys.
B. Overwrite old dimension values in place
In-place overwrites remove prior attribute values, breaking historical reporting and trend analysis.
C. Keep the product attributes only in the fact table
Storing attributes only in facts duplicates data and does not manage dimension history cleanly.
D. Use a surrogate key and SCD Type 2 dimension rows referenced by facts
Under dimensional modeling practice, a Type 2 slowly changing dimension is the standard way to retain attribute history: each change inserts a new row with a new surrogate key, while the prior row is left intact with effective-date/end-date tracking. Facts should point to the surrogate key version valid at the transaction date, so the fact table does not need to be rewritten when the descriptive attributes change, preserving stable reporting across time.