Why optimization controls Snowflake costs - Why It Works

Introduction

Snowflake charges based on how much computing power and storage you use. Optimizing your queries and data helps reduce these costs by using resources more efficiently.

When you want to lower your monthly Snowflake bill by using fewer compute credits

When your queries take too long and use a lot of resources

When you have large data sets but want to avoid unnecessary scanning

When you want to schedule jobs that run faster and cheaper

When you want to avoid paying for idle or wasted compute time

Commands

This command shows how well your table is clustered, which affects query speed and cost by reducing data scanned.

Terminal

SELECT SYSTEM$CLUSTERING_INFORMATION('MY_SCHEMA.MY_TABLE');

Expected Output

CLUSTERING_DEPTH  CLUSTERING_RATIO

5                 0.75

This command changes the warehouse size to a smaller one to save costs when less compute power is needed.

Terminal

ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE = 'XSMALL';

Expected Output

Statement executed successfully.

This command checks recent queries on your table to see how much time and credits they used, helping identify costly queries.

Terminal

SELECT QUERY_ID, EXECUTION_STATUS, TOTAL_ELAPSED_TIME, CREDITS_USED FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE QUERY_TEXT LIKE '%MY_TABLE%' ORDER BY START_TIME DESC LIMIT 5;

Expected Output

QUERY_ID          EXECUTION_STATUS  TOTAL_ELAPSED_TIME  CREDITS_USED

01a2b3c4d5e6f7g8  SUCCESS           1200                0.5

09h8g7f6e5d4c3b2  SUCCESS           800                 0.3

...

This command reorganizes the table data to improve clustering, which can reduce the amount of data scanned and lower costs.

Terminal

ALTER TABLE MY_SCHEMA.MY_TABLE RECLUSTER;

Expected Output

Statement executed successfully.

Key Concept

If you remember nothing else, remember: optimizing data layout and query efficiency directly reduces Snowflake compute costs.

Common Mistakes

Ignoring table clustering and running full scans on large tables

This causes queries to scan more data than needed, increasing compute time and costs.

Use clustering keys and monitor clustering info to keep data organized for efficient scanning.

Using a large warehouse size all the time regardless of workload

This wastes compute credits when smaller warehouses could handle the load.

Adjust warehouse size based on workload demand to balance speed and cost.

Not reviewing query history to find expensive queries

Without this, costly queries go unnoticed and keep increasing your bill.

Regularly check query history to identify and optimize expensive queries.

Summary

Use SYSTEM$CLUSTERING_INFORMATION to check how well your tables are organized.

Adjust warehouse size with ALTER WAREHOUSE to save compute costs.

Review query history to find and optimize expensive queries.

Recluster tables to improve data layout and reduce scanned data.