Benefits of Snowflake Time Travel

With Snowflake Time Travel, you can access historical data, including data that has been altered or deleted, at any given point. This feature is helpful for various tasks, such as:

• • Querying data that has been modified or erased in the past
• • Duplicating entire tables, schemas, or databases at or before specific dates
• • Restoring deleted tables, schemas, and databases

How to activate Snowflake Time Travel?

Activating Snowflake Time Travel is a simple process that requires no additional effort. It is automatically activated with a retention period of one day. Nonetheless, upgrading to the Snowflake Enterprise Edition is necessary to customise the Data Retention Period and extend it to 90 days for Databases, Schemas, and Tables. It’s important to note that increasing the Data Retention Period results in additional storage usage, reflected in your monthly Storage Fees.

Data Retention Period in Snowflake

In Snowflake, Data Retention Period determine how long historical data is retained to support Time Travel functionality. When data in a table is altered, such as through deletions or updates, Snowflake maintains the previous state of the data so that Time Travel operations (like SELECT, CREATE…CLONE, UNDROP) can be performed on it. By default, all Snowflake accounts have a standard retention period of one day (24 hours).

However, the Retention Period can be adjusted at the account and object level in the Snowflake Standard Edition to 0 (or unset to the default of 1 day) for databases, schemas, and tables.

In the Snowflake Enterprise Edition or higher, the Retention Period can be set to 0 for temporary databases, schemas, tables, and temporary tables. For permanent databases, schemas, and tables, the Retention Time can be configured to any duration between 0 and 90 days.

Functions of Snowflake Time Travel SQL Extensions

Snowflake Time Travel SQL Extensions are special SQL commands that allow users to query historical data from a specific point in time using the Time Travel feature. These extensions enable users to perform various Time Travel operations, including:

• a. CLONE: This command creates a copy of a table, schema, or database at a specific point in time using Time Travel.
• b. UNDROP: This command restores a dropped table, schema, or database to a specific point in time using Time Travel.
• c. HISTORY: This command retrieves the history of changes made to a table, schema, or database over time using Time Travel.
• d. AS OF: This command retrieves data from a table as it appeared at a specific point in time using Time Travel.

Specifying a Custom Data Retention Period for Snowflake Time Travel

To specify a custom Data Retention Period for Snowflake Time Travel, you can use the DATA_RETENTION_TIME IN_DAYS argument in the command when creating a table, schema, or database. By default, the maximum Retention Time in Standard Edition is set to 1 day (i.e. 24 hours), while in Snowflake Enterprise Edition (and higher), it can be set to any value up to 90 days.

The Data Retention Time can be set in the way it has been placed in the example below.

To create a schema with a custom Data Retention Period of 60 days, you can use the following SQL command:

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

Modify the Data Retention Period for Snowflake Objects

To modify the Data Retention Period of a Snowflake object, any change made to the Retention Period affects both active data and data in Time Travel. Depending on whether the period is increased or decreased, the following impacts occur:

• a. Increasing Retention
• b. Decreasing Retention

Let’s dive deep into more details:

a. Increasing Retention
Snowflake Time Travel preserves the data for a more extended period. For instance, if a Table’s Retention Time is increased from 10 to 20 days, the data set to be deleted after ten days will be retained for an additional ten days before being moved to Fail-Safe. However, data over ten days old and already transferred to Fail-Safe mode is unaffected.

b. Decreasing Retention
The duration of data stored in Time Travel is reduced. The shorter Retention Period applies only to active data updated after the Retention Period is shortened. If the data is still within the new Retention Period, it stays in Time Travel; otherwise, it is placed in Fail-Safe Mode. For instance, if a table with a 10-day Retention Period is reduced to 1 day, data from day 2 through day ten will be transferred to Fail-Safe, and only data from day one will be accessible through Time Travel.

Since the background process moves the data from Snowflake Time Travel to Fail-Safe, it may take some time to see the changes. Although Snowflake guarantees that the data will be transferred, it does not specify when the process will be finished. The data remains accessible via Time Travel until the background process is completed.
To change an object’s Retention Period, use ALTER object command, such as the following command for modifying a table’s Retention Period:

alter table mytable set data_retention_time_in_days=30;

Snowflake Time Travel Data Query

To query previous versions of data in Snowflake Time Travel, you can use the AT | BEFORE Clause after making any DML actions on a table. This clause allows you to query data at or before a certain point in the table’s history throughout the retention period. The specified threshold can be either time-based (e.g., a timestamp or time offset from the present) or a statement ID (e.g., SELECT or INSERT).

For example, to select historical data from a table as of a specific date and time, you can use a query like:

sql

SELECT * FROM my table AT (TIMESTAMP => ‘Fri, 05 May 2023 16:20:00 –

If you want to pull data from a table that was last updated a certain number of minutes ago, you can use a query like:

sql
SELECT * FROM my_table AT(OFFSET => -60*5);

And to collect historical data from a table up to a specified statement’s modifications, but not including them, you can use a query like:

Sql

SELECT * FROM my_table BEFORE(STATEMENT => ‘8e5d0ca9-005e-44e6-b858-a8f5b37c57

How to Restore Deleted Objects by Utilising the UNDROP Command?

To restore a deleted object that hasn’t been permanently removed from the system (meaning it can still be seen in the “SHOW object type> HISTORY” output), you can use the UNDROP command in conjunction with Snowflake Time Travel. This command can be applied to various objects, such as tables, schemas, and databases. It effectively reverts the thing to its previous state before it was deleted with the DROP command. For example, the UNDROP command can also restore a dropped database.

Summing Up

Snowflake Time Travel’s features can enhance your decision-making process and overall data experience. If you’re looking for a Snowflake service provider, Beinex is an excellent option. Our partnership with Snowflake enables us to offer advanced features like automated tuning, elastic compute, and analytics modernisation services to help your organisation realise exponential Returns on Investment.