SnowPro Associate: Platform Question 10
Single answer● Elastic computeA retail company runs hourly ETL jobs and also supports hundreds of analysts who query the same Snowflake database throughout the day. Recently, analysts have reported slow dashboard performance whenever the hourly ETL jobs start. The data engineering team wants to reduce query contention without changing the SQL or redesigning tables. Which Snowflake approach best uses elastic compute to address this problem?
- A
Convert the existing virtual warehouse to a multi-cluster warehouse so Snowflake can add clusters to handle concurrent workloads
- B
Enable Time Travel for the database so analysts can query historical versions without waiting on ETL activity
- C
Increase the data retention period so Snowflake has more versions of the data available for concurrent access
- D
Create a materialized view on all frequently queried tables so ETL and BI workloads do not share compute resources
Show answer and explanation
Correct answer: A
Explanation
Snowflake separates storage and compute, allowing compute to scale elastically through virtual warehouses. When the main issue is concurrent workload pressure, especially when many users and batch jobs overlap, a multi-cluster warehouse is the most appropriate elastic compute solution. It can automatically add clusters based on demand to improve throughput for concurrent queries. This is different from resizing a warehouse, which gives a single cluster more power for individual queries, but does not address concurrency as effectively as multi-cluster scaling. Features such as Time Travel, retention changes, and materialized views serve different purposes and do not directly solve compute contention in this scenario. This aligns with Snowflake documentation on virtual warehouses and multi-cluster warehouses, which emphasizes scaling out compute for concurrency and workload bursts.
- A. Correct.
Correct. A multi-cluster warehouse is a core Snowflake elastic compute feature designed to address concurrency by automatically starting additional clusters when query demand increases. In this scenario, ETL and analyst queries are competing for warehouse resources at the same time. Converting the warehouse to multi-cluster helps absorb spikes in concurrent workload without requiring SQL changes or table redesign. This matches the requirement to reduce contention using compute elasticity.
- B. Incorrect.
Incorrect. Time Travel is a data protection and recovery feature that allows access to historical data states, but it does not reduce warehouse contention or improve concurrency between ETL and BI queries. Both workloads would still consume compute resources from warehouses when queries run.
- C. Incorrect.
Incorrect. Increasing data retention affects how long historical table versions are preserved for features such as Time Travel and Fail-safe-related lifecycle behavior, not how compute resources are allocated. It does nothing to address concurrent query execution bottlenecks caused by ETL and analyst activity sharing warehouse capacity.
- D. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but they do not directly separate ETL and BI compute usage in the way described. They also introduce maintenance costs and are not a general elastic compute solution for handling concurrency spikes. The requirement specifically points to using elastic compute rather than redesigning data access patterns.