Skip to main content

Snowflake Time Travel

🌟 What is Time Travel?​

Snowflake Time Travel allows you to access historical data in tables, schemas, and databasesβ€”whether you want to query, clone, or restore data as it existed at a previous point in time.


Why Use Time Travel?​

  • βœ”οΈ Recover from accidental data modifications or deletions
  • βœ”οΈ Audit historical changes
  • βœ”οΈ Reproduce reports as of specific past snapshots
  • βœ”οΈ Debug & troubleshoot data issues
  • βœ”οΈ Clone historical versions of tables, schemas, or databases for testing or backup
  • βœ”οΈ Restore dropped objects such as tables or entire schemas
  • βœ”οΈ Perform audit and compliance analysis on data changes over time

Time Travel Retention Periods​

  • Standard Edition: Up to 1 day (24 hours) retention period by default
  • Enterprise/Business Critical Editions: Up to 90 days
  • Retention period is configurable at the table, schema, and database level using the DATA_RETENTION_TIME_IN_DAYS parameter.

Different Levels of Time Travel​

βœ… 1️⃣ Table-Level Time Travel​

  • Scope: Single table
  • Usage: Recover table state, restore dropped table, clone table

Snowflake Time Travel Demo with permanent_employees Table​

This demo is standalone and ready to execute. It creates a dedicated database and schema, inserts data, updates a row, deletes a row, and then uses Time Travel to see the table before each change.

Note: This script uses CREATE OR REPLACE DATABASE, so run it only in a training/demo environment.

1. Create a Demo Database, Schema, and Table​

-- Create a dedicated demo area
CREATE OR REPLACE DATABASE time_travel_training_db;
CREATE OR REPLACE SCHEMA time_travel_training_db.demo;

USE DATABASE time_travel_training_db;
USE SCHEMA demo;

-- Optional: keep Time Travel history for 1 day on this table
CREATE OR REPLACE TABLE permanent_employees (
emp_id INT,
name STRING,
department STRING
)
DATA_RETENTION_TIME_IN_DAYS = 1;

2. Insert Data and Verify the Current Table​

-- Capture timestamp before insert
SET bfr_ins_ts = CURRENT_TIMESTAMP();

-- Insert sample data
INSERT INTO permanent_employees VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', 'Finance'),
(3, 'Charlie', 'IT'),
(4, 'Diana', 'Sales'),
(5, 'Ethan', 'Support');

-- Capture timestamp after insert
SET afr_ins_ts = CURRENT_TIMESTAMP();

-- Before insert: should show 0 rows
SELECT 'BEFORE INSERT' AS step, e.*
FROM permanent_employees BEFORE (TIMESTAMP => $bfr_ins_ts) AS e
ORDER BY emp_id;

-- After insert: should show 5 rows
SELECT 'AFTER INSERT' AS step, e.*
FROM permanent_employees e
ORDER BY emp_id;

Expected result: AFTER INSERT shows Alice, Bob, Charlie, Diana, and Ethan.


3. Update a Row and Query the Previous Version​

-- Capture timestamp before update
SET bfr_upd_ts = CURRENT_TIMESTAMP();

-- Update Charlie's department
UPDATE permanent_employees
SET department = 'Marketing'
WHERE emp_id = 3;

-- Capture timestamp after update
SET afr_upd_ts = CURRENT_TIMESTAMP();

-- Before update: Charlie should still be in IT
SELECT 'BEFORE UPDATE' AS step, e.*
FROM permanent_employees BEFORE (TIMESTAMP => $bfr_upd_ts) AS e
WHERE emp_id = 3;

-- After update: Charlie should now be in Marketing
SELECT 'AFTER UPDATE' AS step, e.*
FROM permanent_employees e
WHERE emp_id = 3;

Expected result: Time Travel shows Charlie as IT before the update, while the current table shows Marketing.


4. Delete a Row and Query the Previous Version​

-- Capture timestamp before delete
SET bfr_del_ts = CURRENT_TIMESTAMP();

-- Delete Ethan
DELETE FROM permanent_employees
WHERE emp_id = 5;

-- Capture timestamp after delete
SET afr_del_ts = CURRENT_TIMESTAMP();

-- Before delete: Ethan should still exist
SELECT 'BEFORE DELETE' AS step, e.*
FROM permanent_employees BEFORE (TIMESTAMP => $bfr_del_ts) AS e
WHERE emp_id = 5;

-- After delete: Ethan should be gone
SELECT 'AFTER DELETE' AS step, e.*
FROM permanent_employees e
ORDER BY emp_id;

Expected result: BEFORE DELETE shows Ethan, while AFTER DELETE returns only 4 employees.


5. Restore Deleted Data Using Time Travel​

You can recover the deleted row by inserting it back from the previous table version.

INSERT INTO permanent_employees
SELECT emp_id, name, department
FROM permanent_employees BEFORE (TIMESTAMP => $bfr_del_ts)
WHERE emp_id = 5;

-- Verify Ethan is restored
SELECT 'AFTER RESTORE' AS step, e.*
FROM permanent_employees e
ORDER BY emp_id;

Expected result: Ethan is back in the table.


6. Clone the Table as It Existed Before the Delete​

Create a zero-copy clone of the table as it existed before Ethan was deleted.

CREATE OR REPLACE TABLE permanent_employees_before_delete
CLONE permanent_employees
BEFORE (TIMESTAMP => $bfr_del_ts);

SELECT 'CLONED BEFORE DELETE' AS step, e.*
FROM permanent_employees_before_delete e
ORDER BY emp_id;

7. Undrop the Table If It Was Accidentally Dropped​

-- Drop the table
DROP TABLE permanent_employees;

-- Restore it within the Time Travel retention window
UNDROP TABLE permanent_employees;

-- Verify the table is back
SELECT 'AFTER UNDROP' AS step, e.*
FROM permanent_employees e
ORDER BY emp_id;

Table-Level Time Travel Quick Reference​

This quick reference is useful after the demo because learners can compare the three common Time Travel styles in one place.

OptionMeaningBest Used When
BEFORE (TIMESTAMP => ...)Return data before a known point in timeYou captured a timestamp before a change
AT (TIMESTAMP => ...)Return data exactly at a known point in timeYou know the exact historical timestamp
AT (OFFSET => -seconds)Return data relative to nowYou want a quick look back, like 5 minutes ago

Beginner tip: Use BEFORE when you are recovering from a mistake. Use OFFSET when you are quickly investigating recent changes.

-- 1. Query table before a captured timestamp
SELECT *
FROM table_name BEFORE (TIMESTAMP => $timestamp_variable);

-- 2. Query table at a specific timestamp
SELECT *
FROM table_name AT (TIMESTAMP => TO_TIMESTAMP('2025-06-09 08:00:00'));

-- 3. Query table using an offset in seconds
SELECT *
FROM table_name AT (OFFSET => -3600); -- 1 hour ago

-- 4. Clone a table from a past point in time
CREATE TABLE table_name_clone
CLONE table_name
BEFORE (TIMESTAMP => $timestamp_variable);

-- 5. Restore a dropped table within the retention window
UNDROP TABLE table_name;

Table-Level Offset Example​

OFFSET means β€œgo back this many seconds from the current time.” The value must be negative.

-- Example: see the table as it looked 5 minutes ago
SELECT 'TABLE 5 MINUTES AGO' AS step, e.*
FROM permanent_employees AT (OFFSET => -300) AS e
ORDER BY emp_id;

Use OFFSET for quick checks. Use TIMESTAMP or captured variables when you need an exact recovery point.


βœ… 2️⃣ Schema-Level Time Travel in Snowflake​

Schema-level Time Travel is useful when you want to manage or recover a group of related tables together.

Think of a schema like a folder. If the folder contains employees, departments, and other related tables, schema-level Time Travel helps you clone or restore that whole folder state.

When to Use Schema-Level Time Travel​

SituationWhat to Use
One table was updated incorrectlyQuery that table using BEFORE or AT
One table was dropped inside a schemaUNDROP TABLE schema.table_name
Many related tables need the same previous stateClone the schema using Time Travel
The whole schema was droppedUNDROP SCHEMA schema_name
You want all tables in a schema to keep history for a fixed periodSet schema retention

Schema-Level Demo: HR Schema Recovery​

This demo creates a schema with two related tables. Then it changes data, drops a table, clones the schema from a previous point in time, and restores the dropped schema.

Note: This script uses CREATE OR REPLACE SCHEMA, so run it only in a training/demo environment.

1. Create a Demo Schema with Two Tables​

CREATE DATABASE IF NOT EXISTS time_travel_training_db;
USE DATABASE time_travel_training_db;

CREATE OR REPLACE SCHEMA hr_time_travel_demo
DATA_RETENTION_TIME_IN_DAYS = 1;

USE SCHEMA hr_time_travel_demo;

CREATE OR REPLACE TABLE employees (
emp_id INT,
emp_name STRING,
department_id INT
);

CREATE OR REPLACE TABLE departments (
department_id INT,
department_name STRING
);

INSERT INTO departments VALUES
(10, 'HR'),
(20, 'Finance'),
(30, 'IT');

INSERT INTO employees VALUES
(1, 'Alice', 10),
(2, 'Bob', 20),
(3, 'Charlie', 30);

SELECT 'CURRENT EMPLOYEES' AS step, e.*
FROM employees e
ORDER BY emp_id;

SELECT 'CURRENT DEPARTMENTS' AS step, d.*
FROM departments d
ORDER BY department_id;

Expected result: The schema has two tables: employees and departments.


2. Capture a Schema-Level Checkpoint​

This timestamp represents the clean state of the entire schema.

SET bfr_schema_changes_ts = CURRENT_TIMESTAMP();

Now any table inside this schema can be queried or cloned as it existed at this point.


3. Make Changes Inside the Schema​

-- Move Charlie from IT to Finance
UPDATE employees
SET department_id = 20
WHERE emp_id = 3;

-- Remove the IT department
DELETE FROM departments
WHERE department_id = 30;

-- Drop one table inside the schema
DROP TABLE departments;

At this point:

  • Charlie now belongs to Finance.
  • The departments table is dropped.
  • The schema is no longer in the clean original state.

4. Query a Table Before the Schema Changes​

Even though the checkpoint was taken for the whole schema, Time Travel queries still happen at the table level.

-- Charlie before the update: department_id should be 30
SELECT 'EMPLOYEES BEFORE SCHEMA CHANGES' AS step, e.*
FROM employees BEFORE (TIMESTAMP => $bfr_schema_changes_ts) AS e
WHERE emp_id = 3;

-- Charlie now: department_id should be 20
SELECT 'EMPLOYEES NOW' AS step, e.*
FROM employees e
WHERE emp_id = 3;

Expected result: Time Travel shows Charlie in department 30, while the current table shows department 20.


4a. Schema-Level Offset Example​

OFFSET can also be used with tables inside a schema. This is still a table query, but it is useful when you are investigating a schema-level issue.

-- Example: see employees as they looked 5 minutes ago
SELECT 'SCHEMA TABLE 5 MINUTES AGO' AS step, e.*
FROM hr_time_travel_demo.employees AT (OFFSET => -300) AS e
ORDER BY emp_id;

-- Example: see departments as they looked 5 minutes ago
SELECT 'SCHEMA TABLE 5 MINUTES AGO' AS step, d.*
FROM hr_time_travel_demo.departments AT (OFFSET => -300) AS d
ORDER BY department_id;

For schema-level recovery, OFFSET is most useful when cloning the whole schema from a recent point:

CREATE OR REPLACE SCHEMA hr_time_travel_demo_5_min_ago
CLONE hr_time_travel_demo
AT (OFFSET => -300);

5. Restore a Dropped Table Inside the Schema​

UNDROP TABLE departments;

SELECT 'DEPARTMENTS AFTER UNDROP' AS step, d.*
FROM departments d
ORDER BY department_id;

Expected result: The departments table is back.

Important note: UNDROP TABLE restores the table object. It does not automatically reverse every data change made before the table was dropped. For a full previous version of all tables, use a schema clone.


6. Clone the Entire Schema from the Previous State​

This is the most useful schema-level Time Travel pattern.

CREATE OR REPLACE SCHEMA hr_time_travel_demo_before_changes
CLONE hr_time_travel_demo
BEFORE (TIMESTAMP => $bfr_schema_changes_ts);

-- Check employees in the cloned schema
SELECT 'CLONED EMPLOYEES' AS step, e.*
FROM hr_time_travel_demo_before_changes.employees e
ORDER BY emp_id;

-- Check departments in the cloned schema
SELECT 'CLONED DEPARTMENTS' AS step, d.*
FROM hr_time_travel_demo_before_changes.departments d
ORDER BY department_id;

Expected result: The cloned schema shows the clean previous state:

  • Charlie is still in department 30.
  • The departments table still has HR, Finance, and IT.

7. Restore a Dropped Schema​

-- Drop the schema
DROP SCHEMA hr_time_travel_demo;

-- Restore it within the Time Travel retention window
UNDROP SCHEMA hr_time_travel_demo;

-- Verify the restored schema has the employees table
SELECT 'SCHEMA AFTER UNDROP' AS step, e.*
FROM hr_time_travel_demo.employees e
ORDER BY emp_id;

8. Set or Change Schema Retention​

ALTER SCHEMA hr_time_travel_demo
SET DATA_RETENTION_TIME_IN_DAYS = 1;

SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS'
IN SCHEMA hr_time_travel_demo;

Retention set at schema level applies to tables inside the schema unless a table has its own override.


Schema-Level Key Points​

  • Schema-level Time Travel is best for recovering or cloning many related tables together.
  • To view old data, you still query individual tables with AT or BEFORE.
  • To restore one dropped table, use UNDROP TABLE.
  • To restore one dropped schema, use UNDROP SCHEMA.
  • To get a clean historical copy of all tables, use CREATE SCHEMA ... CLONE ... BEFORE.

βœ… 3️⃣ Database-Level Time Travel​

Database-level Time Travel is useful when you want to recover or clone an entire database, including multiple schemas and tables.

Think of a database like a large container. It can contain many schemas, and each schema can contain many tables.

When to Use Database-Level Time Travel​

SituationWhat to Use
Entire database was dropped accidentallyUNDROP DATABASE database_name
Need a full copy of the database from yesterday or before a releaseCREATE DATABASE clone CLONE database BEFORE (...)
Need to audit one table inside the databaseQuery that specific table with AT or BEFORE
Need consistent old versions across many schemasClone the database from a timestamp

Database-Level Demo: Clone and Restore a Full Database​

This demo creates a database with two schemas: hr and finance. Then it captures a checkpoint, changes data in both schemas, clones the whole database from the checkpoint, and demonstrates database undrop.

Note: This script uses CREATE OR REPLACE DATABASE, so run it only in a training/demo environment.

1. Create a Demo Database with Multiple Schemas​

CREATE OR REPLACE DATABASE db_time_travel_demo
DATA_RETENTION_TIME_IN_DAYS = 1;

CREATE OR REPLACE SCHEMA db_time_travel_demo.hr;
CREATE OR REPLACE SCHEMA db_time_travel_demo.finance;

CREATE OR REPLACE TABLE db_time_travel_demo.hr.employees (
emp_id INT,
emp_name STRING,
department STRING
);

CREATE OR REPLACE TABLE db_time_travel_demo.finance.payroll (
emp_id INT,
monthly_salary NUMBER
);

INSERT INTO db_time_travel_demo.hr.employees VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', 'Finance'),
(3, 'Charlie', 'IT');

INSERT INTO db_time_travel_demo.finance.payroll VALUES
(1, 5000),
(2, 6500),
(3, 7000);

2. Capture a Database-Level Checkpoint​

SET bfr_db_changes_ts = CURRENT_TIMESTAMP();

This timestamp represents the clean state of the whole database.


3. Make Changes Across Multiple Schemas​

-- HR schema change
UPDATE db_time_travel_demo.hr.employees
SET department = 'Marketing'
WHERE emp_id = 3;

-- Finance schema change
UPDATE db_time_travel_demo.finance.payroll
SET monthly_salary = 7500
WHERE emp_id = 3;

-- Drop a table
DROP TABLE db_time_travel_demo.finance.payroll;

Now the database has changes in more than one schema.


4. Query a Table Before the Database Changes​

-- Charlie before database changes: department should be IT
SELECT 'EMPLOYEE BEFORE DB CHANGES' AS step, e.*
FROM db_time_travel_demo.hr.employees
BEFORE (TIMESTAMP => $bfr_db_changes_ts) AS e
WHERE emp_id = 3;

-- Charlie now: department should be Marketing
SELECT 'EMPLOYEE NOW' AS step, e.*
FROM db_time_travel_demo.hr.employees e
WHERE emp_id = 3;

Expected result: Time Travel shows Charlie in IT, while the current database shows Charlie in Marketing.


4a. Database-Level Offset Example​

OFFSET can be used with fully qualified table names inside a database.

-- Example: see HR employees as they looked 5 minutes ago
SELECT 'DB TABLE 5 MINUTES AGO' AS step, e.*
FROM db_time_travel_demo.hr.employees AT (OFFSET => -300) AS e
ORDER BY emp_id;

For database-level recovery, the stronger pattern is cloning the entire database from a recent offset:

CREATE OR REPLACE DATABASE db_time_travel_demo_5_min_ago
CLONE db_time_travel_demo
AT (OFFSET => -300);

This creates a separate database showing the state from approximately 5 minutes ago.


5. Clone the Entire Database from the Previous State​

This is the strongest database-level recovery pattern.

CREATE OR REPLACE DATABASE db_time_travel_demo_before_changes
CLONE db_time_travel_demo
BEFORE (TIMESTAMP => $bfr_db_changes_ts);

-- Check HR table in the cloned database
SELECT 'CLONED DB EMPLOYEES' AS step, e.*
FROM db_time_travel_demo_before_changes.hr.employees e
ORDER BY emp_id;

-- Check Finance table in the cloned database
SELECT 'CLONED DB PAYROLL' AS step, p.*
FROM db_time_travel_demo_before_changes.finance.payroll p
ORDER BY emp_id;

Expected result: The cloned database contains the old clean state:

  • Charlie is still in IT.
  • The payroll table exists.
  • Charlie's salary is still 7000.

6. Restore a Dropped Database​

-- Drop the full database
DROP DATABASE db_time_travel_demo;

-- Restore it within the Time Travel retention window
UNDROP DATABASE db_time_travel_demo;

-- Verify the database is back
SELECT 'DATABASE AFTER UNDROP' AS step, e.*
FROM db_time_travel_demo.hr.employees e
ORDER BY emp_id;

7. Set or Change Database Retention​

ALTER DATABASE db_time_travel_demo
SET DATA_RETENTION_TIME_IN_DAYS = 1;

SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS'
IN DATABASE db_time_travel_demo;

Retention set at database level is inherited by schemas and tables unless a lower-level object overrides it.


Database-Level Key Points​

  • Database-level Time Travel is best when many schemas or tables need a consistent previous state.
  • To view old data, query the specific table with AT or BEFORE.
  • To recover a full previous copy, clone the database with a timestamp.
  • To restore a dropped database, use UNDROP DATABASE.
  • Database-level retention can be inherited by schemas and tables.

Limitations & Restrictions​

  • Retention Period: Limited to 1–90 days, depending on Snowflake edition & configuration. Historical data older than this cannot be recovered.
  • No Updates on Historical Data: Historical data is read-only; DML operations like INSERT, UPDATE, DELETE are not allowed on past versions.
  • Cost Impact: Longer retention periods increase storage costs.
  • Table Drops: Dropped tables can be restored only within the retention window.
  • Applies to: Tables, schemas, and databasesβ€”not views.
  • Temporary & Transient Tables: Have limited or no support for Time Travel.
  • Fail-safe: After Time Travel retention expires, data is retained for an additional 7 days in Fail-safe mode (non-queryable, for disaster recovery).
  • Enabled by Default: Time Travel is automatically enabled; custom retention settings are optional.

Best Practices​

  • βœ… Set appropriate DATA_RETENTION_TIME_IN_DAYS for critical tables or objects.
  • βœ… Use clones for point-in-time backups, testing, or data exploration.
  • βœ… Periodically review data recovery needs and balance retention with storage cost impacts.
  • βœ… Combine Time Travel with Fail-safe for enhanced protection against data loss.

Fail-Safe vs Time Travel​

FeatureRetentionQueryable?Purpose
Time Travel1–90 daysβœ… YesUser-level recovery & auditing
Fail-Safe7 days (after Time Travel)❌ NoSnowflake-managed disaster recovery

βœ… Key Takeaways​

  • Time Travel is a powerful self-service feature to recover or review historical data at the table, schema, or database level.
  • Use it with awareness of retention periods and cost impacts.
  • Complement Time Travel with Snowflake’s Fail-safe for ultimate data durability.

Metadata Information: DATA_RETENTION_TIME_IN_DAYS Parameter​


1. What is DATA_RETENTION_TIME_IN_DAYS?​

  • A Snowflake object parameter controlling the number of days Time Travel data is retained.
  • Defaults to 1 day (24 hours) for standard accounts.
  • Enterprise Edition allows retention up to 90 days.
  • Setting this to 0 disables Time Travel for the object (no recovery possible).

2. Where Can DATA_RETENTION_TIME_IN_DAYS Be Set?​

Parameter can be set with inheritance at these object levels:

  • Account (default for all)
  • Database
  • Schema
  • Table

Settings cascade downward unless overridden at a child level.


3. How to Check Current DATA_RETENTION_TIME_IN_DAYS​

Use the following SQL commands to check the setting at different levels:

-- Account level
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN ACCOUNT;

-- Database level
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN DATABASE ;

-- Schema level
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN SCHEMA .;

-- Table level
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN TABLE ..;

These commands reveal current and default retention values and level of configuration.


4. How to Set or Alter DATA_RETENTION_TIME_IN_DAYS​

During object creation:

CREATE DATABASE my_database DATA_RETENTION_TIME_IN_DAYS = 30;

CREATE TABLE my_table (
id INT,
name STRING
)
DATA_RETENTION_TIME_IN_DAYS = 30;

Using ALTER on existing objects:

ALTER DATABASE my_database SET DATA_RETENTION_TIME_IN_DAYS = 30;

ALTER SCHEMA my_database.my_schema SET DATA_RETENTION_TIME_IN_DAYS = 30;

ALTER TABLE my_database.my_schema.my_table SET DATA_RETENTION_TIME_IN_DAYS = 30;

5. To Disable Time Travel on an Object​

Set retention period to zero:

ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 0;

Warning: This disables the ability to recover or query historical versions of this object.


6. Important Notes​

  • Minimum retention period can be enforced at the account level by parameter MIN_DATA_RETENTION_TIME_IN_DAYS.
  • Effective retention is the maximum of the object's own setting and the account minimum.
  • Temporary and transient tables have limited Time Travel support (usually up to 1 day).
  • Changing retention affects future data only, not historic snapshots.

7. Useful Metadata Query for Tables​

You can query Snowflake’s information schema for metadata like table retention time:

SELECT 
table_catalog,
table_schema,
table_name,
retention_time
FROM information_schema.tables
WHERE table_schema = '';

Note: For Time Travel retention, prefer SHOW PARAMETERS commands.


This structured overview covers Snowflake Time Travel fundamentals, usage, limitations, and parameter management, making it ideal for freshers and learners to understand and demonstrate this powerful feature.