How to Choose Warehouse Size in Snowflake for Optimal Performance
Choose a Snowflake
warehouse size based on your workload's complexity, concurrency needs, and budget. Larger warehouses process data faster but cost more, while smaller ones save cost but may slow down queries. Monitor query performance and scale up or down as needed using ALTER WAREHOUSE commands.Syntax
To set or change the size of a Snowflake warehouse, use the CREATE WAREHOUSE or ALTER WAREHOUSE commands with the WAREHOUSE_SIZE parameter.
WAREHOUSE_SIZE: Defines the compute size, such as X-Small, Small, Medium, Large, etc.CREATE WAREHOUSE: Creates a new warehouse with the specified size.ALTER WAREHOUSE: Changes the size of an existing warehouse.
sql
CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'MEDIUM'; ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'LARGE';
Example
This example creates a warehouse with size SMALL, runs a query, then scales the warehouse to MEDIUM to handle more workload.
sql
CREATE WAREHOUSE example_warehouse WITH WAREHOUSE_SIZE = 'SMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE; -- Run a sample query USE WAREHOUSE example_warehouse; SELECT COUNT(*) FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER; -- Scale up the warehouse ALTER WAREHOUSE example_warehouse SET WAREHOUSE_SIZE = 'MEDIUM';
Output
COUNT(*)
----------
150000
Common Pitfalls
Choosing too large a warehouse: This increases cost unnecessarily if your workload is small or infrequent.
Choosing too small a warehouse: This can cause slow query performance and queueing when many queries run concurrently.
Not monitoring usage: Without monitoring, you may not notice performance issues or overspending.
Always monitor query times and warehouse credit usage, then adjust size accordingly.
sql
/* Wrong: Creating a very large warehouse for light workload */ CREATE WAREHOUSE big_warehouse WITH WAREHOUSE_SIZE = 'X-LARGE'; /* Right: Start small and scale up if needed */ CREATE WAREHOUSE small_warehouse WITH WAREHOUSE_SIZE = 'SMALL'; -- Monitor and then ALTER WAREHOUSE to larger size if needed
Quick Reference
| Warehouse Size | Description | Use Case |
|---|---|---|
| X-Small | Lowest compute, lowest cost | Light workloads, development, testing |
| Small | Small compute, low cost | Small production workloads, low concurrency |
| Medium | Moderate compute and cost | Medium workloads, moderate concurrency |
| Large | High compute, higher cost | Heavy workloads, high concurrency |
| X-Large and above | Very high compute, highest cost | Very large workloads, peak concurrency |
Key Takeaways
Start with a small or medium warehouse size and scale based on workload needs.
Larger warehouses process queries faster but cost more credits.
Monitor query performance and credit usage regularly to optimize size.
Use
ALTER WAREHOUSE to change size without downtime.Avoid over-provisioning to save costs and under-provisioning to avoid slow queries.
