DAA-C01 Question 112
Single answerDomain 2.0: Data Transformation and Data Modeling (23%)A retail analytics team is redesigning its Snowflake semantic layer for sales reporting. The current SALES_FACT table stores one row per order line and includes ORDER_ID, ORDER_TS, CUSTOMER_ID, PRODUCT_ID, STORE_ID, QUANTITY, UNIT_PRICE, DISCOUNT_AMT, and several descriptive columns such as PRODUCT_NAME and STORE_REGION. Analysts frequently join this table to product, customer, store, and date-related tables to build dashboards. Query performance is inconsistent, and the team wants a model that is easier for BI users to understand while preserving flexibility for slicing by customer, product, store, and calendar attributes. Which design approach is the BEST fit for this requirement?
- A
Keep all descriptive attributes in the SALES_FACT table so BI tools can avoid joins, creating a single wide fact table for reporting
- B
Model the data as a star schema with SALES_FACT containing measures and foreign keys, and separate dimension tables for product, customer, store, and date
- C
Normalize all dimensions into many related sub-dimension tables and require analysts to join across the fully normalized model for each report
- D
Replace the dimensional model with a single view that aggregates SALES_FACT to monthly totals by product category and store region
Show answer and explanation
Correct answer: B
Explanation
The best answer is to model the data as a star schema. For analytical workloads, Snowflake supports dimensional modeling patterns commonly used in data warehousing: a fact table at a clearly defined grain and dimension tables containing descriptive attributes. In this scenario, the grain is one row per order line, which belongs in SALES_FACT along with measures such as QUANTITY, UNIT_PRICE, and DISCOUNT_AMT. Descriptive attributes like PRODUCT_NAME and STORE_REGION should generally be stored in their respective dimensions rather than duplicated in the fact table. This improves usability for BI consumers, supports conformed dimensions, and simplifies governance when attributes change. A star schema is typically preferred over a highly normalized snowflake design when the goal is ease of use and flexible slicing across dimensions. This reflects common dimensional modeling best practices described by Kimball-style warehouse design and is consistent with Snowflake guidance for organizing analytical data for reporting and BI consumption.
- A. Incorrect.
This is not the best fit. A very wide fact table can reduce some joins, but it mixes measures with repeating descriptive attributes, increases storage duplication, and makes governance and maintenance harder when descriptive attributes change. It also weakens the clarity of dimensional modeling for BI tools and users. While denormalized reporting tables can be useful in specific cases, they are generally less flexible and less maintainable than a well-designed star schema for broad analytical use cases.
- B. Correct.
This is correct. A star schema is the standard dimensional modeling approach for analytical workloads like sales reporting. The fact table should contain transactional grain, additive or semi-additive measures, and foreign keys to dimensions. Separate dimension tables for product, customer, store, and date make the model easier for BI users to understand, support flexible slicing and filtering, and reduce duplication of descriptive attributes. This aligns with best practices for analytic data modeling in Snowflake and downstream BI semantic layers.
- C. Incorrect.
This is not the best fit for the stated requirement. Fully normalizing dimensions moves the design toward a snowflake schema, which can be appropriate in some cases, but it generally increases join complexity and makes the model less intuitive for BI users. The scenario specifically emphasizes usability and consistent reporting performance. A star schema usually better balances usability, maintainability, and analytical flexibility.
- D. Incorrect.
This is incorrect because it removes too much detail. Aggregating to monthly totals by product category and store region may improve performance for one narrow dashboard, but it does not preserve flexibility for slicing by customer, product, store, and calendar attributes at multiple levels of detail. It also prevents many ad hoc analyses that require order-line grain.