Snowflake Time Travel and Fail-safe

Photo by Jocke Wulcan on Unsplash

Time Travel is an interesting and powerful feature of Snowflake that makes it possible to access data at a specific point in the past.

This feature comes in very handy in case there are accidental updates on data that you would want to revert. Similarly, if you accidentally drop an object (table, view, database, schema, etc…), you can easily UNDROP it and return to the previous state. You also have the ability to clone databases, schemas, or tables at a specific time.

Another interesting use of time travel is to query the data at different points in time for analytical purposes.

Snowflake Time Travel

How does Snowflake time travel work?

Snowflake seamlessly saves the state of the data before performing any operation on it.

What is the retention period for Snowflake time travel?

Depending on your Snowflake subscription. For a Snowflake Standard account, the retention period is 1 day only. However with an enterprise version of Snowflake, the retention period can be configured and can be anywhere between 0 days and 90 days for permanent tables. For transient and temporary tables, the retention period is 1 day (can be dropped to 0 days). It is also possible to configure the retention period at the object level, ie: database, schema, table, etc…

Example of performing time travel on a table

-- select the current timestamp to use in the time travelSELECT CURRENT_TIMESTAMP; --2022-05-02 04:10:11.788-- update the Job column setting all rows to the same valueUPDATE PRODUCT SET name = 'Noise Cancelling Headphones';-- time travel to a time just before the update was runSELECT * FROM PRODUCT BEFORE(timestamp => '2022-05-02 04:10:11.788'::timestamp);-- time travel to 5 minutes ago (i.e. before we ran the update)SELECT * FROM PRODUCT AT(offset => -60*5);-- time travel to the time before the update query was run (insert your query id)SELECT * FROM PRODUCT BEFORE(statement => '934875-kjh39824-23o49u-234-j328947');

Example of undropping a table


Combining Time Travel with cloning

Snowflake allows you to easily clone databases without creating the database structure and then inserting the data. Cloning in Snowflake makes a copy of the objects at the metadata level. This is also known as Zero Copy cloning.

A powerful feature of Snowflake is the ability to combine time travel capability with zero copy cloning. This is useful for creating development databases with data that existed at a point in time in the past for troubleshooting purposes.

Example of cloning a table with time travel:

CREATE TABLE PRODUCT_DEV CLONE PRODUCT BEFORE (timestamp=>'2022-05-02 04:10:11.788'::timestamp)

It is possible to clone entire databases and schemas in a similar fashion.

Snowflake Fail-Safe

Aside from the Time Travel feature, Snowflake provides the fail-safe feature to ensure data protection in case of failure. Fail-safe provides a non-configurable 7-day further storage of historical data in permanent after the time travel period has ended. For transient and temporary tables, the fail-safe period is 0 days.

As opposed to time travel, fail-safe data can only be recovered by Snowflake support, and no queries can be run by Snowflake users on the fail-safe data.

It is worth noting that both time travel and fail-safe contribute to the storage costs.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store