DAA-C01 Question 171
Single answer2.5 Optimize query performance.A data analyst supports a BI dashboard that runs every 5 minutes against a large SALES_FACT table. The dashboard query filters on ORDER_DATE and REGION, joins to small dimension tables, and aggregates revenue by product category. Query History shows the SQL text is stable, but execution time is inconsistent: some runs complete in 3 seconds, while others take more than 25 seconds. The analyst notices that the query repeatedly scans many micro-partitions from SALES_FACT even though most dashboard requests only need the most recent 7 days of data for a single region. Which action would most effectively improve and stabilize query performance for this workload?
- A
Create a clustering key on SALES_FACT for ORDER_DATE and REGION to improve micro-partition pruning for the dashboard filters
- B
Convert the small dimension tables to temporary tables so the join optimizer can skip statistics collection
- C
Increase the warehouse size permanently because larger warehouses eliminate micro-partition scans on filtered queries
- D
Rewrite the query to use SELECT DISTINCT before the GROUP BY so Snowflake can cache fewer intermediate results
Show answer and explanation
Correct answer: A
Explanation
The best answer is to improve pruning on the large fact table. In Snowflake, query performance for large filtered scans often depends on effective micro-partition pruning. When a workload repeatedly filters on the same columns, clustering the table on those columns can improve locality of related rows within micro-partitions and reduce the amount of data scanned. This is particularly useful for large, frequently queried fact tables in dashboard scenarios where the SQL pattern is stable. By contrast, increasing warehouse size addresses compute capacity but not data access efficiency, and changing small dimension tables to temporary tables does not solve the scanning problem. Snowflake documentation and best practices for performance optimization emphasize examining Query Profile, bytes scanned, and pruning effectiveness, and using clustering selectively for large tables with common selective filter predicates.
- A. Correct.
Correct. The main performance issue described is excessive scanning of micro-partitions despite highly selective filters on ORDER_DATE and REGION. Defining a clustering key aligned to the common filter predicates can improve clustering depth over time and enable better micro-partition pruning, reducing data scanned and making runtimes more consistent. This is especially relevant for large fact tables with repeated filter patterns in BI workloads.
- B. Incorrect.
Incorrect. Temporary tables do not provide a query performance advantage for this scenario. The problem is not with the small dimension tables or optimizer statistics on those joins; it is with scanning too much data from the large SALES_FACT table. Snowflake can efficiently handle joins to small dimensions already, and converting them to temporary tables would not address poor pruning on the fact table.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time through more compute resources, but it does not eliminate unnecessary micro-partition scans. If the query is reading far more data than needed due to poor pruning, simply scaling compute can mask the issue and increase cost without addressing the root cause of inconsistent performance.
- D. Incorrect.
Incorrect. Adding SELECT DISTINCT before a GROUP BY is typically redundant or harmful in an aggregation query and does not target the stated bottleneck. The scenario points to excessive scanning in SALES_FACT, not duplicate rows in intermediate results. This rewrite could add unnecessary work rather than improve performance.