Answer-first summary for fast verification
Answer: an effective end date and time, a surrogate key, an effective start date and time
For a Type 2 slowly changing dimension (SCD), three essential column types that are typically not present in the source and must be added are: a surrogate key (D), an effective start date and time (E), and an effective end date and time (C). The surrogate key provides a unique, stable identifier for each row, especially important in SCD Type 2 where multiple rows may exist for the same natural key due to changes over time. The effective start and end dates track the validity period of each row, enabling historical analysis. Options A (foreign key) and B (natural key) are incorrect because foreign keys are used in fact tables to reference dimension tables, and natural keys typically exist in the source and are not added as new columns in the dimension table design for SCD Type 2. The community discussion strongly supports CDE with 96% consensus, emphasizing the necessity of these columns for SCD Type 2 implementation in a star schema.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
You have a Fabric tenant with a warehouse. You are designing a star schema model that includes a customer dimension table implemented as a Type 2 slowly changing dimension (SCD). You need to recommend three types of columns to add to the table that are not already present in the source. Which three column types should you recommend?
A
a foreign key
B
a natural key
C
an effective end date and time
D
a surrogate key
E
an effective start date and time