SnowPro Associate: Platform Question 242
Single answer● Warehouse sizingA data engineering team runs a nightly ETL job on a Snowflake virtual warehouse. The job processes a fixed amount of data each night and currently runs on a MEDIUM warehouse in about 2 hours. The business wants the job to finish in about 1 hour without redesigning the SQL. The team plans to change only the warehouse size for this workload. Which action is the most appropriate?
- A
Resize the warehouse from MEDIUM to LARGE before the ETL job runs
- B
Enable auto-suspend and auto-resume on the existing MEDIUM warehouse
- C
Create additional databases so the ETL queries can run in parallel on the same MEDIUM warehouse
- D
Convert the MEDIUM warehouse to a multi-cluster warehouse with a higher maximum cluster count for the ETL job
Show answer and explanation
Correct answer: A
Explanation
Warehouse sizing in Snowflake is primarily about matching compute resources to workload characteristics. To make a single batch ETL job complete faster, the recommended approach is usually to scale up the warehouse size, because larger warehouses provide more compute resources for query execution. In contrast, multi-cluster warehouses are intended mainly for concurrency scaling when many users or jobs run at the same time and need separate clusters. Auto-suspend and auto-resume improve cost efficiency but do not increase query speed. This aligns with Snowflake best practices for choosing between scaling up for performance and scaling out for concurrency.
- A. Correct.
Correct. Increasing warehouse size adds more compute resources to a single cluster, which is the standard way to improve performance for an individual workload when the SQL and data volume remain the same. Moving from MEDIUM to LARGE is a reasonable first step when the goal is to reduce elapsed time for a batch ETL job.
- B. Incorrect.
Incorrect. Auto-suspend and auto-resume help control cost by stopping compute when idle and restarting it on demand, but they do not make a running query complete faster. They are useful for warehouse management, not for reducing ETL execution time.
- C. Incorrect.
Incorrect. Databases are logical containers for data and do not increase compute capacity. Creating additional databases does not improve query performance on the same warehouse. This option reflects a misconception that storage organization changes compute throughput.
- D. Incorrect.
Incorrect. Multi-cluster warehouses are primarily designed to handle concurrency by adding clusters when many queries compete for resources. They are not the best choice for speeding up a single ETL workload running as one batch on a fixed data set. For a single workload, scaling up the warehouse size is typically more appropriate than scaling out for concurrency.