Warehouse sizes and scaling in Snowflake - Step-by-Step Execution

Process Flow - Warehouse sizes and scaling

Start: Choose Warehouse Size

↓

Warehouse Size Options

↓

Warehouse Starts with Chosen Size

↓

Scaling Decision

↓

Scale Up

↓

Increase Size

↓

More Compute Power

↓

Warehouse Processes Queries

↓

Scale Down or Remove Clusters

↓

End

This flow shows how you pick a warehouse size, start it, then scale up by size or scale out by adding clusters to handle more work.

Execution Sample

Snowflake

CREATE WAREHOUSE mywh WITH WAREHOUSE_SIZE = 'SMALL' WAREHOUSE_TYPE = 'STANDARD';
ALTER WAREHOUSE mywh SET WAREHOUSE_SIZE = 'MEDIUM';
ALTER WAREHOUSE mywh SET MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 3;
ALTER WAREHOUSE mywh RESUME;

This code creates a small warehouse, scales it up to medium size, enables multi-cluster scaling, and starts it.

Process Table

Step	Action	Warehouse Size	Clusters	Result
1	Create warehouse with size SMALL	SMALL	1	Warehouse created, ready to start
2	Start warehouse	SMALL	1	Warehouse running with 1 cluster
3	Scale up warehouse size to MEDIUM	MEDIUM	1	More compute power per cluster
4	Enable multi-cluster scaling (min 1, max 3)	MEDIUM	1-3	Warehouse can add clusters automatically
5	Load increases, auto scale out adds 2 clusters	MEDIUM	3	3 clusters running in parallel
6	Load decreases, auto scale in removes 2 clusters	MEDIUM	1	Back to single cluster
7	Pause warehouse	MEDIUM	0	Warehouse stopped, no compute cost

💡 Warehouse paused, no clusters running, compute stopped

Status Tracker

Variable	Start	After Step 1	After Step 3	After Step 4	After Step 5	After Step 6	Final
Warehouse Size	N/A	SMALL	MEDIUM	MEDIUM	MEDIUM	MEDIUM	MEDIUM
Clusters	N/A	1	1	1-3	3	1	0
State	N/A	Created	Created	Created	Running	Running	Paused

Key Moments - 3 Insights

Why does the warehouse size change from SMALL to MEDIUM but clusters stay at 1 initially?

Because scaling up changes the power of each cluster, not the number of clusters. Clusters remain 1 until multi-cluster scaling is enabled (see step 3 and 4 in execution_table).

How does multi-cluster scaling affect the number of clusters?

It allows the warehouse to add or remove clusters automatically between the min and max cluster count based on load (see steps 4, 5, and 6).

What happens to compute cost when the warehouse is paused?

When paused, all clusters stop and no compute resources are used, so cost stops (see step 7).

Visual Quiz - 3 Questions

Look at the execution_table, what is the warehouse size after step 3?

A SMALL

B MEDIUM

C LARGE

D X-SMALL

💡 Hint

Check the 'Warehouse Size' column at step 3 in execution_table

At which step does the warehouse add more clusters due to load increase?

A Step 2

B Step 4

C Step 5

D Step 7

💡 Hint

Look for when clusters increase from 1 to 3 in execution_table

If the warehouse size was never changed from SMALL, how would step 3's 'Warehouse Size' value appear?

A SMALL

B MEDIUM

C LARGE

D X-LARGE

💡 Hint

Step 3 shows the size change; if no change, it stays as initial size

Concept Snapshot

Warehouse sizes define compute power per cluster (X-Small to X-Large).
Scaling up increases cluster size (more power per cluster).
Scaling out adds clusters (multi-cluster scaling) for parallel work.
Auto-scaling adjusts clusters based on load between min and max.
Pausing warehouse stops all clusters and compute cost.
Use ALTER commands to change size and scaling settings.

Full Transcript

This lesson shows how Snowflake warehouses start with a chosen size that sets compute power per cluster. You can scale up by increasing the warehouse size, which makes each cluster more powerful. You can also scale out by enabling multi-cluster scaling, which adds or removes clusters automatically based on workload. When load increases, more clusters run in parallel; when load decreases, clusters are removed. Pausing the warehouse stops all clusters and stops compute charges. The execution table traces creating a small warehouse, scaling it up to medium, enabling multi-cluster scaling, auto-scaling clusters, and pausing the warehouse.