Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 38 ยท Warehouses and Performance

Multi-Cluster Warehouses and Concurrency

Learn how Snowflake multi-cluster warehouses add clusters for many concurrent users, how scaling policies work, and when queuing means you need scale-out, not size.

Resizing a warehouse makes one query faster. It does not help when the problem is many queries at once. If fifty analysts open dashboards at 9:00, a single Medium warehouse has a fixed amount of capacity, and extra queries wait in a queue. For that problem Snowflake offers multi-cluster warehouses: the same size, but several copies that start and stop automatically.

Multi-cluster warehouses need Enterprise edition or higher.

Scale up versus scale out

Two different directions, two different problems:

  • Scale up (bigger size): one query gets more servers. Fixes slow, heavy queries.
  • Scale out (more clusters): more queries run side by side. Fixes queued, concurrent queries.
flowchart LR
  A["Slow single query"] --> B["Scale UP<br/>bigger size"]
  C["Many users, queries queue"] --> D["Scale OUT<br/>more clusters"]

Match the fix to the symptom: size for long queries, clusters for waiting queries.

Configuring a multi-cluster warehouse

SQL
CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE    = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY    = 'STANDARD'
  AUTO_SUSPEND      = 300
  AUTO_RESUME       = TRUE;
  • MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT set the range. Each cluster is a full Medium here, so four running clusters bill four times the Medium rate.
  • If min = max (and above 1), the warehouse runs in maximized mode: that fixed number of clusters starts and stops together.
  • If min < max, it runs in auto-scale mode: Snowflake adds clusters when queries queue and removes them when load drops.

All clusters of a warehouse have the same size, and a query runs on exactly one cluster; it is never split across clusters.

Scaling policies

In auto-scale mode, SCALING_POLICY controls how eager Snowflake is:

Policy Adds a cluster Removes a cluster
STANDARD (default) As soon as a query would queue, so queueing is prevented After a few consecutive checks show the load fits on fewer clusters
ECONOMY Only when there is enough sustained work to keep the new cluster busy for about six minutes After a longer period of low load

STANDARD favors fast response; ECONOMY favors saving credits and tolerates some waiting. Choose economy for batch workloads where a short queue does not matter.

Simulating the queue

Snowflake's scheduler is not something you can run, so here is a small stand-in that models the idea. Each query takes a fixed time; a cluster handles a fixed number of queries at the same time.

Python Queue simulator (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The waiting time falls sharply as clusters are added, then flattens once every query has a free slot. Beyond that point extra clusters would only cost money, which is why a sensible MAX_CLUSTER_COUNT matters.

Python Cost of the extra clusters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

How to tell what you need

Look at the queue columns in the query history. The Snowsight Query History and the QUERY_HISTORY view in SNOWFLAKE.ACCOUNT_USAGE show how long a query spent in each phase:

SQL
SELECT warehouse_name,
       AVG(queued_overload_time) / 1000 AS avg_queued_seconds,
       COUNT(*)                          AS queries
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY avg_queued_seconds DESC;
  • QUEUED_OVERLOAD_TIME high: too many queries for the capacity. Add clusters (or a separate warehouse).
  • QUEUED_PROVISIONING_TIME high: the warehouse was starting up.
  • Compilation or execution time high with no queueing: resize or fix the data access instead.

(ACCOUNT_USAGE data can lag by up to a few hours, so use INFORMATION_SCHEMA.QUERY_HISTORY() when you need the last few minutes.)

Another remedy is to split workloads into separate warehouses. A long-running data science job should not share a warehouse with the dashboard users.

Two related limits: MAX_CONCURRENCY_LEVEL (default 8) controls how many queries one cluster runs together, and STATEMENT_QUEUED_TIMEOUT_IN_SECONDS cancels a query that waited too long.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Queries wait in a queue at peak time, but each query is fast when it runs. What helps most?

    The problem is concurrency, not the speed of a single query, so scaling out with clusters removes the queue.
  2. 2. In auto-scale mode, which setting is true?

    Auto-scale needs a range. Equal values give maximized mode, and a query always runs on a single cluster.
  3. 3. Which scaling policy waits to be sure a new cluster would stay busy before starting it?

    ECONOMY saves credits by starting a cluster only when there is sustained work, accepting a little queueing.
  4. 4. A multi-cluster Medium warehouse runs 3 clusters for one hour. How many credits?

    Each Medium cluster uses 4 credits per hour, so three clusters use 12.

Summary

  • Scale up for slow queries, scale out for queued queries.
  • Multi-cluster warehouses (Enterprise edition) use a min and max cluster count; all clusters share one size.
  • STANDARD prevents queueing; ECONOMY saves credits.
  • A query runs on one cluster only.
  • Read queue columns in query history before deciding.

Next: The caching layers