Multi-cluster warehouses in Snowflake - Mini Project: Build & Apply

Multi-cluster Warehouses in Snowflake

📖 Scenario: You are managing a Snowflake data warehouse for a growing company. The company experiences varying query loads throughout the day. To handle this efficiently, you want to set up a multi-cluster warehouse that can automatically scale out and in based on demand.

🎯 Goal: Build a Snowflake multi-cluster warehouse configuration that starts with one cluster and can scale up to three clusters automatically when needed.

📋 What You'll Learn

Create a warehouse named analytics_wh with size MEDIUM

Enable multi-cluster warehouse (MULTI_CLUSTER = TRUE)

Set the minimum number of clusters to 1

Set the maximum number of clusters to 3

Enable auto-suspend after 10 minutes of inactivity

Enable auto-resume

💡 Why This Matters

🌍 Real World

Multi-cluster warehouses in Snowflake allow companies to handle fluctuating query loads by automatically scaling compute resources, ensuring fast query performance without manual intervention.

💼 Career

Cloud data engineers and analysts often configure multi-cluster warehouses to optimize cost and performance in Snowflake environments.

1

Create the initial warehouse

Create a warehouse named analytics_wh with size MEDIUM.

Snowflake

# Your code here to create the warehouse with name analytics_wh and size MEDIUM

Need a hint?

Use the CREATE WAREHOUSE command with the WAREHOUSE_SIZE parameter.

2

Configure multi-cluster mode and cluster limits

Alter the warehouse analytics_wh to set MULTI_CLUSTER = TRUE, minimum clusters to 1, and maximum clusters to 3.

Snowflake

CREATE WAREHOUSE analytics_wh WITH WAREHOUSE_SIZE = 'MEDIUM';
-- Your code here to enable multi-cluster and cluster limits

Need a hint?

Use ALTER WAREHOUSE to set MULTI_CLUSTER = TRUE, MIN_CLUSTER_COUNT, and MAX_CLUSTER_COUNT.

3

Enable auto-suspend and auto-resume

Alter the warehouse analytics_wh to enable AUTO_SUSPEND after 600 seconds (10 minutes) and enable AUTO_RESUME.

Snowflake

CREATE WAREHOUSE analytics_wh WITH WAREHOUSE_SIZE = 'MEDIUM';
ALTER WAREHOUSE analytics_wh SET MULTI_CLUSTER = TRUE, MIN_CLUSTER_COUNT = 1, MAX_CLUSTER_COUNT = 3;
-- Your code here to enable auto-suspend and auto-resume

Need a hint?

Use ALTER WAREHOUSE to set AUTO_SUSPEND and AUTO_RESUME.

4

Complete the multi-cluster warehouse setup

Verify the full warehouse creation and configuration commands for analytics_wh include size MEDIUM, multi-cluster enabled (TRUE), minimum clusters 1, maximum clusters 3, auto-suspend after 600 seconds, and auto-resume enabled.

Snowflake

CREATE WAREHOUSE analytics_wh WITH WAREHOUSE_SIZE = 'MEDIUM';
ALTER WAREHOUSE analytics_wh SET MULTI_CLUSTER = TRUE, MIN_CLUSTER_COUNT = 1, MAX_CLUSTER_COUNT = 3;
ALTER WAREHOUSE analytics_wh SET AUTO_SUSPEND = 600, AUTO_RESUME = TRUE;
-- Your code here to finalize the warehouse configuration if needed

Need a hint?

Review all previous commands to ensure the warehouse is fully configured.