Warehouse selection strategies in Snowflake - Mini Project: Build & Apply

Warehouse selection strategies

📖 Scenario: You are managing a Snowflake cloud data platform. You want to create a simple strategy to select the best warehouse for running queries based on their size and availability.

🎯 Goal: Build a Snowflake SQL script that defines warehouses with their sizes and statuses, sets a minimum size threshold, selects warehouses that meet the threshold and are available, and finally chooses the warehouse with the smallest size that meets these conditions.

📋 What You'll Learn

Create a table variable called warehouses with columns name, size, and status and insert exact rows

Create a variable called min_size with the value 'MEDIUM'

Write a query that selects warehouses with size greater than or equal to min_size and status equal to 'AVAILABLE'

Select the warehouse with the smallest size from the filtered results

💡 Why This Matters

🌍 Real World

Selecting the right warehouse in Snowflake helps optimize query performance and cost by choosing the best available compute resource.

💼 Career

Cloud engineers and data platform administrators often need to automate warehouse selection to improve efficiency and resource management.

1

Create the warehouses table variable

Create a table variable called warehouses with columns name (string), size (string), and status (string). Insert these exact rows: ('WH_SMALL', 'SMALL', 'AVAILABLE'), ('WH_MEDIUM', 'MEDIUM', 'AVAILABLE'), ('WH_LARGE', 'LARGE', 'BUSY'), ('WH_XLARGE', 'XLARGE', 'AVAILABLE').

Snowflake

-- Your code here

Need a hint?

Use DECLARE to create a table variable and INSERT INTO to add rows.

2

Set the minimum warehouse size

Create a variable called min_size and set it to the string value 'MEDIUM'.

Snowflake

DECLARE warehouses TABLE (name STRING, size STRING, status STRING);

INSERT INTO warehouses VALUES
('WH_SMALL', 'SMALL', 'AVAILABLE'),
('WH_MEDIUM', 'MEDIUM', 'AVAILABLE'),
('WH_LARGE', 'LARGE', 'BUSY'),
('WH_XLARGE', 'XLARGE', 'AVAILABLE');

-- Your code here

Need a hint?

Use DECLARE with DEFAULT to set the variable.

3

Filter warehouses by size and availability

Write a SELECT query that retrieves all columns from warehouses where size is greater than or equal to min_size and status is exactly 'AVAILABLE'.

Snowflake

DECLARE warehouses TABLE (name STRING, size STRING, status STRING);

INSERT INTO warehouses VALUES
('WH_SMALL', 'SMALL', 'AVAILABLE'),
('WH_MEDIUM', 'MEDIUM', 'AVAILABLE'),
('WH_LARGE', 'LARGE', 'BUSY'),
('WH_XLARGE', 'XLARGE', 'AVAILABLE');

DECLARE min_size STRING DEFAULT 'MEDIUM';

-- Your code here

Need a hint?

Use a WHERE clause with both conditions joined by AND.

4

Select the smallest suitable warehouse

Modify the previous SELECT query to order the results by size ascending and limit the output to 1 row to select the smallest warehouse that meets the criteria.

Snowflake

DECLARE warehouses TABLE (name STRING, size STRING, status STRING);

INSERT INTO warehouses VALUES
('WH_SMALL', 'SMALL', 'AVAILABLE'),
('WH_MEDIUM', 'MEDIUM', 'AVAILABLE'),
('WH_LARGE', 'LARGE', 'BUSY'),
('WH_XLARGE', 'XLARGE', 'AVAILABLE');

DECLARE min_size STRING DEFAULT 'MEDIUM';

SELECT * FROM warehouses
WHERE size >= min_size
AND status = 'AVAILABLE'
-- Your code here

Need a hint?

Use ORDER BY and LIMIT to get the smallest warehouse.