โ๏ธ Warehouse Sizing & Scaling in Snowflake
Warehouse sizing & scaling help you control performance, speed, and cost of Snowflake compute. Choosing the right size โ and knowing when to scale โ is one of the most important responsibilities in Snowflake architecture.
๐ง 1. What Is Warehouse Sizing?
Warehouse size means the compute power available to process SQL queries, ETL pipelines, BI dashboards, and ML workloads.
Snowflake sizes:
| Size | Nodes | Power Level |
|---|---|---|
| XS | 1 | Low |
| S | 2 | Light |
| M | 4 | Medium |
| L | 8 | Strong |
| XL | 16 | Very Strong |
| 2XL โ 4XL | 32 โ 128 | Extremely High |
๐ Rule: Bigger size = faster compute, more parallelism, more credits per second.
๐ 2. When to Increase Warehouse Size (Vertical Scaling)
Vertical scaling โ upgrading from XS โ S โ M โ L โ XL.
Increase size when:โ
- Queries take too long
- ETL load is heavy
- Transformations cause spilling
- BI dashboards lag
- Window functions or joins become expensive
- You process large datasets (GB โ TB)
Example:โ
Your ETL pipeline takes 45 mins on Small. Switching to Medium reduces it to 15 mins.
โฑ Saving 30 mins = saving cost & delivering data faster.
๐ When NOT to Increase Size
Do NOT scale up when:
- Many users run queries concurrently โ use multi-cluster instead
- Single query is slow โ might be due to bad SQL, not compute
- Micro-partition pruning is poor
- Queries spill due to wrong JOIN type or missing filters
- BI tool is bottlenecking (PowerBI/Tableau)
๐ Scaling does NOT fix poor query design.
๐ 3. Vertical Scaling vs Horizontal Scaling
Vertical Scalingโ
Increase compute power (XS โ S โ M โ L)
- Makes each query faster
- Helps ETL/Transformations
- Helps complex SQL and big joins
Horizontal Scaling (Multi-Cluster)โ
Add more clusters (like opening more checkout counters in a supermarket).
Great for:
- Many users running at once
- BI dashboards with 100+ users
- Shared warehouses across multiple teams
- Concurrency spikes
Example:
MAX_CLUSTER_COUNT = 5
Now up to 5 clusters run in parallel, avoiding queueing.
๐งฉ 4. Warehouse Scaling Policies
Snowflake supports two scaling policies:
๐น ECONOMYโ
- Adds clusters only when absolutely needed
- Removes clusters quickly
- Best for cost saving
๐น STANDARDโ
- Adds clusters aggressively
- Keeps clusters active longer
- Best for high performance
๐ BI and dashboards โ STANDARD ๐ ETL workloads โ ECONOMY
๐ 5. Auto-Suspend & Auto-Resume
To reduce cost, Snowflake suspends unused warehouses.
Recommended:
AUTO_SUSPEND = 60 -- suspend after 1 minute idle
AUTO_RESUME = TRUE
โณ Pay only for active usage โก Resume is instant
๐งช 6. How to Choose the Right Size (Simple Rules)
โ ETL Pipelinesโ
- Medium / Large
- Snowpark or ML workloads โ Snowpark-Optimized
โ BI & Reportingโ
- Small / Medium
- Multi-cluster for concurrency
โ Ad-hoc Developer Queriesโ
- X-Small / Small
โ Machine Learningโ
- XL / 2XL
- Prefer Snowpark-Optimized Warehouses
๐ 7. Cost Impact of Scaling
| Operation | Cost Impact |
|---|---|
| Scaling UP (XS โ M) | Cost โ (per second) |
| Scaling DOWN (L โ S) | Cost โ |
| Multi-cluster ON | Cost โโ |
| Auto-suspend fast | Cost โ |
| Bad queries | Cost โ unnecessarily |
๐ Snowflake cost = size ร time running
๐๏ธ 8. Best Practices for Warehouse Sizing & Scaling
๐น 1. Start small โ scale only when neededโ
๐น 2. Separate warehouses for teams (DE, BI, DS)โ
๐น 3. Enable auto-suspend (30โ60 sec)โ
๐น 4. Use multi-cluster for BI dashboardsโ
๐น 5. Do not oversize ETL โ medium usually bestโ
๐น 6. Use Warehouse Monitoring โ Query Profileโ
๐น 7. Use Snowpark-Optimized only when requiredโ
๐น 8. Resize during peak workloads (temporal scaling)โ
๐ฏ Final Summary
Warehouse sizing & scaling ensures:
โ Fast performance โ Low cost โ High concurrency โ Predictable workload behavior
A correctly sized warehouse makes Snowflake:
- Efficient
- Responsive
- Cost-effective
- Ready for growth