Every database administrator has at some point in their life had the dreaded call: Something went wrong in the production database. Corrupted data was inserted, vital data overwritten, or the very worst of all, an entire table or database was dropped. In traditional database management systems these actions were definitive. Safe for restoring your manual backups – and in the process undoing all work since making them – the data was lost. Thanks to time travel in Snowflake the solution is just a query away.
The basics of time travel in Snowflake
Snowflake offers time travel retention of data up to 90 days for Enterprise editions and higher, up to 1 day for the Standard edition. Within this (user-defined) period, the state of all data-related objects is stored on modification or deletion. This extremely powerful function allows retrieving specific databases, schemas and tables when a mistake is discovered, even if this happens weeks down the line of it happening.
Querying an historical data state is as easy as adding the keywords at or before to a select statement. Specifying a time can be done with either a timestamp or a relative time offset. Alternatively, instead of a timestamp we can provide a past query ID marking the point we want to revert to.
Undropping and undoing replacements
If an entire database or table is dropped, selecting with the at/before keywords will no longer suffice. The object I attempt to select no longer exists, making the request for historical states unclear. Lucky for us, Snowflake still stores dropped objects in the time travel retention layer. Retrieving them is done through the use of the undrop command. Unlike historical selections, undropping will not just show you the historical state but instantly restore the object completely.
Special care is needed however when the dropped or deleted object has already been replaced. Deleting through a create or replace command or manually recreating a dropped object prevents undropping of the original object: The identifier is now in use again, and duplicate objects are not allowed. Time travel through the at/before keywords on the new object will not work either, as it does not share a history with the old object.
The solution here is to rename the new object, thereby making space to undrop the old one. Important to note here is that this can be done multiple times in case of multiple overwrites. Say I have a table A that I have dropped and recreated twice. To retrieve my original table, the following combination of commands will work:
ALTER TABLE A RENAME TO B --Rename my current table A to B
UNDROP TABLE A --Retrieve the previous table A
ALTER TABLE A RENAME TO C --Rename the retrieved table A to C
UNDROP TABLE A --Retrieve the original table AConsiderations and fail-safe
While time travel retention is very useful, storing historical states is not free. All previous states of databases, schemas and tables take up storage, even if they no longer exist in the current state. When dealing with larger datasets, you will need to weigh the retention time needed against the cost to do so. Snowflake allows setting different retention times per object, so finetuning this is very much possible. In addition to setting retention periods manually, Snowflake also offers transient and temporary table types with inherent limited time travel retention.
After the set time travel retention period has passed, all data objects (except transient and temporary tables) will enter into the fail-safe period. These states can no longer be accessed directly by the user. For up to 7 days, this data may be recoverable by Snowflake support in case of extreme operational failures. Note that this is a best-effort practice with no guarantees, and should not be considered a replacement for time travel.
