DAA-C01 Question 166
Single answerSelect and implement an effective data modelA retail analytics team is redesigning its Snowflake data model for sales reporting. Analysts frequently join a 12-billion-row SALES_FACT table with CUSTOMER_DIM, PRODUCT_DIM, STORE_DIM, and DATE_DIM to build dashboards in BI tools. The current model stores many descriptive attributes directly in SALES_FACT, causing repeated values and inconsistent business definitions across reports. The team needs a model that improves maintainability, supports conformed dimensions across subject areas, and remains performant for analytic queries in Snowflake. Which design is the MOST appropriate?
- A
Implement a star schema with SALES_FACT at the center and separate dimension tables for customer, product, store, and date, keeping surrogate keys in the fact table.
- B
Flatten all dimension attributes into a single wide SALES table so BI tools can query one table without joins.
- C
Normalize the model into a highly relational 3NF design for reporting, splitting dimensions into multiple child tables to reduce storage duplication.
- D
Store each business subject area in separate denormalized tables with duplicated customer and product attributes so each dashboard can optimize for its own use case.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to implement a star schema. For analytic reporting, dimensional modeling is a best practice because it separates facts from descriptive dimensions, supports conformed dimensions, and makes business data easier for analysts and BI tools to consume. In this scenario, the problem statement specifically highlights duplicated descriptive data in the fact table and inconsistent definitions across reports. A star schema addresses both issues by moving descriptive attributes into shared dimensions and keeping the fact table focused on measurable events and foreign keys. This also aligns with Kimball-style dimensional modeling practices commonly used for enterprise analytics. In Snowflake, while the platform can handle large-scale joins efficiently, data model design still matters for usability, governance, and semantic consistency. Snowflake documentation and analytics best practices generally favor modeling approaches that simplify analytical consumption rather than highly normalized operational designs.
- A. Correct.
Correct. A star schema is the standard dimensional modeling approach for analytic workloads like dashboarding and ad hoc reporting. It centralizes numeric business events in a fact table and stores descriptive attributes in dimensions, which improves consistency, maintainability, and usability for BI tools. Using surrogate keys in the fact table helps support conformed dimensions and slowly changing dimension strategies. In Snowflake, star schemas are commonly effective for analytic models because they simplify joins and business semantics while separating measures from attributes.
- B. Incorrect.
Incorrect. Flattening all descriptive attributes into one very wide sales table may reduce joins, but it creates significant redundancy, increases storage of repeated dimension values, and makes governance harder. It also leads to inconsistent definitions when the same business entity appears in multiple subject areas. This approach can work for some narrow-serving marts, but it is not the best fit when the requirement is maintainability and conformed dimensions across the enterprise.
- C. Incorrect.
Incorrect. A highly normalized 3NF model is typically better suited for operational systems or integration layers than for end-user analytics. While normalization reduces duplication, it often increases join complexity and makes BI reporting less intuitive. For Snowflake analytic consumption, dimensional modeling is generally preferred because it aligns better with reporting patterns and business-friendly query design.
- D. Incorrect.
Incorrect. Creating separate denormalized tables for each dashboard can appear attractive for performance tuning, but it duplicates logic and attributes across subject areas, increasing the risk of inconsistent KPIs and customer/product definitions. This directly conflicts with the requirement for conformed dimensions and maintainability. It also creates long-term operational overhead as each dashboard-specific table must be maintained separately.