SnowPro Associate: Platform Question 12
Single answer● Elastic computeA retail company uses Snowflake to support both hourly ETL jobs and ad hoc BI dashboards. During business hours, analysts complain that dashboard queries slow down whenever the ETL workload starts. The company wants to improve concurrency for the BI team without changing the ETL SQL and while minimizing operational overhead. Which Snowflake feature should be implemented?
- A
Enable multi-cluster warehouses for the BI virtual warehouse
- B
Convert the ETL workload to run on Snowpipe Streaming
- C
Increase the data retention period for the BI tables
- D
Create a materialized view for every dashboard query
- E
Suspend and resume the ETL warehouse more frequently
Show answer and explanation
Correct answer: A
Explanation
Snowflake separates storage and compute, allowing compute resources to scale elastically through virtual warehouses. For concurrency-driven workloads such as BI dashboards with many simultaneous users, multi-cluster warehouses are a best-practice feature because they can automatically start additional clusters when demand increases and shut them down when demand drops. This reduces queueing and improves user experience without requiring query rewrites. In Snowflake documentation, multi-cluster warehouses are specifically positioned for managing concurrent users and queries, whereas resizing a warehouse is more commonly associated with increasing resources for individual query performance. In this scenario, the key requirement is improving BI concurrency with minimal operational overhead, making a multi-cluster warehouse the best answer.
- A. Correct.
Correct. Multi-cluster warehouses are designed to provide elastic compute for handling higher concurrency by automatically adding additional clusters to a virtual warehouse as query demand increases. This is a practical way to support many simultaneous BI queries with minimal administrative effort. It directly addresses the problem of dashboard slowdown caused by compute contention during busy periods.
- B. Incorrect.
Incorrect. Snowpipe Streaming is used for continuous low-latency data ingestion, not for solving query concurrency issues between BI dashboards and ETL workloads. Changing ingestion architecture would also violate the requirement to avoid changing the ETL SQL and does not directly address elastic compute for query performance.
- C. Incorrect.
Incorrect. Data retention period affects Time Travel and storage usage, not compute concurrency or warehouse performance. Someone might choose this if they confuse storage-related settings with compute-related scaling, but it will not help BI queries run faster during ETL activity.
- D. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but creating one for every dashboard query is not a practical or scalable solution. It also does not specifically solve concurrency pressure in the way elastic compute does. The scenario asks for a feature that minimizes operational overhead and handles variable demand.
- E. Incorrect.
Incorrect. More frequent suspend/resume cycles may reduce cost when warehouses are idle, but they do not improve concurrency while workloads are actively competing for compute resources. This option addresses cost management, not elastic scaling for simultaneous query execution.