Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 SizeDescriptionUse Case
X-SmallLowest compute, lowest costLight workloads, development, testing
SmallSmall compute, low costSmall production workloads, low concurrency
MediumModerate compute and costMedium workloads, moderate concurrency
LargeHigh compute, higher costHeavy workloads, high concurrency
X-Large and aboveVery high compute, highest costVery 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.