Auto-suspend and auto-resume in Snowflake - Step-by-Step Execution

Process Flow - Auto-suspend and auto-resume

Warehouse starts

↓

Warehouse active

↓

No queries for X minutes?

No→Keep running

Yes↓

Auto-suspend warehouse

↓

Warehouse suspended

↓

New query arrives?

No→Stay suspended

Yes↓

Auto-resume warehouse

↓

Warehouse active again

The warehouse runs and processes queries. If idle for a set time, it suspends automatically. When a new query comes, it resumes automatically.

Execution Sample

Snowflake

ALTER WAREHOUSE mywh SET AUTO_SUSPEND = 5;
ALTER WAREHOUSE mywh SET AUTO_RESUME = TRUE;

This sets the warehouse to suspend after 5 minutes idle and to resume automatically on new queries.

Process Table

Step	Warehouse State	Idle Time (minutes)	Event	Action Taken
1	Running	0	Query executed	Continue running
2	Running	3	No queries	Continue running
3	Running	5	No queries	Auto-suspend triggered
4	Suspended	N/A	No queries	Stay suspended
5	Suspended	N/A	New query arrives	Auto-resume triggered
6	Running	0	Query executed	Continue running

💡 Warehouse suspends after 5 minutes idle and resumes on new query.

Status Tracker

Variable	Start	After Step 1	After Step 3	After Step 5	Final
Warehouse State	Running	Running	Suspended	Running	Running
Idle Time (minutes)	0	3	5	0	0

Key Moments - 2 Insights

Why does the warehouse suspend at step 3 even though it was running before?

Because the idle time reached the AUTO_SUSPEND threshold of 5 minutes as shown in execution_table row 3.

What triggers the warehouse to resume at step 5?

A new query arrives, triggering auto-resume as shown in execution_table row 5.

Visual Quiz - 3 Questions

Look at the execution table, what is the warehouse state at step 4?

A Running

B Starting

C Suspended

D Stopping

💡 Hint

Check the 'Warehouse State' column at step 4 in the execution_table.

At which step does the warehouse auto-suspend due to inactivity?

A Step 2

B Step 3

C Step 5

D Step 6

💡 Hint

Look for 'Auto-suspend triggered' in the 'Action Taken' column.

If AUTO_SUSPEND was set to 10 minutes instead of 5, how would the idle time at suspension change?

A It would suspend at 10 minutes

B It would suspend at 5 minutes

C It would suspend at 3 minutes

D It would never suspend

💡 Hint

AUTO_SUSPEND controls the idle time before suspension, see variable_tracker for idle time values.

Concept Snapshot

Auto-suspend pauses a Snowflake warehouse after set idle minutes.
Auto-resume restarts it when a new query arrives.
Set AUTO_SUSPEND with minutes, AUTO_RESUME to TRUE.
Saves cost by not running idle warehouses.
Warehouse state changes: Running -> Suspended -> Running.

Full Transcript

Snowflake warehouses run to process queries. When no queries run for a set time, the warehouse auto-suspends to save resources. When a new query arrives, it auto-resumes automatically. This behavior is controlled by AUTO_SUSPEND and AUTO_RESUME settings. For example, setting AUTO_SUSPEND to 5 means the warehouse suspends after 5 minutes idle. AUTO_RESUME true means it starts again on new queries. The execution table shows the warehouse running, then suspending after 5 minutes idle, staying suspended until a new query arrives, then resuming to run again.