DAA-C01 Question 164
Single answer2.4 Use data modeling to manipulate the data to meet BI requirements.A retail company uses Snowflake as the source for several BI dashboards. Analysts report that sales totals are sometimes overstated when they join the SALES_FACT table to the PRODUCT_DIM table because product attributes such as category and brand change over time. The BI team needs a model that supports accurate historical reporting by the product attributes that were valid when each sale occurred, while still allowing users to analyze current product attributes separately. Which data modeling approach best meets this requirement?
- A
Store only the latest product attributes in PRODUCT_DIM and have dashboards filter SALES_FACT by sale date when historical reports are needed.
- B
Create a slowly changing dimension Type 2 PRODUCT_DIM with surrogate keys and effective date ranges, and store the corresponding product surrogate key in SALES_FACT.
- C
Replace PRODUCT_DIM with a view that selects DISTINCT product_id, category, and brand so that duplicate product rows do not affect BI joins.
- D
Flatten product attributes directly into SALES_FACT and update historical fact rows whenever product category or brand changes.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to model PRODUCT_DIM as a Type 2 slowly changing dimension and link SALES_FACT to the appropriate surrogate key. In dimensional modeling for BI, facts should join to the dimensional version that was valid when the event occurred if the business requires historically correct reporting. This avoids overstated or mismatched aggregates caused by joining fact rows to multiple or incorrect dimension versions. Snowflake supports these modeling patterns well because tables, views, and ELT processes can be used to maintain dimension history and populate surrogate-key relationships. This approach aligns with common Kimball-style BI modeling best practices and with Snowflake guidance to shape data models for analytics workloads, including star-schema patterns and history-preserving dimensions when business attributes change over time.
- A. Incorrect.
This is incorrect because storing only the current version of product attributes does not preserve historical context. Filtering the fact table by sale date does not solve the core issue: the dimension row no longer reflects the attributes that were valid at the time of sale. This is a common misconception when teams try to use transaction dates alone to reconstruct attribute history without modeling dimension changes.
- B. Correct.
This is correct because a Type 2 slowly changing dimension preserves historical versions of product attributes by creating a new dimension row whenever tracked attributes change. Using surrogate keys and effective date ranges allows each fact row to reference the exact dimensional state that was valid when the sale occurred. This is a standard dimensional modeling pattern for BI requirements involving historical accuracy and point-in-time analysis, while current-state analysis can still be supported by filtering to the active dimension version.
- C. Incorrect.
This is incorrect because using DISTINCT may reduce duplicated result rows in some queries, but it does not correctly model attribute history. If a product legitimately has multiple historical versions, collapsing rows with DISTINCT either loses valid history or still leaves ambiguity about which attributes should apply to a given sale. This treats the symptom of row multiplication rather than addressing the underlying many-to-one time-dependent relationship.
- D. Incorrect.
This is incorrect because denormalizing attributes into the fact table and retroactively updating historical fact rows would destroy historical accuracy. Historical sales should usually remain associated with the attributes that were valid at the time of the event. Updating past facts to current product attributes changes business meaning and can create audit and reconciliation issues. Although selective denormalization can sometimes help performance, it is not the correct primary modeling approach for this requirement.