Course contents

SnowPro Core COF-C03 Study GuidePart 2: Warehouses and performanceScaling Warehouses: Up vs Out

Scaling Warehouses: Up vs Out

Scaling Warehouses: Up vs Out

Snowflake gives you two scaling levers, and they solve different problems. Scaling up means resizing: same single cluster, more compute, so each individual query gets more muscle. Scaling out means multi-cluster: same size, more identical clusters, so more queries run at once. Confusing them is the most reliably planted trap in Domain 4.

Scale up versus scale out. Left, scale up: ALTER WAREHOUSE resizes one Medium cluster to Large, so each query gets twice the CPU and memory; this fixes spilling and slow heavy queries, but there is still one queue. Right, scale out: a multi-cluster warehouse keeps the Medium size and runs up to three clusters; this fixes queuing at peak concurrency, but a single query runs no faster. Multi-cluster needs Enterprise edition or higher.SCALE UP · RESIZESCALE OUT · MULTI-CLUSTERBEFOREMediumone clusterAFTERLargeone cluster2× CPU and memoryALTER WAREHOUSE wh SETWAREHOUSE_SIZE = 'LARGE';BEFOREMediumone clusterAFTER · 3 × MEDIUMcluster 1cluster 2cluster 3addclustersALTER WAREHOUSE wh SETMAX_CLUSTER_COUNT = 3;✓Fixes spilling, slow heavy queries✗Still one queue at peak✓Fixes queuing at peak✗A single query runs no fasterCredits double with each size stepEnterprise edition or higherresize

SCALE UP · RESIZE

BEFOREMediumone cluster

resize

AFTERLargeone cluster2× CPU and memory

ALTER WAREHOUSE wh SET
WAREHOUSE_SIZE = 'LARGE';

  • Fixes spilling, slow heavy queries
  • Still one queue at peak

Credits double with each size step

SCALE OUT · MULTI-CLUSTER

BEFOREMediumone cluster

add clusters
AFTER · 3 × MEDIUMcluster 1cluster 2cluster 3

ALTER WAREHOUSE wh SET
MAX_CLUSTER_COUNT = 3;

  • Fixes queuing at peak
  • A single query runs no faster

Enterprise edition or higher

Figure 2.4 Scale up for heavy queries, scale out for many queries. Left: a resize gives each query more CPU and memory, which fixes spilling but keeps one queue. Right: a multi-cluster warehouse (Enterprise edition) adds same-size clusters, which fixes queuing but doesn't make any single query faster.
Scale UP (resize)
  • Each query gets more CPU, memory, and SSD
  • Fixes spilling and heavy single queries
  • One cluster still serves one queue
  • Cost doubles per size step, busy or not
Scale OUT (multi-cluster)
  • More identical clusters, started only when needed
  • Fixes queuing when many users hit at once
  • Doesn't make any single query faster
  • Requires Enterprise edition

The one-line rule: one slow query, scale up; many queued queries, scale out. The Query Profile tells you which world you're in before you spend anything: spilled bytes point up, queued time points out.

Definition

A multi-cluster warehouse is one warehouse name backed by several clusters of identical size, up to its MAX_CLUSTER_COUNT (see the limits below). Snowflake adds clusters when queries queue and retires them when load drops. It exists to solve concurrency, and it requires Enterprise edition or higher.

The problem

Monday 09:00, three hundred analysts open dashboards against BI_WH. A single Medium cluster runs perhaps eight queries at once; the rest queue, and dashboards spin. Resizing to 4X-Large wouldn't help much: each query is small, so the extra power per query is wasted while the queue logic stays the same. What you need is more lanes, not a faster car.

Two modes

Set MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT. Equal values (both above 1) give Maximized mode: all clusters run whenever the warehouse runs, for predictable full capacity. Different values give Auto-scale mode: Snowflake starts at the minimum and adds clusters up to the maximum as queries queue.

Scaling policies (auto-scale only)

PolicyStarts a new clusterShuts a cluster downOptimizes for
Standard (default)Immediately when a query queuesAfter a sustained period of low load, the least-loaded cluster is shut down once its running queries finishUser experience: no queuing
EconomyOnly if the new cluster would stay busy for at least 6 minutes of queued workWhen Snowflake estimates less than 6 minutes of work remains for the least-loaded cluster, after its queries finishCredits: tolerates some queuing

A Monday morning in auto-scale, MIN 1 / MAX 3

Auto-scale on BI_WH, a Medium warehouse with MIN 1 and MAX 3 clusters, from 08:45 to 10:00 (illustrative). One cluster runs until 09:02, two until 09:10, three until 09:40, then one again. Each running cluster bills 4 credits per hour: about 1.1 credits, then 1.1, then 6.0, then 1.3, so about 9.5 credits for the morning, against 15 credits if three clusters ran the whole time.123CLUSTERS RUNNING4 cr/h≈ 1.1 cr8 cr/h≈ 1.1 cr12 cr/h= 6.0 cr4 cr/h≈ 1.3 cr08:4509:0209:1009:4010:00Billed ≈ 9.5 credits for the morningThree clusters from 08:45 to 10:00 would cost 15. Medium = 4 credits per hour per cluster. Auto-scale on BI_WH, a Medium warehouse with MIN 1 and MAX 3 clusters, from 08:45 to 10:00 (illustrative). One cluster runs until 09:02, two until 09:10, three until 09:40, then one again. Each running cluster bills 4 credits per hour: about 1.1 credits, then 1.1, then 6.0, then 1.3, so about 9.5 credits for the morning, against 15 credits if three clusters ran the whole time.CLUSTERS RUNNING123≈ 1.1 cr4 cr/h≈ 1.1 cr8 cr/h= 6.0 cr12 cr/h≈ 1.3 cr4 cr/h08:4509:0209:1009:4010:00Billed ≈ 9.5 credits for the morningThree clusters from 08:45 to 10:00 wouldcost 15. Medium = 4 credits per hour percluster.
Figure 2.5 Auto-scale bills only the clusters that are running. The bars show how many clusters run at each moment of the Monday-morning timeline below (illustrative). Each cluster is billed at the Medium rate, so the morning costs about 9.5 credits instead of 15 for three clusters running throughout.
08:451 cluster · 4 cr/hr

A few early queries, no queue. The minimum cluster handles it alone.

09:022 clusters · 8 cr/hr

Dashboards pile in, queries queue. Standard policy starts cluster 2 the moment the first query waits.

09:103 clusters · 12 cr/hr

Peak. All three lanes open; nobody queues. This is the ceiling, MAX_CLUSTER_COUNT, and further demand would queue again.

09:40back to 1 · 4 cr/hr

Load fades. After consecutive checks confirm fewer clusters suffice, 3 and then 2 retire. Alice's late query lands on cluster 1 like nothing happened.

Billing follows the badges: you paid for one cluster until 09:02, two for the eight minutes after that, three for the half hour from 09:10 to 09:40, and one again afterward. Figure 2.5 adds it up: about 9.5 credits for the morning, against 15 if all three clusters had run from 08:45 to 10:00. That per-cluster, per-second arithmetic is what makes auto-scale cheaper than a permanently bigger warehouse for spiky concurrency.

?Queries are queuing on a multi-cluster warehouse in Auto-scale mode. Which scaling policy?
Standard (default)
Start a cluster now

A cluster starts as soon as a query queues or Snowflake estimates the running clusters lack capacity. Queues drain fast; credits are spent sooner.

Economy
Start only for ≥ 6 minutes of work

A cluster starts only if Snowflake estimates enough queued load to keep it busy for at least 6 minutes. Short bursts queue instead, saving credits.

✓Standard spends credits to remove queues; Economy accepts some queuing to save credits.
Figure 2.6 The two scaling policies as a decision. Both apply only in Auto-scale mode (minimum and maximum cluster counts differ).

Billing is simply the sum of running clusters: a Medium (4 credits/hr) running 3 clusters burns 12 credits per hour while all three are up. Each cluster suspends independently under auto-scale.

Exam traps
  • Maximum clusters: the long-standing answer is 10, and Snowsight still caps the setting there. Since February 2025, SQL accepts more on smaller sizes: up to 300 for X-Small to Medium, 160 for Large, 80 for X-Large, 40 for 2X-Large, 20 for 3X-Large, and 10 for 4X-Large and above. Check where the value is set and the warehouse size before you answer 10.
  • Multi-cluster answers concurrency scenarios (many users, queuing). If the scenario is one giant slow query, the answer is resizing, and multi-cluster is the distractor.
  • All clusters in one multi-cluster warehouse are the same size. You can't mix a Small and a Large.
  • Minimum edition: Enterprise. A Standard-edition scenario asking for multi-cluster is really an edition question.
Memory aid

"Size for weight, clusters for crowd." A heavy query needs a bigger warehouse. A crowd of users needs more clusters. And the policy names mean what they say: Standard is the standard user experience (no waiting), Economy is the economy fare (you might wait at the gate).

Key takeaways
  • Clusters are all the same size, Enterprise edition minimum. The long-standing cap of 10 clusters still applies in Snowsight and for 4XL and larger; since February 2025, SQL allows more on smaller sizes (up to 300 for XS to Medium). min = max is Maximized, min < max is Auto-scale.
  • Standard starts a cluster the moment anything queues; Economy starts one only for at least 6 minutes of estimated work and retires clusters when less than 6 minutes remains.
  • Billing is per running cluster: a Medium on 3 clusters burns 12 credits per hour, and each cluster suspends independently in auto-scale.