Auto-suspend and auto-resume in Snowflake - Mini Project: Build & Apply

Auto-suspend and Auto-resume in Snowflake Warehouse

📖 Scenario: You manage a Snowflake data warehouse that runs queries for your team. To save costs, you want the warehouse to automatically suspend when idle and automatically resume when queries start.

🎯 Goal: Configure a Snowflake warehouse with auto-suspend set to 300 seconds and auto-resume enabled.

📋 What You'll Learn

Create a warehouse named my_warehouse with size XSMALL

Set AUTO_SUSPEND to 300 seconds

Enable AUTO_RESUME

Use valid Snowflake SQL syntax for warehouse creation and alteration

💡 Why This Matters

🌍 Real World

Auto-suspend and auto-resume help control costs by stopping compute when not in use and starting it when needed automatically.

💼 Career

Cloud engineers and data analysts use these settings to optimize Snowflake warehouse usage and reduce cloud expenses.

1

Create the initial warehouse

Write a Snowflake SQL statement to create a warehouse named my_warehouse with size XSMALL.

Snowflake

-- Your code here

Need a hint?

Use CREATE WAREHOUSE followed by the warehouse name and size.

2

Set auto-suspend time

Write a Snowflake SQL statement to alter the warehouse my_warehouse and set AUTO_SUSPEND to 300 seconds.

Snowflake

CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'XSMALL';
-- Your code here

Need a hint?

Use ALTER WAREHOUSE to change the AUTO_SUSPEND property.

3

Enable auto-resume

Write a Snowflake SQL statement to alter the warehouse my_warehouse and enable AUTO_RESUME.

Snowflake

CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'XSMALL';
ALTER WAREHOUSE my_warehouse SET AUTO_SUSPEND = 300;
-- Your code here

Need a hint?

Use ALTER WAREHOUSE to set AUTO_RESUME to TRUE.

4

Verify the warehouse configuration

Write a Snowflake SQL statement to show the properties of the warehouse my_warehouse to verify AUTO_SUSPEND and AUTO_RESUME settings.

Snowflake

CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'XSMALL';
ALTER WAREHOUSE my_warehouse SET AUTO_SUSPEND = 300;
ALTER WAREHOUSE my_warehouse SET AUTO_RESUME = TRUE;
-- Your code here

Need a hint?

Use SHOW WAREHOUSES LIKE 'my_warehouse' to see the warehouse settings.