Cost optimization strategies in Snowflake - Commands & Configuration

Introduction

Cloud data warehouses like Snowflake charge based on compute and storage usage. Cost optimization strategies help you reduce your bills by using resources efficiently without losing performance.

When you want to reduce your monthly Snowflake bill without affecting data availability.

When you notice your queries are running longer than expected and costing more.

When you want to pause compute resources during idle times to save money.

When you want to automatically scale resources up or down based on workload.

When you want to monitor and control your Snowflake usage to avoid surprises.

Commands

This command changes the size of the warehouse to XSMALL, which uses fewer compute resources and costs less.

Terminal

ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'XSMALL';

Expected Output

Statement executed successfully.

This sets the warehouse to automatically suspend after 5 minutes (300 seconds) of inactivity, saving costs when not in use.

Terminal

ALTER WAREHOUSE my_warehouse SET AUTO_SUSPEND = 300;

Expected Output

Statement executed successfully.

This enables the warehouse to automatically resume when a query is submitted, so you don't have to manually start it.

Terminal

ALTER WAREHOUSE my_warehouse SET AUTO_RESUME = TRUE;

Expected Output

Statement executed successfully.

This command checks the current configuration of the warehouse to verify your cost optimization settings.

Terminal

SELECT WAREHOUSE_NAME, WAREHOUSE_SIZE, STATE, AUTO_SUSPEND, AUTO_RESUME FROM INFORMATION_SCHEMA.WAREHOUSES WHERE WAREHOUSE_NAME = 'MY_WAREHOUSE';

Expected Output

WAREHOUSE_NAME | WAREHOUSE_SIZE | STATE     | AUTO_SUSPEND | AUTO_RESUME

MY_WAREHOUSE  | XSMALL         | SUSPENDED | 300          | TRUE

Key Concept

If you remember nothing else from this pattern, remember: automatically suspending and resuming warehouses and choosing the right size saves money without losing performance.

Common Mistakes

Not setting AUTO_SUSPEND, so warehouses keep running and costing money even when idle.

Warehouses consume compute credits while running, even if no queries are executed.

Always set AUTO_SUSPEND to a low value like 300 seconds to pause warehouses when idle.

Choosing a warehouse size that is too large for the workload.

Larger warehouses cost more credits per hour, increasing your bill unnecessarily.

Start with a smaller warehouse size and scale up only if performance is insufficient.

Disabling AUTO_RESUME and manually starting warehouses.

This causes delays and may lead to warehouses running longer than needed.

Enable AUTO_RESUME so warehouses start automatically when queries run.

Summary

Use ALTER WAREHOUSE commands to set warehouse size, auto suspend, and auto resume.

Auto suspend pauses warehouses after inactivity to save compute costs.

Auto resume starts warehouses automatically when queries run, avoiding manual steps.