Time Travel is most powerful feature introduced by snowflake, and it will be discussed in detail in this article.

Introduction to Time Travel

Data projects are complex in nature and there are many scenarios where we, as data engineers, would like to see how data looked like yesterday or at some point in time in history.

To achieve a goal like this, we put a lot of effort and bring the data with some past state and then we start our analysis or debug and that’s what Snowflak’s time travel feature is all about.

Time travel feature in snowflake allows database or schema or tables to preserve all the changes done up to last 90 days and its extended SQL allows a data developer to fetch past state of database or schema or table without any additional burden.

Advantages of Time Travel

There are many use cases and scenario where we need time travel feature desperately and you might have gone through one of them if you have managed a production grade large enterprise complex data projects.

1. You intent to modify data and mistakenly applied those DML operations (insert or update or delete) in production environment and commited them too. After some time, you realized that the changes you wanted to make is not in production and the damange is already done.
2. You have done production hot fix for a data bug and want to perform a regression testing with a state the data from past.
3. You are adding new metrics or KPI to existing data models and would like to validate before and after behaviour, in that case, you want the past state of data to simulate different state of data and validate your result.
4. If you have to restore data for any regulatory or audit purpose.

Time Travel Visual Guided Tour

Watch this 20 minutes visual guide tour from snowflake tutorial playlist and this video chapter will help you understand everything about time travel including

• How time travel works in snowflake
• Time Travel And Data Retention Paramter
• Snowflake Extended SQL for Time Travel
• Time Travel vs Fail Safe Period
• Time Travel for Transitent And Temporary Tables
• Define Time Travel Retention Parameter
• Alter Time Travel Retention Value
• Drop/Undrop Time Travel Feature
• Clone a table using time travel SQL Syntax
• Create table using CAST + Time Travel Extended SQL
• Time Travel Cost - Account usage views

Create Table Using Time Travel

Here is the Snowflake SQL syntax to define data retention period in days. If you don’t add this paramter, it takes 1 day as default value.

1
2
3
4
5
6
7
8
9
10
11
12
13
 create or replace table my_time_travel_table (
        orderkey number(38,0),
        custkey number(38,0),
        orderstatus varchar(1),
        totalprice number(12,2),
        orderdate date,
        orderpriority varchar(15),
        clerk varchar(15),
        shippriority number(38,0),
        comment varchar(79)
    )
    data_retention_time_in_days=3
    ;

If you would like see to the retention period for a table, you can use following SQL statement and this will bring the result as a field value. The describe table does not bring the retention period value.

1
show tables like 'my_time_travel_table';

We generally expect that desc or extended describe (unlike Hive external table), should bring the retention period, however it does not.

If you look into the snowsight modern web UI, it also does not bring the table’s retention period.