Why Time Travel enables data recovery in Snowflake - Why It Works

Introduction

Sometimes data gets deleted or changed by mistake. Time Travel in Snowflake lets you go back to see or restore your data as it was before the change. This helps fix errors without losing important information.

When you accidentally delete rows or tables and want to get them back quickly.

When you want to compare current data with how it looked in the past.

When you need to recover data after a wrong update or overwrite.

When you want to audit changes by looking at previous versions of your data.

When you want to create a backup copy of data from a specific past time.

Commands

This command shows the data in 'my_table' as it was one hour ago. It helps you see past data before changes happened.

Terminal

SELECT * FROM my_table AT (OFFSET => -3600);

Expected Output

ID | NAME  | VALUE

1  | apple | 10

2  | orange| 20

3  | banana| 15

This command creates a new table 'my_table_restore' with data from before a specific statement that changed or deleted data. It recovers the old data safely.

Terminal

CREATE TABLE my_table_restore AS SELECT * FROM my_table BEFORE (STATEMENT => '12345-abcde-67890');

Expected Output

Table MY_TABLE_RESTORE successfully created.

If you accidentally dropped 'my_table', this command restores it from Time Travel within the allowed retention period.

Terminal

UNDROP TABLE my_table;

Expected Output

Table MY_TABLE successfully undropped.

Key Concept

If you remember nothing else from this pattern, remember: Time Travel lets you see and restore your data exactly as it was at a past time to fix mistakes.

Common Mistakes

Trying to recover data after the Time Travel retention period has expired.

Snowflake only keeps past data for a limited time (default 1 day, up to 90 days). After that, data cannot be recovered.

Act quickly to use Time Travel commands before the retention period ends.

Using incorrect syntax or wrong timestamps when querying past data.

This causes errors or returns no data because the system cannot find the requested past state.

Use valid OFFSET, TIMESTAMP, or STATEMENT identifiers exactly as Snowflake expects.

Summary

Use SELECT ... AT (OFFSET => -seconds) to view data as it was in the past.

Use CREATE TABLE ... BEFORE (STATEMENT => id) to restore data before a change.

Use UNDROP TABLE to recover a dropped table within the retention period.