How to Recover deleted rows with Time Travel in Snowflake

Deleted a table data accidentally ? You can now recover the data using Time travel feature in snowflake. 

What is Snowflake Time Travel ?

The Snowflake Time Travel enables accessing historical data that has been deleted at any point within a defined period. It serves as a powerful tool for restoring intentionally or accidentally deleted records.

For example, you can use the time travel tool if you want to recover the rows that has been deleted by some clean up application.

Recover Accidentally deleted data using Time Travel - Syntax

For example, Lets consider you accidentally deleted the Employee table.

Delete from Employee;

To recover the data and to do an undelete, here is the syntax:

First, lets check the data as of 10 minutes ago, Just to make sure

select * from my_table at(offset => -60*10);

Once you verified the data exists, you can simply create a new table and insert the data.

Create table my_table_new as
select * from my_table at(offset => -60*10);

You can also do an absolute time snapshot as well, like below:

select * from my_table 
at(timestamp => 'Mon, 01 Mar 2020 16:20:00 -0700'::timestamp);

nVector

posted on 27 Apr 20