Type 2 SCD Required Columns in Star Schema

DP-600 By islever Editorial Last updated Oct 4, 2026

Design and build semantic models

Short answer

CDE Add a surrogate key, effective start date and time, and effective end date and time to track Type 2 SCD history.

Question

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.

a foreign key
a natural key
an effective end date and time Correct Answer
a surrogate key Correct Answer
an effective start date and time Correct Answer

Key Takeaways

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.

Common mistake: 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.

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

This link opens the official vendor documentation in a new tab.

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.

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.

Discussion 6 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors, not of islever. The comments shown first are the most substantive of the thread — ranked by depth, community endorsement and relevance; expand the list below to read the rest.

From public community threads 6

6c79d6f 👍 13

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

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

To create SCD type 2 one needs to add a surrogate key + start/end date beside the other technical attributes. Therefore CDE.
VAzureD 👍 2

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.
vish9 👍 1

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.

Show 1 more comment

Unbounded 👍 1

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.

Reader comments & corrections

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

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