Multi-Cluster Warehouses for Concurrency Scaling

Handle high query concurrency by automatically spinning up additional compute clusters.

Two different problems look alike from a user's seat, and they have opposite fixes.

It's like a checkout area at a store that automatically opens more registers (clusters) the moment lines get long, and closes them again once the rush passes — versus warehouse sizing, which is more like giving each cashier a faster scanner so each individual transaction completes quicker.

Key Concepts

    one query is slow          -> the query needs more machines  -> size up
    many queries are queuing   -> you need more warehouses       -> more clusters

A multi-cluster warehouse is the second. It starts additional clusters of the same size when queries start queuing, and stops them when demand falls.

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

Sizing up would not have helped. A Medium warehouse runs a fixed number of concurrent queries. The fiftieth user at 9 a.m. waits in a queue, and their query is not slow — it has not started.

An X-Large would run each query faster and still queue the fiftieth. Adding clusters is what removes the queue.

The scaling policy is a cost dial.

    STANDARD   start a cluster as soon as queuing appears   less waiting, more credits
    ECONOMY    wait for sustained demand first              some waiting, fewer credits

STANDARD for interactive dashboards where people are watching. ECONOMY for batch work where a minute of queuing costs nothing.

STANDARDECONOMY

Setting min equal to max gives a fixed pool. MIN = 3, MAX = 3 keeps three clusters running whenever the warehouse is active — no scale-up delay, and you pay for all three. Worth it only for a known, predictable peak.

MIN = 3, MAX = 3

How the billing works. Each running cluster bills at the warehouse's size. A Medium (4 credits/hour) scaled to 3 clusters bills 12 credits/hour while all three run.

So MAX_CLUSTER_COUNT is a spending limit as much as a capacity setting. Setting it to 10 on a Large warehouse permits 80 credits an hour.

MAX_CLUSTER_COUNT

Use both dimensions deliberately. They are independent, and the right answer usually uses each for the problem it solves:

    Medium, 1-5 clusters    -> many users running moderate dashboard queries
    X-Large, 1 cluster      -> a few very heavy analytical queries
    Small, 1-10 clusters    -> many small, frequent, concurrent queries

How to tell which you have. The query profile and query history distinguish them plainly: time spent queuing means add clusters, time spent executing means size up. Check before changing either, since the wrong change costs money and fixes nothing.

What the interviewer is probing.1. "What problem does it solve?" Probing: the fit. Stalls: "Slow queries." Moves up: queuing from many concurrent users — it adds clusters, it does not make one query faster.

2. "Maximized or auto-scale?" Probing: the two modes. Stalls: "The same." Moves up: auto-scale starts and stops clusters on demand; maximized keeps the maximum running for predictable low latency at full cost.

3. "What does the scaling policy change?" Probing: the detail. Stalls: "Speed." Moves up: standard adds a cluster as soon as queuing appears; economy waits to keep clusters fully loaded, trading latency for credits.

4. "When is a bigger single warehouse the right answer instead?" Probing: the diagnosis. Stalls: "Always." Moves up: when individual queries are slow or spilling — concurrency scaling only helps when queries queue.