Snowflake Time Travel

Snowflake Time Travel - Table of Content

Time Travel snowflake:

Time travel seems to be a good part that enables users to access data from any point in the past. For instance, if you already have an Employee table and unintentionally delete it, users could use time travel to go back 5 minutes and fetch the information. Snowflake Time Travel enables users to access historical data (information which has been altered or removed) at whatever moment in time. It is an effective tool for completing the necessary tasks:

Query data which has been updated or deleted in the past.
Clone entire tables, schemas, and databases at or before specific dates in the past.
Restore previously deleted tables, schemas, and databases

Become a Snowflake Certified professional by learning this HKR Snowflake Training !

Time travel syntax:

See data from a table as of 5 minutes ago:

select * from my_table at(offset => -60*5);

To check the data as of timestamp:

select * from my_table 

at(timestamp => 'Mon, 01 May 2015 16:20:00 -0700'::timestamp);

Snowflake Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

Data retention period:

The data retention period is an important aspect of Snowflake Time Travel.

When information in a table is customised, such as deletion or dropping an object containing data, Snowflake saves the data's previous state. The data retention period identifies the amount of days that this history data is kept and, as a result, Time Travel operations (SELECT, CREATE... CLONE, UNDROP) can be performed on it.

The default retention period is one day (24 hours) and is enabled by default for all Snowflake accounts:

• At the account and object levels in Snowflake Standard Edition, the retention period can be set to 0 (or reset to the default of 1 day) (i.e. databases, schemas, and tables).
• The retention period for transient databases, schemas, and tables can be set to 0. (or unset back to the default of 1 day). The same can be said for temporary tables.
• The retention period for permanent databases, schemas, and tables can be set to any value between 0 and 90 days.

Get ahead in your career with our Snowflake Tutorial !

When an object's retention period expires, the historical data is moved to Snowflake Fail-safe:

• Querying historical data is no longer possible.
• Objects from the past can no longer be cloned.
• Previously dropped objects can no longer be restored.

To clarify the data retention period for Time Travel, use the following syntax:

Users with the ACCOUNT ADMIN role can set the default retention period for your account by using the DATA RETENTION TIME IN DAYS object parameter.
When entering data, schema, or individual table, its same parameter can be used to expressly overpower the default.
A database, schema, or table's data retention period could be modified.

Subscribe to our YouTube channel to get new updates..!

Subscribe

How do I enable or disable Time Travel functionality in Snowflake?

With the standard, 1-day retention period, time travel is enabled by default. However, you may want to consider upgrading to Snowflake Enterprise Edition in order to configure longer data retention periods of up to 90 days for databases, schemas, and tables.

How do I set a custom Time Travel duration for a table in Snowflake?

The example below generates a table with 90 days of retention for time travel.

create table mytable(col1 number, col2 date) data_retention_time_in_days=90;

To reduce the retention period for a particular table

alter table mytable set data_retention_time_in_days=30;

How do I turn off Time Travel in Snowflake for a table?

Time Travel cannot be turned off for an account, but it can be turned off for individual databases, schemas, and tables by specifying DATA RETENTION TIME IN DAYS with a value of 0 for the object.

alter table mytable set data_retention_time_in_days=0;

How do you undrop in Snowflake by using Time Travel?

If you accidentally dropped an object, you can recover it by using the undrop command.

UNDROP TABLE MyTable;
UNDROP SCHEMA MySchema;
UNDROP DATABASE MyDatabase;

Storage costs of snowflake time travel:

Storage fees are levied for keeping historical data during both the Time Travel and Fail-safe periods.

Snowflake reduces the amount of storage needed for historical data by keeping only the information needed to restore the individual table rows that were updated or deleted. As a result, storage usage is calculated as a percentage of the changed table. Table full copies are only kept when tables are dropped or truncated.

Storage fees are calculated for each 24-hour period (i.e. one day) beginning with the time the data was changed. The number of days historical data is kept is determined by the table type and the table's Time Travel retention period.

Top 30 frequently asked snowflake interview questions & answers for freshers & experienced professionals

Working of time travel in snowflake:

Cloud storage files (S3 / Cloud buckets / blob) are immutable. That is, we cannot change the files; whenever a change occurs to a file (such as an insert / delete / update), Snowflake cannot adjust the file; instead, it must open the file. Snowflake saves the previous version of the file for a set period of time to allow for time travel.

This is a restriction of the cloud, and Snowflake has brilliantly transformed this limitation into an appealing characteristic for its clients.

Difference between time travel and fail safe in snowflake:

• Fail-safe safeguards descriptive statistics in the case of a failure or other tragic event, such as an equipment failure or breach of security.
• Fail-safe offers a (non-configurable) 7-day period in which Snowflake can recover historical data. This period begins directly following the expiration of the Time Travel retention period.

Limitation on the snowflake time travel:

The limitations of time travel are outlined below (these were taken from Snowflake's website, which has more information on Time Travel).

The default retention period is one day (24 hours) and is fully implemented for all Snowflake accounts. The retention period for Snowflake Standard Edition can be set to 0 at the account and object levels (for databases, schemas and tables).

• The retention period for Snowflake Enterprise Edition can indeed be altered as follows:
• Set to 0 for temporary or transient objects (or back to the default of one day).
• Set the duration of permanent objects to every value between 0 and 90 days.

Conclusion:

In the above blog post we discussed the importance of time travel in snowflake, how time travel works, limits of time travel, etc in a more detailed way. Had any doubts, drop your queries in the comments section to get them resolved.

Related Articles:

• Snowflake Connector
• Snowflake Documentation
• Snowflake Architecture
• Snowflake Data Sharing