Time Retention
Snowflake · Tables & Data Handling
Theory
Time Travel lets you query, clone or restore data as it existed at an earlier point within the retention period. You use AT or BEFORE with a TIMESTAMP, an OFFSET in seconds, or a STATEMENT (query ID), for example SELECT * FROM orders AT(OFFSET => -300) for five minutes ago, or BEFORE(STATEMENT => '<query_id>') for just before a bad DELETE. UNDROP TABLE, SCHEMA or DATABASE brings back a dropped object.
Retention is 1 day by default and can be raised to 90 days for permanent objects on Enterprise and above. When Time Travel ends, permanent tables move into a 7-day Fail-safe period; that data cannot be queried and can only be recovered by Snowflake Support.
Why we use it
Mistakes happen: a DELETE without WHERE, a wrong UPDATE, a dropped table. Time Travel lets you fix them yourself in minutes, without restoring backups, and lets you compare data with how it looked before.
When to use it
Use Time Travel for self-service recovery and point-in-time queries within the retention window. Treat Fail-safe only as disaster recovery through Support, not a backup plan. For long-term history, keep your own snapshots or clones.
What Time Travel Lets You Do
Query, clone, or restore a table exactly as it looked at a past moment — by timestamp or by an offset in seconds — invaluable for recovering from an accidental UPDATE or DELETE.
Retention Windows
Defaults to 1 day and can be extended up to 90 days on Enterprise edition; UNDROP instantly restores an accidentally dropped table within that window.
Fail-safe as a Last Resort
Once Time Travel expires on a Permanent table, Fail-safe gives a further 7-day, Snowflake-support-only recovery window — not self-service, but a genuine last line of defense.
The Data Lifecycle
┌─────────────┐ │ Active Data │ ← you query this ├─────────────┤ │ Time Travel │ ← recoverable (1-90 days) │ (Queryable) │ SELECT * FROM t AT(OFFSET => -3600) ├─────────────┤ │ Fail-Safe │ ← Snowflake Support only (7 days) │(Not visible)│ ├─────────────┤ │ Purged │ ← gone forever └─────────────┘
How it works
- Bad change or dropped table discovered
- Is it still within the retention period? Yes: Use AT/BEFORE to query the old data. No: Only Fail-safe via Snowflake Support may help.
- Was the whole table dropped? Yes: Run UNDROP TABLE. No: Insert or clone the old rows back.
- Verify the restored data
- Review retention settings for next time
Example
-- Simulate an accidental delete, then recover:
DELETE FROM orders WHERE region = 'NORTH';
-- Query data as it looked 5 minutes ago
SELECT * FROM orders AT(OFFSET => -300);
-- Restore just the deleted rows
INSERT INTO orders
SELECT * FROM orders AT(OFFSET => -300) WHERE region = 'NORTH';
-- If the whole table was dropped:
DROP TABLE orders;
UNDROP TABLE orders;Key points
- Default retention is 1 day, up to 90 days on Enterprise and above
- AT includes the given point in time; BEFORE stops just before it
- UNDROP restores dropped tables, schemas and databases
- Fail-safe adds 7 days after Time Travel, recoverable only by Snowflake Support
- Transient and temporary tables have no Fail-safe