Snowflake Time Travel and Fail-Safe
Snowflake builds two safety nets into every table and schema: Time Travel and Fail-Safe. Time Travel lets you query, restore, or clone data as it existed at any point in the past — within a configurable window. Fail-Safe adds an extra layer after Time Travel expires, giving Snowflake's internal team one more chance to recover data in catastrophic scenarios. Together, these two features eliminate the need for traditional database backup strategies for most use cases.
The Accidental Delete Problem: Why This Matters
Every data professional has accidentally deleted something important. An errant DELETE with no WHERE clause. A TRUNCATE on the wrong table. A DROP that removed months of carefully cleaned data. Without Time Travel, recovery from those mistakes requires restoring from a backup — a process that might take hours and might return yesterday's data rather than data from five minutes ago.
TIMELINE OF A DISASTER — AND THE RECOVERY
==========================================
10:00 AM Table CUSTOMERS contains 500,000 rows (all correct)
10:15 AM Engineer runs: DELETE FROM CUSTOMERS; -- forgot the WHERE clause!
Table CUSTOMERS now has 0 rows
10:16 AM Engineer realises the mistake
WITHOUT TIME TRAVEL:
10:16 AM Panic
10:30 AM Find last backup (from 3:00 AM — 7 hours ago)
11:30 AM Restore backup to a separate server
12:00 PM ETL the restored data back to production
12:30 PM 2.5 hours of new data (10:00-12:30) cannot be recovered
Result: Data gap exists; business impact is significant
WITH TIME TRAVEL:
10:16 AM Engineer runs:
UNDROP TABLE CUSTOMERS; -- if table was dropped
-- OR --
CREATE TABLE CUSTOMERS_RESTORED -- if rows were deleted
CLONE CUSTOMERS AT (OFFSET => -900); -- 15 min ago in seconds
10:17 AM Table restored with all 500,000 rows as of 10:00 AM
Result: Only 15 minutes of data gap; recovery took 1 minute
How Time Travel Works Internally
Snowflake never modifies micro-partition files in place. Every DML operation (INSERT, UPDATE, DELETE, TRUNCATE, DROP) creates new micro-partitions rather than overwriting existing ones. Old micro-partitions are marked as expired but not immediately deleted. They remain in storage for the duration of the Time Travel retention period. Querying historical data simply means Snowflake reads the old micro-partitions instead of the current ones.
TIME TRAVEL INTERNAL MECHANISM ================================ MONDAY 10:00 AM: ORDERS table state Micro-partitions: [A, B, C, D, E] (all active, 500K rows) Storage cost: 5 partitions MONDAY 2:00 PM: UPDATE statement modifies 50,000 rows in partition C New partitions created: [C2] (updated rows) Old partition C: marked expired but RETAINED for Time Travel Active partitions: [A, B, C2, D, E] (current state) Expired partitions: [C] (historical, available for Time Travel) Storage cost: 6 partitions (1 extra retained for history) MONDAY 10:00 PM: You query as-of 10:00 AM Snowflake reads: [A, B, C, D, E] (the state from this morning) C2 is ignored (created after 10:00 AM) AFTER TIME TRAVEL WINDOW EXPIRES: Expired partition C is moved to Fail-Safe storage (7 more days) Then permanently deleted after Fail-Safe period ends
Querying Historical Data with AT and BEFORE
Snowflake provides AT and BEFORE clauses to query a table as it existed at a specific point in the past. You can specify the historical point using a timestamp, a relative offset in seconds, or a Query ID.
-- Method 1: Query at an absolute timestamp
SELECT COUNT(*) FROM ORDERS
AT (TIMESTAMP => '2024-06-15 09:00:00'::TIMESTAMP_NTZ);
-- Returns row count as it was at 9:00 AM on June 15
-- Method 2: Query at a relative offset (seconds before now)
SELECT * FROM ORDERS
AT (OFFSET => -3600); -- 1 hour ago (3600 seconds)
SELECT * FROM CUSTOMERS
AT (OFFSET => -86400); -- 24 hours ago
-- Method 3: Query before a specific query ran
-- First, find the query ID from Query History
SELECT * FROM ORDERS
BEFORE (STATEMENT => '01a2b3c4-0000-1234-0001-abc123def456');
-- Returns the table state as it existed BEFORE that query ran
-- Compare current state to historical state
SELECT
current.order_id,
current.status AS current_status,
hist.status AS status_one_hour_ago
FROM ORDERS AS current
JOIN ORDERS AT (OFFSET => -3600) AS hist
ON current.order_id = hist.order_id
WHERE current.status != hist.status;
-- Shows every order whose status changed in the last hour
Restoring Deleted or Modified Data
-- SCENARIO 1: Restore accidentally deleted rows -- Step 1: Find when the delete happened (check Query History in Snowsight) -- Step 2: Create a clone of the table from just before the delete CREATE TABLE CUSTOMERS_BEFORE_DELETE CLONE CUSTOMERS BEFORE (STATEMENT => 'bad_delete_query_id_here'); -- This creates a table with the data as it was just before the bad DELETE -- Step 3: Insert the missing rows back into the production table INSERT INTO CUSTOMERS SELECT * FROM CUSTOMERS_BEFORE_DELETE WHERE customer_id NOT IN (SELECT customer_id FROM CUSTOMERS); -- SCENARIO 2: Recover a dropped table using UNDROP DROP TABLE ORDERS_STAGING; -- Oops! UNDROP TABLE ORDERS_STAGING; -- Recovered instantly -- SCENARIO 3: Recover a dropped schema (recovers all tables inside it) DROP SCHEMA ANALYTICS; UNDROP SCHEMA ANALYTICS; -- SCENARIO 4: Recover a dropped database DROP DATABASE RETAIL_DB; UNDROP DATABASE RETAIL_DB; -- UNDROP works only within the Time Travel retention period -- After the retention period, the data is gone permanently
Configuring Time Travel Retention
-- Default retention: 1 day for Standard edition, up to 90 days for Enterprise -- Retention is set at database, schema, or table level -- Set a table to 30-day retention ALTER TABLE ORDERS SET DATA_RETENTION_TIME_IN_DAYS = 30; -- Set entire database to 7-day retention ALTER DATABASE RETAIL_DB SET DATA_RETENTION_TIME_IN_DAYS = 7; -- Override at schema level (overrides database default) ALTER SCHEMA RAW_DATA SET DATA_RETENTION_TIME_IN_DAYS = 1; -- Disable Time Travel for a transient table (0 = no retention) ALTER TABLE TEMP_STAGING SET DATA_RETENTION_TIME_IN_DAYS = 0; -- Check current retention setting for a table SHOW TABLES LIKE 'ORDERS'; -- DATA_RETENTION_TIME_IN_DAYS column shows the configured value
Retention Periods and Storage Cost
RETENTION PERIOD EDITIONS STORAGE COST IMPACT ---------------- -------- -------------------- 0 days All No historical storage (transient tables) 1 day Standard+ Minimal extra storage 7 days Enterprise+ ~7x daily change volume in extra storage 30 days Enterprise+ ~30x daily change volume 90 days (max) Enterprise+ ~90x daily change volume COST CALCULATION EXAMPLE: If your ORDERS table changes 10 GB of data per day, and you set retention to 30 days: Extra storage ≈ 10 GB × 30 days = 300 GB of Time Travel storage At $23/TB/month ≈ $6.90/month extra STRATEGY: Production critical tables: 30-90 day retention Staging/raw tables: 1-7 day retention Temporary processing tables: 0 days (use TRANSIENT table type)
Fail-Safe: The Last Resort
After Time Travel expires, Fail-Safe provides a final 7-day window during which Snowflake's support team can attempt data recovery. Fail-Safe is not self-service — you cannot query or restore data from Fail-Safe yourself. You must contact Snowflake Support and they attempt the recovery manually.
COMPLETE DATA PROTECTION TIMELINE
====================================
DAY 0: Data exists in the table (active storage)
DAY 1 to TIME_TRAVEL_DAYS:
Time Travel active
YOU can self-serve query, clone, or restore this data
Using: AT, BEFORE, UNDROP, CLONE AT()
DAY TIME_TRAVEL_DAYS+1 to TIME_TRAVEL_DAYS+7:
Fail-Safe active
Data is retained by Snowflake internally
YOU cannot access it directly
SNOWFLAKE SUPPORT can attempt recovery on request
Use only for genuine catastrophic data loss situations
DAY TIME_TRAVEL_DAYS+8 and beyond:
Data is permanently deleted — no recovery possible
Physical storage space is freed
EXAMPLE WITH 7-DAY RETENTION:
Day 0: Table exists
Day 1-7: Time Travel (self-service)
Day 8-14: Fail-Safe (support-only)
Day 15+: Gone permanently
Fail-Safe Storage and Cost
-- Fail-Safe storage is always 7 days for Permanent tables -- Transient tables: NO Fail-Safe (saves cost for staging data) STORAGE COSTS BY TABLE TYPE: Permanent table: Active + Time Travel + Fail-Safe storage Transient table: Active + Time Travel (max 1 day) storage only Temporary table: Active storage only (no Time Travel, no Fail-Safe) View Fail-Safe storage consumption: SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS WHERE FAILSAFE_BYTES > 0 ORDER BY FAILSAFE_BYTES DESC;
Time Travel for Auditing and Compliance
Beyond disaster recovery, Time Travel enables powerful auditing and compliance workflows. Regulators sometimes require organisations to prove what their data showed on a specific date. Analysts use Time Travel to compare current data to historical snapshots, detect anomalies, and understand how data changed over time.
-- Audit: Show all orders that existed on a specific regulatory date
SELECT COUNT(*), SUM(amount)
FROM ORDERS
AT (TIMESTAMP => '2024-03-31 23:59:59'::TIMESTAMP_NTZ);
-- Returns end-of-quarter figures exactly as they existed at that moment
-- Change detection: Which rows changed today?
SELECT
current_data.order_id,
current_data.status AS current_status,
yesterday.status AS yesterday_status
FROM ORDERS current_data
FULL OUTER JOIN ORDERS AT (OFFSET => -86400) yesterday
ON current_data.order_id = yesterday.order_id
WHERE current_data.status IS DISTINCT FROM yesterday.status
OR yesterday.order_id IS NULL -- new orders not in yesterday's data
OR current_data.order_id IS NULL -- orders deleted today
ORDER BY current_data.order_id;
Key Points
- Time Travel lets you query, clone, or restore data as it existed at any past timestamp within the retention window — up to 90 days on Enterprise edition
- UNDROP recovers accidentally dropped tables, schemas, or databases within the Time Travel period — no restore-from-backup required
- Use CLONE AT (TIMESTAMP) or CLONE BEFORE (STATEMENT) to create a restored copy of a table at any historical point without affecting the production table
- Configure Data Retention Time per table, schema, or database — use longer retention for critical production tables and 0 days for transient staging tables
- Fail-Safe is a 7-day post-Time-Travel safety net managed entirely by Snowflake Support — it requires a support ticket and is not self-service
- Transient tables have no Fail-Safe and max 1-day Time Travel — use them for staging data to reduce storage costs
- Time Travel storage costs equal the volume of changed data multiplied by the retention period — monitor with TABLE_STORAGE_METRICS