ARA-C01 Question 376
Single answerQuery profilingA data engineering team notices that a daily reporting query against a 4 TB fact table has become significantly slower over the last week, even though the virtual warehouse size has not changed. The architect opens the query profile and sees that most elapsed time is spent in a TABLE SCAN node, with a very high percentage of partitions scanned relative to total partitions. The query filters on ORDER_DATE and REGION, and both columns are frequently used in reporting predicates. Which action is the BEST next step to improve this query's performance based on the profiling evidence?
- A
Define a clustering key on ORDER_DATE and REGION, then monitor clustering depth and subsequent query profiles
- B
Increase the warehouse size immediately because TABLE SCAN time indicates insufficient compute
- C
Create a materialized view on the entire fact table without considering the query filter pattern
- D
Rewrite the query to use RESULT_SCAN so that Snowflake can avoid scanning table partitions
Show answer and explanation
Correct answer: A
Explanation
The key evidence in this scenario comes from the query profile: most time is spent in TABLE SCAN, and a high percentage of micro-partitions are scanned. In Snowflake, query profiling is used to determine where time is spent across execution steps and to identify issues such as poor micro-partition pruning. When large tables are frequently filtered on specific columns, clustering on those columns can improve pruning and reduce the number of partitions read. This is especially relevant when the filter columns are repeatedly used in selective predicates, as in ORDER_DATE and REGION for reporting workloads.
The best practice is not to jump straight to warehouse scaling when the profile shows an access-path problem. More compute can help with parallelism, but it does not reorganize data and therefore does not directly fix poor pruning. Likewise, materialized views should be designed for well-defined repeated query patterns, not used as a generic response to any scan-heavy query. RESULT_SCAN is unrelated to improving the execution path of a base query.
Relevant Snowflake documentation and best practices include the use of Query Profile to analyze operator-level execution details, understanding micro-partitions and pruning behavior, and using clustering keys selectively for very large tables with common filter predicates where natural clustering is insufficient. After applying clustering, architects should re-check query profiles and clustering metrics to confirm that fewer partitions are scanned and that elapsed time improves.
- A. Correct.
Correct. The query profile indicates poor micro-partition pruning: a TABLE SCAN consuming most of the time and a high ratio of scanned partitions to total partitions strongly suggests that Snowflake cannot efficiently eliminate irrelevant micro-partitions. Because the query repeatedly filters on ORDER_DATE and REGION, defining a clustering key on those columns is an appropriate next step for a large table with common selective predicates. After reclustering occurs, the architect should validate improvement by reviewing clustering information and new query profiles to confirm reduced partitions scanned and lower scan time.
- B. Incorrect.
Incorrect. Increasing warehouse size can reduce execution time for some workloads, but it does not address the root cause shown in the query profile: poor pruning at scan time. If the engine must still read a large proportion of micro-partitions, additional compute may provide only limited benefit and can increase cost unnecessarily. Query profiling should drive root-cause remediation before scaling compute.
- C. Incorrect.
Incorrect. A materialized view can help for some repeated query patterns, but creating one on the entire fact table without aligning it to the specific aggregation or filter pattern is not the best next step from the evidence provided. The query profile specifically points to inefficient partition pruning on the base table. Clustering is the more direct response to high partition scan percentages on frequently filtered columns.
- D. Incorrect.
Incorrect. RESULT_SCAN reads the result set of a previously executed query by query ID; it is not a mechanism for rewriting a normal reporting query to improve partition pruning on the underlying table. It does not solve the observed issue in the query profile and is a common misconception when candidates confuse result reuse features with physical data access optimization.