COF-C03 Question 272
Single answerWorkload management best practicesA data engineering team uses a single MEDIUM virtual warehouse to support three workloads: hourly ELT jobs, ad hoc analyst queries during business hours, and a dashboard used by executives every morning. Users report that dashboard queries become slow whenever the hourly ELT job starts, even though the warehouse is configured with auto-suspend and auto-resume. The company wants to improve concurrency and response time for user-facing queries while keeping costs controlled. Which action is the BEST recommendation?
- A
Enable Query Acceleration Service on the existing warehouse so ELT and dashboard queries share the same compute more efficiently
- B
Convert the warehouse to a multi-cluster warehouse in Auto-scale mode, with an appropriate minimum and maximum cluster count
- C
Increase STATEMENT_TIMEOUT_IN_SECONDS so long-running ELT queries are less likely to be canceled during peak usage
- D
Disable auto-suspend so the warehouse stays warm and avoids queuing when dashboard users log in
Show answer and explanation
Correct answer: B
Explanation
This scenario tests workload management best practices in Snowflake. When multiple workloads with different performance expectations share one virtual warehouse, concurrency and resource contention can negatively affect interactive and business-critical queries. Snowflake best practices recommend using appropriately sized warehouses, separating workloads when needed, and using multi-cluster warehouses to handle concurrency. In particular, multi-cluster warehouses are designed to improve performance for concurrent workloads by automatically adding clusters when demand increases, especially for BI, dashboarding, and mixed-user access patterns. Auto-suspend and auto-resume are cost-management features, not concurrency-management solutions. Statement timeout settings control query cancellation behavior, not throughput. Query Acceleration Service can improve performance for some eligible queries, but it is not the primary remedy for general warehouse contention. These recommendations align with Snowflake documentation on virtual warehouses, multi-cluster warehouses, and workload management best practices.
- A. Incorrect.
Incorrect. Query Acceleration Service can help accelerate eligible parts of some queries, especially scan- and filter-heavy workloads, but it is not the primary workload management solution for sustained concurrency contention between mixed workloads on the same warehouse. In this scenario, the main issue is that multiple workloads compete for warehouse resources at the same time. A common misconception is that query acceleration replaces proper warehouse sizing or concurrency scaling strategies.
- B. Correct.
Correct. A multi-cluster warehouse in Auto-scale mode is a best-practice option when concurrent queries from different users or workloads cause queuing and slow response times. Auto-scale adds clusters as concurrency increases and can reduce contention for user-facing workloads such as dashboards and analyst queries. This approach directly addresses workload management needs while still allowing cost control through minimum and maximum cluster settings.
- C. Incorrect.
Incorrect. Increasing statement timeout does not improve concurrency or reduce resource contention. It only allows queries to run longer before being canceled. In this case, the problem is not premature cancellation but degraded performance caused by multiple workloads competing for the same warehouse resources.
- D. Incorrect.
Incorrect. Disabling auto-suspend may reduce startup latency in some cases, but it does not solve the core problem of concurrent workload contention. It can also increase cost because the warehouse remains running when idle. The issue described is slowdown when ELT starts, which is better addressed through workload isolation or multi-cluster scaling rather than keeping a single cluster continuously active.