Auto-suspend and auto-resume in Snowflake - Commands & Configuration

Introduction

Running a data warehouse continuously can waste money when no queries are running. Auto-suspend and auto-resume help save costs by pausing the warehouse when idle and restarting it when needed.

When you want to save money by not running your Snowflake warehouse all the time.

When your data warehouse usage is unpredictable and you want it to start only when queries come in.

When you want to avoid manual steps to stop and start your warehouse.

When you want to ensure your warehouse is ready automatically without waiting.

When you want to control how long the warehouse stays active after queries finish.

Commands

This command sets the warehouse to automatically suspend after 300 seconds (5 minutes) of inactivity to save costs.

Terminal

ALTER WAREHOUSE my_warehouse SET AUTO_SUSPEND = 300;

Expected Output

Statement executed successfully.

This command enables the warehouse to automatically resume when a query is submitted, so you don't have to start it manually.

Terminal

ALTER WAREHOUSE my_warehouse SET AUTO_RESUME = TRUE;

Expected Output

Statement executed successfully.

This command shows the current settings of the warehouse, including auto-suspend and auto-resume values.

Terminal

SHOW WAREHOUSES LIKE 'my_warehouse';

Expected Output

name          | state     | auto_suspend | auto_resume

my_warehouse | SUSPENDED | 300          | TRUE

This command confirms which warehouse is currently in use for your session.

Terminal

SELECT CURRENT_WAREHOUSE();

Expected Output

CURRENT_WAREHOUSE()

my_warehouse

Key Concept

If you remember nothing else from this pattern, remember: auto-suspend saves money by pausing idle warehouses, and auto-resume makes them ready automatically when needed.

Common Mistakes

Setting AUTO_SUSPEND to 0 or a very low value.

This causes the warehouse to suspend immediately or too quickly, interrupting queries and causing delays.

Set AUTO_SUSPEND to a reasonable number like 300 seconds to allow short idle times without suspending.

Not enabling AUTO_RESUME after setting AUTO_SUSPEND.

The warehouse will suspend but not start automatically, requiring manual intervention.

Always enable AUTO_RESUME = TRUE to ensure the warehouse restarts automatically on query.

Summary

Use ALTER WAREHOUSE to set AUTO_SUSPEND to pause the warehouse after inactivity.

Enable AUTO_RESUME to have the warehouse start automatically when queries run.

Check warehouse settings with SHOW WAREHOUSES to confirm configuration.