DAA-C01 Question 168
Single answerIdentify when to use a data model and when to use a flattened data setA retail company stores sales data in Snowflake using a star schema with a large FACT_SALES table and dimensions for DATE, STORE, PRODUCT, CUSTOMER, and PROMOTION. Analysts use this model for ad hoc analysis in Snowsight and BI tools. A second group of business users needs a very fast, easy-to-query dataset for a fixed weekly executive dashboard that shows only revenue, units sold, margin, region, product category, and week. The dashboard logic changes rarely, and the users do not need to drill into detailed dimensional attributes. What is the BEST design choice for this requirement?
- A
Keep only the star schema and require dashboard queries to join all dimensions, because normalized or dimensional models are always preferred for analytics in Snowflake.
- B
Create a flattened dataset specifically for the executive dashboard while retaining the star schema for broader self-service and ad hoc analysis needs.
- C
Replace the star schema entirely with a single flattened table so that both ad hoc analysts and dashboard users query the same denormalized structure.
- D
Store the dashboard output as a VARIANT column in a semi-structured table, because executive dashboards do not require relational modeling.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to support both patterns for the right purpose: retain the star schema for flexible analysis and create a flattened dataset for the fixed dashboard. In Snowflake, dimensional models are valuable when analysts need reusable business dimensions, drill-down paths, and support for many changing questions across BI tools. Flattened datasets are often effective when the use case is highly specific, the attributes are stable, the grain is well understood, and simplicity for downstream users is a priority. This mirrors established data warehousing best practices: dimensional models optimize for reuse and analytical flexibility, while denormalized or flattened presentation-layer datasets optimize for ease of querying and narrow reporting needs. Snowflake documentation on tables, views, and performance optimization supports choosing structures based on workload patterns rather than applying a single model universally.
- A. Incorrect.
This is incorrect. A dimensional model is very useful for flexible analytics, conformed dimensions, and reuse across many reporting use cases, but it is not automatically the best structure for every workload. For a stable, narrow dashboard with limited fields and little need for drill-down, a flattened dataset can simplify SQL, reduce join complexity, and improve ease of consumption. The misconception is assuming one model should serve all use cases equally well.
- B. Correct.
This is correct. The scenario clearly separates two workloads: flexible ad hoc analysis and a fixed executive dashboard. The star schema should be retained for broad analytical exploration, governed dimensions, and extensibility. A flattened dataset is appropriate for the dashboard because it has a stable grain, limited attributes, and minimal need for drill-across or dimensional navigation. This aligns with common analytics design practice in Snowflake: use dimensional models when users need flexibility and reusable business entities, and use flattened datasets when the requirement is narrowly scoped, stable, and optimized for simple consumption.
- C. Incorrect.
This is incorrect. Replacing the star schema with a single flattened table would reduce flexibility for ad hoc analysts, make reuse of dimensions harder, and can introduce duplication and governance challenges as requirements expand. Flattened datasets are often useful as purpose-built presentation-layer objects, but they are generally not a complete substitute for a well-designed dimensional model when many analytical questions must be supported.
- D. Incorrect.
This is incorrect. VARIANT is useful for semi-structured data such as JSON, Avro, Parquet, ORC, or XML, but it is not an appropriate recommendation merely because the output is for executives. The dashboard metrics and attributes described are structured, relational analytics fields. Using VARIANT here would add unnecessary complexity and does not address the core design decision between a reusable data model and a flattened presentation dataset.