ARA-C01 Question 417
Single answer4.2 Troubleshoot performance issues with existing architectures.A retail company runs an hourly ELT pipeline and several BI dashboards on the same Snowflake virtual warehouse. Over the last two weeks, dashboard queries that used to finish in under 5 seconds now often take 30-60 seconds during business hours, while the same queries run quickly late at night. Query History shows many dashboard queries spending significant time in the queued_overload_time field, and warehouse load graphs show sustained concurrency spikes at the top of the warehouse's capacity. The data model and SQL text for the dashboards have not changed. Which action is the MOST appropriate to address the performance issue while minimizing architectural change?
- A
Increase the AUTO_SUSPEND timeout so the warehouse stays warm longer and avoids compilation overhead
- B
Enable or increase multi-cluster behavior on the warehouse used by the dashboards and ELT workloads
- C
Convert the core dashboard tables to transient tables so less metadata is maintained during queries
- D
Recluster all large fact tables manually before each business day starts
- E
Raise the STATEMENT_TIMEOUT_IN_SECONDS parameter so queued statements are less likely to fail
Show answer and explanation
Correct answer: B
Explanation
This scenario is a classic warehouse contention problem. The key evidence is that performance degrades only during busy periods, queries perform well off-hours, and Query History shows high queued_overload_time. In Snowflake, queued_overload_time indicates statements are waiting because the warehouse is overloaded, typically due to concurrency limits or insufficient compute for the current workload mix. For existing architectures, the least disruptive remediation is usually to scale concurrency handling on the warehouse: either separate workloads into dedicated warehouses or use a multi-cluster warehouse for workloads with bursty concurrent demand. Because the question asks for the most appropriate action while minimizing architectural change, enabling or increasing multi-cluster behavior is the best answer. Snowflake documentation and best practices for performance troubleshooting emphasize using Query History queue metrics, correlating warehouse load with slow periods, and addressing concurrency issues through warehouse sizing, multi-cluster warehouses, and workload isolation when appropriate.
- A. Incorrect.
Incorrect. A longer AUTO_SUSPEND can reduce cold-start delays, but the scenario specifically identifies queued_overload_time and sustained concurrency spikes. That indicates the bottleneck is warehouse concurrency/capacity, not resume latency or query compilation time. Keeping the warehouse running longer may slightly improve startup behavior, but it will not materially resolve queueing caused by concurrent workload pressure.
- B. Correct.
Correct. Significant queued_overload_time combined with concurrency spikes is a strong indicator that the warehouse is saturated by concurrent requests. Enabling a multi-cluster warehouse, or increasing the maximum cluster count if already enabled, is the most direct way to reduce queueing for mixed ELT and BI workloads without requiring major redesign. This is a standard Snowflake approach for handling high concurrency, especially where dashboards are impacted only during peak periods.
- C. Incorrect.
Incorrect. Transient tables primarily affect data protection features such as Fail-safe cost implications; they do not provide a query performance improvement for this type of concurrency bottleneck. The issue is not metadata overhead on table type, and changing persistent tables to transient would not address queued warehouse load.
- D. Incorrect.
Incorrect. Reclustering can help pruning efficiency for poorly clustered large tables, but the scenario says the SQL and data model have not changed and the same queries perform well at night. That pattern points to shared compute contention rather than table scan inefficiency. Manual daily reclustering would add maintenance overhead and would not directly solve high queued_overload_time caused by concurrent use of the warehouse.
- E. Incorrect.
Incorrect. Increasing statement timeout only allows statements to wait longer before failing. It does not reduce queueing or improve throughput. This option treats a symptom rather than the cause, and users would still experience slow dashboard performance.