Snowflake makes object management simple, but if you leave everything as the default table (permanent tables), storage costs rise up fast, mainly because of Fail-safe, a 7-day retention Snowflake uses for disaster recovery. Fail safe is useful but unnecessary for short-lived data. permanent tables Fail-safe That’s where transient tables help. They persist like permanent tables but skip Fail-safe, meaning lower storage costs. transient tables lower storage costs Before: Permanent Tables Everywhere -- Staging raw events as permanent table
create or replace table raw_events as
select * from @stage/events;
-- transformation
create or replace table daily_session as
select user_id, daily_session_date, count(*) as session_view
from raw_events
group by 1,2; -- Staging raw events as permanent table
create or replace table raw_events as
select * from @stage/events;
-- transformation
create or replace table daily_session as
select user_id, daily_session_date, count(*) as session_view
from raw_events
group by 1,2; These tables trigger Fail-safe charges, even if they’re dropped the next day. Now: Transient Tables Switching to transient table: -- Staging table without Fail-safe
create or replace transient table raw_events as
select * from @stage/events;
-- transformation table
create or replace transient table daily_session as
select user_id, daily_session_date, count(*) as session_view
from raw_events
group by 1,2; -- Staging table without Fail-safe
create or replace transient table raw_events as
select * from @stage/events;
-- transformation table
create or replace transient table daily_session as
select user_id, daily_session_date, count(*) as session_view
from raw_events
group by 1,2; Where Transient Tables Shine ETL staging: data ingested and overwritten daily.Intermediate results: joins, aggregations, rollups before loading into production.Experiments: model features, test datasets, temporary snapshots. ETL staging: data ingested and overwritten daily. ETL staging Intermediate results: joins, aggregations, rollups before loading into production. Intermediate results Experiments: model features, test datasets, temporary snapshots. Experiments Key Difference: Transient tables still support Time Travel (if enabled).But they skip Fail-safe, so once dropped, they’re gone — no 7-day hidden storage bill. Transient tables still support Time Travel (if enabled). still support Time Travel But they skip Fail-safe, so once dropped, they’re gone — no 7-day hidden storage bill. skip Fail-safe Example: one team switched their ETL staging schema to transient and cut Fail-safe storage costs by over 25% monthly, without touching compute. % Conclusion Transient tables are one of the simplest ways to align Snowflake storage costs with actual business needs. By skipping Fail-safe, they remove 7-day retention charges on data you don’t intend to keep, while still giving you persistence across sessions and optional Time Travel. Fail-safe If you’re looking to optimize your Snowflake bill without restructuring pipelines, start by auditing where permanent tables are used today. References References Working with Transient and Temporary TablesData Retention Periods and Fail-safe Working with Transient and Temporary Tables Working with Transient and Temporary Tables Data Retention Periods and Fail-safe Data Retention Periods and Fail-safe

This story contains new, firsthand information uncovered by the writer.

The Safe Way to Create Snowflake System Users for Integrations

The Easiest Snowflake Cost Win: Switch ETL Staging to Transient

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Inline Governance in Snowflake: Tags, Masking Policies, and Roles That Scale

6 Tips on How to Choose a Proper VM and Save on Your Cloud Costs

Google Anthos Vs. Cast.AI: A Review

Modern Approaches to Complex IT Development: Striking a Balance with Limited Resources

Optimizing Airflow: A Case Study in Cloud Resource Efficiency

Optimizing Cloud Investments with AWS Cost Optimization

Inline Governance in Snowflake: Tags, Masking Policies, and Roles That Scale

6 Tips on How to Choose a Proper VM and Save on Your Cloud Costs

Google Anthos Vs. Cast.AI: A Review

Modern Approaches to Complex IT Development: Striking a Balance with Limited Resources

Optimizing Airflow: A Case Study in Cloud Resource Efficiency

Optimizing Cloud Investments with AWS Cost Optimization

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps