Time Travel

 What is time Travel in Snowflake? & Fail safe 

It is feature provided by snowflake to retrieve data at a given time in the past.

In simple words if you have lost some data for any reason or if you want to go back to the the specific date and time and see how your data looks like, Then this is very helpful.

There may be useful in the following situations :
          When data got deleted accidentally.
          When Data got updated incorrectly
          Missed any snapshot window for a dataset and can't recover the view you need.

          If you wat to see the data how it looked at any given time.

So when developers/ Database Admins wants to revert back to the snapshot before their last test execution point.

That is the time Snowflake time Travel allows you to go back in time and view at data. i.e data that has been modified or removed.

We use the Time Travel SQL Extensions AT or BEFORE clause in SELECT queries and CREATE... CLONE commands in Snowflake Time Travel to retrieve or clone historical data.
         TIMESTAMP
         OFFSET (time difference from the current time in seconds).
         STATEMENT (statement's identifier, e.g. query ID).

By default the data retention duration is one day we can set it unto 90 days for enterprise edition and higher accounts and fail safe period is 7 days.

/***** Setting up time travel for table : ***** /

Create 

          Table <Table Name> (<Field Name varchar>, <Field Name Varchar>, <Field Name Varchar>) 

           data_retention_time _in_days = 9

This retention duration varies from type of tables as well. 

In case of temp tables once if you drop the table and leave the session, we can't retrieve temp table but if you are in the same session it is possible to retrieve temp table in 24 hr window.

Time travel can't be turned off for an account. But it can be turned off for individual Databases, Schemas and Tables by setting the object's DATA_RETENTION_TIME_IN_DAYS to 0

/***** Retrieving data before given time *****/

select * 

       from "LEARNING_SQL"."SQL_STUDIES"."REGION" 

       before( timestamp => '2023-05-02 19:25:00.000 +0000'::timestamp)

/***** Retrieving data before given minutes *****/

select * from "LEARNING_SQL"."SQL_STUDIES"."REGION" at ( OFFSET => -60*15)

/***** Retrieving data before given Query ID *****/

select * from "LEARNING_SQL"."SQL_STUDIES"."REGION" before ( statement => '01ac05ff-0001-1668-0003-c6b60002d2da')

/***** Undrooping the table *****/

UNDROP table "LEARNING_SQL"."SQL_STUDIES"."REGION" -- This will allow us to retrieve the dropped table.

Note : We can clone / create a new table using this option as below

CREATE TABLE "LEARNING_SQL"."SQL_STUDIES"."REGION_2023_05_02"  as

select * 

       from "LEARNING_SQL"."SQL_STUDIES"."REGION" 

       before( timestamp => '2023-05-02 19:25:00.000 +0000'::timestamp)

fail-safe.

Snowflake offers a default 7-day period during which historical data can be retrieved as a fail-safe feature. Following the expiration of the Time Travel data retention period, the fail-safe default period begins. Data recovery through fail-safe is performed under best-effort conditions, and only after all other recovery options have been exhausted. Snowflake may use it to recover data that has been lost or damaged due to extreme operational failures. It may take several hours to several days for Fail-safe to complete data recovery