DAA-C01 Question 167
Single answerIdentify when to use a data model and when to use a flattened data setA retail analytics team uses Snowflake to support two different reporting workloads. The executive dashboard shows daily sales, margin, and discount trends by date, region, product category, and channel, and must return results with minimal latency during business hours. A separate ad hoc analysis group frequently asks new questions that require drilling from sales facts into customer, product, promotion, and store attributes at different levels of detail, and business definitions change several times each quarter. The team is deciding whether to publish a flattened reporting table or a dimensional data model as the primary semantic source. Which approach best fits this scenario?
- A
Use a single flattened data set for both workloads because Snowflake performs best when all joins are removed, regardless of reporting flexibility requirements.
- B
Use a dimensional data model as the primary source because it better supports evolving business logic, reusable conformed dimensions, and drill-across analysis across multiple subject areas.
- C
Use a dimensional data model only for the executive dashboard because star schemas are optimized exclusively for fixed dashboards with no ad hoc exploration.
- D
Use a flattened data set as the primary source because dimensional models should be avoided when analysts need to query multiple levels of granularity.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use a dimensional data model as the primary semantic source when the business needs flexible ad hoc analysis, shared definitions, and the ability to combine multiple subject areas consistently. In Snowflake, a flattened data set is often appropriate for highly specific, stable reporting patterns where denormalization improves usability for a known set of queries, such as an executive dashboard with a fixed grain and limited dimensions. However, when requirements change frequently and users need to drill into multiple related entities, a dimensional model is more maintainable and scalable. This aligns with common analytics engineering and dimensional modeling best practices: use fact and dimension tables to manage grain, conformed dimensions, and reusable definitions; use flattened or aggregated tables selectively as downstream consumption layers for performance or simplicity when the reporting pattern is stable. Snowflake documentation and best practices for performance tuning emphasize designing for workload patterns rather than assuming that fewer joins are always better. In other words, choose a flattened data set for simple, stable consumption patterns, and choose a data model when flexibility, consistency, and maintainability are the priority.
- A. Incorrect.
Incorrect. A flattened data set can be useful for a narrow, stable reporting use case where the fields, grain, and metrics are well understood and query simplicity is the priority. However, saying it should be used for both workloads ignores the second team's need for flexible exploration, changing business definitions, and reuse of shared dimensions. Removing joins does not automatically make a flattened table the best design choice for all analytic scenarios in Snowflake.
- B. Correct.
Correct. A dimensional data model is the better primary semantic source when analysts need flexibility, consistent business definitions, and the ability to analyze across shared entities such as customer, product, promotion, and store. Star-schema-style modeling supports maintainability and reuse through fact and dimension tables, especially when business rules change over time. In this scenario, the executive dashboard could still be accelerated with a purpose-built flattened or aggregated presentation layer, but the core model should be dimensional to support the broader analytical workload.
- C. Incorrect.
Incorrect. This reverses the typical tradeoff. Fixed executive dashboards with a stable set of metrics and dimensions are often good candidates for flattened or aggregated presentation tables because they can reduce complexity for predictable queries. Dimensional models are not limited to fixed dashboards; they are commonly used specifically to support broader ad hoc exploration and consistent cross-functional analysis.
- D. Incorrect.
Incorrect. Dimensional models are designed to handle analysis at different levels of detail through well-defined facts, dimensions, hierarchies, and grain. The misconception here is that multiple levels of granularity require flattening. In practice, flattening can make maintenance harder when definitions change and can duplicate descriptive attributes across rows, while dimensional models preserve structure and support flexible slicing and dicing.