Snowflake Time Travel and Fail-Safe Feature

In this tutorial, you will learn:

What is Time Travel in Snowflake ?

Time travel is a cool feature which lets you access data as of any time in the past. For example, if you have a Employee table and if you delete the table accidentally you can use time travel and go back 5 minutes and retrieve the data back. Snowflake Time Travel enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period. It serves as a powerful tool for performing the following tasks:

  • Query data in the past that has since been updated or deleted

  • Create clones of entire tables, schemas, and databases at or before specific points in the past

  • Restore tables, schemas, and databases that have been dropped

Time Travel syntax in Snowflake ?

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);

How many days duration does Time Travel work in Snowflake ?

Table Type

Time Travel Retention Period (Days)

Fail-safe Period (Days)

Total Historical Data Maintained (Days)

Permanent

1 day (for Snowflake Standard Edition)
90 days (for Snowflake Enterprise Edition)

7 days

8 days for Standard, 97 days for Enterprise

Transient

0 days (Standard) or 1 day (Enterprise)

0 days

0 days (Standard) or 1 day (Enterprise)

Temporary

0 days (Standard) or 1 day (Enterprise)

0 days

0 days (Standard) or 1 day (Enterprise)

How to enable or disable Time Travel work in Snowflake ?

Time travel is automatically enabled with the standard, 1-day retention period. However, you may wish to upgrade to Snowflake Enterprise Edition to enable configuring longer data retention periods of up to 90 days for databases, schemas, and tables

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

Below example creates 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 to disable Time Travel in Snowflake for a table ?

Time Travel cannot be disabled for an account; however, it can be disabled 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 to undrop in Snowflake using Time Travel ?

If you dropped an object, you can recover it back using undrop command

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

Snowflake Time Travel Storage costs ?

Storage charges are incurred for maintaining historical data during both the Time Travel and Fail-safe periods

Snowflake minimizes the amount of storage required for historical data by maintaining only the information required to restore the individual table rows that were updated or deleted. As a result, storage usage is calculated as a percentage of the table that changed. Full copies of tables are only maintained when tables are dropped or truncated

The Storage charges are calculated for each 24-hour period (i.e. 1 day) from the time the data changed. The number of days historical data is maintained is based on the table type and the Time Travel retention period for the table

How does Time Travel works in Snowflake ?

The files in the cloud storage (S3 / Cloud buckets / blob) are immutable. Which means we cannot alter the files, anytime a change happens to a file (like an insert / delete / update), Snowflake cannot modify the file, instead it has to create a new file. Snowflake preserves the old version of the file for a period of time to enable time travel.

This is actually a limitation of the cloud and Snowflake turned this limitation beautifully into an attactive feature for its customers

Differnce between Time Travel vs Fail Safe in Snowflake ?

Fail-safe ensures historical data is protected in the event of a system failure or other catastrophic event, e.g. a hardware failure or security breach

Fail-safe provides a (non-configurable) 7-day period during which historical data is recoverable by Snowflake. This period starts immediately after the Time Travel retention period ends



Next Section: Zero Copy Cloning



MetricCamp.com