ARA-C01 Question 120
Single answerData modelsA retail company is redesigning its analytics platform in Snowflake. The current star schema stores one row per order line in a FACT_SALES table, with DIM_CUSTOMER, DIM_PRODUCT, and DIM_DATE dimensions. Analysts now need to track changes to customer attributes such as loyalty_tier, region, and account_manager over time so that historical reports continue to reflect the values that were valid when each sale occurred. At the same time, the business wants to support current-state reporting using the latest customer attributes without rewriting every downstream query. Which design approach best meets these requirements while preserving query performance and dimensional modeling best practices?
- A
Implement DIM_CUSTOMER as a Type 2 slowly changing dimension with surrogate keys, store the customer surrogate key in FACT_SALES, and create a current-row flag or view for current-state reporting.
- B
Keep DIM_CUSTOMER as a Type 1 dimension and use Snowflake Time Travel on the table whenever analysts need historical customer attribute values for prior sales.
- C
Store all historical customer attribute versions in VARIANT within FACT_SALES so each fact row contains the complete customer state at the time of the transaction.
- D
Replace the star schema with a single denormalized SALES table that is rebuilt daily so both current and historical customer attributes are physically stored in the same table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to model DIM_CUSTOMER as a Type 2 slowly changing dimension and reference it from FACT_SALES through surrogate keys. This is a core dimensional modeling best practice for preserving attribute history while maintaining performant star-schema queries. In Snowflake, this pattern works well because joins across large fact and dimension tables are efficiently optimized, and the dimensional model remains understandable for BI tools and analysts. A current-row flag, effective start/end dates, or a view exposing only the latest dimension version can support current-state reporting without forcing all downstream consumers to redesign queries. By contrast, Time Travel is not intended to replace SCD modeling, semi-structured storage in the fact table is not appropriate for standard dimensions, and collapsing the model into a single denormalized table sacrifices maintainability and conformed-dimension design. These recommendations align with general Snowflake data modeling guidance and established Kimball-style dimensional modeling practices for analytics workloads.
- A. Correct.
Correct. A Type 2 slowly changing dimension is the standard dimensional modeling approach when historical attribute changes must be preserved. Each meaningful change creates a new dimension row with a new surrogate key and effective dating. FACT_SALES stores the surrogate key that was valid when the transaction occurred, so historical reporting remains accurate. To support current-state reporting, architects commonly expose only the active row through a view or use a current-row indicator, allowing downstream consumers to access the latest attributes without changing the fact design.
- B. Incorrect.
Incorrect. Type 1 overwrites history, so the dimension no longer preserves the attribute values that were valid at the time of each sale. Time Travel is intended for data recovery and short-term historical access to previous table states, not as a dimensional modeling substitute for business history. It also has retention limits and does not provide a maintainable semantic model for reporting over long periods.
- C. Incorrect.
Incorrect. Embedding all historical customer versions in VARIANT inside the fact table duplicates dimension data, increases storage and processing overhead, and makes joins and filtering more complex. It also breaks common star-schema design principles by pushing descriptive dimension history into the fact table. Although semi-structured data is supported in Snowflake, this is not an appropriate design for standard conformed customer dimensions used broadly across analytics workloads.
- D. Incorrect.
Incorrect. A fully denormalized table can simplify some read patterns, but rebuilding a wide table daily to capture both current and historical states creates significant maintenance overhead, duplicates data, and weakens dimensional consistency across subject areas. It also does not solve the modeling problem as cleanly as a Type 2 dimension and makes reuse of customer attributes across multiple fact tables more difficult.