Data Engineering

Snowflake – Time Travel

Did you ever want to go back in time, to fix a mistake you have made? Did you ever want to go back in time, to see what the exact situation was like at a certain point in time? Did you ever want to go back in time, to start over with all new insights you have gained? While this seems to be science fiction in real life, it is possible when using Snowflake’s data cloud. Their Time Travel feature enables all the above.

Let us take a closer look at the possibilities. Using Time Travel it is possible to consult historical data at a certain point in time after a table has been altered or dropped. In the past, developers had to make sure a backup table was created before any changes took place. Image a scenario where you change the logic behind a table. However, you want to compare to the previous situation after all changes have been completed. This would be a perfect use-case for the AT or BEFORE clause. Using the AT clause, you can give a specific timestamp at which you want to see the situation, or an amount of time before your current moment. The BEFORE clause checks the situation before a specific query was executed, based on its query ID. Using either option can help you create a backup table of an historical situation or to simply compare data.

Another feature of Snowflake’s Time Travel is the ability to undrop lost objects, such as tables, schemas, and databases. This extra safety protects you against unintentionally dropping important data.

Time Travel SQL extensions

As you might know, Snowflake has different editions, depending on the needs of the customer. Even the Standard Edition enables Time Travel, although just for one day. The Enterprise Edition or above enables time travel for up to 90 days. You can change the retention period by changing the parameter DATA_RETENTION_TIME_IN_DAYS. This can be set on multiple levels, depending on your needs. The options are on account, database, schema, or table level. When setting the parameter on multiple levels, priority is given to the lowest level. So, for example, if the retention time on account level is set to 30, while a certain schema within this account has a retention time of 10, all tables existing in this schema will have the 10 days retention period.

However, is there an extra safety when the time travel period has exceeded? The answer is yes! On top of Time Travel, Snowflake keeps your data for an extra seven days after the retention period has closed. This feature is called Fail-safe. That extra safety is only consultable through Snowflake Support. Recovering your data might take several hours to days. Note that transient and temporary tables do not have Fail-safe enabled.

Fail-safe in Continuous Data Protection lifecycle

Whereas Snowflake is very intuitive to use, it is also a very safe option. Using Time Travel (and the extra functionality of Fail-safe) deleted data is not always a lost cause. Even more, you can easily retrieve it without the need of a database administrator, or without the need of taking weekly backups. The execution of one simple query will prevent work-related insomnia. All of this to make sure your data is protected against any mistakes you could make. Unless your mistake is not choosing Snowflake for your data solution.

Do you have any questions regarding your data needs or how Snowflake can help your organization grow? Do not hesitate to contact togaether.