DAA-C01 Question 23
Single answerThe level of data granularity requiredA retail company stores detailed point-of-sale transactions in Snowflake at the line-item level, including timestamp, store, customer, product, quantity, discount, and net sales amount. Business analysts use a BI tool to review hourly sales trends, basket composition, and the impact of promotions. They also frequently drill from store-level summaries down to individual transactions when investigating anomalies. Query performance has become inconsistent during peak reporting times. The data analyst needs to design a semantic layer and supporting data structures that balance performance with analytical flexibility. Which approach best addresses the required level of data granularity?
- A
Store only daily aggregated sales by store and product category, because most dashboards summarize trends and Snowflake can reconstruct lower-level details when needed.
- B
Model the semantic layer only on the raw transaction line table, because keeping the finest grain eliminates ambiguity and aggregation logic can be handled entirely by the BI tool.
- C
Retain the atomic transaction line table as the core fact source, and add purpose-built aggregate tables or dynamic tables for common hourly and store-level reporting patterns.
- D
Replace the transaction line table with a materialized view aggregated by hour, store, and product, because materialized views are intended to serve all granular and summary reporting needs.
Show answer and explanation
Correct answer: C
Explanation
The key design principle is to store data at the lowest grain required by the business questions, then add higher-level summarized structures only where they provide clear performance benefits. In this scenario, analysts need both summarized reporting and drill-down to individual transactions, so the atomic line-item grain must be retained. Snowflake best practices support using summary tables, including dynamic tables where appropriate, to optimize repeated aggregations while preserving detailed source data for flexible analysis. This aligns with dimensional modeling guidance: define the fact table grain explicitly, keep it consistent, and create aggregate data products for common access patterns rather than replacing atomic facts. Materialized views can accelerate certain queries, but they are not a substitute for maintaining the necessary detailed grain when downstream analysis requires it.
- A. Incorrect.
Incorrect. Aggregating only to daily sales by store and product category removes necessary detail for basket analysis, promotion impact at transaction level, and drill-down to individual transactions. Snowflake cannot recreate dimensions or measures that were discarded during aggregation. This option reflects the misconception that summarized data can fully substitute for atomic facts.
- B. Incorrect.
Incorrect. Using only the raw line-item table preserves maximum detail, but it does not best balance performance with analytical flexibility in this scenario. High-concurrency BI workloads with repeated hourly and store-level summaries often benefit from pre-aggregated structures for common access patterns. Relying entirely on the BI tool to aggregate large atomic datasets can lead to inconsistent performance.
- C. Correct.
Correct. This is the best practice for balancing granularity and performance. Keeping the atomic fact table preserves drill-down capability and supports analyses such as basket composition and anomaly investigation. Adding aggregate tables or dynamic tables for common query grains, such as hourly by store, improves performance for repetitive dashboard workloads while still allowing users to navigate to detailed records when needed.
- D. Incorrect.
Incorrect. A materialized view aggregated by hour, store, and product may help some summary queries, but it cannot replace the base transaction line table when analysts need transaction-level drill-down or basket analysis. In addition, materialized views are not a universal solution for every reporting grain and should be used selectively based on query patterns and maintenance considerations.