DAA-C01 Question 24
Single answerThe level of data granularity requiredA retail analytics team uses Snowflake to support executive dashboards and ad hoc analysis. They currently store one row per order line in a fact table with dimensions for date, store, product, and customer. Executives now want a dashboard showing daily sales by store and product category with sub-second response times during peak business hours. Analysts still need the ability to drill down later to individual orders for investigations. Which approach best meets these requirements while using an appropriate level of data granularity?
- A
Replace the order-line fact table with a new table aggregated to daily sales by store and product category, and drop the detailed table to reduce storage and improve query speed.
- B
Keep the detailed order-line fact table as the base granularity and create a derived aggregate table or dynamic table at daily sales by store and product category for the dashboard workload.
- C
Store only monthly sales by region because higher aggregation will give the best performance, and use SQL window functions to recreate daily store-level detail when needed.
- D
Create a view on top of the detailed order-line fact table and rely on the BI tool cache, since changing granularity in Snowflake is unnecessary when the source data is already available.
Show answer and explanation
Correct answer: B
Explanation
The key design principle is to choose and preserve the lowest level of granularity required for future analysis, while creating additional summary structures for known high-performance reporting use cases. In this scenario, the atomic fact grain is order line, which supports drill-down and investigative analysis. The dashboard, however, needs a higher grain: daily sales by store and product category. The best practice is therefore to retain the detailed fact table and add a derived aggregate table or dynamic table at the dashboard grain. This aligns with common dimensional modeling guidance that fact tables should be defined at a clear grain and that summary tables can be added for performance-sensitive workloads. In Snowflake, this pattern is often preferable to replacing atomic data with summary data, because summary-only models limit analytical flexibility. Views alone do not physically optimize granularity, and overly coarse aggregation cannot recover lost detail later.
- A. Incorrect.
Incorrect. Dropping the detailed order-line fact table removes the lowest-grain data needed for drill-down and investigations. In dimensional modeling and analytics design, the base fact table should usually be stored at the lowest practical grain required by business processes. Aggregating away detail may improve some dashboard queries, but it prevents later analysis at the order level.
- B. Correct.
Correct. This approach preserves the atomic grain needed for flexible analysis while providing a pre-aggregated structure aligned to the dashboard's required grain: daily by store and product category. In Snowflake, using a derived aggregate table or dynamic table for common summary workloads is a practical pattern to improve performance without sacrificing detailed analytical capability.
- C. Incorrect.
Incorrect. Monthly by region is a coarser grain than the business requirement, which explicitly needs daily sales by store and product category. Window functions cannot reconstruct detail that was never stored. Once data is aggregated beyond the required dimensions and time level, the lost granularity cannot be recovered accurately.
- D. Incorrect.
Incorrect. A standard view does not change the physical granularity or precompute results, so dashboard queries may still scan the detailed fact table and struggle to meet strict latency requirements. BI caching may help some repeated queries, but it is not a reliable substitute for designing summary data structures at the correct grain for consistent performance.