Skip to main content

Warehouse Sizing & Auto-Scaling Strategies

Choosing the right warehouse size isn't a one-time decision — it's an ongoing process of matching compute resources to workload demands. Too small and your queries spill and crawl. Too large and you're burning credits on idle capacity. In this lesson, we'll cover sizing strategies, multi-cluster scaling, and patterns for organizing warehouses across different workloads.


Warehouse Sizes: Scale Up vs. Scale Out​

Snowflake offers warehouse sizes from X-Small to 6X-Large. Each step up doubles the compute nodes and the per-hour credit cost. The fundamental question is whether your workload benefits more from scaling up (a bigger warehouse) or scaling out (more clusters of the same size).

Scale up when:

  • Individual queries are slow due to data volume or complexity
  • You see spillage to local or remote storage in the Query Profile
  • A single large ETL job needs to process a massive dataset

Scale out when:

  • Many concurrent users are queuing because the warehouse is busy
  • Query latency is fine individually, but wait times are high
  • Dashboard refresh times spike during peak hours

A common mistake is scaling up to solve a concurrency problem. If your queries each run in 10 seconds but users wait 2 minutes in the queue, a bigger warehouse won't help — you need more clusters.


Multi-Cluster Warehouses​

Multi-cluster warehouses automatically spin up additional clusters when concurrency demand increases and shut them down when demand drops.

CREATE WAREHOUSE analytics_wh
WITH
WAREHOUSE_SIZE = 'MEDIUM'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 6
SCALING_POLICY = 'STANDARD'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;
ParameterDescription
MIN_CLUSTER_COUNTMinimum clusters always running (1 for cost efficiency, higher for guaranteed capacity)
MAX_CLUSTER_COUNTMaximum clusters that can be started under load
SCALING_POLICYControls how aggressively clusters are added and removed

Setting MIN_CLUSTER_COUNT greater than 1 means you're paying for multiple clusters even during idle periods. Only do this if you need guaranteed low-latency response for time-sensitive workloads.


Scaling Policies​

STANDARD Policy​

  • Starts a new cluster as soon as a query is queued (or estimated to be queued)
  • Shuts down clusters after 2-3 consecutive idle checks (~60 seconds each)
  • Best for: interactive/BI workloads where user wait time matters

ECONOMY Policy​

  • Only starts a new cluster after enough queries are queued to keep it busy for at least 6 minutes
  • Shuts down clusters more aggressively
  • Best for: batch workloads where a few minutes of queuing is acceptable
ALTER WAREHOUSE batch_etl_wh SET SCALING_POLICY = 'ECONOMY';

Auto-Suspend and Auto-Resume​

These two settings have an outsized impact on your bill:

ALTER WAREHOUSE analytics_wh SET
AUTO_SUSPEND = 300 -- seconds of inactivity before suspending
AUTO_RESUME = TRUE; -- automatically resume when a query arrives
Workload TypeRecommended Auto-Suspend
ETL / batch jobs60 seconds (suspend quickly after the job)
Interactive / BI300-600 seconds (avoid frequent resume overhead)
Development60-120 seconds (developers have unpredictable patterns)
Always-on dashboards0 (never suspend) — only if queries arrive constantly

Remember the 60-second minimum billing: if your warehouse suspends and resumes 20 times in an hour, you're paying for 20 minutes minimum even if actual compute time was 5 minutes. Find the balance between idle cost and resume overhead.


Warehouse Types​

Standard Warehouses​

The default. Good for most SQL queries, BI workloads, and general-purpose analytics.

Snowpark-Optimized Warehouses​

Provide 16x the memory per node compared to standard. Designed for Python, Java, or Scala workloads via Snowpark, or ML training requiring large memory footprints.

CREATE WAREHOUSE ml_training_wh
WITH WAREHOUSE_SIZE = 'MEDIUM'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
AUTO_SUSPEND = 120 AUTO_RESUME = TRUE;

Adaptive Warehouses​

Dynamically allocate resources to optimize for either query performance or throughput depending on the workload, without manual intervention.


Workload Isolation Patterns​

One of the most impactful decisions you'll make is how to divide work across warehouses:

WarehouseSizeClustersSuspendUsed By
ETLLarge160sAirflow, dbt, scheduled loads
AnalyticsMedium1-4300sBI tools, dashboards, analysts
DevelopmentSmall160sData engineers, ad-hoc exploration
Data ScienceMedium (Snowpark)1-2120sML training, Python workloads

The key principle: don't let a runaway ETL job slow down the CEO's dashboard. Separate warehouses mean separate resource pools.


Monitoring Warehouse Utilization​

-- Average load over time (queries running + queued)
SELECT warehouse_name,
DATE_TRUNC('hour', start_time) AS hour,
AVG(avg_running) AS avg_queries_running,
AVG(avg_queued_load) AS avg_queries_queued
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1, 2;

-- Credit consumption by warehouse
SELECT warehouse_name,
SUM(credits_used) AS total_credits,
SUM(credits_used_compute) AS compute_credits,
SUM(credits_used_cloud_services) AS cloud_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY total_credits DESC;

If a warehouse consistently shows avg_queries_queued > 0, that's your signal to scale out. If avg_queries_running is consistently low, you might be over-provisioned.


Right-Sizing Methodology​

There's no formula for the perfect warehouse size. Use this iterative approach:

  1. Start with a Medium warehouse for most workloads.
  2. Run representative queries and check the Query Profile for spillage.
  3. If you see remote spillage, scale up one size and retest. If queries run cleanly, try scaling down.
  4. Monitor for a week using WAREHOUSE_LOAD_HISTORY.
  5. Check concurrency — if users queue during peak, add multi-cluster scaling before increasing size.
-- Find queries with spillage on a specific warehouse
SELECT query_id, warehouse_size,
bytes_spilled_to_remote_storage / POWER(1024, 3) AS gb_spilled_remote,
total_elapsed_time / 1000 AS elapsed_seconds
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE warehouse_name = 'ANALYTICS_WH'
AND start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
AND bytes_spilled_to_remote_storage > 0
ORDER BY bytes_spilled_to_remote_storage DESC
LIMIT 20;

Example: Designing a Warehouse Strategy​

A medium-sized company with 50 analysts, 8 data engineers, and 5 data scientists:

CREATE WAREHOUSE etl_production_wh
WITH WAREHOUSE_SIZE = 'LARGE'
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1
AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;

CREATE WAREHOUSE analyst_wh
WITH WAREHOUSE_SIZE = 'MEDIUM'
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 4
AUTO_SUSPEND = 300 AUTO_RESUME = TRUE
SCALING_POLICY = 'STANDARD';

CREATE WAREHOUSE datascience_wh
WITH WAREHOUSE_SIZE = 'MEDIUM'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2
AUTO_SUSPEND = 120 AUTO_RESUME = TRUE;

CREATE WAREHOUSE dev_sandbox_wh
WITH WAREHOUSE_SIZE = 'SMALL'
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1
AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;

Review utilization monthly and adjust based on actual load data.


Key Takeaways​

  • Scale up for complex queries with spillage; scale out for concurrency problems.
  • Use STANDARD scaling policy for interactive workloads, ECONOMY for batch.
  • Match auto-suspend to workload cadence — too aggressive wastes credits on resume overhead.
  • Isolate workloads into dedicated warehouses to prevent resource contention.
  • Right-sizing is iterative: start with a reasonable default, measure, and adjust.