SnowPro Associate: Platform Question 243
Single answer● Warehouse sizingA data engineering team runs a nightly ETL job on a Snowflake virtual warehouse. The job currently uses a SMALL warehouse and takes 3 hours to complete. During month-end processing, the same job misses its SLA because data volume doubles and query queueing increases. The team wants to reduce elapsed time for this workload with the fewest operational changes. Which action is the most appropriate?
- A
Resize the warehouse to a larger size before the month-end ETL run
- B
Increase the warehouse auto-suspend time so the warehouse stays warm longer
- C
Convert the ETL queries to use the Cloud Services layer instead of the warehouse
- D
Keep the warehouse size the same and enable a resource monitor to improve performance
Show answer and explanation
Correct answer: A
Explanation
Warehouse sizing in Snowflake is a primary mechanism for adjusting compute power for workloads. Larger warehouses provide more compute resources, which can reduce execution time for many ETL and analytical workloads. For predictable peak periods such as month-end processing, resizing a warehouse is a common best practice when the goal is to reduce runtime without redesigning the workload. Auto-suspend affects cost optimization, not processing speed. Resource monitors help manage spending, not performance. Snowflake documentation on virtual warehouses explains that warehouse size determines the compute resources available for query execution, and that warehouses can be resized to match workload demands.
- A. Correct.
Correct. Increasing the warehouse size adds more compute resources, which can improve query performance and reduce elapsed time for compute-bound ETL workloads. In this scenario, the workload is predictable and periodically larger at month-end, so resizing the warehouse before the heavy run is a practical way to meet the SLA with minimal changes.
- B. Incorrect.
Incorrect. Auto-suspend controls how quickly an idle warehouse shuts down to save cost. Increasing the auto-suspend time may reduce resume frequency, but it does not materially increase compute capacity or address query queueing caused by a heavier workload.
- C. Incorrect.
Incorrect. Query execution in Snowflake uses virtual warehouse compute resources. The Cloud Services layer handles coordination and metadata-related services, not the main compute for ETL query processing. This option reflects a misunderstanding of Snowflake architecture.
- D. Incorrect.
Incorrect. Resource monitors are used to observe and control credit consumption, such as sending notifications or suspending warehouses when thresholds are reached. They do not improve query performance or reduce ETL runtime.