ARA-C01 Question 364
Single answerPerformance impactsA retail company runs a nightly ELT process and several analyst dashboards on the same Snowflake virtual warehouse. During the 2-hour nightly load window, dashboard queries that normally finish in seconds begin taking several minutes, even though the warehouse size was increased from LARGE to XLARGE. Query history shows many dashboard queries spending significant time in a queued state, while individual query execution times are not consistently high once they start running. The company wants to improve dashboard responsiveness during the load window with minimal changes to SQL logic. Which action is the BEST solution?
- A
Enable multi-cluster on the warehouse used by both dashboards and ELT, with auto-scale configured to add clusters during concurrency spikes
- B
Increase the warehouse from XLARGE to 2XLARGE so each queued query has more CPU once it starts
- C
Create a materialized view for every dashboard query so queued time is eliminated
- D
Disable result caching during the load window so fresh results can use all warehouse resources
Show answer and explanation
Correct answer: A
Explanation
This scenario distinguishes between two common performance patterns in Snowflake: slow execution versus queueing from concurrency pressure. The important clue is that dashboard queries are spending significant time in a queued state, while runtime after execution begins is not consistently problematic. That indicates the warehouse is overloaded in terms of concurrent statements, not necessarily underpowered for each individual statement. Snowflake best practice is to use multi-cluster warehouses when concurrency is the main issue, because they scale out by adding clusters to handle more simultaneous work. By contrast, increasing warehouse size scales up compute for running queries and is more effective when individual queries are CPU-, memory-, or I/O-bound. Materialized views may help selected repeated queries, but they do not resolve queueing as directly as multi-cluster scaling. Result caching should generally be preserved because it reduces warehouse load. Relevant Snowflake documentation areas include virtual warehouses, multi-cluster warehouses, query history metrics such as queued overload time, and performance optimization guidance on matching warehouse strategy to concurrency versus compute-bound workloads.
- A. Correct.
Correct. The scenario points to a concurrency bottleneck rather than insufficient per-query compute. The key evidence is that queries are spending significant time queued, and execution time after starting is not consistently the main issue. In Snowflake, multi-cluster warehouses are designed to address concurrent query demand by automatically adding clusters when needed, which reduces or eliminates queueing caused by too many simultaneous statements. This is a practical solution when mixed workloads must continue sharing a warehouse and the goal is to improve responsiveness with minimal SQL changes.
- B. Incorrect.
Incorrect. Increasing warehouse size primarily improves the resources available to each running query, which can help long-running or compute-intensive queries complete faster. However, this option does not directly address queueing caused by high concurrency. If the main symptom is queued overload time, making a single cluster larger may still leave queries waiting for execution slots. This is a common misconception: warehouse scaling up helps query performance, while scaling out with multi-cluster helps concurrency.
- C. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but they do not directly solve warehouse queueing due to concurrent workload pressure. They also require careful design, maintenance cost consideration, and are not appropriate as a blanket solution for every dashboard query. The scenario specifically asks for minimal changes to SQL logic and highlights queue time as the issue, making concurrency scaling the better architectural response.
- D. Incorrect.
Incorrect. Disabling result caching would typically worsen performance, not improve it. Result cache allows identical eligible queries to return quickly without consuming warehouse compute. Turning it off increases workload on the warehouse and can further exacerbate contention during the busy load window. This option reflects a misunderstanding of how Snowflake caching contributes to performance and workload reduction.