How to Resize Warehouse in Snowflake: Syntax and Examples
To resize a warehouse in Snowflake, use the
ALTER WAREHOUSE command with the SET WAREHOUSE_SIZE option. For example, ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'LARGE'; changes the warehouse size to LARGE.Syntax
The syntax to resize a warehouse in Snowflake uses the ALTER WAREHOUSE command followed by the warehouse name and the SET WAREHOUSE_SIZE clause. You specify the new size as a string.
ALTER WAREHOUSE <warehouse_name>: identifies the warehouse to resize.SET WAREHOUSE_SIZE = '<size>': sets the new size. Sizes include XSMALL, SMALL, MEDIUM, LARGE, XLARGE, XXLARGE, etc.
sql
ALTER WAREHOUSE <warehouse_name> SET WAREHOUSE_SIZE = '<size>';
Example
This example shows how to resize a warehouse named my_warehouse from its current size to LARGE. This change takes effect immediately and adjusts the compute resources used.
sql
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'LARGE';
Output
Statement executed successfully.
Common Pitfalls
Common mistakes when resizing warehouses include:
- Using an invalid size name (e.g., misspelling or unsupported size).
- Trying to resize a warehouse that does not exist.
- Not having the required privileges to alter the warehouse.
- Expecting the resize to affect running queries immediately; some queries may continue on the old size until completion.
sql
/* Wrong: invalid size name */ ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'BIG'; /* Right: valid size name */ ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'MEDIUM';
Quick Reference
| Warehouse Size | Description |
|---|---|
| XSMALL | Extra small size, lowest compute power |
| SMALL | Small size, low compute power |
| MEDIUM | Medium size, moderate compute power |
| LARGE | Large size, higher compute power |
| XLARGE | Extra large size, high compute power |
| XXLARGE | Double extra large, highest compute power |
Key Takeaways
Use ALTER WAREHOUSE with SET WAREHOUSE_SIZE to resize a warehouse.
Specify a valid size like XSMALL, SMALL, MEDIUM, LARGE, XLARGE, or XXLARGE.
Ensure you have privileges to alter the warehouse before resizing.
Resizing affects compute resources and billing immediately.
Invalid size names or non-existent warehouses cause errors.
