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.