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

DROP TABLE PRODUCT;SELECT * FROM PRODUCT;UNDROP TABLE PRODUCT;SELECT * FROM PRODUCT;SELECT COUNT(*) FROM PRODUCT;

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.

Analytics Engineering | Business Intelligence

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

The Top 15 Websites to Learn To Code

Move WordPress Site To Local Computer

Microservices: Past, present and future

Effective Pair Work: Git, Project Planning and Reflective Feedback

How to pretend to be a Linux kernel expert

ConnectionLog iOS Mobile Application

AI engineering: making AI real

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
Pia Riachi

Pia Riachi

Analytics Engineering | Business Intelligence

More from Medium

Do & Don’t with Snowflake stored Procedures

Sensitive Data Classification Using Snowflake. Part-1

Avengers: Data Governance

Snowflake: how to repartition staged data