Why optimization controls Snowflake costs - See It in Action

Why optimization controls Snowflake costs

📖 Scenario: You are managing a Snowflake data warehouse for a small company. The company wants to keep costs low while running queries efficiently.

🎯 Goal: Build a simple Snowflake setup that shows how optimizing warehouse size and query execution controls costs.

📋 What You'll Learn

Create a Snowflake warehouse with a specific size

Set a cost control variable for maximum credits per hour

Write a query that uses the warehouse efficiently

Configure auto-suspend and auto-resume to save costs

💡 Why This Matters

🌍 Real World

Companies use Snowflake warehouses to run data queries. Optimizing warehouse settings helps control cloud costs while maintaining performance.

💼 Career

Cloud engineers and data analysts must configure warehouses efficiently to balance cost and speed in Snowflake environments.

1

Create a Snowflake warehouse

Write a Snowflake SQL command to create a warehouse named OPTIMIZE_WH with size SMALL.

Snowflake

-- Your code here to create warehouse OPTIMIZE_WH with size SMALL

Need a hint?

Use CREATE WAREHOUSE and specify WAREHOUSE_SIZE = 'SMALL'.

2

Set cost control with max credits per hour

Write a Snowflake SQL command to alter the warehouse OPTIMIZE_WH to set MAX_CLUSTER_COUNT to 1 to control costs.

Snowflake

CREATE WAREHOUSE OPTIMIZE_WH WITH WAREHOUSE_SIZE = 'SMALL';
-- Your code here to set MAX_CLUSTER_COUNT to 1

Need a hint?

Use ALTER WAREHOUSE to set MAX_CLUSTER_COUNT = 1.

3

Write an efficient query using the warehouse

Write a Snowflake SQL query that selects all columns from SALES_DATA using the warehouse OPTIMIZE_WH.

Snowflake

CREATE WAREHOUSE OPTIMIZE_WH WITH WAREHOUSE_SIZE = 'SMALL';
ALTER WAREHOUSE OPTIMIZE_WH SET MAX_CLUSTER_COUNT = 1;
-- Your code here to select all from SALES_DATA using OPTIMIZE_WH

Need a hint?

Use USE WAREHOUSE OPTIMIZE_WH before the query.

4

Configure auto-suspend and auto-resume

Write a Snowflake SQL command to alter the warehouse OPTIMIZE_WH to set AUTO_SUSPEND to 300 seconds and AUTO_RESUME to TRUE.

Snowflake

CREATE WAREHOUSE OPTIMIZE_WH WITH WAREHOUSE_SIZE = 'SMALL';
ALTER WAREHOUSE OPTIMIZE_WH SET MAX_CLUSTER_COUNT = 1;
USE WAREHOUSE OPTIMIZE_WH;
SELECT * FROM SALES_DATA;
-- Your code here to set AUTO_SUSPEND to 300 and AUTO_RESUME to TRUE

Need a hint?

Use ALTER WAREHOUSE to set AUTO_SUSPEND = 300 and AUTO_RESUME = TRUE.