Skip to main content

⚙️ Multi-Cluster Virtual Warehouses (MCW)

Multi-Cluster Warehouses help Snowflake handle high concurrency by automatically adding more compute clusters when needed — ensuring fast performance even when hundreds or thousands of users hit the system at the same time.

Think of it as auto-scaling compute for busy workloads.


🎯 What Is a Multi-Cluster Warehouse?

A Multi-Cluster Warehouse is a Virtual Warehouse with multiple compute clusters, instead of one.

A normal warehouse = 1 cluster
A multi-cluster warehouse = 1 to N clusters

Each cluster runs the same queries independently but reads from the same centralized storage.


🧠 Why Multi-Cluster Warehouses Exist

Standard warehouses scale vertically (XS → S → M → L…).
This improves the power of each node, but does NOT solve:

✔ High number of simultaneous users
✔ Hundreds of BI dashboards refreshing
✔ Multiple analysts querying at peak hours
✔ Many short-running queries arriving at once

Multi-cluster solves concurrency, not compute power.


🔍 Where Multi-Cluster Helps

✅ 1. BI Dashboards​

When 200+ users refresh dashboards at 9 AM → Snowflake auto-creates more clusters.

✅ 2. High-Concurrency Reports (Power BI, Tableau, Looker)​

Multiple dashboards refreshing simultaneously.

✅ 3. Shared Team Warehouse​

Many analysts sharing one warehouse → no queueing.

✅ 4. Ad-hoc Query Bursts​

Users running queries at the same time → auto-scale handles it.

❌ NOT for heavy ETL/ELT​

ETL requires compute power, not concurrency → increase warehouse SIZE, not cluster count.


🏗️ How Multi-Cluster Warehouses Work

They scale horizontally between MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT.​

CREATE WAREHOUSE bi_wh
WAREHOUSE_SIZE = 'LARGE'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 5
SCALING_POLICY = 'STANDARD';

Meaning:

  • Starts with 1 cluster
  • If more users come → auto-adds up to 5 clusters
  • When load reduces → auto-removes clusters (back to minimum)

🖼️ Architecture Diagram


🧩 Scaling Policies (VERY IMPORTANT)

1️⃣ STANDARD (Most Common)​

Snowflake automatically adds/removes clusters based on workload.

✔ Best for BI and reporting ✔ Supports automatic shrink to minimum count


2️⃣ ECONOMY (Cost-Optimized)​

Adds new clusters more slowly.

✔ Reduces cost
✔ Small delay during peak load
✔ Good for predictable workloads


📌 MIN_CLUSTER_COUNT vs MAX_CLUSTER_COUNT​

SettingMeaning
MIN_CLUSTER_COUNTMinimum clusters always running (costs more but always ready)
MAX_CLUSTER_COUNTMaximum clusters Snowflake can auto-scale to

Best Practice: Use 1 for MIN unless you need guaranteed performance.


📊 Example Configurations

💼 BI Warehouse for hundreds of dashboard queries​

CREATE WAREHOUSE bi_wh
WAREHOUSE_SIZE = 'LARGE'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 5
SCALING_POLICY = 'STANDARD';

🧪 Data Science / Internal Tools (less concurrency)​

CREATE WAREHOUSE ds_wh
WAREHOUSE_SIZE = 'XLARGE'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 2
SCALING_POLICY = 'ECONOMY';

💵 Cost Behavior (Critical to Understand)

❗ You pay for each active cluster per second.​

Example:

  • Warehouse Size: Large (16 credits/hour)
  • Active Clusters: 3 → Total = 48 credits / hour (until clusters scale down)

BUT: Clusters scale down automatically when load decreases → cost reduces again.


🧠 When NOT to Use Multi-Cluster

❌ ETL workloads
❌ Long-running transformations
❌ Snowpark ML workloads
❌ Single-user development queries

Use bigger warehouse SIZE, not more clusters.


When Multi-Cluster Is Perfect

✔ High number of small, fast queries ✔ Hundreds of concurrent BI users ✔ Peak-hour spikes ✔ Shared self-service analytics


🎯 Final Takeaway

Multi-Cluster Warehouses solve concurrency, not compute power. Use them when MANY people query at the same time, not when queries are heavy.

They ensure BI dashboards, analysts, and real-time reporting remain fast, responsive, and queue-free — even during peak hours.