Snowflake Time Travel and Fail-safe: Your Safety Net Explained

Last updated: March 2025

Time Travel: What You Can Do With It

Time Travel is Snowflake's mechanism for accessing historical data. Under the hood, Snowflake retains the old micro-partitions that would normally be garbage-collected after a DML operation. When you query with a Time Travel clause, Snowflake reads from those retained partitions instead of the current ones.

The retention window is controlled by DATA_RETENTION_TIME_IN_DAYS, which you can set at the account, database, schema, or table level:

-- Set 30-day retention on a critical table
ALTER TABLE production.orders SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- Set 0 days on a staging table (no Time Travel needed)
ALTER TABLE staging.temp_load SET DATA_RETENTION_TIME_IN_DAYS = 0;

Querying Historical Data

Snowflake provides three ways to specify the point in time you want to query:

AT(TIMESTAMP)

-- Query orders as they existed yesterday at 2 PM
SELECT * FROM production.orders
AT(TIMESTAMP => '2026-02-26 14:00:00'::TIMESTAMP_LTZ);

AT(OFFSET)

-- Query orders as they existed 1 hour ago (offset in seconds)
SELECT * FROM production.orders
AT(OFFSET => -3600);

BEFORE(STATEMENT)

-- Query orders as they existed just BEFORE a specific query ran
SELECT * FROM production.orders
BEFORE(STATEMENT => '01b2c3d4-0000-0000-0000-000000000000');

The STATEMENT approach is the most precise. You can find the query ID in the query history, then recover data from just before that bad UPDATE or DELETE executed.

Undoing Mistakes: Three Recovery Patterns

Pattern 1: UNDROP a Dropped Table

Someone runs DROP TABLE production.orders. As long as you're within the retention period:

UNDROP TABLE production.orders;

The table comes back exactly as it was, including all data, grants, and metadata. This also works for schemas and databases:

UNDROP SCHEMA production;
UNDROP DATABASE analytics_db;

Pattern 2: Recover From a Bad UPDATE

Someone runs UPDATE orders SET status = 'cancelled' without a WHERE clause. Every row is now cancelled. Here's the fix:

-- Step 1: Clone the table from before the bad update
CREATE TABLE production.orders_recovered CLONE production.orders
  BEFORE(STATEMENT => '01b2c3d4-0000-0000-0000-000000000000');

-- Step 2: Verify the recovered data looks correct
SELECT COUNT(*), COUNT(DISTINCT status)
FROM production.orders_recovered;

-- Step 3: Swap the tables
ALTER TABLE production.orders RENAME TO production.orders_bad;
ALTER TABLE production.orders_recovered RENAME TO production.orders;

-- Step 4: Clean up after verification
DROP TABLE production.orders_bad;

Pattern 3: Recover Deleted Rows

Someone deletes 50,000 rows that shouldn't have been deleted. Instead of restoring the whole table, extract just the missing rows:

-- Find rows that existed before but don't exist now
INSERT INTO production.orders
SELECT old.*
FROM production.orders AT(OFFSET => -3600) old
LEFT JOIN production.orders curr ON old.order_id = curr.order_id
WHERE curr.order_id IS NULL;

Fail-safe: The Last Resort

After your Time Travel retention window expires, Snowflake keeps the data for an additional 7 days in Fail-safe. You can't access this data yourself - not through SQL, not through the UI, not through any API. Only Snowflake support can attempt recovery from the Fail-safe period, and it's meant for disaster scenarios (hardware failure, data corruption), not routine recovery.

Fail-safe has real storage costs. For a 1 TB table with 1 day of Time Travel, you're paying for 1 TB of active storage + up to 1 day of Time Travel storage + 7 days of Fail-safe storage. On high-churn tables, this adds up.

Transient vs Permanent Tables

Snowflake offers transient tables specifically for data that doesn't need the full safety net:

-- Transient table: 0-1 day Time Travel, no Fail-safe
CREATE TRANSIENT TABLE staging.daily_load (
  id INT,
  payload VARIANT,
  loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
)
DATA_RETENTION_TIME_IN_DAYS = 0;

-- Temporary table: exists only for the session
CREATE TEMPORARY TABLE session_scratch AS
SELECT * FROM production.orders WHERE order_date = CURRENT_DATE();

Using transient tables for staging data, ETL intermediaries, and temp tables can cut your storage bill significantly. A team we worked with saved 40% on storage costs by converting their 15 staging tables from permanent to transient with 0-day retention.

Storage Cost Implications

Both Time Travel and Fail-safe consume storage. The cost depends on how much data changes during the retention window. A table with 10 million rows that gets 100,000 updates per day retains 100,000 rows worth of old micro-partitions per day of retention.

Check your Time Travel storage consumption:

SELECT table_name,
       active_bytes / (1024*1024*1024) AS active_gb,
       time_travel_bytes / (1024*1024*1024) AS time_travel_gb,
       failsafe_bytes / (1024*1024*1024) AS failsafe_gb
FROM snowflake.account_usage.table_storage_metrics
WHERE table_catalog = 'PRODUCTION_DB'
  AND active_bytes > 0
ORDER BY (time_travel_bytes + failsafe_bytes) DESC
LIMIT 20;

The UNDROP Gotcha

When you UNDROP a table, it comes back with its original DATA_RETENTION_TIME_IN_DAYS setting. If that setting was 0, the recovered table still has 0-day retention - meaning you immediately lose Time Travel protection on it again. After an UNDROP, always check and adjust the retention setting:

UNDROP TABLE production.orders;
-- Immediately verify and fix retention
ALTER TABLE production.orders SET DATA_RETENTION_TIME_IN_DAYS = 7;

Key Takeaways

• Time Travel lets you query, clone, and restore data within your retention window (1-90 days depending on edition). Fail-safe adds 7 days of Snowflake-managed disaster recovery you can't access directly.
• Use AT(TIMESTAMP), AT(OFFSET), or BEFORE(STATEMENT) to query historical data. BEFORE(STATEMENT) is the most precise for recovery.
• UNDROP TABLE/SCHEMA/DATABASE recovers dropped objects within the retention window. Clone from a timestamp to recover from bad UPDATEs or DELETEs.
• Use transient tables with 0-day retention for staging data to avoid unnecessary Time Travel and Fail-safe storage costs.
• Both Time Travel and Fail-safe consume storage. Monitor with TABLE_STORAGE_METRICS and reduce retention on non-critical tables.
• After UNDROP, the table retains its original retention setting - if it was 0, you're unprotected again. Always verify.

Frequently Asked Questions