How Snowflake Time Travel Helps Recover Deleted Data

Data is at the core of modern businesses, and the ability to travel back in time to access historical data can be invaluable. Snowflake, a cloud-based data warehousing platform, offers a unique feature called Time Travel.  

Time travel snowflake empowers organizations to access and recover historical data effortlessly. Snowflake Time Travel is a feature that enables users to access and query historical data at various points in time. It allows for the retrieval of data as it existed in the past, making it an essential tool for auditing, compliance, debugging, and historical analysis.  

Snowflake Time Travel is a significant departure from traditional data warehousing systems, which often struggle to provide efficient historical data access. 

What is Snowflake Time Travel?

Time Travel in Snowflake is a powerful feature that allows users to access and analyze historical data, restore dropped objects, and create data snapshots at specific points in time within a configurable period, enhancing data integrity and facilitating historical analysis.  

Accessing Historical Data: Snowflake Time Travel allows users to access historical data, even if it has been modified or deleted. This feature maintains a historical record of changes to data in the database. 

Restoring Dropped Objects: You can restore tables, schemas, and databases that have been dropped within the defined Time Travel period. This can be useful for recovering accidentally deleted objects. 

Querying Historical Data: You can query data as if it existed in the past, even if it has been updated or deleted since that time. This is valuable for historical analysis, auditing, and compliance purposes. 

Creating Clones: Snowflake Time Travel allows you to create clones of entire tables, schemas, or databases at specific points in the past. Cloning can be useful for various purposes, such as creating a snapshot of your data for analysis or testing. 

Fail-Safe: Once the defined Time Travel period has elapsed, the historical data is moved into Snowflake’s Fail-Safe, and you can no longer perform the actions mentioned above. Fail-Safe is a storage layer in Snowflake that preserves historical data, but it’s not directly accessible for querying. 

DROPPED OBJECTS RESTORE: 

We have a table ‘product’, and it has been dropped accidentally instead of a backup table.  we can restore in snowflake by using the ‘UNDROP’ command. 

We can restore the dropped table by using the Snowflake UNDROP command as shown below. 

Querying Historical Data:

Querying historical data in Snowflake using the Time Travel feature allows you to access and analyze data as it existed at specific points in the past. Here are some examples of how to query historical data in Snowflake: 

Time Travel Using Timestamp: Let us see how it works with the examples below.

We have a product table and let’s see the data first  

Now we will update the table as shown below and see the results.

Now the data is changed let’s see how we can bring back old data into the same shape.

Time travel using offset: 

“OFFSET” represents the time difference in seconds between the present time and the specified timestamp. 

Let’s see some examples of how it is going to work.

We can observe the difference in the table between two time periods. 

Time travel with query ID:  

Will see how it works with some examples. 

We are updating the product table again as shown below. 

Now we want data before this NULL update in table, let’s see how it works.  

The query ID referenced in the statement corresponds to an earlier executed update statement, and you can retrieve this query ID from the “Open History” feature in Snowflake. 

Table Data Recovery:  

We will see examples if we want a timestamp snapshot of data into a table. 

Time Travel Data Retention period of Snowflake: 

In Snowflake, the data retention period, often referred to as the “Time Travel retention period,” specifies how long historical data is preserved and can be queried using the Time Travel feature. The Time Travel retention period is a crucial aspect of data management, as it determines how far back in time you can access and retrieve data. The default retention period in Snowflake is ‘’ONE DAY’’, but it can be extended based on your needs and pricing plan. 

We will see some examples below. 

We can observe the retention time is changed to Zero for the Products_recovery table so we can update the retention period and we can remove data retention time. 

Default Retention Period:

• Snowflake Standard Edition: 1 day (24 hours) – automatically enabled, applies to all databases, schemas, and tables. 
• Snowflake Enterprise Edition (and higher): Transient objects (databases, schemas, tables) inherit the parent object’s setting. Permanent objects can have a retention period of 0 (disable) to 90 days. 

Data Retention Period Rules:

When you modify the data retention period at a higher level, like the account or schema level, any lower-level objects (e.g., databases, schemas, and tables) without their own explicitly defined retention period will automatically adopt the new retention period. 

However, when you drop a database, any child schemas or tables will inherit the database’s retention period, even if they had distinct retention periods defined. This means they will be retained for the same duration as the database. 

To ensure that child schemas or tables maintain their retention periods when a database is dropped, you must explicitly drop those child objects before dropping the database or schema. 

In essence, it’s essential to be aware that changes in the data retention period affect lower-level objects, and when dropping databases, you should explicitly remove any child schemas or tables if you want their distinct retention periods to be honored.