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.
That’s where transient tables help. They persist like permanent tables but skip Fail-safe, meaning 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;
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;
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.
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.
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.
If you’re looking to optimize your Snowflake bill without restructuring pipelines, start by auditing where permanent tables are used today.
References