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 Version You can restore a table to a previous state using a timestamp or statement ID.

RESTORE TABLE table_name 
TO (TIMESTAMP => '2023-08-01 12:00:00');
-- or
RESTORE TABLE table_name
TO (before STATEMENT => '019ea11e-0421-8eb7-0000-1a2b3c4d5e6f');

Miscellaneous

• Cloning 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');

• 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

Disclaimer: This article is AI-assisted. The article structure and idea list are 100% manually curated and researched. I proofread all AI-generated texts to ensure information accuracy and to add some contexts