COF-C03 Question 1
Single answer1.1 Describe and use the Snowflake architectureA retail company runs nightly ELT pipelines that load data into Snowflake while business analysts run dashboard queries during the same time window. Recently, dashboard performance has become inconsistent whenever the ELT workload starts. The data engineering team wants to reduce query contention without duplicating data or changing table structures. Based on Snowflake architecture, which action would best address this requirement?
- A
Create a separate virtual warehouse for the dashboard workload so compute resources are isolated while both workloads access the same centralized storage
- B
Create a second copy of the reporting tables in a new database so analysts can query data without competing with ELT jobs
- C
Increase the maximum size of the existing warehouse so storage and compute scale together for both workloads
- D
Move the dashboard users to a different schema because schemas provide separate compute resources for concurrent workloads
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a separate virtual warehouse for the dashboard workload. Snowflake's multi-cluster, shared-data architecture decouples centralized storage from compute. Data is stored once in Snowflake-managed storage, while virtual warehouses provide independent compute resources for loading, transforming, and querying data. This allows different workloads to run concurrently against the same data with reduced contention. In this scenario, isolating ELT and BI queries by warehouse is the most direct architectural solution and avoids unnecessary data duplication. This aligns with Snowflake documentation and best practices around using separate warehouses for distinct workloads, especially when concurrency and predictable performance are important.
- A. Correct.
Correct. Snowflake's architecture separates storage from compute. Virtual warehouses provide independent compute clusters, so assigning dashboards to a different warehouse isolates analyst queries from ELT processing while both workloads still read the same underlying table data in centralized storage. This is a common architectural pattern for handling mixed workloads and reducing resource contention.
- B. Incorrect.
Incorrect. Snowflake does not require duplicating data to separate workloads. One of the key architectural benefits is that multiple virtual warehouses can access the same centralized data without copying it. Creating another database with duplicated tables would increase storage costs, add maintenance complexity, and does not leverage Snowflake's decoupled architecture effectively.
- C. Incorrect.
Incorrect. Increasing the size of a single warehouse may improve performance in some cases, but both ELT and dashboard queries would still compete for resources within the same compute cluster. Also, Snowflake storage does not scale together with warehouse size; storage and compute are separate architectural layers. This option reflects a misunderstanding of Snowflake's decoupled storage and compute model.
- D. Incorrect.
Incorrect. Schemas are logical containers for database objects and do not provide dedicated compute resources. Moving users to another schema may help organization or access control, but it will not isolate query processing. Compute isolation in Snowflake is achieved through virtual warehouses, not through databases or schemas.