COF-C03 Question 75
Single answerBest practicesA data engineering team loads sales data into Snowflake every 15 minutes and supports dashboards used by analysts throughout the day. Recently, compute costs increased sharply because analysts often run long ad hoc queries at the same time the ETL jobs are loading and transforming data. The team wants to reduce contention and follow Snowflake best practices without changing SQL logic or redesigning tables. Which approach is the BEST recommendation?
- A
Create separate virtual warehouses for the ETL workloads and analyst workloads so they use independent compute resources
- B
Increase the size of the existing warehouse so all ETL and analyst queries run together on a larger cluster
- C
Move the sales data into a temporary table during ETL so analyst queries do not compete for compute
- D
Disable result caching for analysts so ETL queries get more warehouse resources
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create separate virtual warehouses for ETL and analyst workloads. Snowflake best practices emphasize workload isolation by assigning different compute resources to different workload types. Since Snowflake separates storage from compute, multiple warehouses can query the same underlying tables concurrently without duplicating data. This design helps reduce resource contention, improves predictable performance, and supports cost management through right-sizing and auto-suspend/auto-resume settings per workload. By contrast, simply scaling up one shared warehouse does not isolate competing workloads. Snowflake documentation and architecture guidance consistently recommend using multiple virtual warehouses to separate BI, data loading, and transformation workloads when concurrency or mixed usage patterns create performance and cost issues.
- A. Correct.
Correct. A core Snowflake best practice is to separate workloads by using different virtual warehouses for different use cases, such as ELT/ETL and BI/reporting. Because Snowflake storage and compute are decoupled, multiple warehouses can access the same data without contention for compute resources. This improves concurrency management, isolates workloads, and often provides better cost control than forcing all activity through one warehouse.
- B. Incorrect.
Incorrect. Increasing the size of a single warehouse may help performance temporarily, but ETL and analyst queries would still compete within the same compute resource pool. This does not provide workload isolation, which is a key Snowflake best practice for mixed workloads. It can also increase costs unnecessarily if the issue is concurrency and workload interference rather than insufficient compute per query.
- C. Incorrect.
Incorrect. Temporary tables do not solve warehouse compute contention. ETL operations and analyst queries would still consume compute from the same warehouse if they run there. This option reflects a common misconception that table type affects compute isolation; in Snowflake, compute isolation is achieved through warehouse design, not by switching to temporary tables.
- D. Incorrect.
Incorrect. Result caching is a performance optimization for eligible repeated queries and does not reserve or redirect warehouse resources to ETL workloads. Disabling it would likely increase compute consumption for analyst queries rather than reduce contention. This goes against Snowflake best practices for efficient query execution.