Understanding Type 3 Slowly Changing Dimensions (SCD)

Type 3 SCDs are designed to track limited historical data by adding additional columns to store previous values of changing attributes, rather than creating new rows like Type 2 SCDs.

Analysis of the Requirements

The question specifies that:

• ProductKey and ProductSourceID remain constant (these are the natural keys)
• ProductName, ProductDescription, and Color can change (these are the attributes requiring SCD tracking)

Why CEF is Correct

C. [OriginalProductDescription] NVARCHAR(2000) NOT NULL

• This column stores the original value of the changing ProductDescription attribute
• Type 3 SCD requires storing both current and original values for tracked attributes
• The NOT NULL constraint ensures data integrity

E. [OriginalColor] NVARCHAR(50) NOT NULL

• This column stores the original value of the changing Color attribute
• Similar to ProductDescription, Color changes need to be tracked with original values
• The data type and length match typical color attribute requirements

F. [OriginalProductName] NVARCHAR(100) NULL

• This column stores the original value of the changing ProductName attribute
• The NULL constraint allows for cases where products might not have an original name
• Completes the tracking for all three changing attributes

Why Other Options Are Incorrect

A. [EffectiveStartDate] [datetime] NOT NULL and B. [EffectiveEndDate] [datetime] NOT NULL

• These are characteristic of Type 2 SCDs, not Type 3
• Type 3 SCDs don't use effective date ranges; they store original and current values in the same row

D. [IsCurrentRow] [bit] NOT NULL

• This flag is used in Type 2 SCDs to identify the current active record
• Type 3 SCDs maintain only one row per entity with both original and current values

Implementation Approach

In a Type 3 SCD implementation:

• The table would contain both the current columns (ProductName, ProductDescription, Color)
• Plus the original columns (OriginalProductName, OriginalProductDescription, OriginalColor)
• When an attribute changes, the current value is updated while the original value remains preserved
• This allows querying both current and historical states without complex joins or temporal queries

This approach is optimal for scenarios where you need to track limited historical changes and maintain query simplicity in your Azure Synapse Analytics dedicated SQL pool.