COF-C03 Question 84
Single answerWorkloadsA retail company uses Snowflake for two very different workloads: a nightly ELT pipeline that performs large MERGE operations and a business intelligence dashboard used by hundreds of analysts during the day. Recently, dashboard users have reported slow query performance whenever the ELT pipeline is running longer than expected into business hours. The Snowflake administrator wants to reduce resource contention between these workloads while minimizing operational complexity. Which solution should be implemented?
- A
Run both workloads on the same warehouse, but increase the warehouse size so there are more compute resources for all queries.
- B
Create separate virtual warehouses for the ELT pipeline and the BI dashboard so each workload uses isolated compute resources.
- C
Keep a single warehouse, but enable Time Travel on the database so dashboard queries can read historical versions without contention.
- D
Move the BI dashboard queries to a read-only database replica in the same account so the queries no longer compete for warehouse resources.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to separate the workloads onto different virtual warehouses. In Snowflake, compute and storage are decoupled. Multiple warehouses can access the same underlying data without interfering with each other's compute resources. This architecture is specifically designed to support workload isolation, allowing organizations to run ELT, ad hoc analytics, and dashboarding concurrently. For SnowPro Core, candidates should recognize that virtual warehouses are the primary mechanism for managing concurrent workloads and reducing contention. While resizing a warehouse can help performance, it does not isolate workloads. Features such as Time Travel and replication serve different purposes and are not solutions for compute contention. This aligns with Snowflake best practices for warehouse design and workload separation.
- A. Incorrect.
Incorrect. Increasing the size of a single warehouse may improve overall throughput, but both workloads would still share the same compute cluster and can continue to compete for resources. This does not provide workload isolation, which is a key Snowflake best practice for mixed workloads such as ETL/ELT and user-facing analytics.
- B. Correct.
Correct. Snowflake virtual warehouses provide compute isolation. Assigning the ELT pipeline and BI dashboard to separate warehouses prevents one workload from consuming compute resources needed by the other. This is a common and recommended pattern for workload management in Snowflake because storage is shared centrally while compute is isolated per warehouse.
- C. Incorrect.
Incorrect. Time Travel is used to access historical data versions, recover dropped objects, and support data protection use cases. It does not isolate compute resources or reduce query contention between workloads running on the same warehouse.
- D. Incorrect.
Incorrect. Database replication is intended for disaster recovery, business continuity, and cross-region or cross-account data distribution scenarios. It does not eliminate the need for compute resources, and queries against replicated data still require a warehouse. In the same account, the core issue remains workload isolation at the warehouse level.