Why Time Travel enables data recovery in Snowflake - Visual Breakdown

Process Flow - Why Time Travel enables data recovery

Data Modified or Deleted

↓

Time Travel Keeps Snapshots

↓

User Queries Past Data

↓

Snowflake Retrieves Historical Data

↓

Data Recovered or Restored

When data changes or is deleted, Snowflake keeps snapshots. Users can query these snapshots to recover past data.

Execution Sample

Snowflake

SELECT * FROM my_table AT (OFFSET => -3600);
-- Retrieves data from 1 hour ago

This query fetches the state of 'my_table' as it was one hour before the current time.

Process Table

Step	Action	Data State	Result
1	Initial data inserted	Table has 100 rows	Data stored normally
2	Delete 20 rows	Table now has 80 rows	Rows removed from current view
3	Query with Time Travel for 1 hour ago	Table snapshot has 100 rows	Returns full 100 rows including deleted ones
4	Recover deleted rows by cloning or insert	Table restored to 100 rows	Data recovered successfully
5	Time Travel retention period ends	Snapshots expire	Historical data no longer accessible

💡 Time Travel data retention period ends, so older snapshots expire and cannot be queried.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 4	Final
Table Row Count	0	100	80	100	100
Time Travel Snapshots	None	Snapshot at insert	Snapshot after delete	Snapshot used for recovery	Snapshots expire after retention

Key Moments - 3 Insights

How can deleted data still be queried after deletion?

Because Snowflake keeps snapshots of data changes (see execution_table step 3), Time Travel allows querying past states even after deletion.

What happens when the Time Travel retention period ends?

Snapshots expire and historical data is no longer accessible (see execution_table step 5), so recovery is not possible after that.

Does Time Travel change the current data?

No, Time Travel only keeps historical snapshots; current data reflects the latest state (see execution_table step 2 vs step 3).

Visual Quiz - 3 Questions

Look at the execution_table, how many rows does the table have immediately after deletion (step 2)?

A 100 rows

B 80 rows

C 20 rows

D 0 rows

💡 Hint

Check the 'Data State' column at step 2 in the execution_table.

At which step does Snowflake retrieve historical data using Time Travel?

A Step 3

B Step 2

C Step 1

D Step 5

💡 Hint

Look for when the query uses Time Travel to access past data in the execution_table.

If the Time Travel retention period is extended, what changes in the execution_table?

A Snapshots expire sooner

B Current data row count increases

C Snapshots remain accessible longer

D Deleted rows are permanently removed immediately

💡 Hint

Refer to the 'exit_note' and step 5 about snapshot expiration.

Concept Snapshot

Snowflake Time Travel keeps snapshots of data changes.
Users can query past data states using time offsets.
This enables recovery of deleted or changed data.
Snapshots expire after a retention period.
Time Travel does not affect current data state.

Full Transcript

Snowflake's Time Travel feature stores snapshots of data whenever changes happen. When data is deleted or modified, these snapshots keep the previous versions safe. Users can query these snapshots by specifying a time offset, like one hour ago, to see the data as it was then. This allows recovering deleted rows or previous data states without restoring from backups. However, these snapshots only last for a set retention period. After that, the snapshots expire and the historical data is no longer accessible. Time Travel works by keeping historical data separate from the current data, so current queries see the latest state while Time Travel queries can access past states.