Scalability & Performance Mastery
Snowflake · Warehouses & Performance
Theory
Snowflake scales compute in two directions. Scaling up (vertical) means resizing a warehouse to a larger size, which gives each query more CPU, memory and local disk; it helps big, complex queries finish faster and avoid spilling. Scaling out (horizontal) means a multi-cluster warehouse that adds more clusters of the same size when queries start to queue, then removes them when load drops.
Multi-cluster warehouses (Enterprise edition and above) use MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT, plus a scaling policy: Standard starts clusters quickly to avoid queuing, Economy waits to keep clusters busy and save credits. For example, a dashboard used by 200 people at 9 AM needs scale-out, while one slow monthly report needs scale-up.
Why we use it
Slow queries and many concurrent users are different problems. A bigger warehouse does not fix queuing well, and more clusters do not make a single query faster, so you need both tools.
When to use it
Scale up when a single query is slow, scans a lot of data or spills to disk. Scale out when many queries queue at the same time. If neither applies, look at pruning, clustering or the SQL itself before adding compute.
Vertical Scaling
Resizing a warehouse to a bigger T-shirt size gives more raw compute to a single query or session, which speeds up an individual heavy query but costs more per second while running.
Horizontal Scaling
Multi-cluster warehouses automatically add extra clusters of the same size to absorb concurrency spikes — many users querying at once — then scale back down once the spike passes, so read performance for hundreds of users stays consistent.
Choosing Between the Two
Use vertical scaling when a single query or job is too slow; use horizontal (multi-cluster) scaling when many people hit the same warehouse at once and start queueing.
Which One Do I Need? A Quick Flow
One query too slow → resize up (vertical). Many people queueing on the same warehouse → add clusters (horizontal): Query/job too slow? │ ┌────┴────┐ YES NO → queries queueing behind each other? │ │ ▼ YES Resize to a │ bigger T-shirt ▼ size (vertical) Raise MAX_CLUSTER_COUNT (horizontal, multi-cluster)
How it works
- Users report slowness
- Check Query Profile and warehouse load history
- Are queries queuing because of many concurrent users? Yes: Scale out: raise MAX_CLUSTER_COUNT. No: Look at the individual query.
- Is the query spilling or scanning a lot of data? Yes: Scale up: resize the warehouse. No: Tune SQL, pruning or clustering.
- Measure again and keep the cheapest setup that works
Example
CREATE WAREHOUSE bi_shared_wh
WAREHOUSE_SIZE = 'MEDIUM'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 6
SCALING_POLICY = 'STANDARD'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;
SELECT * FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
DATE_RANGE_START => DATEADD(HOUR, -6, CURRENT_TIMESTAMP()),
WAREHOUSE_NAME => 'BI_SHARED_WH'));Key points
- Scale out for concurrency, scale up for heavy individual queries
- Standard policy favours speed; Economy favours keeping clusters fully used
- Resizing does not interrupt running queries; new size applies to new ones
- MIN = MAX cluster count runs in maximized mode; MIN < MAX is auto-scale
- Doubling size roughly halves time only when the query is compute-bound