Cost optimization strategies in Snowflake - Mini Project: Build & Apply

Cost Optimization Strategies in Snowflake

📖 Scenario: You are a cloud data engineer working with Snowflake. Your company wants to reduce costs while keeping data accessible and queries fast. You will create a simple Snowflake setup that shows how to optimize costs by managing warehouses and data retention.

🎯 Goal: Build a Snowflake configuration that creates a warehouse, sets an auto-suspend time to save costs, and configures data retention time for zero-copy cloning to optimize storage costs.

📋 What You'll Learn

Create a warehouse named cost_opt_warehouse with size SMALL

Set the warehouse to auto-suspend after 300 seconds of inactivity

Set the data retention time for the database cost_opt_db to 1 day

Use zero-copy cloning to create a clone of a table named sales_data in cost_opt_db

💡 Why This Matters

🌍 Real World

Companies use these strategies to reduce cloud data warehouse costs while maintaining performance and data availability.

💼 Career

Cloud data engineers and architects must optimize Snowflake resources to control budgets and improve efficiency.

1

Create the warehouse

Create a warehouse named cost_opt_warehouse with size SMALL.

Snowflake

# Your code here to create warehouse cost_opt_warehouse with size SMALL

Need a hint?

Use the CREATE WAREHOUSE command with WAREHOUSE_SIZE option.

2

Set auto-suspend time

Alter the warehouse cost_opt_warehouse to auto-suspend after 300 seconds of inactivity.

Snowflake

CREATE WAREHOUSE cost_opt_warehouse WITH WAREHOUSE_SIZE = 'SMALL';
# Your code here to set auto_suspend to 300 seconds

Need a hint?

Use ALTER WAREHOUSE with SET AUTO_SUSPEND = 300.

3

Set data retention time

Create a database named cost_opt_db and set its data retention time to 1 day.

Snowflake

CREATE WAREHOUSE cost_opt_warehouse WITH WAREHOUSE_SIZE = 'SMALL';
ALTER WAREHOUSE cost_opt_warehouse SET AUTO_SUSPEND = 300;
# Your code here to create database cost_opt_db and set data retention time to 1 day

Need a hint?

Use CREATE DATABASE with DATA_RETENTION_TIME_IN_DAYS = 1.

4

Create a zero-copy clone

Create a zero-copy clone of the table sales_data from cost_opt_db named sales_data_clone in the same database.

Snowflake

CREATE WAREHOUSE cost_opt_warehouse WITH WAREHOUSE_SIZE = 'SMALL';
ALTER WAREHOUSE cost_opt_warehouse SET AUTO_SUSPEND = 300;
CREATE DATABASE cost_opt_db DATA_RETENTION_TIME_IN_DAYS = 1;
# Your code here to create zero-copy clone sales_data_clone from sales_data

Need a hint?

Use CREATE TABLE ... CLONE ... syntax for zero-copy cloning.