Snowflake Recover Deleted Rows – Time Travel Examples

Post author:Vithal S
Post last modified:June 16, 2021
Post category:Snowflake
Reading time:5 mins read

The Snowflake cloud data warehouse works with heterogeneous data sets. You may work with huge amount of data. It is a common requirement to recover the deleted rows or records. During the clean up process, the developer may delete the data accidentally. In this article, we will check how to recover deleted rows in Snowflake using time travel settings.

Snowflake Recover Deleted Rows

As mentioned, you can recover the deleted records. Snowflake uses the time travel setting to store the historical data.

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.

The time travel also enables you to backup the data that have been deleted including drop tables.

Recover Deleted Records in Snowflake

Following section allows you to recover the deleted records.

Test Data

For the demonstration, we will delete records from E_DEPT table and recover them.

select * from E_DEPT;
+-----+-------------+
| DID | NAME        |
|-----+-------------|
|   1 | Engineering |
|   2 | Support     |
+-----+-------------+

Let us create temp_e_dept table to hold recovered records.

create table TMP_E_DEPT like E_DEPT;

Now, let us delete all records from the E_DEPT table.

delete from E_DEPT;
+------------------------+
| number of rows deleted |
|------------------------|
|                      2 |
+------------------------+

Now, let us recover the records that are deleted 10 mins ago.

select * from E_DEPT at(offset => -60*10);
+-----+-------------+
| DID | NAME        |
|-----+-------------|
|   1 | Engineering |
|   2 | Support     |
+-----+-------------+

Copy the records to temp tables.

insert into TMP_E_DEPT (select * from E_DEPT at(offset => -60*10));
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       2 |
+-------------------------+

select * from TMP_E_DEPT;
+-----+-------------+
| DID | NAME        |
|-----+-------------|
|   1 | Engineering |
|   2 | Support     |
+-----+-------------+

Instead of offset, you can also provide the TIMESTAMP, OFFSET, or STATEMENT.

Note that, if the TIMESTAMP, OFFSET, or STATEMENT specified in the AT | BEFORE clause falls outside the data retention period for the table, the query fails and returns an error.