11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies

11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies

When multiple concurrent users, dashboards, and automated pipelines submit queries to the same virtual warehouse simultaneously, available compute threads and memory can become saturated. In a standard single-cluster warehouse, excess queries are placed into a FIFO (First-In, First-Out) wait queue. In Snowflake Enterprise Edition and above, architects can deploy Multi-Cluster Warehouses (MCW) to scale compute capacity horizontally (Scale OUT), dynamically spinning up and shutting down identical clusters to eliminate queuing.

Multi-Cluster Warehouse Architecture

A Multi-Cluster Warehouse expands a single logical warehouse into an elastic fleet of compute clusters. Each cluster within the warehouse has the exact same T-shirt size and configuration.

Core Architectural Principles of MCW

1. Homogeneous Cluster Sizing: All clusters in an MCW share identical hardware specifications. If an MCW is configured with WAREHOUSE_SIZE = 'LARGE' and MAX_CLUSTER_COUNT = 5, each provisioned cluster is an independent Large cluster consuming 8 credits per hour. Snowflake does not support heterogeneous sizing (e.g., mixing Medium and Large clusters) within a single warehouse.
2. Dynamic Query Routing: The Snowflake Cloud Services layer acts as an intelligent load balancer. When a client executes a query against an MCW, Cloud Services inspects active cluster utilization and routes the query to a cluster with available execution capacity.
3. Independent Local SSD Caches Across Clusters: While all clusters read from the same underlying shared database storage, each individual cluster maintains its own independent local SSD cache. As queries are distributed across clusters, each cluster warms its own cache independently. A query routed to Cluster 2 cannot leverage data cached on the local SSDs of Cluster 1.
4. Linear Credit Multiplier: Credit consumption is metered per cluster on a per-second basis (subject to the 60-second minimum for each cluster when it starts). If 3 clusters of a Large warehouse (8 credits/hour) run concurrently for one hour, total consumption is $3 \times 8 =$ 24 credits.

Scaling Policies: Standard vs. Economy

When configuring an auto-scaling Multi-Cluster Warehouse, architects choose between two scaling policies that govern when additional clusters are spun up or spun down: Standard and Economy.

-- Create an Enterprise Multi-Cluster Warehouse with Standard policy
CREATE OR REPLACE WAREHOUSE bi_standard_mcw
  WAREHOUSE_SIZE = 'MEDIUM'               -- 4 credits/hr per cluster
  MIN_CLUSTER_COUNT = 1                   -- Minimum 1 cluster online
  MAX_CLUSTER_COUNT = 5                   -- Burst up to 5 clusters (20 credits/hr max)
  SCALING_POLICY = 'STANDARD'             -- Prioritize minimal queuing
  AUTO_SUSPEND = 300                      -- Suspend entire warehouse after 5 mins idle
  AUTO_RESUME = TRUE;

-- Create an Enterprise Multi-Cluster Warehouse with Economy policy
CREATE OR REPLACE WAREHOUSE batch_economy_mcw
  WAREHOUSE_SIZE = 'LARGE'                -- 8 credits/hr per cluster
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY = 'ECONOMY'              -- Prioritize credit conservation
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE;

Standard Scaling Policy (Low Latency / High Concurrency)

The Standard scaling policy minimizes query queuing latency above all else:

• Spin-Up Heuristic: A new cluster is provisioned immediately when a query is queued because existing clusters are saturated, or when the system detects that incoming query volume exceeds the execution capacity of current clusters.
• Spin-Down Heuristic: Snowflake continuously monitors cluster load. A cluster is shut down if it has been idle or under-utilized for 2 to 3 consecutive monitoring intervals (evaluated every minute). The system verifies that the remaining clusters can sustain current query throughput before terminating a cluster.
• Recommended Use Cases: Interactive BI dashboards (Tableau, PowerBI), customer-facing analytics portals, and executive reporting where query wait times are strictly unacceptable.

Economy Scaling Policy (Credit Conservation)

The Economy scaling policy prioritizes financial efficiency over query response latency:

• Spin-Up Heuristic: When queries begin to queue, Snowflake does not spin up a new cluster immediately. Instead, the Cloud Services engine evaluates the queued backlog and estimates whether the accumulated queries will keep an additional cluster fully saturated for at least 6 minutes.
• If the estimated workload can be cleared by the existing cluster in under 6 minutes, the queries remain in the queue and run sequentially without spinning up another cluster.
• Spin-Down Heuristic: Snowflake marks the least-loaded cluster for shutdown when it estimates the cluster has less than 6 minutes of work left, then shuts it down after its running queries finish.
• Recommended Use Cases: Internal batch transformations, non-urgent scheduled reports, and background processing pipelines where queries waiting in a queue for 2 to 5 minutes causes zero operational detriment.

Scaling Policy Comparison Matrix

Multi-Cluster Modes: Maximized vs. Auto-Scaling

Multi-Cluster Warehouses operate in one of two distinct operational modes determined by the relationship between MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT.

+-----------------------------------------------------------------------------------------+
|                           MULTI-CLUSTER OPERATIONAL MODES                               |
+-------------------------------------------+---------------------------------------------+
| AUTO-SCALING MODE                         | MAXIMIZED MODE                              |
| MIN_CLUSTER_COUNT < MAX_CLUSTER_COUNT     | MIN_CLUSTER_COUNT = MAX_CLUSTER_COUNT (> 1) |
+-------------------------------------------+---------------------------------------------+
| • Starts with MIN_CLUSTER_COUNT clusters  | • All clusters start immediately on resume  |
| • Scales up dynamically to MAX as needed  | • Static capacity; no auto-scaling logic    |
| • Scales back down to MIN when idle       | • Constant credit burn (Clusters * Rate)    |
| • Best for unpredictable user concurrency | • Best for known, large-scale peak events   |
+-------------------------------------------+---------------------------------------------+

1. Auto-Scaling Mode (MIN_CLUSTER_COUNT < MAX_CLUSTER_COUNT)

In auto-scaling mode (e.g., MIN = 1, MAX = 5), the warehouse dynamically adjusts its running cluster count based on real-time query volume and the configured SCALING_POLICY.

• When resumed, the warehouse launches with exactly MIN_CLUSTER_COUNT clusters online.
• If concurrency spikes, additional clusters spin up until MAX_CLUSTER_COUNT is reached.
• When query activity subsides, extra clusters spin down until only MIN_CLUSTER_COUNT remains.
• If no queries run for the duration specified by AUTO_SUSPEND, the entire warehouse suspends (all clusters shut down to zero). When a new query arrives, AUTO_RESUME brings up MIN_CLUSTER_COUNT clusters.

2. Maximized Mode (MIN_CLUSTER_COUNT = MAX_CLUSTER_COUNT where value > 1)

In maximized mode (e.g., MIN = 4, MAX = 4), auto-scaling logic is completely bypassed.

• When the warehouse resumes, all specified clusters start immediately and run continuously.
• Zero Provisioning Latency: Incoming queries immediately have access to the full aggregate compute power of all clusters without waiting for dynamic cluster spin-up.
• Predictable Credit Consumption: If set to 4 clusters on a Large warehouse (8 credits/hour), the warehouse burns exactly $4 \times 8 = 32$ credits per hour while running.
• When to Use: Predictable, high-throughput batch events—such as Monday 8:00 AM enterprise dashboard spikes, quarterly financial consolidations, or strict SLA data loads—where queue wait times and dynamic provisioning latency are unacceptable.

Concurrency Governing Parameters & Diagnostics

Snowflake provides granular parameters to govern how individual clusters handle concurrent statements and prevent runaway query pile-ups.

1. MAX_CONCURRENCY_LEVEL

The MAX_CONCURRENCY_LEVEL parameter specifies the soft ceiling of concurrent SQL statements that can execute on a single warehouse cluster simultaneously (the system default is 8).

• It is a soft target, not a rigid throttle. Snowflake's query scheduler dynamically evaluates the memory requirements of each executing query.
• If 8 lightweight lookup queries arrive, the scheduler may run all 8 concurrently.
• However, if a single query requires 70% of cluster RAM, the scheduler automatically limits concurrency on that cluster to prevent memory exhaustion, queuing subsequent queries even if fewer than 8 statements are active.

2. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

Defines the maximum time in seconds that a query can remain waiting in a warehouse queue before it is automatically cancelled by the system.

• Default Value: 0 (meaning disabled; a query can queue indefinitely until warehouse capacity becomes available).
• Architect Recommendation: Set this parameter on production BI warehouses (e.g., STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300) to prevent cascading query pile-ups during unexpected traffic surges, returning a clear timeout error to client applications rather than leaving connections hanging.

3. STATEMENT_TIMEOUT_IN_SECONDS

Defines the maximum total time a statement may take — including queued, locked, compilation, and execution time — before Snowflake cancels it. When set both in the session hierarchy and on the warehouse, the lowest non-zero value applies.

• Default Value: 172,800 seconds (48 hours / 2 days).
• Setting an explicit timeout (e.g., 3600 seconds) on ad-hoc warehouses prevents runaway, poorly written cartesian joins from burning credits indefinitely.

Diagnosing Queuing in SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

Snowflake captures detailed queuing telemetry in QUERY_HISTORY to help architects pinpoint concurrency bottlenecks:

-- Audit warehouse queuing and identify concurrency bottlenecks
SELECT 
    warehouse_name,
    COUNT(query_id) AS total_queries,
    AVG(execution_time) / 1000 AS avg_exec_sec,
    AVG(queued_overload_time) / 1000 AS avg_queue_overload_sec,
    AVG(queued_provisioning_time) / 1000 AS avg_queue_provisioning_sec,
    MAX(queued_overload_time) / 1000 AS max_queue_overload_sec
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND warehouse_name = 'BI_REPORTING_WH'
GROUP BY warehouse_name;

• QUEUED_OVERLOAD_TIME: The time (in milliseconds) a query spent waiting in queue because the warehouse cluster was fully saturated executing other queries. Remedy: Scale OUT (add clusters or increase MAX_CLUSTER_COUNT) or switch from Economy to Standard scaling policy.
• QUEUED_PROVISIONING_TIME: The time (in milliseconds) a query spent waiting for a new cluster to be allocated from the cloud provider during an auto-scaling event or warehouse resume. Remedy: Increase MIN_CLUSTER_COUNT or increase AUTO_SUSPEND to prevent frequent cold restarts.
• QUEUED_REPAIR_TIME: The time spent waiting for a failed node to be repaired/replaced (rare hardware fault recovery).