ADA-C01 Question 211
Single answer3.1 Given business requirements, design, manage, and maintain virtual warehouses.A retail company runs a nightly ELT process in Snowflake that starts at 1:00 AM and must finish by 3:00 AM. The workload consists of many independent transformation statements submitted concurrently by an orchestration tool. Recently, the process has been missing its SLA even though query profiles show little spilling and individual statements complete quickly when run alone. Administrators also notice long queue times during the batch window. The company wants to improve throughput during the 2-hour batch window while avoiding unnecessary compute cost outside that period. Which warehouse design change would best meet these requirements?
- A
Increase the warehouse size to a larger single-cluster warehouse and disable auto-suspend so the warehouse stays running overnight.
- B
Configure a multi-cluster warehouse in Auto-scale mode with an appropriate minimum and maximum cluster count, and keep auto-suspend enabled.
- C
Enable Query Acceleration Service on the existing warehouse because it reduces queueing for concurrent statements.
- D
Convert the workload to use a Snowflake-managed reader account so batch queries can execute on separate compute resources.
Show answer and explanation
Correct answer: B
Explanation
The key clue is that individual statements complete quickly when run alone, but the workload experiences long queue times during a narrow batch window. That points to a concurrency bottleneck rather than a need for more compute per query. Snowflake best practice for handling many concurrent queries is to use a multi-cluster warehouse, particularly in Auto-scale mode, so additional clusters can be added during demand spikes and removed afterward. This aligns with the business requirement to finish a concurrent workload within a 2-hour window while controlling costs outside that period. A larger single-cluster warehouse can help with larger or more resource-intensive queries, but it is less effective than multi-cluster scaling for queueing caused by concurrency. Query Acceleration Service is not a concurrency management feature, and reader accounts are unrelated to warehouse throughput. Relevant Snowflake documentation includes guidance on virtual warehouses, multi-cluster warehouses, Auto-scale behavior, and warehouse auto-suspend/auto-resume best practices.
- A. Incorrect.
This is not the best choice for the stated problem. A larger single-cluster warehouse can improve performance for queries that need more compute individually, but the scenario indicates the main issue is queueing caused by many concurrent independent statements. Simply scaling up a single cluster does not address concurrency as effectively as adding clusters. Disabling auto-suspend also increases cost outside the batch window, which conflicts with the requirement to avoid unnecessary compute cost.
- B. Correct.
This is correct. Multi-cluster warehouses are designed to handle concurrency by adding clusters when query demand increases, especially for workloads with many simultaneous independent statements. Auto-scale mode allows Snowflake to add and remove clusters based on demand, improving throughput during the batch window while minimizing cost when demand drops. Keeping auto-suspend enabled helps avoid charges outside the processing window.
- C. Incorrect.
This is incorrect because Query Acceleration Service is intended to speed up eligible individual queries by offloading portions of scan and filter processing, not to solve warehouse queueing caused by concurrency pressure. In this scenario, the queries already run quickly in isolation, which further suggests that the bottleneck is concurrent execution capacity rather than per-query execution speed.
- D. Incorrect.
This is incorrect. Reader accounts are used to share data with consumers who do not have their own Snowflake account. They are not a warehouse design feature for improving internal batch concurrency in the provider's account. This option reflects a misunderstanding of Snowflake data sharing rather than virtual warehouse management.