Snowflake Time Travel : Handy SQLs

Snowflake's Time Travel feature allows you to access historical data in tables, schemas, and databases, enabling you to query or restore data as it existed at a previous time or before a specific transaction.

My team and I are engineering cloud native and distributed applications which are developed on Software as a Service (SaaS) foundations and also supports Data as a Service (DaaS) features as well. I have been part of many calls where we try to recover data loss or state due to unwanted operations and restore to an earlier state in Snowflake. You may also have seen this happens a lot in lower engineering (DEV/QA) regions where multiple teams are collaborating, testing on common data.

Time Travel SQL Extensions

It's useful to review SQL Extensions which would help us with Time Travel. In Snowflake, you can use the AT and BEFORE clauses with different parameters like TIMESTAMP, OFFSET, and STATEMENT to access or revert data to a specific point in time or before a particular query execution.

Here are some useful SQL queries that leverage Snowflake's Time Travel capabilities:

Triage

• Querying Historical Data by offset (Time → seconds/minutes/hours) This query allows you to view the data based on time (mins/hours) offset. This one is my personal favorite and very handy in triage and support.

--retrives as data was 15 min ago
SELECT * FROM 
table_name at (offset => -60*15); 
 --retrives a day old data.
SELECT * FROM 
table_name at (offset => -60*60*24);

• Querying Historical Data by/before specific Timestamp This query allows you to view the data as it existed at a specific point in time.

SELECT * FROM 
table_name at (TIMESTAMP => '2024-08-24 12:00:00');
-- or
SELECT * FROM 
table_name before (TIMESTAMP => '2024-08-24 12:00:00');

• Querying Historical Data by relative Timestamp (hour/mins/seconds) This is useful for time-based calculations, data filtering, or setting time ranges. Snowflake has DATEADD function which would help with relative time. You can adjust the unit (hour, minute, second, etc.) and the amount (-1, -2, etc.) according to your specific needs.

SELECT * FROM 
table_name at (TIMESTAMP => DATEADD(hour, -1, CURRENT_TIMESTAMP));
or
SELECT * FROM 
table_name before (TIMESTAMP => DATEADD(hour, -1, CURRENT_TIMESTAMP));

• Querying Historical Data by Statement ID Every transaction in Snowflake is associated with a unique QUERY_ID. This query allows you to view the data as it existed before a specific query.

SELECT * FROM 
table_name before (STATEMENT => '019ea11e-0421-8eb7-0000-1a2b3c4d5e6f');

Restore

• Restoring a Dropped Table You can restore a table that was dropped within the last 90 days (the default time travel retention period).

UNDROP TABLE table_name ;

• Restoring a Table to a Previous State Using AT/BEFORE TIMESTAMP in Snowflake In Snowflake, you can restore a table to a prior state using Time Travel with the AT/BEFORE clause and a specific timestamp. This is useful for data recovery, triage, or rollback scenarios.

CREATE TABLE your_restored_table_name AS
SELECT * FROM table_name
BEFORE (TIMESTAMP => DATEADD(hour, -1, CURRENT_TIMESTAMP));

This query creates a new table (your_restored_table_name) that contains the data from table_name as it existed one hour ago.

Once the data has been validated and approved through triage and analysis, you can proceed to update or overwrite the main table as needed, for example:

-- Optional: Backup current state
CREATE TABLE table_name_backup AS SELECT * FROM table_name;

-- Replace main table with restored data
INSERT OVERWRITE INTO table_name
SELECT * FROM your_restored_table_name;

Note: Ensure that your Snowflake Time Travel retention period covers the required historical window

Miscellaneous

• Cloning/Restore a Table as it Existed at a Specific Time You can create a clone of a table, schema, or database as it existed at a specific point in time.

CREATE TABLE your_clone_table_name 
CLONE table_name at (TIMESTAMP => '2023-08-01 12:00:00');
/** Date format 'YYYY-MM-DD' **/

• Getting a List of Historical Versions of a Table This query helps you view the history of changes made to a table.

SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE TABLE_NAME = 'your_table_name'
ORDER BY START_TIME DESC;

• Time Travel with Data Sampling To examine historical data with sampling, you can combine time travel with the SAMPLE clause.

SELECT * FROM 
table_name at (TIMESTAMP => '2023-08-01 12:00:00')
SAMPLE (10);

Mastering these commands will greatly enhance your ability to query, clone, or restore historical data, making your data operations more resilient and flexible.

Happy Time Traveling!! And have a great day.

References

Again, A huge thanks to the Snowflake documentation, community and all the resources available that made this write-up possible.

1. Time Travel
2. Restoring deleted rows in Snowflake
3. Time travelling feature in Snowflake
4. Info-graphic via DALL-E