Snowflake Time Travel is a fascinating feature that grants you the power to access historical data. Imagine you have an Employee table, and you accidentally delete it. With Time Travel, you can effortlessly journey back in time, let’s say 5 minutes, and rescue your data. The versatility of Snowflake Time Travel goes beyond just data retrieval; it’s an invaluable tool for a variety of essential tasks:
- Retrieve Altered or Deleted Data: Easily query data that has been modified or removed in the past, ensuring you never lose critical information.
- Create Historical Clones: Make exact replicas of entire Tables, Schemas, or even Databases at or before specific dates, perfect for historical analysis or data comparisons.
- Restore Deleted Tables, Schemas, and Databases: In case of accidental deletion, Time Travel lets you bring back lost Tables, Schemas, and Databases, ensuring you have a safety net for your data.
Snowflake Time Travel isn’t just about accessing the historical data; it’s a versatile tool that empowers you to manage your data’s history, perform insightful analysis, and safeguard against unexpected data mishaps.
Data Retention Period
A crucial aspect of Snowflake Time Travel is the data retention period, which plays a pivotal role in preserving your data history.
When data within a table undergoes changes, whether through modifications, deletions, or object removals, Snowflake ensures the preservation of the data’s state prior to these updates. The data retention period is the duration during which this historical data is safeguarded, allowing for Time Travel operations such as SELECT, CREATE, CLONE, and UNDROP to be performed on the data.
The default retention period stands at 1 day (equivalent to 24 hours), and it is automatically active for all Snowflake accounts. Here’s how it works for different editions:
- Snowflake Standard Edition: Users have the flexibility to set the retention period to 0 (or revert it to the default 1 day) at both the account and object levels, including databases, schemas, and tables.
- Snowflake Enterprise Edition (and higher):
- For transient databases, schemas, and tables, the retention period can be configured to 0 (or reverted to 1 day).
- The same holds true for temporary tables.
- In the case of permanent databases, schemas, and tables, the retention period can be set to any value ranging from 0 up to a maximum of 90 days.
When the retention period concludes for an object, the historical data is moved into Snowflake Fail-safe, resulting in the following implications:
- Historical data becomes inaccessible for querying.
- Past objects can no longer be cloned.
- Objects that were previously dropped cannot be restored.
Enabling Snowflake Time Travel:
Enabling Snowflake Time Travel is a breeze, as it comes turned on by default with a one-day retention period. However, if you wish to extend your Data Retention Periods beyond this default setting, allowing you to preserve data for up to 90 days within Databases, Schemas, and Tables, you’ll need to upgrade to Snowflake Enterprise Edition. It’s worth noting that lengthier Data Retention entails increased storage requirements, which will be reflected in your monthly Storage Fees. For more detailed insights into storage costs associated with Time Travel and Fail-safe, consult Snowflake documentation on Storage Costs for Time Travel and Fail-safe.
As an example, let’s take a look at configuring a table with a generous 90-day retention period for Snowflake Time Travel.
create table my_table (col1 number, col2 date) data_retention_time_in_days=90;To reduce the retention period to 30 days for a specific table, you can utilize the following query :
alter table my_table set data_retention_time_in_days=30;Disable Snowflake Time Travel
For a Snowflake account, it’s important to note that the Time Travel feature cannot be completely deactivated. However, you have the option to disable Time Travel for specific Databases, Schemas, and Tables by setting the object’s DATA_RETENTION_TIME_IN_DAYS to 0.
Users holding the ACCOUNTADMIN role also have the authority to set the DATA_RETENTION_TIME_IN_DAYS to 0 at the account level. This implies that, by default, all Databases (and consequently, all Schemas and Tables within those databases) created in the account will have no retention period. Nonetheless, it’s worth highlighting that this default setting can be overridden at any point in time for any Database, Schema, or Table as needed.
alter table my_table set data_retention_time_in_days=0;