SOL-C01 · Question #139
A data engineering team is experiencing significant delays during their nightly ETL process in Snowflake. The process involves loading data from several external cloud storage locations (AWS S3, Azure
The correct answer is C. Implement multi-clustering on the virtual warehouse, setting both MIN_CLUSTER_COUNT and. Multi-cluster warehouses (Option C) directly address the root cause: 100% CPU saturation under concurrent load. By setting MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT greater than 1, Snowflake automatically spins up additional compute clusters to distribute concurrent queries across
Question
A data engineering team is experiencing significant delays during their nightly ETL process in Snowflake. The process involves loading data from several external cloud storage locations (AWS S3, Azure Blob Storage) into a Snowflake table, transforming the data, and then loading it into multiple target tables. Monitoring shows the virtual warehouse CPU utilization is consistently at 100% during the peak ETL hours. Which of the following strategies would be MOST effective in reducing the ETL processing time and improving resource utilization?
Options
- AIncrease the virtual warehouse size (e.g., from MEDIUM to LARGE) and monitor performance.
- BEnable auto-suspend on the virtual warehouse to reduce credits consumed during idle time.
- CImplement multi-clustering on the virtual warehouse, setting both MIN_CLUSTER_COUNT and
- DReduce the number of micro-partitions in the source data files by consolidating smaller files into
- ERepartition the Snowflake table into smaller micro-partitions to improve query performance.
How the community answered
(30 responses)- A3% (1)
- B7% (2)
- C60% (18)
- D20% (6)
- E10% (3)
Explanation
Multi-cluster warehouses (Option C) directly address the root cause: 100% CPU saturation under concurrent load. By setting MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT greater than 1, Snowflake automatically spins up additional compute clusters to distribute concurrent queries across ETL steps - horizontally scaling to absorb the peak load rather than relying on a single overwhelmed cluster.
Why the distractors fail:
- A (increase warehouse size) is vertical scaling only - a LARGE single cluster still serializes concurrent queries and won't solve a concurrency bottleneck as effectively as scaling out.
- B (enable auto-suspend) addresses idle-time cost savings, not peak performance - the opposite of the problem described.
- D (consolidate small files) reduces file-open overhead, which can help ingestion, but has marginal impact when the bottleneck is compute saturation, not I/O.
- E (repartition into smaller micro-partitions) misunderstands Snowflake's architecture - micro-partitions are automatically managed by Snowflake; you cannot manually repartition a table.
Memory tip: When you see "100% CPU + concurrent ETL workload," think scale out, not up - multi-cluster is Snowflake's answer to concurrency problems, while sizing up only helps single-query resource limits.
Topics
Community Discussion
No community discussion yet for this question.