ARA-C01 Question 387
Single answerVirtual warehouse configurationsA retail company runs two main workloads in Snowflake. The first is a dashboard application that issues many short, concurrent BI queries during business hours. The second is a nightly ELT process that runs a small number of long-running transformation queries. Recently, dashboard users have reported inconsistent response times between 9 AM and 11 AM, while the ELT window is already meeting its SLA. The architect must improve dashboard performance with minimal operational overhead and without overprovisioning compute for the nightly ELT workload. Which configuration change is the BEST fit?
- A
Increase the BI virtual warehouse size from MEDIUM to XLARGE and disable auto-suspend so queries avoid startup delay
- B
Configure the BI virtual warehouse as a multi-cluster warehouse in Auto-scale mode with an appropriate minimum and maximum cluster count, and keep the ELT warehouse separately sized for long-running jobs
- C
Move both BI and ELT workloads onto a single larger warehouse so Snowflake can prioritize short queries ahead of long-running transformations
- D
Enable Query Acceleration Service on the ELT warehouse only, because long-running queries are the most likely cause of BI latency spikes
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use a multi-cluster virtual warehouse for the BI workload and keep the ELT workload isolated on its own warehouse. In Snowflake, warehouse size primarily affects the compute power available to a single cluster, which can improve execution time for individual queries. However, when the main issue is many concurrent short queries, the more appropriate configuration is often a multi-cluster warehouse, which is specifically intended to handle concurrency by adding clusters as needed. Auto-scale mode allows Snowflake to automatically add clusters up to a configured maximum and remove them when demand subsides, balancing performance and cost.
Separating the BI and ELT workloads follows Snowflake best practices for workload isolation. BI workloads typically benefit from responsiveness and concurrency handling, while ELT workloads often require sustained compute for fewer, longer-running operations. Using separate warehouses allows each to be tuned independently for its pattern.
Relevant Snowflake documentation and best practices include the guidance on virtual warehouses, multi-cluster warehouses for concurrent user/query workloads, auto-suspend/auto-resume for cost control, and workload isolation by assigning different workloads to different warehouses.
- A. Incorrect.
Incorrect. Increasing warehouse size can help individual query performance by adding more compute per cluster, but it does not directly address concurrency-driven queueing as effectively as adding clusters. Disabling auto-suspend also increases cost and is not necessary to solve the stated 9 AM to 11 AM concurrency issue. For many short BI queries, concurrency scaling through a multi-cluster warehouse is typically a better fit than simply making one cluster much larger.
- B. Correct.
Correct. A multi-cluster warehouse is designed for workloads with many concurrent queries, such as BI dashboards. In Auto-scale mode, Snowflake can start additional clusters when concurrency increases and scale back down when demand drops, reducing queueing and improving response-time consistency. Keeping the ELT workload on a separate warehouse is also a best practice because long-running batch transformations have different compute characteristics and should be sized independently rather than forcing one configuration to serve both patterns.
- C. Incorrect.
Incorrect. Combining BI and ELT on one warehouse is generally a poor architectural choice because the workloads compete for the same compute resources. Snowflake does not provide a setting that simply prioritizes short BI queries over long ELT transformations in a shared warehouse in the way implied here. Separate warehouses are a common best practice for workload isolation, performance stability, and cost governance.
- D. Incorrect.
Incorrect. Query Acceleration Service can help certain eligible queries by offloading portions of scan and filter processing, but it is not the primary solution to concurrency-related dashboard latency. The issue described is inconsistent BI performance during a period of high concurrent demand, not an ELT SLA failure. Enabling the service only on the ELT warehouse would not directly solve dashboard queueing or concurrency pressure.