Virtual Warehouses: Compute, Sizing & Auto-Scaling

Snowflake's architecture separates storage and compute, allowing you to scale compute independently. Virtual warehouses are the compute layer—they process queries, load data, and perform DML operations. Choosing the right warehouse size and configuration is critical for performance and cost. A warehouse too small causes slow queries; too large wastes credits. Auto-scaling (multi-cluster) handles variable concurrency, automatically adding clusters when queries queue up. This tutorial covers everything you need to know: sizing guidelines, multi-cluster setup, auto-suspend, and real-world debugging. By the end, you'll be able to design cost-effective, performant warehouses for your workloads.

What is a Virtual Warehouse?

A virtual warehouse is a cluster of compute resources (CPU, memory, and storage) that Snowflake uses to execute SQL queries and perform DML operations. It is separate from storage, so you can scale compute independently. Each warehouse can be sized from X-Small (1 node) to 6X-Large (128 nodes). You can create multiple warehouses for different workloads (e.g., ETL, BI, development) and they can be suspended when not in use to save credits. Key properties: size, auto-suspend, auto-resume, and multi-cluster (for concurrency).

-- Create a virtual warehouse
CREATE WAREHOUSE my_wh
  WITH WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Show all warehouses
SHOW WAREHOUSES;

Sizing a Warehouse: How to Choose the Right Size

Choosing the right warehouse size depends on the workload. For small data loads or simple queries, X-Small or Small is sufficient. For complex aggregations on large tables, consider Large or larger. A common approach: start with Medium, monitor query performance, and adjust. Use the QUERY_HISTORY view to check bytes_spilled_to_remote_storage—if >0, the warehouse is too small. Also check average execution time. Rule of thumb: double the size until spillage disappears or performance plateaus.

-- Check queries that spilled to remote storage
SELECT query_id, warehouse_size, bytes_spilled_to_remote_storage, execution_time
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE warehouse_name = 'MY_WH'
  AND start_time > DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND bytes_spilled_to_remote_storage > 0
ORDER BY bytes_spilled_to_remote_storage DESC;

Auto-Suspend and Auto-Resume: Saving Credits

Auto-suspend automatically suspends a warehouse after a period of inactivity (default 10 minutes). Auto-resume starts it again when a query is submitted. This is critical for cost savings—idle warehouses still consume credits. Set auto-suspend to a low value (e.g., 60 seconds) for development warehouses. For production, 5-10 minutes is common to avoid frequent restarts. You can also manually suspend warehouses with ALTER WAREHOUSE ... SUSPEND.

-- Set auto-suspend to 1 minute
ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 60;

-- Suspend warehouse immediately
ALTER WAREHOUSE my_wh SUSPEND;

-- Resume warehouse
ALTER WAREHOUSE my_wh RESUME;

Multi-Cluster Warehouses: Handling Concurrency

Multi-cluster warehouses allow Snowflake to automatically add or remove clusters (up to a max) based on query concurrency. This is ideal for variable workloads like BI dashboards. Configure MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT. When queries queue, Snowflake adds clusters up to MAX. When concurrency drops, clusters are removed. Each cluster is a full warehouse of the specified size, so costs multiply. Use multi-cluster only when concurrency is unpredictable.

-- Create a multi-cluster warehouse
CREATE WAREHOUSE bi_wh
  WITH WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 5
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;

-- Alter existing warehouse to enable multi-cluster
ALTER WAREHOUSE my_wh SET MIN_CLUSTER_COUNT = 2 MAX_CLUSTER_COUNT = 10;

Best Practices for Warehouse Configuration

1. Use separate warehouses for different workloads (ETL, BI, ad-hoc). 2. Start with X-Small and scale up based on monitoring. 3. Set auto-suspend to 1-5 minutes for dev, 5-10 for prod. 4. Enable multi-cluster only when concurrency is an issue. 5. Use resource monitors to set credit quotas. 6. Regularly review warehouse usage in ACCOUNT_USAGE views. 7. Consider using 'Warehouse Sizing' recommendations from Snowflake's UI.

-- Create a resource monitor
CREATE RESOURCE MONITOR daily_limit
  WITH CREDIT_QUOTA = 500
  FREQUENCY = 'DAILY'
  START_TIMESTAMP = '2024-01-01 00:00:00'
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

-- Assign monitor to warehouse
ALTER WAREHOUSE my_wh SET RESOURCE_MONITOR = daily_limit;

Real-World Example: ETL vs. BI Workloads

Consider a company with two main workloads: daily ETL that loads and transforms large tables, and a BI dashboard with many concurrent users. For ETL, use a Large warehouse (or bigger) to process data quickly, with auto-suspend set to 10 minutes (since it runs once daily). For BI, use a Medium multi-cluster warehouse with MIN=1, MAX=5 to handle variable concurrency, auto-suspend 5 minutes. This separation ensures ETL doesn't impact BI performance and costs are isolated.

-- ETL warehouse
CREATE WAREHOUSE etl_wh
  WITH WAREHOUSE_SIZE = 'LARGE'
  AUTO_SUSPEND = 600
  AUTO_RESUME = TRUE;

-- BI warehouse
CREATE WAREHOUSE bi_wh
  WITH WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 5
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;