How to Set Auto Resume Warehouse in Snowflake
In Snowflake, you set auto resume for a warehouse using the
AUTO_RESUME parameter in the CREATE WAREHOUSE or ALTER WAREHOUSE command. Setting AUTO_RESUME = TRUE makes the warehouse automatically start when a query is submitted and it is suspended.Syntax
The AUTO_RESUME parameter controls whether a warehouse automatically resumes when a query is submitted after it has been suspended.
Use it in the CREATE WAREHOUSE or ALTER WAREHOUSE commands as follows:
AUTO_RESUME = TRUE: Enables auto resume.AUTO_RESUME = FALSE: Disables auto resume.
sql
CREATE WAREHOUSE warehouse_name WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;
Example
This example creates a warehouse named my_warehouse that automatically suspends after 5 minutes of inactivity and automatically resumes when a query is run.
sql
CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'SMALL' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;
Output
Warehouse MY_WAREHOUSE created.
Common Pitfalls
Common mistakes when setting auto resume include:
- Not enabling
AUTO_RESUME, so the warehouse stays suspended until manually resumed. - Setting
AUTO_SUSPENDto 0 or a very high value, which prevents suspension and wastes credits. - Confusing
AUTO_RESUMEwithAUTO_SUSPEND; they control different behaviors.
sql
/* Wrong: AUTO_RESUME disabled, warehouse won't start automatically */ ALTER WAREHOUSE my_warehouse SET AUTO_RESUME = FALSE; /* Right: Enable auto resume for automatic start */ ALTER WAREHOUSE my_warehouse SET AUTO_RESUME = TRUE;
Quick Reference
| Parameter | Description | Values |
|---|---|---|
| AUTO_RESUME | Automatically resumes warehouse on query | TRUE or FALSE |
| AUTO_SUSPEND | Automatically suspends warehouse after inactivity (seconds) | Integer (e.g., 300) |
| WAREHOUSE_SIZE | Size of the warehouse | XSMALL, SMALL, MEDIUM, etc. |
Key Takeaways
Set AUTO_RESUME = TRUE to have the warehouse start automatically on query.
AUTO_SUSPEND controls how long the warehouse waits before suspending.
Without AUTO_RESUME enabled, you must manually resume the warehouse.
Use AUTO_RESUME and AUTO_SUSPEND together to optimize cost and performance.
