DP-203 · Question #306
You have an Azure Synapse Analytics dedicated SQL pool named pool1. You plan to implement a star schema in pool and create a new table named DimCustomer by using the following code. You need to…
The correct answer is B. [EffectiveEndDate] [datetime] NOT NULL E. [EffectiveStartDate] [datetime] NOT NULL. Type 2 SCD tracks historical attribute changes by storing multiple rows per entity, each bounded by a validity date range. Two date columns are required to define when each version of a record is active.
Question
You have an Azure Synapse Analytics dedicated SQL pool named pool1. You plan to implement a star schema in pool and create a new table named DimCustomer by using the following code. You need to ensure that DimCustomer has the necessary columns to support a Type 2 slowly changing dimension (SCD). Which two columns should you add? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
Exhibit
Options
- A[HistoricalSalesPerson] [nvarchar] (256) NOT NULL
- B[EffectiveEndDate] [datetime] NOT NULL
- C[PreviousModifiedDate] [datetime] NOT NULL
- D[RowID] [bigint] NOT NULL
- E[EffectiveStartDate] [datetime] NOT NULL
How the community answered
(43 responses)- A9% (4)
- B74% (32)
- C14% (6)
- D2% (1)
Why each option
Type 2 SCD tracks historical attribute changes by storing multiple rows per entity, each bounded by a validity date range. Two date columns are required to define when each version of a record is active.
HistoricalSalesPerson is a business attribute, not a metadata column; adding it would store a measure, not track row validity periods required by SCD Type 2.
EffectiveEndDate marks when a historical version of the record expired; it is set to a sentinel future date (e.g., 9999-12-31) for the current record and updated when a newer version supersedes it. Together with EffectiveStartDate, it defines the validity window for each row, which is the core mechanism of SCD Type 2.
PreviousModifiedDate records when a source record was last changed in the source system, which is not the same as an effective date range bounding a historical dimension row.
RowID is a surrogate or sequence key used for row identification, not for defining the temporal validity window that distinguishes SCD Type 2 from other SCD types.
EffectiveStartDate marks when a version of the customer record became active, allowing queries to reconstruct the dimension's state at any point in time.
Concept tested: Type 2 slowly changing dimension date range columns
Source: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview
Topics
Community Discussion
No community discussion yet for this question.