You need to design an Azure Synapse Analytics dedicated SQL pool that meets the following requirements: - Can return an employee record from a given point in time. - Maintains the latest employee info

The correct answer is D. as a Type 2 slowly changing dimension (SCD) table. A Type 2 SCD supports versioning of dimension members. Often the source system doesn't store versions, so the data warehouse load process detects and manages changes in a dimension table. In this case, the dimension table must use a surrogate key to provide a unique reference to

Submitted by kevin_r· Mar 30, 2026Data Modeling and Design

Question

You need to design an Azure Synapse Analytics dedicated SQL pool that meets the following requirements:

Can return an employee record from a given point in time.
Maintains the latest employee information.
Minimizes query complexity.

How should you model the employee data?

Options

Aas a temporal table
Bas a SQL graph table
Cas a degenerate dimension table
Das a Type 2 slowly changing dimension (SCD) table

How the community answered

(25 responses)

A
12% (3)
B
4% (1)
C
4% (1)
D
80% (20)

Explanation

A Type 2 SCD supports versioning of dimension members. Often the source system doesn't store versions, so the data warehouse load process detects and manages changes in a dimension table. In this case, the dimension table must use a surrogate key to provide a unique reference to a version of the dimension member. It also includes columns that define the date range validity of the version (for example, StartDate and EndDate) and possibly a flag column (for example, IsCurrent) to easily filter by current dimension members. https://docs.microsoft.com/en-us/learn/modules/populate-slowly-changing-dimensions-azure- synapse-analytics-pipelines/3-choose-between-dimension-types

Topics

#slowly changing dimension#Type 2 SCD#point-in-time retrieval#employee dimension

Community Discussion

No community discussion yet for this question.

Full DP-203 Practice