Cost optimization strategies in Snowflake - Step-by-Step Execution

Process Flow - Cost optimization strategies

Identify Cost Drivers

↓

Analyze Usage Patterns

↓

Apply Optimization Techniques

↓

Monitor & Adjust

↓

Repeat Cycle for Continuous Savings

This flow shows how to find what costs the most, analyze usage, apply ways to save money, then watch and adjust continuously.

Execution Sample

Snowflake

CREATE WAREHOUSE mywh WITH WAREHOUSE_SIZE = 'SMALL';
ALTER WAREHOUSE mywh SET AUTO_SUSPEND = 60;
ALTER WAREHOUSE mywh SET AUTO_RESUME = TRUE;

This code creates a small compute warehouse and sets it to suspend after 60 seconds of inactivity and resume automatically to save costs.

Process Table

Step	Action	Parameter/Value	Effect on Cost	Result
1	Create warehouse	Size = SMALL	Lower compute cost	Warehouse 'mywh' created with small size
2	Set auto suspend	AUTO_SUSPEND = 60 seconds	Stops warehouse when idle	Warehouse suspends after 60s inactivity
3	Set auto resume	AUTO_RESUME = TRUE	Resumes warehouse on demand	Warehouse resumes automatically on query
4	Query runs	Warehouse resumes	Cost incurred only during usage	Warehouse active during query execution
5	Idle period	60 seconds passed	Warehouse suspends	No cost during idle time
6	Repeat usage	Warehouse resumes again	Cost only when needed	Cost optimized by suspending when idle

💡 Warehouse suspends after 60 seconds idle, minimizing cost when not in use

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 5	Final
Warehouse Size	N/A	SMALL	SMALL	SMALL	SMALL	SMALL
Warehouse State	Not created	Suspended	Suspended	Suspended	Suspended	Suspended
Auto Suspend	N/A	Enabled (60s)	Enabled (60s)	Enabled (60s)	Enabled (60s)	Enabled (60s)
Auto Resume	N/A	Enabled	Enabled	Enabled	Enabled	Enabled

Key Moments - 3 Insights

Why does setting AUTO_SUSPEND help reduce costs?

Because it stops the warehouse when not in use, so you don't pay for idle compute time. See execution_table step 2 and 5.

What happens if AUTO_RESUME is not enabled?

You would have to manually start the warehouse before queries run, which can cause delays and extra manual work. See execution_table step 3.

Does choosing a SMALL warehouse size always save money?

It saves money for light workloads but may slow queries if too small. Balance size with performance needs. See execution_table step 1.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the warehouse suspend after inactivity?

A Step 2

B Step 4

C Step 5

D Step 6

💡 Hint

Check the 'Effect on Cost' column for when the warehouse stops to save cost.

According to variable_tracker, what is the warehouse state after step 5?

A Running

B Suspended

C Not created

D Starting

💡 Hint

Look at the 'Warehouse State' row under 'After Step 5' column.

If AUTO_RESUME was set to FALSE, what would change in the execution flow?

A Warehouse would not resume automatically on query

B Warehouse would suspend automatically

C Warehouse size would increase

D Warehouse would never suspend

💡 Hint

Refer to execution_table step 3 and key_moments about AUTO_RESUME.

Concept Snapshot

Cost Optimization in Snowflake:
- Use SMALL warehouse size for light workloads
- Enable AUTO_SUSPEND to stop compute when idle
- Enable AUTO_RESUME to start compute on demand
- Monitor usage and adjust settings regularly
- Balance cost savings with performance needs

Full Transcript

This lesson shows how to save money using Snowflake by creating a small compute warehouse, setting it to suspend automatically after 60 seconds of inactivity, and enabling it to resume automatically when queries run. The warehouse only runs when needed, reducing idle compute costs. Variables like warehouse size and state change as the warehouse is created, suspended, and resumed. Key points include why auto suspend saves cost, the importance of auto resume, and balancing warehouse size with workload. The visual quiz tests understanding of when suspension happens, warehouse state changes, and effects of disabling auto resume.