EXPLORE TIME TRAVEL IN SNOWFLAKE

Have you ever wondered if it would ever be possible to time travel like in the old movies with a time machine? If we could go back in time and see the world, would we? If you asked me, I would have said yes! But not in the way that Hollywood portrays science fiction films. Today, we’ll look at one such feature. Go get ready to Time travel in the Data’s world of Snowflake.

Introduction to Time Travel

“Snowflake Time Travel enables accessing historical data (i.e., data that has been changed or deleted) at any point within a defined period.” – Snowflake

Time Travel is one of the cool features that Snowflake provides to its users. It allows us to recover data that has been changed or deleted at any point within a specified time frame.

We can do some amazing things with this powerful feature, such as:

• We can recover deleted objects such as tables, schemas, and databases. So there’s no need to worry about new employees accidentally deleting data.
• Duplicating and backing up data from key points in the past was never as simple as it is now.
• Examine data usage and manipulation over specified time periods.

NOTE:

Snowflake’s Time travel define by Databases, Schemas, and Tables. The data retention period parameter specifies the amount of time we can view the table’s historical data. In all Snowflake editions, It is set to 1 day by default for all objects.

This parameter can be extended to 90 days for Enterprise and Business-Critical editions.

The parameter “DATA RETENTION PERIOD” controls an object’s time travel capability.

Once the time travel duration is exceeded the object enters the Fail-safe region. If you need to retrieve the object while it is in Fail safe mode, you must contact the snowflake itself.

The following SQL extensions have been implemented to support Time Travel:

• The AT | BEFORE clause, which can be used in SELECT statements and CREATE… CLONE commands (immediately after the object name).

To pinpoint the exact historical data you want to access, the clause uses one of the following parameters:

• TIMESTAMP
• OFFSET (time difference in seconds from the present time)
• STATEMENT (identifier for statement, e.g. query ID)

#select the data for the specified Query ID executed at specific period of time
SELECT * FROM OUR_FIRST_DB.public.test before (statement => '01a58f86-3200-7cb6-0001-25ce0002d232') //  Query ID

#select the data as of before a couple of (seconds, minutes, hours) ago in snowflake using the time travel
SELECT * FROM OUR_FIRST_DB.public.test before (offset => -300) //  seconds only

#select the data as of specified date time in snowflake using the time travel
select * from OUR_FIRST_DB.public.test  at (TIMESTAMP=>'2022-12-07 00:57:35.967'::timestamp) //  Timestamp

• UNDROP command for tables, schemas, and databases.

#Will UNDROP TABLE
UNDROP TABLE TABLENAME

#Will UNDROP SCHEMA
UNDROP SCHEMA SCHEMANAME

#Will UNDROP DATABASE
UNDROP DATABASE DATABASENAME

Let us illustrate this with an example.

EXAMPLE OF TIME TRAVEL

1. Create an employee table with a 4-day data retention period. Note that I am using the DEMO_DB database and PUBLIC schema.

create or replace table EMPLOYEE (empid int ,emp_name varchar(20) ) data_retention_time_in_days=4;

insert into EMPLOYEE values(1,'Shubham');
insert into EMPLOYEE values(2,'Chandan');
insert into EMPLOYEE values(3,'Simran');
insert into EMPLOYEE values(4,'Nikita')
insert into EMPLOYEE values(5,'Achal');
insert into EMPLOYEE values(6,'Aditi');
select * from EMPLOYEE;

2. After 5 minutes, I inserted another row with EMPID 7 as follows:

insert into EMPLOYEE values(7,'Shobit'); 
select * from EMPLOYEE;

3. The table now has 7 rows, but let’s go back 5 minutes and see how the table looks.

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

In this way, you can check the data the table holds in the past.

Final Reflections

This brings us to the conclusion about the Snowflake time travel. This article has taught us what time travel is and how to use it in Snowflake. Additionally, I have demonstrated to you how to customize the Snowflake retention settings at table levels. I hope you gained an overview of one of Snowflake’s most significant features.

Please share your thoughts and suggestions in the space below, and I’ll do my best to respond to all of them as time allows.

Refer to the official Snowflake documentation here if you want to learn more.

for more such blogs click here