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.

Practice

1. What is the main purpose of the auto-suspend feature in a Snowflake warehouse?

easy

A. To increase the warehouse size during peak hours

B. To automatically start the warehouse when a query is submitted

C. To automatically stop the warehouse after a period of inactivity to save costs

D. To backup warehouse data automatically

Solution

1. Step 1: Understand auto-suspend functionality

   Auto-suspend stops the warehouse when it is idle for a set time to avoid unnecessary charges.

2. Step 2: Differentiate from auto-resume

   Auto-resume starts the warehouse when a query runs, not auto-suspend.

3. Final Answer:

   To automatically stop the warehouse after a period of inactivity to save costs -> Option C

4. Quick Check:

   Auto-suspend = stops warehouse after inactivity [OK]

Hint: Auto-suspend stops idle warehouse to save money [OK]

Common Mistakes:

• Confusing auto-suspend with auto-resume
• Thinking auto-suspend increases warehouse size
• Assuming auto-suspend backs up data

2. Which of the following is the correct way to enable auto-resume for a Snowflake warehouse using SQL?

easy

A. ALTER WAREHOUSE my_wh SET AUTO_RESUME = TRUE;

B. ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = TRUE;

C. CREATE WAREHOUSE my_wh AUTO_RESUME = ON;

D. ALTER WAREHOUSE my_wh ENABLE AUTO_RESUME;

Solution

1. Step 1: Recall correct syntax for enabling auto-resume

   The correct command uses ALTER WAREHOUSE with SET AUTO_RESUME = TRUE.

2. Step 2: Eliminate incorrect options

   ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = TRUE; sets auto_suspend, not auto_resume. CREATE WAREHOUSE my_wh AUTO_RESUME = ON; uses wrong syntax for CREATE. ALTER WAREHOUSE my_wh ENABLE AUTO_RESUME; uses invalid command.

3. Final Answer:

   ALTER WAREHOUSE my_wh SET AUTO_RESUME = TRUE; -> Option A

4. Quick Check:

   Enable auto-resume with SET AUTO_RESUME = TRUE [OK]

Hint: Use ALTER WAREHOUSE ... SET AUTO_RESUME = TRUE to enable [OK]

Common Mistakes:

• Confusing AUTO_RESUME with AUTO_SUSPEND
• Using CREATE instead of ALTER for existing warehouse
• Using invalid commands like ENABLE AUTO_RESUME

3. Given this SQL command sequence:

ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 300;
ALTER WAREHOUSE my_wh SET AUTO_RESUME = TRUE;
-- Warehouse is currently suspended
SELECT CURRENT_WAREHOUSE();

What happens when the SELECT query runs?

medium

A. The warehouse automatically resumes and the query runs successfully

B. The query fails because the warehouse is suspended

C. The warehouse remains suspended and query waits indefinitely

D. The warehouse resumes but the query returns NULL

Solution

1. Step 1: Understand auto-resume behavior

   With AUTO_RESUME = TRUE, the warehouse starts automatically when a query runs.

2. Step 2: Apply to the SELECT query

   The warehouse is suspended but will resume automatically before executing the SELECT.

3. Final Answer:

   The warehouse automatically resumes and the query runs successfully -> Option A

4. Quick Check:

   Auto-resume starts warehouse on query run [OK]

Hint: Auto-resume starts suspended warehouse on query execution [OK]

Common Mistakes:

• Assuming query fails if warehouse is suspended
• Thinking warehouse stays suspended during query
• Believing query returns NULL when warehouse resumes

4. You set AUTO_SUSPEND = 0 and AUTO_RESUME = TRUE on a warehouse. What issue might occur?

medium

A. The warehouse suspends and resumes repeatedly every second

B. The warehouse suspends immediately and never resumes

C. The warehouse suspends after default 60 seconds

D. The warehouse never suspends, causing continuous charges

Solution

1. Step 1: Understand AUTO_SUSPEND = 0 meaning

   Setting AUTO_SUSPEND to 0 disables auto-suspend, so warehouse stays running.

2. Step 2: Consider AUTO_RESUME effect

   AUTO_RESUME = TRUE allows auto-start but does not suspend warehouse automatically.

3. Final Answer:

   The warehouse never suspends, causing continuous charges -> Option D

4. Quick Check:

   AUTO_SUSPEND=0 disables suspend, warehouse runs always [OK]

Hint: AUTO_SUSPEND=0 means warehouse never suspends [OK]

Common Mistakes:

• Thinking AUTO_SUSPEND=0 suspends immediately
• Assuming warehouse suspends after default time
• Believing warehouse cycles suspend/resume every second

5. You want to minimize costs by suspending a warehouse after 5 minutes of inactivity but ensure it resumes automatically when needed. Which configuration is best?

hard

A. Set AUTO_SUSPEND = 60 and AUTO_RESUME = FALSE

B. Set AUTO_SUSPEND = 300 and AUTO_RESUME = TRUE

C. Set AUTO_SUSPEND = 0 and AUTO_RESUME = FALSE

D. Set AUTO_SUSPEND = 600 and AUTO_RESUME = TRUE

Solution

1. Step 1: Choose auto-suspend time for 5 minutes

   5 minutes equals 300 seconds, so AUTO_SUSPEND = 300 is correct.

2. Step 2: Enable auto-resume for automatic start

   Setting AUTO_RESUME = TRUE ensures warehouse starts when queries run.

3. Final Answer:

   Set AUTO_SUSPEND = 300 and AUTO_RESUME = TRUE -> Option B

4. Quick Check:

   5 min suspend + auto-resume = AUTO_SUSPEND=300 & AUTO_RESUME=TRUE [OK]

Hint: Use 300 seconds for 5 min suspend and enable auto-resume [OK]

Common Mistakes:

• Using 0 for AUTO_SUSPEND disables suspend
• Disabling AUTO_RESUME causes manual start
• Setting suspend time too short or too long