Snowflake Virtual Warehouses
A virtual warehouse is Snowflake's compute engine. Every SQL query, every data load, every transformation runs on a virtual warehouse. Understanding how warehouses work — how to size them, when to create multiple ones, how auto-scaling works, and how they affect your costs — is one of the most valuable skills you can develop as a Snowflake practitioner. Poor warehouse decisions cause slow queries and unexpected bills. Good warehouse decisions make your workloads fast and cost-efficient.
What a Virtual Warehouse Actually Is
A virtual warehouse is a named cluster of cloud servers (virtual machines) that Snowflake provisions on your behalf inside AWS, Azure, or GCP. You never see those servers directly. You interact with the warehouse only through SQL commands and Snowsight settings. When a warehouse is running, those cloud VMs are actively reserved for your account. When you suspend a warehouse, those VMs are released back to the cloud provider and you stop paying immediately.
VIRTUAL WAREHOUSE INTERNAL STRUCTURE ====================================== VIRTUAL WAREHOUSE: ANALYTICS_WH (Medium size) +--Node 1 (cloud VM)--+ +--Node 2 (cloud VM)--+ +--Node 3 (cloud VM)--+ | CPU: 8 cores | | CPU: 8 cores | | CPU: 8 cores | | RAM: 32 GB | | RAM: 32 GB | | RAM: 32 GB | | Local cache: 200 GB| | Local cache: 200 GB | | Local cache: 200 GB | +---------------------+ +---------------------+ +---------------------+ All 3 nodes work together to execute your queries in parallel. Each node downloads its share of micro-partitions from central storage. Results from all nodes are merged and returned to you as a single result set.
The local cache on each warehouse node stores recently accessed micro-partitions. When you run the same query twice, or run a similar query on the same table, the warehouse often reads from local cache instead of going back to cloud storage. Cache reads are dramatically faster than storage reads, which is why repeated queries on the same data feel nearly instant.
Warehouse Sizes and What They Mean
Snowflake offers eight warehouse sizes. Each step up doubles the number of nodes, which roughly doubles the compute power available for large queries. It also doubles the credit consumption per hour.
WAREHOUSE SIZE GUIDE
=====================
SIZE CREDITS/HR NODES RAM LOCAL CACHE BEST FOR
-------- ---------- ----- ------- ----------- --------
X-Small 1 1 VM ~16 GB ~100 GB Learning, small queries
Small 2 2 VMs ~32 GB ~200 GB Light reporting
Medium 4 4 VMs ~64 GB ~400 GB Regular analytics
Large 8 8 VMs ~128 GB ~800 GB Heavy transformations
X-Large 16 16 VMs ~256 GB ~1.6 TB Complex ETL jobs
2X-Large 32 32 VMs ~512 GB ~3.2 TB Very large datasets
3X-Large 64 64 VMs ~1 TB ~6.4 TB Petabyte scale
4X-Large 128 128 VMs ~2 TB ~12.8 TB Extreme workloads
Note: Actual node counts and specs are managed by Snowflake internally.
The ratios above reflect the relative compute scaling between sizes.
Choosing the Right Size: The Goldilocks Rule
The right warehouse size is the smallest size that completes your query in an acceptable time. Running a 30-second query on an X-Large warehouse when a Medium would finish it in 45 seconds wastes 4x the credits for a 15-second benefit. Conversely, running a 4-hour ETL job on an X-Small when an X-Large would finish it in 30 minutes wastes time and pays for 8x as many hours of idle waiting.
SIZING DECISION GUIDE ====================== WORKLOAD TYPE RECOMMENDED SIZE ------------- ---------------- Interactive exploration queries X-Small to Small Single-user reporting Small to Medium Multi-user BI dashboard Medium (or multi-cluster) Daily ETL transformation Medium to Large Weekly full refresh of large DW Large to X-Large Complex ML feature engineering X-Large to 2X-Large Loading terabytes of raw data Large (parallelises file loading)
Creating and Managing Warehouses with SQL
-- Create a warehouse for BI reporting CREATE WAREHOUSE REPORTING_WH WAREHOUSE_SIZE = 'SMALL' AUTO_SUSPEND = 120 -- suspend after 2 minutes of inactivity AUTO_RESUME = TRUE -- wake up automatically when a query arrives MIN_CLUSTER_COUNT = 1 -- start with 1 cluster MAX_CLUSTER_COUNT = 1 -- single-cluster (Standard edition) SCALING_POLICY = 'STANDARD' COMMENT = 'Warehouse for Tableau and Power BI dashboards'; -- Create a larger warehouse for ETL jobs CREATE WAREHOUSE ETL_WH WAREHOUSE_SIZE = 'LARGE' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE COMMENT = 'Used for nightly dbt transformation runs'; -- Create a multi-cluster warehouse (Enterprise edition) CREATE WAREHOUSE CONCURRENT_WH WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 120 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 -- scales up to 5 clusters under load SCALING_POLICY = 'ECONOMY' -- adds clusters only when queries queue COMMENT = 'For high-concurrency analyst access'; -- Resize an existing warehouse ALTER WAREHOUSE ETL_WH SET WAREHOUSE_SIZE = 'X-LARGE'; -- Suspend a warehouse manually ALTER WAREHOUSE REPORTING_WH SUSPEND; -- Resume a suspended warehouse ALTER WAREHOUSE REPORTING_WH RESUME; -- Drop a warehouse you no longer need DROP WAREHOUSE OLD_DEV_WH;
Multi-Cluster Warehouses: Scaling for Concurrent Users
A standard warehouse has one cluster of nodes. When 50 users submit queries simultaneously, all 50 queries share that one cluster. Queries queue up and wait their turn if the cluster is fully occupied. A multi-cluster warehouse solves this by automatically adding more clusters — each cluster handles its own set of concurrent queries.
MULTI-CLUSTER SCALING DIAGRAM
================================
09:00 AM — 5 users querying:
CONCURRENT_WH: [Cluster 1 active] [Cluster 2: off] [Cluster 3: off]
All 5 queries handled by Cluster 1, no waiting
09:30 AM — 80 users querying (morning rush):
CONCURRENT_WH: [Cluster 1: full] [Cluster 2: added] [Cluster 3: added]
Queries distributed:
Cluster 1 → users 1-30
Cluster 2 → users 31-60
Cluster 3 → users 61-80
No queue, all queries start immediately
11:00 AM — traffic drops to 10 users:
Cluster 2 and 3 idle for auto-suspend period, then shut down
Cost drops back to single-cluster rate
CONCURRENT_WH: [Cluster 1 active] [Cluster 2: off] [Cluster 3: off]
Scaling Policies
POLICY BEHAVIOUR BEST FOR
------ --------- --------
STANDARD Add cluster as soon as any query queues Prioritise speed
ECONOMY Add cluster only when queued queries
cannot be served within 2 minutes Prioritise cost savings
Separate Warehouses for Separate Workloads
Running all workloads on a single warehouse causes two problems: ETL jobs slow down analyst queries because they compete for resources, and analyst queries can accidentally delay critical data loads. The solution is to create separate warehouses for separate workload types.
RECOMMENDED MULTI-WAREHOUSE ARCHITECTURE ========================================== WAREHOUSE SIZE USERS/PURPOSE --------- ---- ------------- LOADING_WH Large Data engineers — COPY INTO, dbt runs TRANSFORM_WH Large dbt models, complex SQL transformations REPORTING_WH Medium BI tools (Tableau, Power BI, Looker) ANALYST_WH Small Ad-hoc analyst queries, exploration ADMIN_WH X-Small Metadata queries, SHOW commands, monitoring Benefits: - ETL jobs never compete with dashboard queries - BI tools always have dedicated compute - Cost tracked separately per team by warehouse - Each warehouse can be sized optimally for its workload
Warehouse Resource Monitors: Controlling Costs
A Resource Monitor sets credit usage limits on a warehouse. When a warehouse reaches the limit, Snowflake can alert you, suspend the warehouse, or do nothing — depending on your configuration. Resource Monitors prevent runaway queries or forgotten warehouses from exhausting your entire monthly credit allocation.
-- Create a resource monitor limiting a warehouse to 100 credits per month
CREATE RESOURCE MONITOR analyst_monthly_limit
CREDIT_QUOTA = 100 -- 100 credits per monthly period
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 75 PERCENT DO NOTIFY -- email alert at 75% usage
ON 90 PERCENT DO NOTIFY -- another alert at 90%
ON 100 PERCENT DO SUSPEND; -- suspend warehouse at 100%
-- Attach the monitor to a warehouse
ALTER WAREHOUSE ANALYST_WH
SET RESOURCE_MONITOR = analyst_monthly_limit;
-- Create an account-level monitor (limits total account spend)
CREATE RESOURCE MONITOR account_guard
CREDIT_QUOTA = 1000
FREQUENCY = MONTHLY
TRIGGERS
ON 80 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND_IMMEDIATE; -- kill running queries immediately
Warehouse Query Queuing and Concurrency
Each warehouse has a maximum number of concurrent queries it handles without queuing. When that limit is reached, additional queries queue and wait. The concurrency limit depends on warehouse size — larger warehouses handle more simultaneous queries before queuing begins.
CONCURRENCY AND QUEUING BEHAVIOUR =================================== X-Small warehouse: handles ~8 concurrent statements before queuing Medium warehouse: handles ~16 concurrent statements before queuing Large warehouse: handles ~32 concurrent statements before queuing When a query queues: - You see "QUEUED" status in query history - The query waits for a running query to finish - Then it starts executing using freed resources Solutions to frequent queuing: Option 1: Resize the warehouse to a larger size Option 2: Enable multi-cluster mode (Enterprise edition) Option 3: Create a second warehouse and route some users to it
Monitoring Warehouse Performance
-- View currently running and queued queries on a warehouse
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE(
WAREHOUSE_NAME => 'REPORTING_WH',
END_TIME_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP()),
RESULT_LIMIT => 100
))
ORDER BY start_time DESC;
-- Warehouse credit usage over the past 7 days
SELECT
warehouse_name,
DATE_TRUNC('day', start_time) AS usage_date,
SUM(credits_used) AS daily_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1, 2;
-- Find idle warehouses (running but executing no queries)
SELECT
warehouse_name,
SUM(credits_used_cloud_services) AS cloud_service_credits,
SUM(credits_used_compute) AS compute_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY compute_credits DESC;
Key Points
- A virtual warehouse is a cluster of cloud VMs that executes SQL queries — you pay credits only while it runs
- Eight sizes from X-Small to 4X-Large each double the compute power and credit cost per hour
- Set AUTO_SUSPEND to 60–120 seconds and AUTO_RESUME to TRUE on every warehouse to eliminate idle credit waste
- Multi-cluster warehouses (Enterprise edition) automatically add clusters under high concurrency and drop them when traffic decreases
- Separate warehouses for separate workloads (loading, transformation, reporting, ad-hoc) prevent competition and enable per-team cost tracking
- Resource Monitors set credit quotas per warehouse or per account, with triggers that alert or suspend warehouses at defined thresholds
- The right warehouse size is the smallest one that finishes your workload in an acceptable time — test before committing to a size