Bird
Raised Fist0
Snowflakecloud~5 mins

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

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
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 OutputExpected
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 OutputExpected
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 OutputExpected
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 OutputExpected
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.

Practice

(1/5)
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
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard