DP-203 · Question #275
You are implementing a star schema in an Azure Synapse Analytics dedicated SQL pool. You plan to create a table named DimProduct. DimProduct must be a Type 3 slowly changing dimension (SCO) table…
The correct answer is C. [OriginalProductDescription] NVARCHAR(2000) NOT NULL E. [OriginalColor] NVARCHAR(50) NOT NULL F. [OriginalProductName] NVARCHAR(100) NULL. Type 3 SCDs track changes by adding "original" columns alongside "current" columns - one pair per changeable attribute, no extra rows. Since ProductName, ProductDescription, and Color can change, Type 3 requires storing the previous value for each: OriginalProductName (F)…
Question
You are implementing a star schema in an Azure Synapse Analytics dedicated SQL pool. You plan to create a table named DimProduct. DimProduct must be a Type 3 slowly changing dimension (SCO) table that meets the following requirements:
- The values in two columns named ProductKey and ProductSourceID will
remain the same.
- The values in three columns named ProductName, ProductDescription,
and Color can change. You need to add additional columns to complete the following table definition.
Exhibit
Options
- A[EffectiveStartDate] [datetime] NOT NULL
- B[EffectiveEndDate] [datetime] NOT NULL
- C[OriginalProductDescription] NVARCHAR(2000) NOT NULL
- D[IsCurrentRow] [bit] NOT NULL
- E[OriginalColor] NVARCHAR(50) NOT NULL
- F[OriginalProductName] NVARCHAR(100) NULL
How the community answered
(28 responses)- A4% (1)
- B11% (3)
- C79% (22)
- D7% (2)
Explanation
Type 3 SCDs track changes by adding "original" columns alongside "current" columns - one pair per changeable attribute, no extra rows. Since ProductName, ProductDescription, and Color can change, Type 3 requires storing the previous value for each: OriginalProductName (F), OriginalProductDescription (C), and OriginalColor (E). The "current" values stay in the existing columns, while these new "Original" columns capture what the values were before the last change.
Why A, B, and D are wrong: EffectiveStartDate (A), EffectiveEndDate (B), and IsCurrentRow (D) are hallmarks of Type 2 SCDs, which track full history by inserting new rows and using date ranges or a current-row flag to identify the active record. Type 3 never inserts new rows, so it has no need for these constructs.
Memory tip: Think of SCD types by what they add - Type 1 adds nothing (overwrites), Type 2 adds rows (with dates/flags), Type 3 adds columns (with "Original" or "Previous" prefixes). If you see EffectiveDate or IsCurrentRow, it's Type 2; if you see OriginalX columns, it's Type 3.
Topics
Community Discussion
No community discussion yet for this question.