SnowPro Associate: Platform Question 233
Single answer3.2 Explain virtual warehouses.A data engineering team runs a nightly ETL workload on a Snowflake virtual warehouse. Most nights, the warehouse finishes in 20 minutes, but on month-end processing days the workload takes more than 2 hours because many large transformation queries run at the same time. The team wants to reduce the month-end runtime without changing the SQL logic and without affecting other teams that use separate warehouses. Which action is the best way to address this requirement?
- A
Resize the ETL virtual warehouse to a larger size during month-end processing to provide more compute resources
- B
Increase the Time Travel retention period for the ETL database so queries can access more historical data faster
- C
Convert the ETL virtual warehouse to a reader account so it can process more concurrent queries
- D
Create additional schemas for the ETL tables so Snowflake can parallelize the transformations automatically
Show answer and explanation
Correct answer: A
Explanation
The best answer is to resize the ETL virtual warehouse during peak processing periods. In Snowflake, virtual warehouses are independent compute clusters used to run queries, DML, and other processing tasks. A larger warehouse provides more compute resources, which is a common and practical way to improve performance for resource-intensive ETL jobs, especially when many queries run concurrently. This aligns with Snowflake best practices for separating compute from storage and scaling compute independently based on workload needs. Time Travel and schemas are storage/logical organization features, not performance-scaling mechanisms. Reader accounts are for data sharing, not warehouse scaling. Snowflake documentation on virtual warehouses explains that warehouses can be resized to meet workload demands, allowing teams to scale compute without impacting workloads running on other warehouses.
- A. Correct.
Correct. Virtual warehouses provide the compute resources used to execute queries in Snowflake. Increasing the warehouse size adds more compute resources, which can improve performance for heavy workloads and reduce runtime for large or concurrent transformation jobs. Because other teams use separate warehouses, resizing this ETL warehouse affects only this workload's compute consumption and cost, not other teams' performance.
- B. Incorrect.
Incorrect. Time Travel controls how long historical data versions are retained for querying and recovery. It does not increase compute performance or reduce execution time for ETL transformations. This option reflects a common misconception that storage-related settings improve query speed.
- C. Incorrect.
Incorrect. Reader accounts are used to share data with non-Snowflake users and are unrelated to scaling query execution for an internal ETL workload. Converting a warehouse to a reader account is not a valid way to increase concurrency or performance.
- D. Incorrect.
Incorrect. Schemas are logical containers for database objects and do not provide compute resources. Creating additional schemas does not cause Snowflake to automatically parallelize ETL processing. Query execution performance is driven primarily by warehouse compute resources, query design, and data characteristics.