Question

You are building a star schema in a Fabric warehouse for a retail sales analysis. The Product dimension must track historical changes so that sales facts are always associated with the product attributes (price tier, category) that were in effect at the time of the sale. Customers frequently reclassify products between categories. Which design approach should you implement for the DimProduct dimension?

Accepted Answer

Implement a Type 2 slowly changing dimension using a surrogate key, with EffectiveDate, EndDate, and IsCurrent columns, and reference the surrogate key in the fact table.

Answer

Implement a Type 1 slowly changing dimension that overwrites the product attributes in place, keyed on the natural ProductID.

Answer

Store the product attributes directly on the fact table to avoid joins, denormalizing the entire product hierarchy.

Answer

Use the natural business key ProductID as the fact table foreign key and add a single CurrentCategory column that is updated on each change.