Multi-cluster warehouses in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Multi-cluster Warehouse Behavior

What happens when a Snowflake multi-cluster warehouse reaches its maximum number of clusters during a high query load?

A Queries are rejected with an error indicating warehouse capacity exceeded.

B New queries are queued until an existing cluster finishes processing and frees capacity.

C Snowflake automatically creates additional clusters beyond the maximum limit to handle the load.

D The warehouse scales down to a single cluster to reduce costs.

💡 Hint

Think about how Snowflake manages concurrency within set limits.

Snowflake queues new queries when all clusters are busy and the maximum cluster count is reached. It does not create extra clusters beyond the configured maximum, nor does it reject queries or scale down automatically under load.

❓ Configuration

intermediate

Configuring a Multi-cluster Warehouse

Which Snowflake SQL command correctly creates a multi-cluster warehouse named 'ANALYTICS_WH' with a minimum of 2 clusters and a maximum of 5 clusters?

A CREATE WAREHOUSE ANALYTICS_WH WAREHOUSE_SIZE = 'MEDIUM' MIN_CLUSTER_COUNT = 2 MAX_CLUSTER_COUNT = 5;

B CREATE WAREHOUSE ANALYTICS_WH WAREHOUSE_SIZE = 'MEDIUM' SET MIN_CLUSTER_COUNT = 2 SET MAX_CLUSTER_COUNT = 5;

C CREATE WAREHOUSE ANALYTICS_WH WITH WAREHOUSE_SIZE = 'MEDIUM' SET MIN_CLUSTER_COUNT = 2 MAX_CLUSTER_COUNT = 5;

D CREATE WAREHOUSE ANALYTICS_WH WITH WAREHOUSE_SIZE = 'MEDIUM' MIN_CLUSTER_COUNT = 2 MAX_CLUSTER_COUNT = 5;

💡 Hint

Check the correct syntax for warehouse creation with multi-cluster settings.

The correct syntax uses 'WITH' followed by parameters separated by spaces and equals signs. 'MIN_CLUSTER_COUNT' and 'MAX_CLUSTER_COUNT' must be set within the 'WITH' clause.

❓ Architecture

advanced

Choosing Multi-cluster Warehouse Settings for Variable Load

You have a workload with unpredictable spikes in query volume. Which multi-cluster warehouse configuration best balances cost and performance?

A Set MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT both to 1 to avoid extra costs.

B Set MIN_CLUSTER_COUNT equal to MAX_CLUSTER_COUNT at a high number to always have maximum clusters running.

C Set MIN_CLUSTER_COUNT to 1 and MAX_CLUSTER_COUNT to a higher number like 10 to allow scaling during spikes.

D Disable multi-cluster and rely on a single large cluster to handle all queries.

💡 Hint

Consider how multi-cluster warehouses scale and cost implications.

Setting a low minimum and a higher maximum cluster count allows the warehouse to scale out during spikes and scale in when idle, balancing cost and performance.

❓ security

advanced

Security Implications of Multi-cluster Warehouses

Which statement about security and multi-cluster warehouses in Snowflake is true?

A All clusters in a multi-cluster warehouse share the same security context and access controls.

B Each cluster in a multi-cluster warehouse runs in a separate virtual private cloud (VPC) for isolation.

C Multi-cluster warehouses require separate user roles for each cluster to enforce security.

D Data encryption keys differ per cluster in a multi-cluster warehouse.

💡 Hint

Think about how Snowflake manages security across clusters in the same warehouse.

All clusters in a multi-cluster warehouse operate under the same security context and access controls defined at the warehouse and database level.

❓ service_behavior

expert

Impact of Auto Suspend on Multi-cluster Warehouses

If a multi-cluster warehouse is configured with AUTO_SUSPEND = 300 seconds and MIN_CLUSTER_COUNT = 2, what happens when the warehouse is idle for 10 minutes?

A Clusters above the minimum suspend after 5 minutes, but the minimum number of clusters remain running.

B The warehouse suspends completely after 5 minutes, regardless of minimum cluster count.

C The warehouse never suspends because minimum clusters must always be active.

D All clusters suspend after 5 minutes of inactivity, including reducing clusters below the minimum count.

💡 Hint

Consider how auto suspend interacts with minimum cluster count in multi-cluster warehouses.

Clusters above the minimum count suspend after the AUTO_SUSPEND period, but the minimum number of clusters remain active to ensure availability.