I really like the Time Travel feature in Snowflake. It is very handy and absurdly simple. If you have incorrectly updated one of your Snowflake tables and if you know that your table was in the correct state at 9 am on Sun, Oct 13, 2019, then you can simply run the query:

SELECT * FROM TABLE_NAME AT(TIMESTAMP => 'Sun, 13 Oct 2019 09:00:00 -0800'::TIMESTAMP);

where -0800 refers to your time zone in relation to UTC.

However, this query will not work if you dropped or replaced the table altogether; it only works if you modified or deleted the rows in the table.

Time Travel for Dropped Tables

For dropped or replaced tables, Time Travel provides another way: the UNDROP command. But, hang on, even this command will not work directly, as a recent experience with our ETL Pipeline illustrates.

Say your table is called LOCATIONS, and you ran a CREATE OR REPLACE LOCATIONS query that ended up dropping the correct table and replaced it with an empty table. This happened to us because of an incorrect incoming file in the ETL pipeline that slipped through a validation step. Say, moreover, that the process ran twice before you realized that your LOCATIONS table is empty!

The way the UNDROP command works, it first checks the existing list of tables in the current schema. If the command does not find the table, it will look for the most recent dropped or replaced table with the same name in History.

Since you already have a LOCATIONS table in your schema, the query UNDROP TABLE LOCATIONS, will return an error:

Time travel data is not available for table LOCATIONS.

To allow the UNDROP command to find the replaced LOCATIONS in History, you will need to rename the current table:

ALTER TABLE LOCATIONS RENAME AS LOCATIONS_EMPTY;

Now run the UNDROP TABLE DIM_LOCATIONS query, which will retrieve the most recent table named DIM_LOCATIONS from History. Since you ran the incorrect query twice, even this table will turn out to be empty – Oops!!

Travel Back … Again

That’s right. Rinse and Repeat. Once again, change the name of this table:

ALTER TABLE LOCATIONS RENAME AS LOCATIONS_EMPTY_2;

Run the UNDROP TABLE LOCATIONS a second time. You will now get the correct table. The two REPLACE commands had to be undone with two UNDROP commands. Simple, right? This is explained with more examples in the Snowflake documentation.

You can now relax and tell your boss that everything’s fine!

For another example of the brilliance of Time Travel, see how we recovered an entire database.

Snowflake Data Retention Time

Remember that Time Travel can only retrieve your data if it is within the retention time that has been configured for your Snowflake instance or the particular database object. Time Travel comes with several more commands and options such as CLONE and STATEMENT, which you can check out in the documentation.