Multi-cluster warehouses in Snowflake - Commands & Configuration

Introduction

Sometimes many users run queries at the same time and slow down the system. Multi-cluster warehouses let Snowflake add more compute clusters automatically to handle the load. This keeps queries fast without waiting.

When many users run reports or dashboards at the same time and queries slow down.

When you want to avoid queues during busy hours without manual intervention.

When your team runs many data loads and transformations in parallel.

When you want to keep query performance steady during unpredictable spikes.

When you want Snowflake to manage scaling compute resources automatically.

Commands

This command creates a multi-cluster warehouse named 'my_multi_cluster' with medium size. It starts with 2 clusters and can scale up to 4 clusters automatically. Auto suspend stops the warehouse after 5 minutes of inactivity to save costs, and auto resume restarts it when queries come.

Terminal

CREATE WAREHOUSE my_multi_cluster WITH WAREHOUSE_SIZE = 'MEDIUM' WAREHOUSE_TYPE = 'MULTI_CLUSTER' MIN_CLUSTER_COUNT = 2 MAX_CLUSTER_COUNT = 4 AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;

Expected Output

Warehouse MY_MULTI_CLUSTER successfully created.

→

WAREHOUSE_TYPE = 'MULTI_CLUSTER' - Enables multiple clusters for scaling.

→

MIN_CLUSTER_COUNT = 2 - Starts with 2 clusters to handle load.

→

MAX_CLUSTER_COUNT = 4 - Limits scaling to 4 clusters to control cost.

This command shows details about the warehouse we just created to verify its settings.

Terminal

SHOW WAREHOUSES LIKE 'MY_MULTI_CLUSTER';

Expected Output

name           | state  | type          | size   | min_cluster_count | max_cluster_count | auto_suspend | auto_resume

MY_MULTI_CLUSTER | SUSPENDED | MULTI_CLUSTER | MEDIUM | 2                 | 4                 | 300          | TRUE

This command changes the warehouse to start with 3 clusters and scale up to 6 clusters if needed. This is useful if you expect more load and want more compute power.

Terminal

ALTER WAREHOUSE my_multi_cluster SET MIN_CLUSTER_COUNT = 3 MAX_CLUSTER_COUNT = 6;

Expected Output

Warehouse MY_MULTI_CLUSTER successfully altered.

This command checks how many clusters are currently active in the multi-cluster warehouse. It helps monitor scaling behavior.

Terminal

SELECT SYSTEM$CLUSTER_COUNT('my_multi_cluster');

Expected Output

3

Key Concept

If you remember nothing else from this pattern, remember: multi-cluster warehouses let Snowflake add or remove compute clusters automatically to keep queries fast during busy times.

Common Mistakes

Creating a warehouse without setting WAREHOUSE_TYPE to MULTI_CLUSTER.

The warehouse will not scale with multiple clusters and will behave like a single cluster warehouse.

Always specify WAREHOUSE_TYPE = 'MULTI_CLUSTER' to enable multi-cluster scaling.

Setting MIN_CLUSTER_COUNT higher than MAX_CLUSTER_COUNT.

This causes an error because minimum clusters cannot be more than maximum clusters.

Ensure MIN_CLUSTER_COUNT is less than or equal to MAX_CLUSTER_COUNT.

Not enabling AUTO_RESUME, causing warehouse to stay suspended and queries to fail.

Without auto resume, the warehouse won't start automatically when queries run, causing delays or errors.

Set AUTO_RESUME = TRUE to let Snowflake start the warehouse automatically on demand.

Summary

Create a multi-cluster warehouse with CREATE WAREHOUSE specifying WAREHOUSE_TYPE, MIN_CLUSTER_COUNT, and MAX_CLUSTER_COUNT.

Use SHOW WAREHOUSES to verify the warehouse settings after creation.

Use ALTER WAREHOUSE to adjust cluster counts as needed for changing workloads.

Use SYSTEM$CLUSTER_COUNT function to monitor how many clusters are active.

Practice

1. What is the main purpose of a multi-cluster warehouse in Snowflake?

easy

A. To automatically add or remove compute clusters based on workload demand

B. To store data in multiple regions for disaster recovery

C. To encrypt data at rest and in transit

D. To create backups of data automatically every hour

Solution

1. Step 1: Understand multi-cluster warehouse functionality

   Multi-cluster warehouses adjust the number of compute clusters automatically to handle workload changes.

2. Step 2: Compare options with this functionality

   Only To automatically add or remove compute clusters based on workload demand describes automatic scaling of compute clusters based on demand.

3. Final Answer:

   To automatically add or remove compute clusters based on workload demand -> Option A

4. Quick Check:

   Multi-cluster warehouses scale clusters automatically = A [OK]

Hint: Remember: multi-cluster means auto scaling clusters [OK]

Common Mistakes:

• Confusing multi-cluster warehouses with data replication
• Thinking multi-cluster warehouses handle backups
• Assuming multi-cluster warehouses manage encryption

2. Which of the following is the correct way to set the minimum and maximum cluster count for a multi-cluster warehouse in Snowflake?

easy

A. ALTER WAREHOUSE mywh SET MIN_CLUSTER_COUNT = 2, MAX_CLUSTER_COUNT = 5;

B. ALTER WAREHOUSE mywh SET MIN_CLUSTERS = 2, MAX_CLUSTERS = 5;

C. ALTER WAREHOUSE mywh SET MIN_CLUSTER = 2, MAX_CLUSTER = 5;

D. ALTER WAREHOUSE mywh SET CLUSTER_MIN = 2, CLUSTER_MAX = 5;

Solution

1. Step 1: Recall correct parameter names for cluster count

   The correct parameters are MIN_CLUSTERS and MAX_CLUSTERS.

2. Step 2: Match syntax with correct parameters

   ALTER WAREHOUSE mywh SET MIN_CLUSTERS = 2, MAX_CLUSTERS = 5; uses the exact correct syntax and parameter names for Snowflake.

3. Final Answer:

   ALTER WAREHOUSE mywh SET MIN_CLUSTERS = 2, MAX_CLUSTERS = 5; -> Option B

4. Quick Check:

   Use MIN_CLUSTERS and MAX_CLUSTERS for scaling [OK]

Hint: Use full parameter names with _CLUSTERS suffix [OK]

Common Mistakes:

• Using incorrect parameter names like MIN_CLUSTER_COUNT
• Omitting the _CLUSTERS suffix
• Using wrong syntax for ALTER WAREHOUSE

3. Consider a multi-cluster warehouse configured with MIN_CLUSTER_COUNT=1 and MAX_CLUSTER_COUNT=3. If the workload suddenly increases, what will happen?

medium

A. The warehouse will reduce clusters to save cost

B. The warehouse will stay at 1 cluster and queue queries

C. The warehouse will shut down automatically

D. The warehouse will add clusters up to 3 to handle the workload

Solution

1. Step 1: Understand scaling behavior of multi-cluster warehouses

   When workload increases, Snowflake adds clusters up to the MAX_CLUSTER_COUNT to reduce query wait times.

2. Step 2: Apply configuration limits

   Since MAX_CLUSTER_COUNT is 3, the warehouse can scale from 1 up to 3 clusters automatically.

3. Final Answer:

   The warehouse will add clusters up to 3 to handle the workload -> Option D

4. Quick Check:

   Workload increase triggers cluster scaling up to max [OK]

Hint: Scaling adds clusters up to max limit on demand [OK]

Common Mistakes:

• Assuming clusters stay fixed at minimum count
• Thinking warehouse shuts down on high load
• Believing clusters reduce during high workload

4. You configured a multi-cluster warehouse with MIN_CLUSTER_COUNT=2 and MAX_CLUSTER_COUNT=4, but it never scales beyond 2 clusters even under heavy load. What is the most likely cause?

medium

A. The warehouse is paused

B. The MAX_CLUSTER_COUNT parameter is set incorrectly

C. The warehouse is set to SINGLE cluster mode instead of MULTI cluster mode

D. The warehouse size is too small to scale

Solution

1. Step 1: Check warehouse mode setting

   Multi-cluster scaling only works if the warehouse is in MULTI cluster mode, not SINGLE cluster mode.

2. Step 2: Understand effect of SINGLE cluster mode

   If set to SINGLE cluster mode, the warehouse will not scale beyond one cluster regardless of MIN or MAX settings.

3. Final Answer:

   The warehouse is set to SINGLE cluster mode instead of MULTI cluster mode -> Option C

4. Quick Check:

   Multi-cluster scaling requires MULTI cluster mode [OK]

Hint: Check warehouse mode: must be MULTI cluster for scaling [OK]

Common Mistakes:

• Ignoring warehouse mode and expecting scaling
• Assuming warehouse size limits scaling
• Not realizing paused warehouse cannot scale

5. You want to optimize a multi-cluster warehouse for unpredictable workloads that spike quickly but also have long idle periods. Which configuration is best to balance performance and cost?

hard

A. Set MIN_CLUSTER_COUNT=1 and MAX_CLUSTER_COUNT=5 with auto-suspend enabled

B. Set MIN_CLUSTER_COUNT=5 and MAX_CLUSTER_COUNT=5 with auto-suspend disabled

C. Set MIN_CLUSTER_COUNT=3 and MAX_CLUSTER_COUNT=3 with auto-suspend enabled

D. Set MIN_CLUSTER_COUNT=0 and MAX_CLUSTER_COUNT=1 with auto-suspend disabled

Solution

1. Step 1: Analyze workload characteristics

   Unpredictable spikes require ability to scale up quickly; long idle times require cost saving by suspending.

2. Step 2: Evaluate configuration options

   Set MIN_CLUSTER_COUNT=1 and MAX_CLUSTER_COUNT=5 with auto-suspend enabled allows scaling from 1 to 5 clusters and enables auto-suspend to save cost during idle periods.

3. Step 3: Compare other options

   Configurations with fixed cluster counts reduce flexibility; MIN_CLUSTER_COUNT=0 and MAX_CLUSTER_COUNT=1 with auto-suspend disabled limits max clusters and increases cost.

4. Final Answer:

   Set MIN_CLUSTER_COUNT=1 and MAX_CLUSTER_COUNT=5 with auto-suspend enabled -> Option A

5. Quick Check:

   Flexible scaling plus auto-suspend balances cost and performance [OK]

Hint: Use low min, high max clusters with auto-suspend for spikes [OK]

Common Mistakes:

• Fixing cluster count too high wastes cost
• Disabling auto-suspend wastes resources during idle
• Setting max clusters too low limits performance