Snowflake Time Travel - A Detailed Guide

Traveling across time would be fantastic! There are a lot of time travel movies and series out there, on the other hand, consistently caution us about the risks of altering timelines. Perhaps time travel isn't a good idea. I can think of a few scenarios in which time travel would be helpful!

Snowflake Time Travel - Table of Contents

• Introduction to Time Travel
• Time Travel SQL Extensions
• Data Retention Period
• How to Specify the data retention period for Time Travel?
• What is fail-safe in Snowflake?
• Which Snowflake Edition Provides Time Travel?
• How to Restore Objects?
• Enabling and Disabling Time Travel
• Snowflake Time Travel Storage cost
• Difference between Time Travel and Fail-Safe in Snowflake

Do you wish you could travel across time and observe the evolution of your data over time without having to restore backups or implement a fully functional data warehouse as a business user? Please don't misunderstand what I'm saying: I'm not stating that a data warehouse isn't necessary; I'm simply stating that the data you require may not be available in your current data warehouse, or you may never have had one and wish you had.

Have you ever accidentally dropped, truncated, or erased rows from a table? Hopefully, you've prepared backups to fall back on! It's critical to be extra cautious when using update and remove statements. Developers or Database administrators occasionally find themselves in situations where they would like to execute a single piece of code and revert back to the snapshot before their last test SQL execution.

These are the kinds of circumstances where database time travel might be extremely useful. Snowflake supports database time travel. Snowflake Time Travel allows you to go back in time and view past data, i.e. data that has been modified or removed). As the data storage layer, Snowflake employs immutable cloud storage. This indicates that it makes a new version of the file rather than changing it. Time travel is one of the new and fascinating possibilities that this manner of functioning opens up.

It is an effective tool for doing the following tasks:

• Restoring data-related objects (tables, schemas, and databases) that may have been removed by accident or on purpose.
• Duplicating and backing up data from previous periods of time.
• Analyzing data manipulation and consumption over a set time.

 

 

Introduction to Snowflake Time Travel

Time travel is an interesting feature that allows you to access data from any point in the past. If you have an Employee table, for example, and you unintentionally delete it, you can utilize time travel to go back 5 minutes and retrieve the data.

It also can be used to back up data, compare data, and examine previous data usage over a period of time. As a result, it acts as a continuous data protection lifecycle that is dependent on the data retention durations set for each item. It's critical to comprehend the various data retention periods which can be used in distinct versions. All Snowflake accounts have a standard retention duration of one day (24 hours), which is enabled by default.  At the account and object level, the retention period in Snowflake Standard Edition can be adjusted to 0 (or unset to the default of 1 day). The retention time for everlasting databases, schemas, and tables in Snowflake Enterprise Edition and higher can be specified to any value between 0 and 90 days (with the exception of transient tables).

Consider the following scenario: You're working on a bug patch and are connected to the PROD database, where you executed an update statement on a table that updated billions of records. On that table, you also conducted additional delete and update logic. You later realized that when using update and delete statements in your SQL query, you forgot to use the necessary where clause.

So, what are you going to do now? Perhaps you'll consider retrieving the database's most recent backup copy and restoring it. Alternatively, you might try to truncate the table and load new data from the source. Alternatively, you will inform your boss.

If you're working in a PROD environment, you won't have enough time to backup your data and load new data. When it comes to restoring a backup copy, there is a potential that data will be lost between the last backup and the current data. If you want to do a fresh data load, it will take anything from hours to days, depending on the amount of data you have.

Then you're probably considering going back in time and restoring things to their previous state. What I mean is that I want to go back to that point in time and get the data as it was before I made a mistake with my initial update/Delete statement.

And Snowflake has a capability that allows you to go back in time and retrieve your data. This property of a snowflake is also known as its time-traveling property.

If you want to enrich your career and become a professional in Snowflake, then enroll in " Snowflake Online Training " - This course will help you to achieve excellence in this domain.

Time Travel SQL Extensions

We use the Time Travel SQL Extensions AT or BEFORE clause in SELECT queries and CREATE... CLONE commands in Snowflake Time Travel to retrieve or clone historical data. We use the SQL Extensions with the following arguments to locate the particular historical data that you want to access:

• TIMESTAMP
• OFFSET (time difference from the current time in seconds).
• STATEMENT (statement's identifier, e.g. query ID).

What is Data Retention Period For Snowflake Time Travel?

The characteristic of time travel relies heavily on the data retention duration. When a user makes changes to a table, Snowflake saves the current state of the data before making any alterations. And this state of data from the past will last for a set amount of time, which is known as the Time travel data retention period.

For all Snowflake accounts, the default data retention duration is one day. It is 1 day by default for standard aims, and it ranges from 0 - 90 days for enterprise edition and higher accounts.

How to Specify the Data Retention Period For Time Travel?

Steps to specify the data retention duration are as follows:

• DATA_RETENTION_TIME_IN_DAYS object parameter can be used by users with the ACCOUNTADMIN role to set the data retention time for your account.
• When building a schema, database, or individual table, the DATA_RETENTION_TIME_IN_DAYS object option is also used to alter the default.
• At any moment, users can change the data retention term for a schema, database, or table.

You can either increase or decrease the data retention duration.

• Increasing Retention: It extends the data retention time for time travel data.
• Decreasing Retention: It reduces the data retention duration for time travel data.

Preparing for Snowflake Interview? Here are the Top Snowflake Interview Questions and Answers

What is Fail-safe in Snowflake?

Fail-safe gives a (non-configurable) 7-day timeframe during which Snowflake may be able to retrieve prior data. This time begins as soon as the Time Travel retention period expires. 

It is a data recovery service that is offered with the best effort and should only be used after all other options have been exhausted. After the Time Travel retention term has expired, there is no fail-safe method for accessing prior data. It's just for Snowflake's use to recover data that's been lost or destroyed as a result of extreme operational failures. Fail-safe data recovery might take somewhere from a few hours to many days.

Which Snowflake Edition provides Time Travel?

These functions are offered as standard for all accounts, requiring no additional license; however, standard Time Travel is limited to one day. Snowflake Enterprise Edition is required for extended time travel (up to 90 days). Furthermore, both Time Travel and Fail-safe necessitate additional data storage, which comes with a cost.

How to Restore Objects?

The UNDROP command can be used to restore a dropped table, schema, or database that has not yet been purged from the system (i.e. not yet put into Snowflake Fail-safe). UNDROP returns the item to its most recent state, which was before it was dropped.

Enabling and Disabling Time Travel

Snowflake's time travel feature is activated by default in all editions, with a 1-day data retention period. For enterprise and higher versions, however, we can enable a longer data retention duration of up to 90 days. Individual databases, schemas, and tables can have time travel disabled by setting the data retention time in days to 0. Use the appropriate ALTER command to adjust an object's retention duration.

[ Related Article: Star schema and Snowflake schema in QlikView ]

Snowflake Time Travel Storage Cost

During both the Time Travel and Fail-safe phases, storage charges are incurred for retaining prior data.

Snowflake keeps only the information needed to restore the individual table rows that were updated or deleted, reducing the amount of storage required for historical data. As a result, storage utilization is expressed as a percentage of the altered table. Tables are only kept in full copies when they are dropped or shortened.

The charges for storage are assessed for each 24-hour period (i.e. one day) starting from the time the data was modified. The number of days historical data is kept is determined by the table type and the table's Time Travel retention period.

Visit here to learn Snowflake Training in Pune

Difference Between Time Travel and Fail-Safe in Snowflake

Time travel allows the user to query the data and see how it appeared previously, as well as query and restore the table's former state. Internally, snowflakes use fail-safe to restore data in the event of hardware failure.

Conclusion

Snowflake's Time Travel feature is a wonderful way to save data that was either deleted or lost accidentally in the past. Fail-safe offers free 7-day storage and begins working immediately after the time-travel period has ended. I hope you found some useful information from this Snowflake Time Travel blog.