Snowflake Time Travel vs. Fail-safe: What Gets Recovered and When

3:14 a.m., and a migration script hands off to DROP TABLE orders_staging; against what everyone on the team swore was a permanent table. It wasn’t.

3:14 a.m., and a migration script hands off to DROP TABLE orders_staging; against what everyone on the team swore was a permanent table. It wasn’t. Somewhere in the last quarter it got recreated as TRANSIENT to shave storage costs, and nobody updated the runbook. The on-call engineer isn’t worried — Snowflake has Time Travel, Snowflake has Fail-safe, this is a solved problem. Except Fail-safe doesn’t apply to transient tables. Zero days. The table is gone the moment its one-day Time Travel window closes, and by the time anyone notices, it already has. Six hours of ingestion, rebuilt by hand from source, on a Saturday.

That’s the gap this article is about. Time Travel and Fail-safe get talked about together so often that people assume they’re one continuous safety net. They’re not the same feature, they don’t behave the same way, and the difference has real financial and recovery-time consequences that most teams only discover during an incident.

TL;DR

→ Time Travel lets you query, clone, or UNDROP historical data yourself for a retention window you configure — 0 to 1 day on Standard Edition, up to 90 days on Enterprise Edition and above, for permanent objects.
→ Fail-safe is a separate, fixed 7-day recovery period that starts after Time Travel expires, and it is not self-service — only Snowflake Support can pull data back from it, and only for permanent tables.
→ Transient and temporary tables carry zero Fail-safe days. They’re cheaper precisely because Snowflake gives up that protection.
→ Both features bill as storage: Time Travel data accrues at the normal storage rate, and a table with heavy daily updates on a long retention window can multiply its effective storage several times over.
→ Time Travel doesn’t create a second copy of your table — it retains the old micro-partitions that a write would otherwise discard, via Snowflake’s copy-on-write architecture.
→ UNDROP TABLE, AT, and BEFORE only work inside the Time Travel window. Once an object crosses into Fail-safe, none of those commands work anymore.

What Time Travel Actually Stores

Snowflake tables are stored as immutable micro-partitions — compressed, columnar chunks of roughly 50–500 MB of uncompressed data each. When you run an UPDATE or DELETE, Snowflake doesn’t rewrite rows in place. It writes new micro-partitions reflecting the change and stops referencing the old ones from the table’s current state. That’s the whole trick: Time Travel is Snowflake choosing not to immediately throw those old partitions away.

Snowflake doesn’t back up a table for Time Travel — it just delays discarding the partitions a write would otherwise drop.

Every table, schema, and database has a DATA_RETENTION_TIME_IN_DAYS parameter that controls how long those superseded partitions stick around before they’re eligible for permanent deletion. Retention is inherited: set it on a database and every schema and table created under it picks up the value unless overridden lower down. There’s also an account-level MIN_DATA_RETENTION_TIME_IN_DAYS floor — if it’s set, the effective retention for any object becomes whichever is larger, its own setting or the floor. That parameter is easy to forget exists and even easier to be surprised by months later.

Standard Edition accounts get 1 day of retention by default, and you can only turn it down to 0 — there’s no way to go longer without upgrading to Enterprise Edition. Enterprise and above allow up to 90 days for permanent databases, schemas, and tables, configurable per object.

Retention by Table Type — the Comparison Nobody Reads Until It’s Too Late

The gotcha in the opening story lives entirely in this table. Table type determines the retention ceiling independent of edition, and it determines whether Fail-safe exists at all.

Table type	Time Travel range	Fail-safe period	Notes
Permanent	0–1 day (Standard) · 0–90 days (Enterprise+)	7 days, fixed	The only table type with Fail-safe protection
Transient	0–1 day, on any edition	0 days	Capped at 1 day even on Enterprise — cannot be extended
Temporary	0–1 day, session-scoped	0 days	Dropped automatically when the session ends

Notice that transient tables don’t just lose Fail-safe — their Time Travel ceiling is capped at one day regardless of what edition you’re on or what the account default says. That’s the entire reason transient tables cost less to store: Snowflake is retaining less history for them, full stop.

Querying and Restoring Inside the Window

Time Travel is queryable directly in SQL, three ways: by timestamp, by relative offset, or by the query ID of the statement that changed the data.

-- Query a table as it existed at a specific timestamp
SELECT * FROM orders
AT (TIMESTAMP => '2026-07-28 09:00:00'::timestamp);

-- Query as it existed immediately before a specific statement ran
SELECT * FROM orders
BEFORE (STATEMENT => '8e5d0c1d-0073-4f57-8263-6e6bb1a2b1d4');

-- Restore an accidentally dropped table, in place
UNDROP TABLE orders_staging;

-- Clone a table's state from 6 hours ago into a new object,
-- useful for diffing without touching the live table
CREATE TABLE orders_audit_clone
CLONE orders AT (OFFSET => -60*60*6);

All four of those commands only work while the object — or the specific rows you’re targeting — is still inside its Time Travel window. Past that point, UNDROP returns an object-not-found error, not a graceful fallback into Fail-safe. This trips people up constantly: Fail-safe existing doesn’t mean these commands quietly keep working against it. They don’t.

Fail-safe: What It’s For, and What It Isn’t

Fail-safe is the part of this system most engineers get wrong, because the name suggests self-service safety and it’s the opposite. It’s a fixed, non-configurable 7-day period that begins the moment an object’s Time Travel retention expires, and it exists for Snowflake’s disaster-recovery purposes — not for routine “oops I dropped a table” moments. You cannot query it, clone from it, or run UNDROP against it. The only path back is a support ticket, and Snowflake is explicit that recovery through Fail-safe can take anywhere from hours to several days, positioning it as a last resort rather than a recovery SLA you can plan around.

Only permanent tables get Fail-safe. It’s a fixed 7-day buffer after Time Travel expires, and it’s not something you access yourself.

And critically, Fail-safe only exists for permanent tables. Look back at the comparison table above — transient and temporary objects get 0 days of it. If your team leans on transient tables for staging (a completely reasonable cost optimization, discussed in our zero-copy cloning guide), you’ve implicitly decided that a mistake on those tables gets exactly one day — the Time Travel window — before it’s unrecoverable at any price, support ticket included.

The Cost Math

Time Travel and Fail-safe both bill as storage, at your account’s normal per-terabyte rate. As of 2026, Snowflake’s published on-demand list price is around $23 per compressed terabyte per month for AWS US East, with regional variation — worth flagging because a lot of older guides still cite a $40/TB figure that Snowflake has since moved off of.

The part that surprises people isn’t the rate, it’s the multiplier. Time Travel storage isn’t billed once — it’s billed for the entire retention window, for every version of every changed row, not just until the next write overwrites it.

Illustrative numbers, not a live account screenshot — but the shape of the math holds: retention window × daily churn rate is the real cost driver, not table size alone.

A 100 GB table with 10% of its rows modified daily, sitting on a 90-day retention setting, accrues on the order of 10 GB of Time Travel history per day. Over the full window that’s roughly 900 GB — close to a 9x storage multiplier over the table’s own size, from one retention setting on one heavily-churned table. Multiply that across every staging and fact table on a 90-day account default and it stops being a rounding error.

This is also where the MIN_DATA_RETENTION_TIME_IN_DAYS parameter bites teams that think they’ve already optimized. Someone sets an individual table’s retention to 1 day to cut costs, but the account-level minimum is still 30 — the effective retention is the larger of the two, and the storage bill doesn’t move.

The Gotchas Nobody Warns You About

Transient tables have zero Fail-safe, by design, not by oversight. That’s the trade you’re making every time you choose transient for cost savings. It’s a good trade for genuinely disposable staging data. It’s a bad surprise for anything that turns out to matter more than you thought.

Dropping and recreating a schema resets what its children inherit. If you drop a schema and recreate it with a different DATA_RETENTION_TIME_IN_DAYS, tables created afterward inherit the new value — but objects dropped under the old setting keep whatever retention was active at the time they were dropped, not the new one. It’s easy to assume a schema-level change is retroactive. It isn’t.

The account-level minimum silently overrides a lower table-level setting. As covered above — if you’re trying to cut Time Travel storage costs by lowering retention on specific tables and the number on your bill doesn’t move, check MIN_DATA_RETENTION_TIME_IN_DAYS before assuming the change didn’t take.

Cloning at a past timestamp quietly locks in stale data. CREATE TABLE ... CLONE x AT (...) is a zero-copy operation that materializes as a real object pointing at that historical state. It’s easy to leave one of these lying around after an investigation and forget it’s not tracking the live table anymore.

Fail-safe recovery is not a routine operation, and Snowflake treats it that way. There’s no dashboard, no self-service button, and no fixed turnaround time — it’s a support ticket that gets prioritized as the disaster-recovery mechanism it was designed to be, not an extension of your undo history.

The One Principle

Time Travel is a tool you use; Fail-safe is a safety net Snowflake uses on your behalf — design your retention and table types as if Fail-safe doesn’t exist, because for anything outside a permanent table, it doesn’t.