Introduction

Data, whether it be on customer information, financial records, transactions—and much more, is an indispensable asset for businesses. Unfortunately, it can be lost or damaged through human error or any technical issue. That's why having a robust data backup and recovery plan is crucial for any business that values its data. For Snowflake users, one feature that can help is Snowflake Time Travel. Snowflake Time Travel is a powerful feature of Snowflake that enables users to access historical data and recover deleted or corrupted data quickly and easily.

In this article, we'll talk about how powerful Snowflake Time Travel is and what it can do for Snowflake backup and recovery. We'll talk about the benefits of using Snowflake time travel to recover lost data and provide easy-to-follow steps on how to set it up and use it.

What is Snowflake Time Travel?

Snowflake Time Travel is a powerful feature that enables users to examine and analyze historical data, even if it has been modified or deleted. With Snowflake Time Travel, users can restore deleted objects, make duplicates, make a Snowflake backup and recovery of historical data, and look at how it was used in the past (historical data).

What are the benefits of Snowflake Time Travel?

Snowflake Time Travel offers a range of benefits, which include:

• Provides protection for accidental or intentional data deletion.
• Allows users to query and analyze historical data at any point in time within the defined retention period.
• Allows cloning and restoring tables, schemas, and databases at specific points in time.
• Minimizes the complexity of data recovery by providing a straightforward way to retrieve lost data without complicated Snowflake backup and recovery processes.
• It helps keep track of how data is used and changed over time.
• Offers a low-cost approach to continuous data protection.
• Provides granular control over the retention period for different types of objects.
• Automatically keeps track of historical data and doesn't need any extra setup or configuration.

Data Retention Period in Snowflake Time Travel

The data retention period is a critical component of Snowflake Time Travel. Whenever data is modified, Snowflake preserves the state of the data before the update, allowing users to perform Time Travel operations. The data retention period determines the number of days for which the historical data is preserved.

Snowflake Standard Edition has a retention period of 24 hours(1 day) by default and is automatically enabled for all Snowflake accounts. However, users can adjust this period by setting it to 0 (or resetting it to the default of 1 day) at the account and object level, including databases, schemas, and tables. For Snowflake Enterprise Edition and higher, the retention period can be set to 0 (or reset back to the default of 1 day) for transient and permanent databases, schemas, and tables. Permanent objects can have a retention period ranging from 0 to 90 days, giving users more flexibility and control over their data storage.

Whenever a data retention period ends, the historical data of the object will be moved into a failsafe, where past objects can no longer be queried, cloned, or restored. Snowflake's failsafe store data for up to seven days, giving users enough time to recover any lost or damaged data.

Setting the Data Retention Period for Snowflake Time Travel

Users with the ACCOUNTADMIN role can set the default retention period for their accounts using the DATA_RETENTION_TIME_IN_DAYS object parameter be set at the account, database, schema, or table level.

The default retention period for a database, schema, or individual table can be overridden using the parameter "DATA_RETENTION_TIME_IN_DAYS" during creation. Also, the retention period can be adjusted at any point in time, allowing users to customize it to suit their requirements.

Here is one example of a sample query that demonstrates how the "DATA_RETENTION_TIME_IN_DAYS" object parameter can be used to set a retention period of 30 days for a Snowflake table and database:

-- DBwith a retention period of 30 days
CREATE DATABASE my_database
DATA_RETENTION_TIME_IN_DAYS = 30;

-- Table with a retention period of 30 days
CREATE TABLE my_table (
  id INT,
  name VARCHAR,
  created_at TIMESTAMP
)
DATA_RETENTION_TIME_IN_DAYS = 30;

Let's take another example to understand it even better; let's say a schema has a parent database with a 10-day time travel value. The schema inherits that value. If you change the value of the parent database, the schema and any tables within it will inherit the new value.

You can also set an exact value for a specific object, which will not change even if its parent objects change. BUT temporary and transient tables can only have a time travel value of 1 day. Always remember that setting the value to 0 turns off the time travel feature, but you shouldn't do this at the account level because it only gives objects a default value. It's better to set individual objects' retention periods instead.

Use the following commands to set, alter, and display the DATA_RETENTION_TIME_IN_DAYS parameter value:

Set and display 90-day time travel at the account level:

ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS=90;
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN ACCOUNT;

Set and display 70-day time travel at the database level:

CREATE OR REPLACE DATABASE some_db DATA_RETENTION_TIME_IN_DAYS=60;
ALTER DATABASE some_db SET DATA_RETENTION_TIME_IN_DAYS=70;
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN DATABASE some_db;

Set and display 50-day time travel at the schema level:

CREATE SCHEMA someschema DATA_RETENTION_TIME_IN_DAYS=40;
ALTER SCHEMA someschema SET DATA_RETENTION_TIME_IN_DAYS=50;
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN SCHEMA someschema ;

Set and display 40-day time travel at the table level:

CREATE TABLE some_table (col1 string) DATA_RETENTION_TIME_IN_DAYS=10;
ALTER TABLE some_table SET DATA_RETENTION_TIME_IN_DAYS=40;
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN TABLE some_table;

How to Enable or Disable Snowflake Time Travel?

Snowflake Time Travel is automatically enabled with the standard 1-day retention period.

However, if you want to extend the data retention period to 90 days for db, schemas, and tables, you can upgrade to Snowflake Enterprise Edition.

**Note: **Additional storage charges will apply for extended data retention.

Disable Snowflake Time Travel for the account (level).

Disabling Snowflake Time Travel for an account is not possible, but the data retention period can be set to 0 for all db, schemas, and tables created in the account by setting DATA_RETENTION_TIME_IN_DAYS to 0 at the account level. But remember that this default can be easily overridden for individual databases, schemas, and tables.

Now let's talk about the MIN_DATA_RETENTION_TIME_IN_DAYS parameter. This parameter does not alter or replace the DATA_RETENTION_TIME_IN_DAYS parameter value. It may, however, affect the effective data retention time.

The MIN_DATA_RETENTION_TIME_IN_DAYS parameter can be set at the account level to set a minimum data retention period for all databases, schemas, and tables without changing or replacing the DATA_RETENTION_TIME_IN_DAYS value. Whenever MIN_DATA_RETENTION_TIME_IN_DAYS is set at the account level, the effective data retention period for objects is determined by:

MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS)

Disable Snowflake Time Travel for individual db, schemas and tables

You cannot disable it for an account, but you may disable it for individual databases, schemas, and tables by setting DATA_RETENTION_TIME_IN_DAYS to 0. If MIN_DATA_RETENTION_TIME_IN_DAYS is greater than 0 and set at the account level, the higher value setting takes precedence.

How Snowflake Time Travel Works in Snowflake Backup and Recovery?

Now let's begin the process of recovering the deleted data from Snowflake.

Whenever a table performs any DML operations in Snowflake, the platform keeps track of previous versions of the table's data for a specific duration, enabling users to query previous versions of the data using the AT | BEFORE clause.

With the help of this AT | BEFORE clause, users can easily query data that existed either precisely at or just before a particular point in the table's history. The specified point can be a time-based value (like a timestamp) or a time offset from the present, or it can be the ID for a completed statement like SELECT or INSERT.

Querying Historical Data in Snowflake

let's begin!

Step 1: Login/signup to your Snowflake account.

Step 2: Open the Snowflake web UI and navigate to the worksheet where you want to recover the deleted data.

Step 3: Lets create a table named **awesome_first_table **with two columns id and name and insert three rows of data into the **awesome_first_table **table.

CREATE TABLE awesome_first_table (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);
INSERT INTO awesome_first_table (id, name)
VALUES
  (1, 'abc'),
  (2, 'abc12'),
  (3, 'abc33');

Step 4: Let's start with a basic demo: delete records from the awesome first table table and recover them, but first select the entire table.

select * from awesome_first_table ;

Step 5: Create **temporary_awesome_first_table **table to hold recovered records

create table temporary_awesome_first_table like awesome_first_table;

Step 6: Now, let us delete all records from the awesome_first_table table.

delete from awesome_first_table ;

Step 7: Time to recover the records that are deleted a few mins ago

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

Instead of using offset, you can also provide the TIMESTAMP, or STATEMENT.

Learn more from here.

Step 8: Finally, Copy all the records to temp tables

insert into temporary_awesome_first_table (select * from awesome_first_table at(offset => -60*5));

Cloning Objects with Snowflake Time Travel

You can use the AT | BEFORE clause with the CLONE keyword in the CREATE command for a table, schema, or database to create a logical duplicate of the object at a specific point in its history.

Snowflake does not have backups, but you can use cloning for backup purposes. If you have Enterprise Edition or higher, Snowflake supports time travel retention of up to 90 days. You can, however, create a zero-copy clone every 3 months to indefinitely preserve the object's history. You can save the table as a clone every 90 days for up to one year.

When you clone a table using Snowflake time travel, the DATA_RETENTION_TIME_IN_DAYS parameter value is also preserved in the cloned table.

After cloning a table, the parameter values are independent, meaning you can change the parameter value in the source table and it won't affect the clone.

You can use the CREATE TABLE, CREATE SCHEMA, and CREATE DATABASE commands with the CLONE keyword to create a clone of a table, schema, or database, respectively. The clone will represent the object as it existed at a specific point in its history.

To create a table clone, you can use the CREATE TABLE command:

CREATE TABLE restored_table CLONE my_table
  AT (TIMESTAMP => 'Sat, 09 May 2015 01:01:00 +0300'::timestamp_tz);

This above command will create a clone of **my_table **as it existed at the specified timestamp.

To create a clone of a schema and all its objects, you can use the following CREATE SCHEMA command:

CREATE SCHEMA restored_schema CLONE my_schema AT (OFFSET => -3600);

This above command will create a clone of **my_schema **and all its objects as they existed 1 hour before the current time.

To create a clone of a database and all its objects, you can use the following CREATE DATABASE command:

CREATE DATABASE restored_db CLONE my_db
  BEFORE (STATEMENT => '----------------------');

The above command will create a clone of **my_db **and all its objects as they existed before the completion of the specified statement.

Recovering Objects with Snowflake Time Travel

Dropping and restoring objects in Snowflake is a simple process that allows you to keep a copy of dropped objects for a certain period of time before purging. Here's what you should know:

Dropping Objects:

When a table, schema, or database is dropped in Snowflake, it is not immediately overwritten or removed from the system. Instead, it is retained for the object's data retention period, during which time the object can be restored. The object can only be restored within only 7 days period. However, once this period has elapsed, restoration of the object becomes impossible.

To drop an object, use one of the following commands:

• DROP TABLE
• DROP SCHEMA
• DROP DATABASE

DROP TABLE <table_name>;
DROP SCHEMA <schema_name>;
DROP DATABASE <database_name>;

Note: After dropping an object, creating an object with the same name does not restore the dropped object. Instead, it creates a new version of the object. The original, dropped version is still available and can be restored.

Listing Dropped Objects:

Dropped tables, schemas, and databases can be listed using the following commands with the HISTORY keyword specified:

• SHOW TABLES
• SHOW SCHEMAS
• SHOW DATABASES

For example,

SHOW TABLES HISTORY LIKE 'load%' IN mytestdb.myschema;

SHOW SCHEMAS HISTORY IN some_db;

SHOW DATABASES HISTORY;

As you can see in the screenshot above, the output includes all dropped objects and an additional DROPPED_ON column, which displays the date and time when the object was dropped. If an object has been dropped more than once, each version of the object is included as a separate row in the output.

**Note: **After the retention period for an object has passed and the object has been purged, it is no longer displayed in the SHOW HISTORY output.

Restoring Objects:

If an object has been dropped but is still listed in the output of SHOW HISTORY, it can be restored easily using the following commands:

• UNDROP TABLE
• UNDROP SCHEMA
• UNDROP DATABASE

Calling UNDROP restores the object to its most recent state before the DROP command was issued.

For example,

UNDROP TABLE mytable;

UNDROP SCHEMA myschema;

UNDROP DATABASE mydatabase;

Note: if an object with the same name already exists, UNDROP will fail. In this case, you must rename the existing object before restoring the previous version of the dropped object.

Top 4 Snowflake Time Travel Best Practices

1) Monitor Data Retention Periods

Snowflake allows users to set a Snowflake Time Travel retention period, specifying how long the platform should keep a history of changes. Snowflake stores Time Travel data for one day by default, but users can increase this period to 90 days. However, monitoring your retention period carefully is crucial to ensure that you only store data for a short amount of time. Longer retention periods can consume more storage, resulting in higher costs. Also, retaining unnecessary data for an extended period can pose a security risk, as it may contain sensitive information that should no longer be kept.

2) Monitor Storage Consumption

Snowflake Time Travel data can consume significant storage space, particularly when you have a long retention period. Therefore, it is essential to monitor your storage consumption carefully to ensure that you have the sufficient storage capacity to support your data warehousing needs. Snowflake provides various tools and features that can help you monitor your storage usage, including Storage Billing and Snowflake’s Query Profile UI. By monitoring your storage consumption, you can identify areas of inefficiency and optimize your data management practices to reduce costs and improve performance.

3) Implement an Extra Snowflake Backup and Recovery Plan

While Snowflake provides Time Travel capabilities, having an extra backup recovery plan in place is always good. Accidents can happen, and data loss can occur, making it critical to have a plan in place to ensure that you can recover your data in case of any mishap. One way to implement an extra backup recovery plan is to use Snowflake’s Data Replication feature, which allows you to create backups in real time on another Snowflake account, providing you with an additional layer of protection against data loss.

4) Cost Optimization

Cost optimization is a crucial factor when it comes to Snowflake Time Travel, as it can consume a significant amount of resources and add to your expenses. Therefore, monitoring your costs carefully and optimizing your data management practices to minimize expenses is essential. One way to optimize costs is by setting up data retention policies to ensure that you only store data for a short time.

If you're searching for tools to optimize Snowflake costs, using an observability tool like Chaos Genius can be incredibly beneficial. Chaos Genius gives you the best possible view of your Snowflake workflows. It breaks down costs into actionable insights and shows you where your Snowflake use could be improved. You can use this tool to pinpoint your Snowflake usage pattern and get informed cost-cutting recommendations, resulting in up to 10%–30% savings on Snowflake costs without sacrificing performance.

Schedule a demo with us today and see it for yourself!

Conclusion

Snowflake Time Travel is a powerful feature that simplifies data recovery on the Snowflake platform. In this article, we talked about how important it is for Snowflake users to have data recovery plans and Snowflake Time Travel. We also talked about the several benefits of using Snowflake Time Travel for data recovery, including its ability to retrieve historical data and rapidly and effectively recover deleted or corrupted data. Moreover, we also provided a step-by-step guide for setting up and using Snowflake Time Travel from the ground up.

Snowflake Time Travel is like having a wizard at your fingertips—a time-traveling data wizard—but without the wand or a hat. Simply put, it's a magical way to restore your data and turn back the clock on any mistakes, and it's as easy as saying "ABRACADABRA."