What is Time Travel in Snowflake?
Snowflake Time Travel is a feature that allows users to access historical versions of data in a table, schema, or database for a specified retention period. This is useful for recovering deleted data, analyzing past states of a dataset, or restoring accidentally dropped tables.
Time Travel in Snowflake enables:
- Point-in-time analysis – Query historical data as it existed at a specific timestamp.
- Data recovery – Restore tables or schema that were dropped within the retention period.
- Auditing and debugging – Compare past and present data states to track changes.
How to Use Time Travel in Snowflake
Snowflake provides multiple ways to query historical data using Time Travel:
1. Querying a Past Version Using AT or BEFORE
You can use the AT or BEFORE clause with TIMESTAMP, OFFSET, or STATEMENT to retrieve past data.
SELECT * FROM my_table AT (TIMESTAMP => '2024-02-01 12:00:00');SELECT * FROM my_table BEFORE (STATEMENT => '8e5d0c1d-0073-4f57-8263-6e6bb1a2b1d4');2. Restoring a Dropped Table
If a table was dropped, you can restore it using:
UNDROP TABLE my_table;3. Cloning Historical Data
You can create a new table from a historical snapshot using:
CREATE TABLE my_table_clone CLONE my_table AT (TIMESTAMP => '2024-02-01 12:00:00');By Default, How Long is the Standard Retention Period for Time Travel?
The default retention period for Time Travel across all Snowflake accounts is one day (24 hours). However, Enterprise Edition and above allow extended retention of up to 90 days.
How to Check if Time Travel is Enabled
To check the retention period and whether Time Travel is enabled for a table, you can run:
SHOW PARAMETERS LIKE 'data_retention_time_in_days' IN TABLE my_table;If the value is greater than 0, then Time Travel is enabled for the table.
Using Snowflake with Evidence
For teams needing to analyze historical data efficiently, Evidence provides a seamless integration with Snowflake. With Evidence, you can:
- Leverage Time Travel to track data changes over time.
- Build interactive reports that incorporate past and present data.
- Automate historical trend analysis with SQL-based workflows.
Check out the Evidence documentation to get started with Snowflake Time Travel for better data insights.
