Snowflake Time Travel

Snowflake Time Travel: Benefits, Use Cases, and Best Practices

What is Snowflake Time Travel?

Snowflake Time Travel is one of the essential features of Snowflake that allows users to access historical data or data that has been deleted or modified within a defined time period. This feature enables the users to perform the following things in a specified timeline:

  1. To restore tables, databases, and schemas that have been removed or lost.
  2. To retrieve data from a specific point in time from the past.
  3. To analyze changes made to a dataset over a period of time.

The Snowflake time travel stores the data for a specific time period. Once the time period is over the data is transferred to Snowflake Fail-safe and you no longer have a chance to perform the above actions.

Snowflake Time Travel is a feature in the Snowflake cloud data platform that enables users to query data stored in a table as it existed at a specific time. This can be useful in various scenarios, such as auditing, compliance reporting, and debugging.

Time Travel Data Retention Period

The data retention period is a key component of the Time-travel feature. A user may perform various modifications to a table, in such a case Snowflake preserves the state of data before making any updates. And this state of historical data will stay for a specific period of time and this time period is nothing but a Time travel data retention period.

The default data retention period for all Snowflake accounts is 1 day (24 hours). For standard objectives, it is by default 1 day, and for enterprise edition and higher accounts, it is from 0 up to 90 days.

Are you aspiring to become a Snowflake developer by learning in-demand skills?
Then, Checkout our project-oriented, real-time Snowflake Online training here.

How to Specify the data retention period for Time Travel

Following are the simple steps required to specify the data retention period:

  • The users with the ACCOUNT ADMIN role can use the DATA_RETENTION_TIME_IN_DAYS object parameter to define the data retention period for your account.
  • DATA_RETENTION_TIME_IN_DAYS object parameter is also used to override the default when creating a schema, database, and individual table.
  • The users can alter the data retention period for schema, database or table at any time.

You can either extend the data retention time period or decrease it.

Increasing Retention: It extends the retention time for the data available in time travel.

Decreasing Retention: It reduces the retention time for the data available in time travel.

The process to Enable and Disabling Time Travel

There are no tasks actually required to enable Time Travel. By default, it is enabled for 1 day for standard editions. To have a longer data retention period of 90 days for schemas, databases, and tables. The extended retention demands additional storage resources and is reflected in monthly storage charges.

The Time Travel disablement is not possible for an account but it can be possible for individual schemas, databases, and tables by specifying. DATA_RETENTION_TIME_IN_DAYS with a value of 0 for the object.

How to use Snowflake Time Travel

Here is a detailed guide on how to use Snowflake Time Travel:

1. Enable Time Travel on your Snowflake account:

To use Time Travel, your account must have Time Travel enabled. This can be done by contacting your Snowflake account representative or submitting a support ticket.

2. Enable Time Travel on a specific table:

Once Time Travel is enabled for your account, you can help it on a specific table by setting the “TIME_TRAVEL” attribute to several days. For example, to allow Time Travel for seven days on a table named “my_table”, you can run the following SQL command:

TIme Travel Example 1

This means you can query the data in “my_table” as it existed up to 7 days ago.

3. Querying data with Time Travel:

To query data in a table as it existed at a specific time, you can use the special syntax “AS OF” followed by a timestamp or a number of days in the past. For example, to query the data in “my_table” as it existed three days ago, you can run the following SQL command:

TIme Travel Example 2

Or, if you prefer to specify the number of days directly, you can use the following command:

TIme Travel Example 3

Note that Time Travel queries are read-only, so you cannot modify or delete data using Time Travel.

4. Time Travel retention:

By default, Snowflake retains Time Travel data for one day. However, this can be configured per table by setting the “TIME_TRAVEL_CLONE_RETENTION_TIME” attribute on the table. For example, to retain Time Travel data for 30 days on a table named “my_table”, you can run the following SQL command:

TIme Travel Example 4

This means you can query the data in “my_table” as it existed up to 30 days ago.

5. Time Travel costs:

Using Time Travel incurs additional storage costs in Snowflake. The price is based on the amount of data stored in the Time Travel history for each table. To estimate the cost of Time Travel for a table, you can use the Snowflake pricing calculator.

6. Time Travel for schema and database objects:

In addition to tables, you can enable Time Travel on schema and database objects, such as views, stages, and user-defined functions. To allow Time to Travel for a schema, you can set the “TIME_TRAVEL” attribute on the schema to several days. For example:

TIme Travel Example 5

This means you can query the schema objects as they existed up to 7 days ago. To query a view as it existed at a specific time, you can use the “AS OF” syntax in the SELECT statement, similar to querying a table.

7. Time Travel for data changes:

In addition to querying data as it existed at a specific time, you can also track changes to data over time using Snowflake’s “CHANGE_TRACKING” feature. When you enable change tracking on a table, Snowflake captures all changes, including inserts, updates, and deletes. You can then query the changes to the data using the “FOR SYSTEM_TIME AS OF” syntax. For example:

TIme Travel Example 6

This query returns the row with ID 1234 as it existed at the specified timestamp and any changes made to that row before or after the timestamp.

8. Time Travel for compliance:

Snowflake Time Travel can be helpful for compliance purposes, as it allows you to track and audit changes to data over time. You can use Time Travel to provide evidence of compliance with data retention, privacy, and security regulations. For example, you can use Time Travel to demonstrate that you retained data for the required time or to show that you protected sensitive data by restricting access to it.

9. Time Travel for disaster recovery:

In addition to compliance, Snowflake Time Travel can also be helpful for disaster recovery. If you accidentally delete or modify data, you can use Time Travel to recover it as it existed before the deletion or modification. You can also use Time Travel to recover from other disasters, such as system failures or data corruption.

10. Time Travel and storage costs:

It’s important to note that enabling Time Travel can increase the storage cost of your Snowflake account. Each additional day of Time Travel enabled on a table, schema, or database object increases the storage required to store the data. However, Snowflake provides several options to help you manage your storage costs, including automatic data retention policies and the ability to clone tables and objects at specific points in time.

11. Time Travel and Performance:

Enabling Time Travel can also impact query performance, especially if you need to query data over a long period or if you have many changes to your data. Snowflake provides several features to help you optimize query performance with Time Travel, including automatic data pruning and partitioning.

12. Time Travel and security:

Snowflake Time Travel is designed with security in mind. When you enable Time Travel on a table or object, Snowflake creates a read-only snapshot of the data at the specified point in time. This snapshot is stored separately from the live data; only authorized users can access it. Snowflake also provides several security features to help you control access to Time Travel data, including role-based access control and multi-factor authentication.

13. Time Travel and data governance:

Finally, Snowflake Time Travel can be a valuable tool for data governance. By enabling Time Travel on your tables and objects, you can track changes to your data over time and maintain a complete record of all data modifications. This can help you ensure data accuracy, compliance, and accountability and provide a full audit trail for your data.

Snowflake Time Travel is a powerful feature that can benefit data management, compliance, and disaster recovery. By enabling Time Travel on your tables, schema objects, and databases, you can track changes to data over time, audit data usage and compliance, and recover from disasters.

Conclusion

The Time Travel feature in Snowflake acts as a great medium to store past data that was intentionally deleted or accidentally lost. Fail-safe provides free 7 days of storage and starts functioning right after the time-travel period ends. Hope this Snowflake & time-travel blog has helped you in finding some useful information. You can also check out our frequently asked Snowflake interview questions and Snowflake tutorial here.

Who can learn Snowflake?

Following are the professionals who can advance in their careers by learning Snowflake dba training:

  • Data Analysts
  • Data Engineers
  • Data Scientists
  • Database Architects
  • IT professionals and Freshers who wish to build their career in advanced data warehouse tools.

What are the Prerequisites to learn Snowflake?

There are no mandatory prerequisites for learning Snowflake, but having basic knowledge or experience in the data warehouse and SQL is an added advantage.

Popular Courses

Leave a Comment