SnowPro Associate: Platform Question 249
Single answer○ Scaling in or outA data engineering team runs hourly ELT jobs on a Snowflake warehouse. Recently, job duration increased because several long-running transformation queries now overlap with a large number of short validation queries submitted at the same time. The team wants to reduce queueing for these concurrent queries without significantly changing the performance of each individual query. Which action should they take?
- A
Scale up the warehouse to a larger size so each query gets more compute resources
- B
Scale out the warehouse by enabling a multi-cluster warehouse with additional clusters
- C
Convert the warehouse to a reader account so validation queries run separately
- D
Increase the auto-suspend time so the warehouse stays warm between hourly runs
Show answer and explanation
Correct answer: B
Explanation
This scenario tests the distinction between scaling up and scaling out in Snowflake. Scaling up means increasing warehouse size, which gives a single cluster more compute power and is typically used to improve the performance of individual queries or heavier processing. Scaling out means adding more clusters through a multi-cluster warehouse, which is intended to improve concurrency and reduce queueing when many queries run at the same time. In Snowflake best practices, use a larger warehouse when queries themselves need more resources, and use multi-cluster warehouses when the problem is concurrent workload pressure. For this scenario, the team's goal is to reduce queueing from overlapping workloads without materially changing single-query performance, so scaling out is the appropriate choice.
- A. Incorrect.
Incorrect. Scaling up increases the compute resources available to a single cluster, which can improve the performance of individual queries. However, the scenario specifically focuses on overlapping workloads and reducing queueing caused by concurrency. A larger single cluster does not address concurrency as effectively as adding clusters.
- B. Correct.
Correct. Scaling out by using a multi-cluster warehouse is designed to handle higher concurrency by adding additional clusters as query load increases. This reduces queueing when many queries arrive at the same time, while not primarily changing the execution profile of each individual query. This is the best fit for a workload with many concurrent short and long queries.
- C. Incorrect.
Incorrect. Reader accounts are for sharing data with external parties, not for separating internal concurrent workloads on the same compute layer. This option confuses data sharing concepts with warehouse scaling behavior.
- D. Incorrect.
Incorrect. Increasing auto-suspend time may reduce startup delays if the warehouse frequently resumes, but it does not solve query queueing caused by too many concurrent requests hitting a single cluster. The core issue here is concurrency, not resume latency.