Snowflake Multi-cluster Warehouse: Practical Guide

A Multi-cluster Warehouse is a Snowflake warehouse that dynamically starts and stops multiple compute clusters to scale out concurrency. The mental model is simple: control individual query speed with warehouse size (scale up), and control the number of queries that can run concurrently with Multi-cluster (scale out). The feature requires Enterprise Edition or higher.

How It Works

A normal warehouse consists of a single cluster. With a Multi-cluster Warehouse, you set MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT, and Snowflake automatically launches additional clusters when concurrent queries exceed the existing capacity. When load decreases, unused clusters are shut down automatically.

What happens as concurrent queries increase:

[Cluster 1] #### 4 queries running
                    v queuing detected
[Cluster 1] ####   [Cluster 2] ## started
                    v load increases further
[Cluster 1] ####   [Cluster 2] ####   [Cluster 3] ## started

When the load falls:
[Cluster 1] ##     [Cluster 2] stopped  [Cluster 3] stopped

CREATE WAREHOUSE Syntax

-- For BI dashboards: prioritise responsiveness
CREATE WAREHOUSE bi_dashboard_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 6
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- For batch ETL: prioritise cost
CREATE WAREHOUSE batch_etl_wh
  WAREHOUSE_SIZE = 'LARGE'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'ECONOMY'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

-- Always on (MIN = MAX pins the cluster count)
CREATE WAREHOUSE always_on_wh
  WAREHOUSE_SIZE = 'SMALL'
  MIN_CLUSTER_COUNT = 3
  MAX_CLUSTER_COUNT = 3
  AUTO_SUSPEND = 0
  AUTO_RESUME = TRUE;

Standard vs Economy Comparison

Diagnosing and Resolving Queuing

Queuing happens when every cluster in the warehouse is busy and new queries are forced to wait in line. It translates directly into degraded user response times, so use the following steps to identify and fix the root cause.

-- Find queries that are being queued
SELECT
  query_id,
  warehouse_name,
  user_name,
  queued_overload_time,
  total_elapsed_time,
  execution_time,
  query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE queued_overload_time > 0
  AND start_time >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP())
ORDER BY queued_overload_time DESC
LIMIT 20;

-- Analyse the warehouse load pattern by hour of day
SELECT
  DATE_TRUNC('HOUR', start_time) AS hour_slot,
  warehouse_name,
  COUNT(*) AS query_count,
  AVG(queued_overload_time) / 1000 AS avg_queue_sec,
  MAX(queued_overload_time) / 1000 AS max_queue_sec
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
  AND warehouse_name = 'BI_DASHBOARD_WH'
GROUP BY 1, 2
ORDER BY hour_slot;

Queuing Remediation Checklist

Tuning Parameters in Production

-- Change the cluster count (takes effect immediately)
ALTER WAREHOUSE bi_dashboard_wh SET
  MIN_CLUSTER_COUNT = 2
  MAX_CLUSTER_COUNT = 10;

-- Change the scaling policy
ALTER WAREHOUSE bi_dashboard_wh SET
  SCALING_POLICY = 'ECONOMY';

-- Change the warehouse size (takes effect on the next query)
ALTER WAREHOUSE bi_dashboard_wh SET
  WAREHOUSE_SIZE = 'LARGE';

Monitoring

-- Warehouse load history (cluster activity)
SELECT
  start_time,
  end_time,
  warehouse_name,
  avg_running,
  avg_queued_load,
  avg_queued_provisioning,
  avg_blocked
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
  AND warehouse_name = 'BI_DASHBOARD_WH'
ORDER BY start_time DESC;

-- Credit consumption over time
SELECT
  start_time::DATE AS usage_date,
  warehouse_name,
  SUM(credits_used) AS total_credits,
  SUM(credits_used_compute) AS compute_credits,
  SUM(credits_used_cloud_services) AS cloud_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP())
  AND warehouse_name = 'BI_DASHBOARD_WH'
GROUP BY 1, 2
ORDER BY usage_date DESC;

Design Patterns

• Separate warehouses for BI and ETL: Put interactive queries (Standard / high MAX_CLUSTER_COUNT) and batch ETL (Economy / large size) on different warehouses to eliminate cross-workload interference.
• MIN_CLUSTER_COUNT = 1 for cost optimization: Run just one cluster at idle and let AUTO_SUSPEND shut the warehouse down completely.
• Fixed cluster count (MIN = MAX) for predictable cost: Useful when you need to forecast peak credit consumption, at the cost of flexibility under varying load.
• Combine with Resource Monitors: Configure a generous MAX_CLUSTER_COUNT but cap credit consumption via Resource Monitors to prevent budget overruns.

Check Your Understanding

Frequently Asked Questions