Home / Data Platform / Warehouses

Data PlatformHow-toIntermediate

Optimization: Snowflake storage cost - Time Travel, Fail-safe, transient tables, clones and clustering

Snowflake storage includes much more than active data. Time Travel, Fail-safe, data retained for clones and churn from frequent rewrites can multiply the bill. Measure each component per table, set retention per data class, use transient tables for rebuildable data, review automatic clustering spend and clean up stages.

Deepak Bhardwaj3 min read

Applies toSnowflake tables (permanent, transient, temporary), Time Travel, Fail-safe, zero-copy clones, automatic clustering, internal stages

Storage is four things

Component What it is
Active bytes Current table data
Time Travel bytes Previous versions within DATA_RETENTION_TIME_IN_DAYS
Fail-safe bytes 7 days after Time Travel for permanent tables (Snowflake-managed recovery)
Retained for clone Data still referenced by clones after the source changed

High-churn tables (full reloads, frequent updates/deletes) can have Time Travel + Fail-safe several times larger than active data.

1. Measure per table

SELECT table_catalog, table_schema, table_name,
       ROUND(active_bytes / POWER(1024, 3), 1)            AS active_gb,
       ROUND(time_travel_bytes / POWER(1024, 3), 1)       AS time_travel_gb,
       ROUND(failsafe_bytes / POWER(1024, 3), 1)          AS failsafe_gb,
       ROUND(retained_for_clone_bytes / POWER(1024, 3), 1) AS clone_gb
FROM snowflake.account_usage.table_storage_metrics
WHERE deleted = FALSE
ORDER BY (time_travel_bytes + failsafe_bytes) DESC
LIMIT 30;

2. Retention per data class

Data Time Travel
Critical business tables Enough to recover from mistakes (e.g. 7–30 days; max depends on edition)
Staging / intermediate 0–1 day
Rebuildable marts 1 day 
ALTER TABLE staging.orders_raw SET DATA_RETENTION_TIME_IN_DAYS = 1;
ALTER SCHEMA staging SET DATA_RETENTION_TIME_IN_DAYS = 1;   -- default for new tables in the schema

Lowering retention removes Time Travel data beyond the new window, and it then moves into Fail-safe.

3. Transient tables for rebuildable data

Transient tables have no Fail-safe and at most 1 day of Time Travel. Use them for staging, intermediate dbt models and anything you can rebuild from source:

CREATE TRANSIENT SCHEMA staging_t DATA_RETENTION_TIME_IN_DAYS = 0;

In dbt: +transient: true (default for some adapters' table materialisations; check your config).

4. Reduce churn

Pattern Better
Full CREATE OR REPLACE of large tables daily Incremental models / MERGE of changed rows
Updating many rows to change one column Design to append or update only changed rows
Frequent small DML on large tables Batch changes

Each rewrite of a micro-partition leaves the old version in Time Travel and Fail-safe.

5. Clones

Zero-copy clones are cheap until the source changes; then the clone keeps old micro-partitions alive. Drop dev/test clones when done, and refresh them instead of keeping many old ones.

SHOW TABLES LIKE '%_CLONE%' IN ACCOUNT;

6. Automatic clustering spend

SELECT table_name, ROUND(SUM(credits_used), 1) AS credits
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time > DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 2 DESC;

Clustering keys on high-churn tables can cost more than the query savings they bring. Keep keys only where pruning measurably improves important queries; suspend clustering otherwise (ALTER TABLE ... SUSPEND RECLUSTER).

7. Stages and other storage

  • Internal stages with old load files: LIST @stage and REMOVE after loading (or PURGE = TRUE in COPY).
  • Old unused tables and schemas: check ACCESS_HISTORY / last query dates before dropping.
  • Search optimization and materialized views add storage and maintenance credits. Review their value.

Caution

Reducing Time Travel shortens your undo window for mistakes (see the dropped/overwritten table Sev1 post). Agree retention per data class with data owners before changing critical tables.

Checklist

  • Storage split per table (active, TT, Fail-safe, clone)
  • Retention set per data class
  • Transient tables/schemas for rebuildable data
  • Churn-heavy loads made incremental
  • Old clones dropped
  • Clustering spend reviewed
  • Stages and unused tables cleaned

Related fixes