SnowPro Associate: Platform Question 9
Single answer● Elastic computeA retail company runs hourly ETL jobs and supports ad hoc BI queries in the same Snowflake account. During business hours, analysts report slow dashboard performance whenever the ETL workload starts. The company wants to improve concurrency for user queries without forcing analysts to wait for ETL to finish, while keeping the solution aligned with Snowflake's elastic compute capabilities. Which action should they take?
- A
Resize the existing virtual warehouse to a larger size so both ETL and BI queries continue to share the same compute cluster
- B
Create a separate virtual warehouse for BI queries and keep the ETL jobs on their own warehouse
- C
Convert the ETL tables to temporary tables so BI queries are not blocked by ETL processing
- D
Enable Time Travel retention to reduce resource contention between ETL and BI workloads
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use separate virtual warehouses for different workloads. Snowflake's elastic compute architecture allows independent compute clusters to access the same centralized storage, which is ideal for isolating ETL and BI activity. This is a core practical use of elastic compute: one workload can scale or run independently without monopolizing resources needed by another. While resizing a warehouse can add capacity, it does not provide the same workload isolation as separate warehouses. Snowflake documentation and best practices consistently recommend using different virtual warehouses for distinct workloads such as loading, transformation, and analytics when concurrency and predictable performance are important.
- A. Incorrect.
This may improve performance in some cases by adding more compute to the single warehouse, but it does not isolate workloads. ETL and BI queries would still compete for the same warehouse resources and queueing/concurrency issues can remain. Snowflake's elastic compute model is better leveraged by separating workloads onto different virtual warehouses when isolation is needed.
- B. Correct.
Correct. Snowflake separates storage from compute, allowing multiple virtual warehouses to access the same data without interfering with each other at the compute layer. Assigning ETL and BI to different warehouses is a common best practice for workload isolation, improving concurrency and reducing the chance that one workload degrades another.
- C. Incorrect.
Temporary tables do not solve compute contention between workloads. They affect object lifecycle and visibility, not warehouse resource competition. BI queries slowing down during ETL is primarily a compute isolation issue, not a table type issue.
- D. Incorrect.
Time Travel is a data protection and recovery feature that allows access to historical data versions. It does not reduce contention for warehouse compute resources and has no direct role in improving query concurrency between ETL and BI workloads.