Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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

ParameterDescriptionExample Values
WAREHOUSE_NAMEName of the warehouseMY_WAREHOUSE
WAREHOUSE_SIZESize of the warehouseXSMALL, SMALL, MEDIUM, LARGE
AUTO_SUSPENDSeconds to suspend when idle300, 600
AUTO_RESUMEAutomatically resume warehouseTRUE, 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.