These techniques come from the Snowflake Architecture and SQL book by Tom Coffing and David Cook.
Time travel is a Snowflake feature that displays data at a specific point in time. For example, users use the time travel feature for restoring data when they accidentally update or delete data incorrectly.
In the example below, we made a mistake on an update statement. There are multiple techniques to go back in time, but our example below shows how we can display the GRADE_PT three minutes previously. Snowflake proves you can go back in time.
In the picture below, we have again made a mistake with our update statement. Momma always told me, "Updates are fun until someone misses a WHERE clause." No worries because we will use a different time-travel technique to fix it.
The example below shows how to use time travel using a timestamp to display the results.
I will show you a technique that changes the table to its original form using time travel. Notice that we forgot our WHERE clause in the update. Oops! Since I am on CST time, I alter my session and set the timezone to 'America/Chicago.' I select the current_timestamp and get my result. I then make sure the FIRST_NAME column is correct using the time travel BEFORE statement. Once I am confident with the results, I truncate the original table (I will show you a better way soon), but I then insert it into my truncated table using the correct data. Thank you, Snowflake. I get to keep my job.
I will now show you a third way to use time travel. Below is an example of me making another mistake on an UPDATE statement.
It is time to fix the problem and restore the data to perfection. In the example below, I realize I made a mistake on the UPDATE statement. I can then run a query to SELECT last_query_ID(). I am then verifying that I can restore the LAST_NAME column using time travel with the BEFORE statement containing the query id of the update statement.
I will now return the table to its state before the UPDATE went wrong. The technique below creates a BACKUP table with the correct data using time travel and the query id. Once I verify the BACKUP table is correct, I can truncate the STUDENT_TABLE, thus removing the rows, and then do an INSERT/SELECT from the BACKUP table. I can then drop the BACKUP table.
If you want to try the only query tool that queries Snowflake, migrates data to Snowflake, and joins Snowflake tables with all other database platform tables, then download a free trial of Nexus at CoffingDW.com.
Here is a video of Nexus migrating from Teradata to Snowflake. https://www.youtube.com/watch?v=_9ABivdwJPk&t=19s.
If you want the entire Snowflake book or would like Tom Coffing to teach your company in a 2-5 day Snowflake class, please contact Tom at Tom.Coffing@CoffingDW.com.