You are implementing a medallion architecture in a Fabric lakehouse. You plan to create a dimension table that will contain the following columns: • ID • CustomerCode • CustomerName • CustomerAddress • CustomerLocation • ValidFrom • ValidTo You need to ensure that the table supports the analysis of historical sales data by customer location at the time of each sale Which type of slowly changing dimension (SCD) should you use?

The requirement is to analyze historical sales data using the customer's location as it was at the time of each sale. This necessitates preserving a complete history of changes to the customer dimension. A Type 2 Slowly Changing Dimension (SCD) is designed for this exact purpose. It tracks historical data by creating a new record for each change to a specific attribute. The ValidFrom and ValidTo columns, along with a surrogate key (ID), are used to define the time period during which each version of the customer record was active. This allows fact table records (sales) to be joined to the correct historical version of the customer dimension, ensuring analytical accuracy.

B. Type 0: This type is used for fixed dimensions where attributes are assumed to never change. It is unsuitable for tracking customer address changes.

C. Type 1: This type overwrites the old attribute value with the new one, thereby losing all historical context. This would make it impossible to analyze sales by past locations.

D. Type 3: This type tracks limited history by adding a new column to store only the previous value of an attribute. It does not maintain a full history of all changes over time.

1. Microsoft Learn

"Tutorial: Load data to a slowly changing dimension type 2 in Microsoft Fabric": This document explicitly describes the use case for SCD Type 2. It states

"A slowly changing dimension (SCD) Type 2 is a dimension that manages and stores data that changes over time. It keeps a full history of changes." This directly aligns with the question's requirement to analyze historical data.

2. Microsoft Learn

"Slowly changing dimensions": This documentation provides a clear definition of SCD types. For Type 2

it explains

"When the value of a source attribute changes

a new dimension record is added... The versioning columns are updated to identify the effective date ranges." This matches the table structure (ValidFrom

ValidTo) proposed in the question.

3. Kimball

R.

& Ross

M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3rd ed.). Wiley. In Chapter 5

"Dimensional Modeling Techniques

" the authors detail the design of SCDs. Type 2 is presented as the standard method for adding new rows to capture a complete history of attribute changes

which is the core requirement of the scenario.