Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Optimize Storage Cost in Snowflake Efficiently

To optimize storage cost in Snowflake, use data retention policies to limit time travel and fail-safe periods, apply clustering keys to improve data pruning, and regularly archive or delete unused data. Also, leverage Snowflake's automatic data compression and consider using external stages for infrequently accessed data.
📐

Syntax

Here are key Snowflake commands and settings to control storage costs:

  • ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS: Sets how long deleted or changed data is kept for time travel.
  • CREATE TABLE ... CLUSTER BY: Defines clustering keys to improve query pruning and reduce storage scanning.
  • DROP TABLE: Deletes tables to free storage.
  • CREATE STAGE: Defines external storage locations for cheaper data archiving.
sql
ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 1;

CREATE TABLE my_table (
  id INT,
  data STRING
) CLUSTER BY (id);

DROP TABLE old_table;

CREATE STAGE my_stage URL='s3://mybucket/archive/';
💻

Example

This example shows how to reduce storage cost by lowering data retention, clustering a table, and archiving data externally.

sql
ALTER TABLE sales_data SET DATA_RETENTION_TIME_IN_DAYS = 1;

CREATE TABLE sales_data (
  sale_id INT,
  sale_date DATE,
  amount FLOAT
) CLUSTER BY (sale_date);

CREATE STAGE archive_stage URL='s3://company-archive/sales/';

COPY INTO @archive_stage FROM sales_data WHERE sale_date < DATEADD(year, -1, CURRENT_DATE());

DELETE FROM sales_data WHERE sale_date < DATEADD(year, -1, CURRENT_DATE());
Output
Statement executed successfully. Statement executed successfully. Statement executed successfully. Statement executed successfully. Statement executed successfully.
⚠️

Common Pitfalls

Common mistakes when optimizing storage cost in Snowflake include:

  • Setting DATA_RETENTION_TIME_IN_DAYS too high, which keeps unnecessary historical data and increases cost.
  • Not using clustering keys on large tables, causing inefficient queries and higher storage scanning.
  • Failing to archive or delete old data, leading to storage bloat.
  • Ignoring Snowflake's automatic compression, trying manual compression which is unnecessary.
sql
/* Wrong: High retention time causes extra storage cost */
ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 90;

/* Right: Lower retention time to save cost */
ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 1;
📊

Quick Reference

Summary tips to optimize Snowflake storage cost:

  • Set DATA_RETENTION_TIME_IN_DAYS as low as business needs allow.
  • Use CLUSTER BY on large tables to improve pruning.
  • Archive old data to external stages like S3.
  • Delete unused or obsolete data regularly.
  • Trust Snowflake's automatic compression; no manual compression needed.
✅

Key Takeaways

Lower data retention time to reduce storage of historical data.
Use clustering keys to improve query efficiency and reduce scanned data.
Archive or delete old data to free up storage space.
Leverage Snowflake's automatic compression for storage savings.
Regularly review and clean up unused tables and data.