Snowflake Time Travel: The Complete Guide

Time Travel is the core data-protection feature of Snowflake. It lets you access the past state of tables, schemas, and databases so you can recover from mistakes and query historical data. The three points that come up most often on the exam are: past-point-in-time queries with the AT/BEFORE clause, object restoration with UNDROP, and retention control with DATA_RETENTION_TIME_IN_DAYS.

Retention Period and Edition Differences

The Time Travel retention period is controlled by the DATA_RETENTION_TIME_IN_DAYS parameter. This value can be set at the account, database, schema, and table levels, and lower-level settings override higher-level ones.

-- Set retention to 90 days at the table level
ALTER TABLE analytics.orders
  SET DATA_RETENTION_TIME_IN_DAYS = 90;

-- Set retention at the schema level (becomes the default for tables under it)
ALTER SCHEMA analytics
  SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- Check the current setting
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS'
  IN TABLE analytics.orders;

Past-Point-in-Time Queries with AT/BEFORE

The AT clause returns the state at the specified point in time, while the BEFORE clause returns the state immediately before the specified point. There are three patterns: timestamp, offset, and Statement ID.

Timestamp Specification

-- AT: get the state at the specified timestamp
SELECT *
FROM analytics.orders
  AT (TIMESTAMP => '2026-03-27 09:00:00'::TIMESTAMP_NTZ);

-- BEFORE: get the state immediately before the specified timestamp
SELECT *
FROM analytics.orders
  BEFORE (TIMESTAMP => '2026-03-27 09:00:00'::TIMESTAMP_NTZ);

Offset Specification

-- Get the state from 30 minutes ago
SELECT *
FROM analytics.orders
  AT (OFFSET => -60 * 30);

-- Get the state from 2 hours ago
SELECT COUNT(*) AS row_count_2h_ago
FROM analytics.orders
  AT (OFFSET => -60 * 60 * 2);

Statement ID Specification

-- Get the state immediately before a specific DML statement (ideal for mistake recovery)
SELECT *
FROM analytics.orders
  BEFORE (STATEMENT => '01b12345-0600-1234-0000-abcdef012345');

Statement IDs are available from the QUERY_HISTORY view. If you accidentally run an UPDATE/DELETE, specifying that Statement ID in a BEFORE clause lets you roll back to the exact state immediately before that operation.

Data Recovery Patterns Using Time Travel

-- Pattern 1: recover from a bad UPDATE (CLONE + SWAP)
CREATE TABLE analytics.orders_restored
  CLONE analytics.orders
  BEFORE (STATEMENT => '01b12345-0600-1234-0000-abcdef012345');

-- After verifying the restored table, swap the tables
ALTER TABLE analytics.orders RENAME TO analytics.orders_backup;
ALTER TABLE analytics.orders_restored RENAME TO analytics.orders;

-- Pattern 2: recover only specific rows (partial recovery)
INSERT INTO analytics.orders
SELECT * FROM analytics.orders
  BEFORE (STATEMENT => '01b12345-0600-1234-0000-abcdef012345')
WHERE order_id IN (1001, 1002, 1003);

UNDROP TABLE / SCHEMA / DATABASE

Dropped objects can be restored with UNDROP as long as they are still within the retention period. UNDROP is available at three levels: table, schema, and database.

-- Restore a table
DROP TABLE analytics.orders;
UNDROP TABLE analytics.orders;

-- Restore a schema (also restores every table under it)
DROP SCHEMA analytics;
UNDROP SCHEMA analytics;

-- Restore a database (also restores every schema and table under it)
DROP DATABASE sales_db;
UNDROP DATABASE sales_db;

If an object with the same name has already been re-created, UNDROP fails. In that case, rename the existing object first and then run UNDROP.

-- Restore procedure when a table with the same name already exists
ALTER TABLE analytics.orders RENAME TO analytics.orders_new;
UNDROP TABLE analytics.orders;

Relationship with Fail-safe

Fail-safe is an additional 7-day data-protection layer that takes over after the Time Travel period ends. It is fundamentally different from Time Travel and cannot be operated on by users directly.

Data Protection Timeline

|<── Time Travel (0-90 days) ──>|<── Fail-safe (7 days) ──>|
|  Users can freely query / restore  | Snowflake Support only |

Example: Enterprise Edition / DATA_RETENTION = 90 days / Permanent Table
  -> Up to 97 days of data protection (90 + 7)

Example: Standard Edition / DATA_RETENTION = 1 day / Permanent Table
  -> Up to 8 days of data protection (1 + 7)

Example: Transient Table (all editions)
  -> Up to 1 day of Time Travel, no Fail-safe

Data Protection by Table Type

Managing Storage Cost

• TABLE_STORAGE_METRICS: use the ACCOUNT_USAGE.TABLE_STORAGE_METRICS view to check ACTIVE_BYTES / TIME_TRAVEL_BYTES / FAILSAFE_BYTES per table
• Optimize retention period: shorten DATA_RETENTION_TIME_IN_DAYS for less critical tables to reduce storage cost
• Use Transient Tables: use Transient Tables for regeneratable data such as ETL intermediate tables to avoid Fail-safe cost

Check Your Understanding

Frequently Asked Questions