Answer: [CurrentProductCategory] [nvarchar] (100) NOT NULL,, [OriginalProductCategory] [nvarchar] (100) NOT NULL,

## Type 3 Slowly Changing Dimension (SCD) Implementation For a Type 3 SCD implementation in Azure Synapse Analytics dedicated SQL pool, the goal is to track both current and previous values of a changing attribute (in this case, product category) within the same row, rather than creating new rows as in Type 2. ### Correct Selections: **B. [CurrentProductCategory] [nvarchar] (100) NOT NULL** - This column stores the most recent/current value of the product category - The NOT NULL constraint ensures data integrity for the current value - The naming convention clearly indicates this is the current state **E. [OriginalProductCategory] [nvarchar] (100) NOT NULL** - This column preserves the original value of the product category when it first appeared - The NOT NULL constraint maintains data consistency - Together with CurrentProductCategory, this enables tracking of changes over time ### Why These Options Are Optimal: 1. **Type 3 SCD Pattern**: Type 3 dimensions maintain both original and current values in the same row, which is exactly what B and E accomplish. 2. **Data Integrity**: Both columns use NOT NULL constraints, ensuring complete data for both historical and current states. 3. **Clear Naming**: The column names explicitly indicate their purpose (Current vs Original), making the schema self-documenting. ### Why Other Options Are Less Suitable: **A. [EffectiveStartDate] [datetime] NOT NULL** - This is characteristic of Type 2 SCD implementations where new rows are created with effective date ranges - Type 3 doesn't require date tracking since changes are tracked within the same row **C. [EffectiveEndDate] [datetime] NULL** - Similar to A, this is used in Type 2 SCD for tracking validity periods of dimension records - Not applicable to Type 3 implementation **D. [ProductCategory] [nvarchar] (100) NOT NULL** - This generic naming doesn't distinguish between current and historical values - Without clear labeling, it's ambiguous whether this represents current or original state - The existing table already has a 'Category' column, making this redundant without clear purpose ### Best Practice Considerations: - Type 3 SCD is ideal when you need limited history (typically only previous value) and want to avoid the row proliferation of Type 2 - The implementation should clearly distinguish between current and historical values through descriptive column naming - This approach maintains referential integrity while providing basic change tracking capabilities

Author: LeetQuiz Editorial Team