Time Travel retention periods in Snowflake - Commands & Configuration

Introduction

Sometimes you need to see or restore data as it was in the past. Time Travel in Snowflake lets you do this by keeping past versions of your data for a set time. Retention periods control how long these past versions are kept.

When you accidentally delete or change data and want to recover it within a few days.

When you want to audit changes made to your tables over the last few days.

When you want to clone a table as it was at a specific time in the past.

When you want to keep historical data snapshots for a short period without manual backups.

When you want to comply with data retention policies that require short-term data recovery.

Commands

This command sets the Time Travel retention period for 'my_table' to 3 days, meaning you can access past data versions up to 3 days old.

Terminal

ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 3;

Expected Output

ALTER TABLE

This command queries 'my_table' as it was 3600 seconds (1 hour) ago, using Time Travel to see past data.

Terminal

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

Expected Output

ID | NAME

1  | Alice

2  | Bob

This command shows the current Time Travel retention period setting for 'my_table'.

Terminal

SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN TABLE my_table;

Expected Output

name                          | value | level

DATA_RETENTION_TIME_IN_DAYS   | 3     | TABLE

Key Concept

If you remember nothing else from this pattern, remember: Time Travel retention periods control how long you can access past versions of your data for recovery or auditing.

Common Mistakes

Setting the retention period longer than your Snowflake edition allows.

Snowflake editions have maximum allowed retention periods; exceeding them causes errors.

Check your Snowflake edition limits and set retention periods within allowed ranges.

Trying to query past data beyond the retention period.

Data older than the retention period is permanently removed and cannot be accessed.

Only query past data within the retention period you have set.

Not setting retention period explicitly and assuming default is long.

Default retention is usually 1 day; you might lose data history sooner than expected.

Explicitly set retention period to match your recovery needs.

Summary

Use ALTER TABLE to set the Time Travel retention period in days.

Query past data using SELECT with AT clause and OFFSET in seconds.

Check current retention settings with SHOW PARAMETERS command.