ADA-C01 Question 210
Single answer3.1 Given business requirements, design, manage, and maintain virtual warehouses.A retail company runs hourly ELT jobs and ad hoc analyst queries in the same Snowflake virtual warehouse. During peak business hours, analysts report inconsistent query performance because long-running ELT transformations consume most of the warehouse resources. The company wants to improve concurrency for analyst workloads during peaks while minimizing unnecessary credit consumption outside those periods. Which action should the Snowflake administrator take?
- A
Enable multi-cluster on the existing warehouse with MIN_CLUSTER_COUNT = 1 and MAX_CLUSTER_COUNT greater than 1, and set SCALING_POLICY = STANDARD
- B
Increase the warehouse size from MEDIUM to 2XLARGE and disable auto-suspend so compute is always available
- C
Convert the existing standard warehouse to a Snowpark-optimized warehouse to improve concurrency for SQL queries
- D
Keep a single-cluster warehouse and enable Query Acceleration Service to automatically add concurrency capacity for all queued queries
Show answer and explanation
Correct answer: A
Explanation
The key requirement is to improve concurrency for analyst workloads during peak periods without paying for excess compute during low-demand hours. In Snowflake, multi-cluster warehouses are the primary design choice for handling concurrent query loads. A warehouse with MIN_CLUSTER_COUNT = 1 and a higher MAX_CLUSTER_COUNT can automatically scale out when many users submit queries and scale back down later. This is more efficient than simply increasing warehouse size, which improves per-query resources but does not scale concurrency as effectively. STANDARD scaling policy is generally used when minimizing queueing is important, while ECONOMY is more cost-conscious but may allow more queuing before adding clusters. Snowpark-optimized warehouses are for specialized Snowpark workloads, not general SQL concurrency. Query Acceleration Service can complement query performance tuning for eligible queries, but it does not replace proper warehouse design for concurrent workloads. These recommendations align with Snowflake best practices for virtual warehouse management, including using multi-cluster warehouses for high-concurrency workloads and auto-suspend/auto-resume features to control credits.
- A. Correct.
Correct. A multi-cluster warehouse is designed to address concurrency by adding clusters when query load increases and reducing clusters when demand drops. Setting MIN_CLUSTER_COUNT = 1 avoids overprovisioning during quiet periods, while allowing MAX_CLUSTER_COUNT to scale above 1 helps absorb peak analyst demand. Using SCALING_POLICY = STANDARD is appropriate when the goal is to prioritize performance and reduce queuing during busy periods. This directly addresses the requirement to improve concurrent query handling while controlling credit usage outside peak windows.
- B. Incorrect.
Incorrect. Increasing warehouse size can improve performance for individual queries by providing more compute resources per cluster, but it does not address concurrency as effectively as multi-cluster warehouses. Disabling auto-suspend also increases unnecessary credit consumption during idle periods, which conflicts with the business requirement to minimize costs outside peak times. This option reflects a common misconception that bigger warehouses alone solve queuing problems.
- C. Incorrect.
Incorrect. Snowpark-optimized warehouses are intended for workloads requiring more memory and compute for Snowpark operations, such as complex DataFrame processing and machine learning-related tasks. They are not the recommended feature for improving concurrency of standard SQL analyst queries and ELT contention in this scenario.
- D. Incorrect.
Incorrect. Query Acceleration Service can help accelerate certain eligible queries, especially those with large scan and filter processing, but it is not a replacement for warehouse scaling to handle broad concurrency issues. It does not function as a general-purpose mechanism to add clusters for queued workloads. The misconception here is treating query acceleration as equivalent to multi-cluster concurrency scaling.