Skip to main content

โš–๏ธ 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:

SizeNodesPower Level
XS1Low
S2Light
M4Medium
L8Strong
XL16Very Strong
2XL โ€“ 4XL32 โ€“ 128Extremely 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

OperationCost Impact
Scaling UP (XS โ†’ M)Cost โ†‘ (per second)
Scaling DOWN (L โ†’ S)Cost โ†“
Multi-cluster ONCost โ†‘โ†‘
Auto-suspend fastCost โ†“
Bad queriesCost โ†‘ 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