Restoring deleted rows in Snowflake
Losing important data is a nightmare, especially if we talk about production databases. But don’t worry! You can restore deleted rows in Snowflake with just a few steps
By default, the maximum retention period for a table is 1 day, that can be changed but you will not be able to use timetravel for a datetime before the retention period.
Step 0. Creating The Dataset
We are going to proceed to use the following dataset.
Step 1. Deleting Information From The Dataset.
For this step we need to delete information from the table with the following query.
After checking that the table is empty we can proceed with the following Step
Step 2. Using Timetravel To Recover Information.
In Snowflake, TimeTravel allows users to retrieve data from a previous point in time by querying a version of a table that existed at that point.
You can do it by using AT | BEFORE, If you decide to use AT you will have to use a timestamp or an offset, otherwise you can use Before and use the query Id for checking the information that we had on the table before running the delete query.
There are two options for doing it.
If you are going to use any of them, first I recommend using the query history and identify the query that deleted the information from your target table.
For accessing it, if you are on Snowsight, go Main > Activity > Query History, then filter the queries by your user and if you wish you could add a “Sql Text” filter with ‘Delete’ sentence on it.
As you can see on the picture, I executed the query at “2/4/2023, 10:58 PM” and it has a Query ID of “01aa1e52–0000–0f27–0000–00001ff160a9”, you can access that information by clicking on the query that you want to explore.
Option 1
For this case, if you want to retreive the information that we had before running the delete query, you have to target some minutes before running the delete query.
For the timestamp, I used -04:00 due my timezone since my local time is UTC -4.
The following query returns the same information, the only difference here is that I am using offset -60*45, that means that it is checking the information that we had on that table 45 min ago.
This query do the same, but instead of using an offset or a timestamp, we are using the query ID to retreive the information from the table before running the delete query.
For restoring the information that we had before running this statement you can insert those elements back into the table.
That is going to restore the information from the table.
If you only deleted or updated some records and you want only to restore the affected records, you can check this article on How To Compare Two Tables In Snowflake, then identify the differences between both tables and insert/update the required elements depending on your needs.
Option 2
Another more radical option is to clone the table is by using the following query.
This query is going to clone the table that we had before running the delete query.
Then you can replace this new table for the one that has changes by dropping the original table and renaming it.
This is going to restore the table with the information that we had before running that query.
It is the fastest option but you will lose some transaction history by doing it.
Conclusion
In conclusion, restoring deleted rows in Snowflake is a simple process, thanks to timetravel. Whether you have accidentally deleted data or you need to view historical data, TimeTravel provides an easy way to retrieve the data that was present in the database at a specific point in time.
