How to Create a Warehouse in Snowflake: Syntax and Example
To create a warehouse in Snowflake, use the
CREATE WAREHOUSE command followed by the warehouse name and optional parameters like size and auto-suspend. This command sets up a compute resource for running queries and loading data.Syntax
The CREATE WAREHOUSE command creates a new virtual warehouse in Snowflake. You specify the warehouse name and can optionally set parameters like size, auto-suspend time, and auto-resume.
- WAREHOUSE_NAME: The name you choose for your warehouse.
- WAREHOUSE_SIZE: Size of the warehouse (e.g., XSMALL, SMALL, MEDIUM, LARGE).
- AUTO_SUSPEND: Time in seconds to suspend the warehouse when idle.
- AUTO_RESUME: Enables automatic resume when queries are submitted.
sql
CREATE WAREHOUSE WAREHOUSE_NAME WAREHOUSE_SIZE = 'SIZE' AUTO_SUSPEND = SUSPEND_TIME AUTO_RESUME = TRUE|FALSE;
Example
This example creates a warehouse named MY_WAREHOUSE with size SMALL, sets it to suspend after 300 seconds of inactivity, and enables auto-resume so it starts automatically when needed.
sql
CREATE WAREHOUSE MY_WAREHOUSE WAREHOUSE_SIZE = 'SMALL' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;
Output
Warehouse MY_WAREHOUSE successfully created.
Common Pitfalls
Common mistakes when creating warehouses include:
- Using invalid warehouse sizes (only specific sizes like XSMALL, SMALL, MEDIUM, LARGE are allowed).
- Not setting
AUTO_SUSPEND, which can cause unnecessary costs if the warehouse stays running idle. - Forgetting to enable
AUTO_RESUME, which means manual start is needed before queries run.
Always check your warehouse name does not conflict with existing ones.
sql
/* Wrong: Missing AUTO_SUSPEND causes warehouse to run indefinitely */ CREATE WAREHOUSE BAD_WAREHOUSE WAREHOUSE_SIZE = 'MEDIUM'; /* Right: Set AUTO_SUSPEND to save costs */ CREATE WAREHOUSE GOOD_WAREHOUSE WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE;
Quick Reference
| Parameter | Description | Example Values |
|---|---|---|
| WAREHOUSE_NAME | Name of the warehouse | MY_WAREHOUSE |
| WAREHOUSE_SIZE | Size of the warehouse | XSMALL, SMALL, MEDIUM, LARGE |
| AUTO_SUSPEND | Seconds to suspend when idle | 300, 600 |
| AUTO_RESUME | Automatically resume warehouse | TRUE, FALSE |
Key Takeaways
Use CREATE WAREHOUSE with a unique name and specify size to create compute resources.
Set AUTO_SUSPEND to avoid unnecessary costs by suspending idle warehouses.
Enable AUTO_RESUME to have the warehouse start automatically when queries run.
Choose warehouse size based on workload needs to balance cost and performance.
Check for existing warehouse names to avoid conflicts.
