Warehouse sizes and scaling in Snowflake - Mini Project: Build & Apply

Warehouse sizes and scaling

📖 Scenario: You are managing a Snowflake data warehouse for a small company. You need to create a warehouse with a specific size, then configure scaling options to handle varying workloads efficiently.

🎯 Goal: Build a Snowflake warehouse with a defined size and enable auto-scaling with a maximum cluster count.

📋 What You'll Learn

Create a warehouse named MY_WAREHOUSE with size SMALL

Set the warehouse to use AUTO_SUSPEND after 300 seconds

Enable AUTO_RESUME for the warehouse

Configure AUTO_SCALE with a maximum cluster count of 3

💡 Why This Matters

🌍 Real World

In real companies, managing warehouse size and scaling helps balance cost and performance for data processing workloads.

💼 Career

Cloud engineers and data analysts often configure Snowflake warehouses to optimize resource usage and handle varying query loads efficiently.

1

Create a warehouse with size SMALL

Write a Snowflake SQL statement to create a warehouse named MY_WAREHOUSE with size SMALL.

Snowflake

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

Need a hint?

Use CREATE WAREHOUSE followed by the warehouse name and WAREHOUSE_SIZE option.

2

Set auto suspend and auto resume

Write a Snowflake SQL statement to alter the warehouse MY_WAREHOUSE to set AUTO_SUSPEND to 300 seconds and enable AUTO_RESUME.

Snowflake

CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = 'SMALL';
-- Your code here to set AUTO_SUSPEND = 300 and AUTO_RESUME = TRUE

Need a hint?

Use ALTER WAREHOUSE to change settings on an existing warehouse.

3

Enable auto scaling with max cluster count

Write a Snowflake SQL statement to alter the warehouse MY_WAREHOUSE to enable multi-cluster auto-scaling with a maximum cluster count of 3.

Snowflake

CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = 'SMALL';
ALTER WAREHOUSE MY_WAREHOUSE SET AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;
-- Your code here to set MIN_CLUSTER_COUNT = 1 and MAX_CLUSTER_COUNT = 3

Need a hint?

Use MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT to configure auto-scaling clusters.

4

Set auto scaling policy to auto

Write a Snowflake SQL statement to alter the warehouse MY_WAREHOUSE to set the auto scaling policy to AUTO.

Snowflake

CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = 'SMALL';
ALTER WAREHOUSE MY_WAREHOUSE SET AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;
ALTER WAREHOUSE MY_WAREHOUSE SET MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 3;
-- Your code here to set AUTO_SCALE_MODE = 'AUTO'

Need a hint?

Use AUTO_SCALE_MODE = 'AUTO' to enable automatic scaling.