Snowflake Time Travel: A Window To The Past
--
Let's face it, we make mistakes. Sometimes, even when we're just trying to fix a minor issue, we end up escalating it to an even greater ordeal. Like when we thought we've updated a small chuck of a table and later realised that we missed defining a WHERE clause. Or when we dropped a Production table thinking that we're connected in Dev.
So how do we recover from these? Well, we pull the data from backup, schedule a maintenance window, identify and apply the incremental changes that have happened on the table from when the last backup was taken. Yes, it is not straightforward, time consuming and inconvenient both for the users and developers. 1 mistake can be devastating and will make you say, 'If only I can go back in time'. Well, Snowflake have exactly what you needed.
Not that it will take you back in time — I'm sorry to disappoint, but it will allow you to access a version of a table at a specific point in time in the past.
Time Travel In Action
Now on the fun part. We will force a disaster and see how Snowflake can cope with it.
Let’s look at the TABLE_1 below and note that it has 1M rows.
Now, let’s delete some data from it. Taking out COL3 values that are less than 10 removed a total of 359,994 rows from TABLE_1.
Let’s confirm that all COL3 values that is less than 10 has been deleted.
Great. It’s gone now. But what if that was not intended? How can we recover from this in Snowflake?
