Type 2 SCD Required Columns in Star Schema

Design and build semantic models
Answer Correct answer: C, D, E — Add a surrogate key, effective start date and time, and effective end date and time to track Type 2 SCD history.

You have a Fabric tenant that contains a warehouse. You are designing a star schema model that will contain a customer dimension. The customer dimension table will be a Type 2 slowly changing dimension (SCD). You need to recommend which columns to add to the table. The columns must NOT already exist in the source. Which three types of columns should you recommend? Each correct answer presents part of the solution. NOTE: Each correct answer is worth one point.

  1. a foreign key
  2. a natural key
  3. an effective end date and time Correct Answer
  4. a surrogate key Correct Answer
  5. an effective start date and time Correct Answer

Community Votes

CDE
100%

100% of anonymous learners picked answer CDE. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

Type 2 SCD implementation requires adding a surrogate key and effective start/end dates to track row versions, avoiding columns that already exist in the source like natural keys.

Designing a star schema with a Type 2 slowly changing dimension (SCD) requires adding specific columns to track historical changes accurately. This guide establishes that a surrogate key, effective start date and time, and effective end date and time are the necessary additions not found in the source system.

Choosing a natural key (B) because it is a primary key in the source, but the question specifies columns that must NOT already exist in the source, and natural keys already exist there.

Community Discussion (6 comments)

6c79d6f 👍 13 Selected: CDE
Because the table is set to SCD2 there will be a new row for each change in an existing row with an start and an end date (from when to when the row was valid). therefore the ' old' primary key will be duplicated. That is why a new key, a surrogate key, is introduced which makes each row unique again
stilferx 👍 4 Selected: CDE
IMHO, CDE, because: start & end - must surrogate - which is autoincremental id - is must. Link: https://learn.microsoft.com/en-us/training/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types
newusername 👍 3 Selected: CDE
To create SCD type 2 one needs to add a surrogate key + start/end date beside the other technical attributes. Therefore CDE.
vish9 👍 1 Selected: CDE
As per chat GPT: Surrogate keys are typically used in dimension tables rather than fact tables. In a data warehouse, a surrogate key is a unique identifier assigned to each record in a dimension table, usually for internal processing and joining purposes. It provides a stable reference to the dimension record, regardless of any changes in the natural key or other attributes.
Unbounded 👍 1 Selected: BCE
B: A natural key would be the Dim tables own Primarykey column not the source Primary key C & D: is requires to incorporate SCD Type 2.
VAzureD 👍 2 Selected: CDE
Let's discard, A. A FOREIGN KEY in SQL is a key (a column field) that is used to relate two tables. The FOREIGN KEY field is related or linked to the PRIMARY KEY of another database table. It already exists at the origin. B. Natural key, likewise, already exists in the origin. CDE, are the fields that we must create in our ETL to create an SCD2.

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Expert Analysis

Why the Answer Is Correct

To implement a Type 2 slowly changing dimension (SCD), you must track historical changes to dimension attributes by creating multiple rows for the same entity. This requires a surrogate key (D) to uniquely identify each row, replacing the natural key which will duplicate. Additionally, effective start date and time (E) and effective end date and time (C) columns are required to define the period during which each row version was valid. None of these columns exist in the source system.

Why the Other Options Are Wrong

A foreign key (A) is used to establish relationships between tables and typically exists in the source or is a structural element, not a specific tracking column added for SCD2. A natural key (B) is the primary key from the source system; since the question explicitly requires columns that do NOT already exist in the source, and natural keys inherently exist there, it is incorrect.

Community Comment Notes

Commenters correctly emphasize that for SCD2, a new row is created for each change, duplicating the old primary key, which necessitates a surrogate key. As one user noted, foreign and natural keys "already exists at the origin", so they cannot be selected.

Official Reference

https://learn.microsoft.com/en-us/training/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types

Exam Strategy

For SCD Type 2 questions, remember the three standard technical columns added during ETL: surrogate key, start date, and end date. Eliminate any option that represents a key already existing in the source system.

Related Analysis

You have a Fabric tenant that contains a lakehouse named Lak

A-Grade

You have a Fabric tenant that contains a semantic model. The

S-Grade

You have a Fabric tenant that contains a data pipeline. You

S-Grade

Practice All DP-600 Questions

Access 115 questions with complete answers and detailed explanations.

View Full DP-600 Practice Test →

← Back to DP-600 Study Guide