Time Travel retention periods in Snowflake - Step-by-Step Execution

Process Flow - Time Travel retention periods

Set Retention Period

↓

Data Changes Occur

↓

Time Travel Window Active?

No→Data Older Than Retention Removed

Yes↓

Query Historical Data Within Window

↓

Return Data Snapshot

This flow shows how Snowflake keeps data snapshots for a set retention period, allowing queries on past data within that window.

Execution Sample

Snowflake

ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 3;
-- Insert data
INSERT INTO my_table VALUES (1, 'A');
-- Update data
UPDATE my_table SET col2 = 'B' WHERE col1 = 1;
-- Query historical data
SELECT * FROM my_table AT (OFFSET => -1);

This code sets a 3-day retention, modifies data, and queries the previous version using Time Travel.

Process Table

Step	Action	Retention Period (days)	Data State	Query Result
1	Set retention period	3	No data	No output
2	Insert row (1, 'A')	3	Row inserted	Row (1, 'A') visible
3	Update row col2='B'	3	Row updated to (1, 'B')	Current row (1, 'B') visible
4	Query at offset -1 (before update)	3	Row updated	Row (1, 'A') returned
5	Wait 4 days (exceed retention)	3	Data older than 3 days removed	Historical query fails or returns current data

💡 After 3 days, historical data older than retention period is removed, so Time Travel cannot access it.

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 5
DATA_RETENTION_TIME_IN_DAYS	1 (default)	3	3	3
DATA_STATE	Empty	Row (1, 'A')	Row (1, 'B')	Row (1, 'B')
HISTORICAL_DATA_AVAILABLE	No	Yes (1 day old)	Yes (updated)	No (older than 3 days)

Key Moments - 3 Insights

Why can't we query data older than the retention period?

Because Snowflake removes historical data beyond the retention period, as shown in step 5 of the execution_table where data older than 3 days is deleted.

Does updating data reset the retention period?

No, the retention period is fixed when set. Updates create new versions but do not extend retention, as seen in steps 3 and 4.

What happens if we query historical data within the retention period?

Snowflake returns the data snapshot from that time, demonstrated in step 4 where querying at offset -1 returns the previous row version.

Visual Quiz - 3 Questions

Look at the execution_table, what is the data state after step 3?

A Row (1, 'A')

B Row (1, 'B')

C No data

D Historical data removed

💡 Hint

Check the 'Data State' column for step 3 in the execution_table.

At which step does historical data become unavailable due to retention period expiry?

A Step 2

B Step 3

C Step 5

D Step 4

💡 Hint

Look for when data older than retention is removed in the execution_table.

If the retention period was set to 1 day instead of 3, how would step 4's query result change?

A It would fail or return current data if more than 1 day passed

B It would still return the previous version

C It would return no data at all

D It would return data from 3 days ago

💡 Hint

Refer to the 'Retention Period' and 'Query Result' columns in the execution_table.

Concept Snapshot

Snowflake Time Travel lets you query past data versions.
Set retention period (default 1 day, max 90 days).
Data changes are kept for that period.
Queries with AT or OFFSET access snapshots.
After retention, old data is removed and inaccessible.

Full Transcript

This visual execution shows how Snowflake's Time Travel retention period controls how long historical data is kept. First, you set the retention period, for example 3 days. When data is inserted or updated, Snowflake keeps snapshots for that time. You can query previous versions within the retention window using special syntax. After the retention period passes, Snowflake removes old data snapshots, so querying older data fails or returns current data. The execution table traces each step: setting retention, inserting data, updating data, querying historical data, and data removal after retention expires. Variables track retention days, data state, and availability of historical data. Key moments clarify why data older than retention is unavailable, that updates don't reset retention, and how queries return snapshots within the window. The quiz tests understanding of data state changes, when data becomes unavailable, and effects of changing retention period. The snapshot summarizes the core idea: Time Travel keeps data snapshots for a set time, enabling queries on past data until retention expires.