Time Travel and Fail-Safe

Analogy categoryAnalogies

What Is Time Travel in Snowflake?

Time Travel is Snowflake's mechanism for accessing historical versions of data at any point within a defined retention window. Because Snowflake never overwrites micro-partitions in place, every DML operation (insert, update, delete, merge) simply creates new immutable file versions while the old ones are retained, which is what makes querying, cloning, or restoring a prior state possible without a separate backup system.

🏏

Cricket analogy: It's like the DRS system replaying ball-tracking data from seconds earlier to re-examine whether Virat Kohli was genuinely out leg-before-wicket, pulling up a version of the moment that still technically exists.

Querying Historical Data with AT and BEFORE

You access historical data with the AT and BEFORE clauses, specifying a TIMESTAMP, an OFFSET in seconds relative to the present, or a STATEMENT ID. AT includes the specified point, BEFORE excludes it and returns the state immediately prior. These clauses work not only in SELECT statements but also with CREATE TABLE ... CLONE and UNDROP, letting you restore an entire dropped table, schema, or database in a single statement rather than restoring from external backups.

🏏

Cricket analogy: It's the difference between requesting the scorecard exactly at the moment of the declaration (AT) versus the state of play just before Ben Stokes declared the innings (BEFORE).

sql

-- Query a table as it existed 30 minutes ago
SELECT * FROM orders
  AT(OFFSET => -60*30);

-- Query as of a specific timestamp
SELECT * FROM orders
  BEFORE(TIMESTAMP => '2026-07-09 14:00:00'::timestamp);

-- Restore a table dropped by mistake
UNDROP TABLE orders;

-- Clone a table using its state before a bad UPDATE ran
CREATE TABLE orders_recovered CLONE orders
  BEFORE(STATEMENT => '019c1f2e-0000-1a2b-0000-abcdef012345');

Retention Periods and DATA_RETENTION_TIME_IN_DAYS

The DATA_RETENTION_TIME_IN_DAYS parameter controls how long Time Travel history is kept, settable at the account, database, schema, or table level, from 0 up to 1 day on Standard Edition and up to 90 days on Enterprise Edition and above. A longer retention period increases storage costs because Snowflake must keep the superseded micro-partitions around, so teams typically set aggressive retention (90 days) only on critical audit or financial tables and shorter windows (1-7 days) on high-churn staging tables.

🏏

Cricket analogy: A stadium's broadcast archive might keep full match footage for 90 days for a World Cup final but only 24 hours for a routine domestic warm-up game.

Time Travel retention can be set per-object with ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = N. Setting it to 0 disables Time Travel for that object entirely, which also removes the ability to UNDROP it — that object goes straight to Fail-Safe (Enterprise+) or is permanently gone (Standard).

Fail-Safe: The Last Resort

Fail-Safe is a non-configurable 7-day period that begins automatically after a table's Time Travel retention period ends, available on permanent tables in Enterprise Edition and above. Unlike Time Travel, customers cannot query or clone data in Fail-Safe themselves; recovery requires contacting Snowflake Support, and it exists purely as a disaster-recovery mechanism for catastrophic failures rather than as a self-service undo tool, which is why well-designed retention policies matter far more in daily operations.

🏏

Cricket analogy: It's like a match-referee's sealed report that only the ICC can unseal in extreme disputes, not something a captain can pull up mid-game like a DRS replay.

Fail-Safe is not a substitute for careful retention planning and is not accessible via SQL at all — there is no AT/BEFORE clause that reaches into it. Treat it strictly as an emergency-only, support-mediated backstop, and rely on properly tuned DATA_RETENTION_TIME_IN_DAYS plus UNDROP for all routine recovery needs.

Time Travel lets you query, clone, or restore historical data using AT, BEFORE, OFFSET, TIMESTAMP, or STATEMENT clauses.
UNDROP restores an entire dropped table, schema, or database within the Time Travel retention window.
DATA_RETENTION_TIME_IN_DAYS controls the window: 0-1 day on Standard Edition, up to 90 days on Enterprise Edition and above.
Setting retention to 0 disables Time Travel for that object and removes UNDROP capability.
Fail-Safe is a fixed 7-day, non-configurable period that follows Time Travel on permanent tables in Enterprise+ editions.
Fail-Safe recovery requires contacting Snowflake Support; it is not self-service and has no SQL access.
Longer retention periods increase storage costs, so retention should be tuned per table based on criticality and churn rate.