ARA-C01 Question 379
Single answerInterpret a Query Profile, identify bottlenecks, and outline recommendationsA retail analytics team reports that a dashboard query became much slower after the SALES_FACT table grew from 2 TB to 9 TB. The query joins SALES_FACT to a small DIM_STORE table and filters on STORE_REGION, ORDER_DATE, and ORDER_STATUS. In the Snowflake Query Profile, the architect observes the following: the TableScan node on SALES_FACT consumes most of the elapsed time, bytes scanned are very high relative to the final result set, only a small percentage of micro-partitions are pruned, and there is no significant time spent in the Join node or on remote disk spill. The warehouse is not at maximum concurrency and query acceleration is not enabled. Which action would MOST directly address the primary bottleneck shown in the profile?
- A
Cluster the SALES_FACT table on columns that align with the common filter predicates, such as ORDER_DATE and possibly STORE_REGION, to improve micro-partition pruning
- B
Increase the warehouse size by one level so the Join node can process the DIM_STORE table faster
- C
Enable search optimization service on DIM_STORE because the query joins SALES_FACT to a dimension table
- D
Rewrite the query to use a CROSS JOIN and then apply the filters in a WHERE clause to give the optimizer more flexibility
Show answer and explanation
Correct answer: A
Explanation
The key skill being tested is interpreting the Query Profile to identify the actual bottleneck rather than reacting generically to slow performance. In this scenario, the profile shows: (1) TableScan on SALES_FACT dominates elapsed time, (2) bytes scanned are disproportionately large compared to the result set, (3) only a small fraction of micro-partitions are pruned, and (4) neither join processing nor spill behavior is significant. Those observations indicate the query is I/O-heavy because Snowflake cannot eliminate enough micro-partitions during the scan. For large tables with recurring selective predicates, Snowflake best practice is to evaluate clustering keys that align to those predicates so pruning improves over time. This is especially relevant when natural clustering has degraded as data volume has grown. By contrast, resizing the warehouse addresses compute capacity, not data pruning; search optimization should be used selectively for the access patterns it is designed to accelerate; and query rewrites that worsen join semantics do not solve scan inefficiency. Relevant Snowflake documentation areas include Query Profile interpretation, micro-partitions and data pruning, and clustering keys/automatic clustering best practices.
- A. Correct.
Correct. The Query Profile indicates the main bottleneck is the scan of SALES_FACT, not the join or spilling. High bytes scanned combined with poor micro-partition pruning strongly suggests that Snowflake is reading far more data than necessary. Clustering a large fact table on columns frequently used in selective filters can improve pruning so fewer micro-partitions are scanned. ORDER_DATE is commonly a strong clustering candidate for time-based fact tables, and STORE_REGION may help depending on query patterns and cardinality. This recommendation directly addresses the profile evidence.
- B. Incorrect.
Incorrect. Increasing warehouse size can reduce execution time for compute-intensive operations, but the profile does not indicate the join is the bottleneck. If the dominant issue is scanning too many micro-partitions, scaling compute may help somewhat through parallelism but does not fix the root cause of poor pruning. This is a common misconception: more compute is not the best first response when the profile shows inefficient data elimination.
- C. Incorrect.
Incorrect. Search optimization service can help certain highly selective point lookup and substring/search patterns, including some join use cases, but the profile shows the expensive work is scanning the large SALES_FACT table. Enabling search optimization on the small DIM_STORE table would not materially reduce the fact table scan. A candidate might choose this because a dimension join is present, but the profile evidence points elsewhere.
- D. Incorrect.
Incorrect. Rewriting the query as a CROSS JOIN would generally make the logical operation worse, not better, and would not improve pruning of SALES_FACT. Snowflake's optimizer already handles standard join syntax effectively. This option reflects a misunderstanding that syntactic rewrites alone can solve a physical scan bottleneck when the profile clearly shows inadequate pruning on the large table.