Answer: [OriginalProductDescription] NVARCHAR(2000) NOT NULL, [OriginalColor] NVARCHAR(50) NOT NULL, [OriginalProductName] NVARCHAR(100) NULL

## 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.

Author: LeetQuiz Editorial Team