Feature of Snowflake: Time Travel

Snowflake:

Snowflake’s data cloud is powered by advanced data platform provided as software as a service.  Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional offerings. Snowflake is Cloud hosted relational database used to create Data warehouse on demand.

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.”

In this particular blog we are looking into one of the most important and valuable feature of the snowflake that is Time Travel. this feature provides us to go back in time and check your data in the table or if we mistakenly updated any data in the table so by using this feature we can go back in time and restore our original data.

To support Time Travel, the following SQL extensions have been implemented:

• AT | BEFORE clause which can be specified in SELECT statements and CREATE … CLONE commands (immediately after the object name). The clause uses one of the following parameters to pinpoint the exact historical data you wish to access:
  • TIMESTAMP
  • OFFSET (time difference in seconds from the present time)
  • STATEMENT (identifier for statement, e.g. query ID)

As we all know what Time Travel is, Let’s see how to use Time Travel function.

Step 1->First we Connect to the Snowflake DB and Create table.

Step2–>Let’s insert some dummy data into the table-

Step3–>CASE: Let’s assume we forgot to add any condition in the update statement and data will updated for all the rows by mistake in the table-

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

Step4–>By using below methods we can get back our data in the table, let’s see all method in time travel-

    1.Using time travel: Method 1 -> 2 minutes back: In this method we can just travel back certain number of                                          minutes or seconds to see original data, later we see how to retain that data.

    2. Using time travel: Method 2 ->Before timestamp: here we updated age and using timestamp we can see                                                                                                      original data in the table.

     3. Using time travel: Method 3 -> before Query ID: in this method when we update any data, snowflake                       creates a unique query id for it, by using that query id we can travel back to see the original data in the table.

Step4->we all see different methods of time travel feature. Now Let’s see method to restore original data of the table->In the table LNAME, and AGE columns are updated with wrong data.  Using query id, we will travel back and restore the original data.

Step5->Using query id method, we will travel back and restore the original data.

Step6->For restoring the original data first, we create a backup table with inserting original data from the table using query id time travel feature.

Step7->Now we truncate our original table because this table has wrong updated data.

Step8->After truncating the table we can insert data from backup table to our original table.

Here we learned about Time Travel feature of snowflake and how we can restore original data after accidently updating wrong data in the table.

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

for more such blogs click here

Happy Reading!