In this tutorial, you will learn:
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
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);
Table Type |
Time Travel Retention Period (Days) |
Fail-safe Period (Days) |
Total Historical Data Maintained (Days) |
|---|---|---|---|
Permanent |
1 day (for Snowflake Standard 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) |
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
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;
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;
If you dropped an object, you can recover it back using undrop command
UNDROP TABLE MyTable; UNDROP SCHEMA MySchema; UNDROP DATABASE MyDatabase;
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
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
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