Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 SizeDescription
XSMALLExtra small size, lowest compute power
SMALLSmall size, low compute power
MEDIUMMedium size, moderate compute power
LARGELarge size, higher compute power
XLARGEExtra large size, high compute power
XXLARGEDouble 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.