SnowPro Associate: Platform Question 234
Single answer3.2 Explain virtual warehouses.A retail company runs hourly ELT jobs that load data into Snowflake and a separate set of BI dashboards used by hundreds of analysts throughout the day. Recently, dashboard users reported slow performance whenever the ELT jobs are running. The Snowflake administrator wants to reduce this contention without changing the SQL. Which action is the best solution?
- A
Resize the existing virtual warehouse to a larger size so both ELT and BI queries continue to share the same compute resources
- B
Create a separate virtual warehouse for the BI dashboards and leave the ELT jobs on their own warehouse
- C
Enable Time Travel for the BI database so reporting queries do not compete with data loading operations
- D
Move the BI dashboards to a different schema so Snowflake can isolate reporting workloads automatically
Show answer and explanation
Correct answer: B
Explanation
The best answer is to separate the workloads onto different virtual warehouses. In Snowflake, virtual warehouses are independent compute clusters that execute queries, DML, and data loading operations. Because compute is decoupled from storage, organizations can assign different workloads, such as ELT and BI, to different warehouses to prevent resource contention and improve performance predictability. Resizing a warehouse may help in some cases, but it does not isolate workloads. Features like Time Travel and logical structures like schemas do not affect warehouse-level compute isolation. This aligns with Snowflake best practices for workload management and the Snowflake documentation describing virtual warehouses as separate compute resources that can be started, stopped, resized, and dedicated to specific workloads.
- A. Incorrect.
Incorrect. Increasing the size of a single warehouse can improve overall throughput, but ELT and BI workloads would still compete for the same warehouse resources. This does not address workload isolation as effectively as separating the workloads onto different virtual warehouses.
- B. Correct.
Correct. Virtual warehouses provide independent compute resources in Snowflake. Placing ELT and BI workloads on separate warehouses isolates compute contention, allowing loading and reporting to run concurrently without competing for the same warehouse resources. This is a common best practice for mixed workloads.
- C. Incorrect.
Incorrect. Time Travel is a data protection and recovery feature that allows access to historical data versions. It does not isolate compute resources or reduce query contention between workloads running on the same virtual warehouse.
- D. Incorrect.
Incorrect. Schemas organize database objects logically, but they do not provide separate compute resources. Snowflake does not isolate reporting and loading workloads automatically based on schema placement.