Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use Auto Suspend for Cost Savings in Snowflake

Use the AUTO_SUSPEND parameter to set the idle time in seconds before a Snowflake warehouse automatically suspends, saving costs by stopping compute charges. You can set this when creating or altering a warehouse with CREATE WAREHOUSE or ALTER WAREHOUSE commands.
📐

Syntax

The AUTO_SUSPEND parameter defines the number of seconds a warehouse waits while idle before suspending automatically. Setting it to 0 disables auto suspend.

Use it in these commands:

  • CREATE WAREHOUSE <name> WITH AUTO_SUSPEND = <seconds>
  • ALTER WAREHOUSE <name> SET AUTO_SUSPEND = <seconds>
sql
CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 300;

ALTER WAREHOUSE my_warehouse SET AUTO_SUSPEND = 600;
💻

Example

This example creates a warehouse named cost_saver that automatically suspends after 5 minutes (300 seconds) of inactivity. This helps reduce costs by stopping compute charges when not in use.

sql
CREATE WAREHOUSE cost_saver
  WITH WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;

-- Check the warehouse settings
SHOW WAREHOUSES LIKE 'cost_saver';
Output
name | state | warehouse_size | auto_suspend | auto_resume -----------|-----------|----------------|--------------|------------ cost_saver | SUSPENDED | SMALL | 300 | TRUE
⚠️

Common Pitfalls

Common mistakes when using AUTO_SUSPEND include:

  • Setting AUTO_SUSPEND too high, causing unnecessary costs by keeping warehouses running idle.
  • Setting AUTO_SUSPEND to 0 disables auto suspend, which can lead to unexpected charges.
  • Not enabling AUTO_RESUME, which means the warehouse won't start automatically when queries run.
sql
/* Wrong: disables auto suspend, warehouse never suspends */
ALTER WAREHOUSE cost_saver SET AUTO_SUSPEND = 0;

/* Right: suspends after 5 minutes idle */
ALTER WAREHOUSE cost_saver SET AUTO_SUSPEND = 300;
📊

Quick Reference

ParameterDescriptionExample Value
AUTO_SUSPENDSeconds of inactivity before warehouse suspends300
AUTO_RESUMEAutomatically resume warehouse on queryTRUE
WAREHOUSE_SIZESize of the warehouse affecting cost and performance'SMALL'
✅

Key Takeaways

Set AUTO_SUSPEND to a reasonable idle time to save costs by suspending warehouses automatically.
Avoid setting AUTO_SUSPEND to 0 unless you want to disable auto suspend and keep warehouses always running.
Enable AUTO_RESUME to have warehouses start automatically when queries run.
Use ALTER WAREHOUSE to update AUTO_SUSPEND on existing warehouses without downtime.
Check warehouse settings with SHOW WAREHOUSES to verify your auto suspend configuration.