How to do time travel in Snowflake

Are you looking to find what is the time travel in the snowflake cloud data warehouse or maybe you are looking for a solution to the scenario where you can use the snowflake, how you can use the snowflake. You may also want to learn what is the cost of time travel in the Snowflake or maybe what is fail-safe in the snowflake. In this article, I will take you through a step-by-step process of enabling time travel, querying the tables based on time travel. After going through this article you will be able to understand and operate fully around the time travel in snowflake. I will also share my expertise and experience as well about time travel. Let’s don’t waste the time and I will take you quickly to the code directly where I will show you how you can use time travel.

What is the time travel in the Snowflake?

Time Travel is one of the most important functionalities provided by the snowflake cloud data warehouse. As a part of this functionality, you can access the historical data, the data which has been changed over time that can also be accessed up to a certain limit. Time travel would help in recovering the data which has been deleted accidentally or intentionally.

There has been a defined retention period. Once the retention period gets over, that data can’t be accessed by Snowflake users. It will automatically move into the Snowflake Fail-safe.

The retention period is configurable and can be ranged from 1-90 days.

How to get the history data at a specific time using time travel in Snowflake?

Using the time travel feature of the Snowflake, we can select and retrieve the data from the table, present at the specified time in the table. For example, table data is changing continuously and you wanted to get back the data available at a specific previous date-time (like at 1 AM, at 2 PM, etc.).

For this purpose we can use AT clause along with select query as follows:

-- select the data as of specified date time in snowflake using the time travel
select * from Snowflake_Task_Demo  at (TIMESTAMP=>'2021-12-17 00:57:35.967'::timestamp)

How to get the history data that exist at a specific time using offset in time travel in Snowflake?

Using the time travel feature of the Snowflake, we can select and retrieve the data from the table, present before the specified time in the table. For example, table data is changing continuously and you wanted to get back the data available before a specific date time (like 2 days ago, 2 hours ago, etc.).

For this purpose, we can use AT clause with the OFFSET Parameter along with the select query. In the offset, you can pass the offset in terms of the seconds. For example, if you want to get the data 1 hour ago, you can provide the offset value is 60.

OFFSET value needs to be provided in SECONDS only.

-- select the data as of before a couple of (seconds, minutes, hours) ago in snowflake using the --- time travel

select * from Snowflake_Task_Demo  at (OFFSET=> -300)     //  seconds only

-- This will print all the records available in the table 5 minutes ago

How to get the history data existing before a specified query or transaction using time travel in Snowflake?

Using the time travel feature of the Snowflake, we can select and retrieve the data from the table, present before the specified query or transaction is executed. This may be very useful in multiple cases. For example, you have accidentally deleted the data, now for every query execution, you get the query id. If you to see what was the data there before this delete query you can pass the query id.

For this purpose, we can use the BEFORE clause with QUERYID Parameter along with the select query. In the query id, you can pass the query id.

-- select the data as of before query or transaction in snowflake using the time travel

select * from Snowflake_Task_Demo  at (statement => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') 

How to get the history data as of seconds ago using the time travel in Snowflake?

Using the time travel feature of the Snowflake, we can select and retrieve the data from the table, present before the specified number of seconds ago in the table. For example, table data is changing continuously and you wanted to get back the data available before a few seconds ago then use the following snippet.

-- select the data as of before 30 seconds ago in snowflake using the time travel

select * from Snowflake_Task_Demo  at (OFFSET=> -30)     //  seconds only

How to get the history data as of minutes ago using the time travel in Snowflake?

Using the time travel feature of the Snowflake, we can select and retrieve the data from the table, present before the specified number of minutes ago in the table. For example table data is changing continuously and you wanted to get back the data available before few minutes ago then use the following snippet.

-- select the data as of before 40 minutes ago in snowflake using the time travel

select * from Snowflake_Task_Demo  at (OFFSET=> -60*40)     //  seconds only

How to get the history data as of hours ago using the time travel in Snowflake?

Using the time travel feature of the Snowflake, we can select and retrieve the data from the table, present before the specified number of hours ago in the table. For example, table data is changing continuously and you wanted to get back the data available before a few hours ago then use the following snippet.

-- select the data as of before 2 hours ago in snowflake using the time travel

select * from Snowflake_Task_Demo  at (OFFSET=> -60*60*2)     //  seconds only

How to check the data retention time in days in Snowflake

Data retention time can be set at various level. For example at topmost data retention time is set at the Account level. You can also set the retention time at Database, SCHEMA and table level as well.

-- Check Data Retention time in Days at Account Level in Snowflake
show parameters in ACCOUNT;

-- This will display all the parameters, there look for DATA_RETENTION_TIME_IN_DAYS

How to check the data retention time in days for the database in Snowflake

Data retention time can be set at various levels. For example, data retention time can be set at the database level. You can also check the retention time at Database level as follows:

-- Check Data Retention time in Days at Account Level in Snowflake

show parameters in DATABASE db_name;
-- This will display all the parameters, there look for DATA_RETENTION_TIME_IN_DAYS

How to check the data retention time in days for SCHEMA in Snowflake

Data retention time can be set at various levels. For example, data retention time can be set at the SCHEMA level. You can also check the retention time at SCHEMA level as follows:

-- Check Data Retention time in Days at SCHEMA Level in Snowflake

show parameters in SCHEMA db_name.schema_name;
-- This will display all the parameters, there look for DATA_RETENTION_TIME_IN_DAYS

How to check the data retention time in days for TABLE in Snowflake

Data retention time can be set at various levels. For example, data retention time can be set at the table level. You can also check the retention time at TABLE level as follows:

-- Check Data Retention time in Days at TABLE Level in Snowflake

show parameters in TABLE db_name.schema_name.table_name;
-- This will display all the parameters, there look for DATA_RETENTION_TIME_IN_DAYS

How to set/Alter the data retention time for the table in Snowflake

Snowflake data retention time can be set for the table at the time of table creation. You can also change or alter the data retention time for the specific table using the following SQL.

-- Set The Data retention time for the Table while creating

create table employee (id number, join_date date)  data_retention_time_in_days=20;


-- Alter the Data retention time for the table 

alter table employee set data_retention_time_in_days=30;

How to show all the dropped or delete Databases in the Snowflake?

Using the history command along with the show command will show all the dropped databases. However, only the databases which are still in the retention period will be shown. Once the retention time is over it won’t be displayed using the SHOW command.

Example:

-- Show deleted Database

show databases history;

-- Check for dropped_on column. For deleted database it would be not null.

How to show all the dropped or delete Schemas in the Snowflake?

Using the history command along with the show command will show all the dropped schemas. However, only the schemas which are still in the retention period will be shown. Once the retention time is over it won’t be displayed using the SHOW command.

Example:

-- Show deleted schemas

show SCHEMAS history;

-- Check for dropped_on column. For deleted schemas it would be not null.

How to show all the dropped or delete Tables in the Snowflake?

Using the history command along with the show command will show all the dropped tables. However, only the tables which are still in the retention period will be shown. Once the retention time is over it won’t be displayed using the SHOW command.

Example:

-- Show deleted tables

show TABLES history;

-- Check for dropped_on column. For deleted tables it would be not null.

How to retrieve the deleted databases in the Snowflake?

If by mistake a database got deleted or dropped then you might want it to restore or undrop again. In the snowflake it is possible to restore the deleted database only if it is still under the DATA RETENTION period.

SQL to restore the database as follows:

 -- Undrop database

undrop database database_name;

-- This will restore the database

How to retrieve the deleted schema in the Snowflake?

If by mistake a schema got deleted or dropped then you might want it to restore or undrop again. In the snowflake it is possible to restore the deleted schema only if it is still under the DATA RETENTION period.

SQL to restore the schema as follows:

 -- Undrop schema 

undrop schema  schema _name;

-- This will restore the schema 

How to retrieve the deleted table in the Snowflake?

If by mistake a table got deleted or dropped then you might want it to restore or undrop again. In the snowflake it is possible to restore the deleted table only if it is still under the DATA RETENTION period.

SQL to restore the table as follows:

 -- Undrop table

undrop table table_name;

-- This will restore the table

Snowflake official Documentation Link

Final Thoughts

By this, we have reached to end of this insightful article about the Snowflake time traveling. In this post, we have learned what is time travel, how to use time travel in Snowflake. I have also taught you, how to set the retention time in Snowflake at multiple levels like account, database, schema, and table. Besides this, I have also shared the tips to use the retention time effectively within Snowflake as per my long experience in the Snowflake cloud data warehouse.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

Deepak Goyal

Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.