Snowflake : Time travelling

Introduction

In this blogpost I'll dive into the timetravel feature of Snowflake. Timetravel is the way that handles data protection (CDP) of the database. This is another concept than backups, that we were used to do with Oracle or SQL Server. With time travel you can time travel until 90 days with Enterprise Edition. With the ability of timetravel you can perform actions like querying previous version of the state of a table, restore tables, schemas or even databases at specific points in the past. Or you can even restore tables or databases that have been dropped. For these reasons you had to restore a backup from the backup device and perhaps you were dependent on administrators for that and that could be very timeconsuming. Now with a simple statement a simple restore is done.

I've gathered some experiments with Timetravel, below.

Experiments

1. Drop and undrop database

The first experiment is dropping the database and undropping the database. With the UNDROP command the database is restored.

CREATE DATABASE IF NOT EXISTS TIMETRAVELDB;

DROP DATABASE IF EXISTS TIMETRAVELDB;

UNDROP DATABASE TIMETRAVELDB;

2 . Retrieve an earlier version of a table (as a clone)
The next experiment is restoring a previous version of a table. We start with the creation of a table, insert some data and then delete a value. With time travel we retreive the previous version of the table.

USE TIMETRAVELDB;

CREATE TABLE IF NOT EXISTS customer (id int, name varchar);

INSERT INTO customer VALUES (1, 'Hennie'), (2, 'Peter'), (3, 'Karen') ;

SELECT * FROM customer

Resulting in: 

Now, we delete a record with the DELETE statement

DELETE FROM customer WHERE ID = 2;

SELECT * FROM customer

Resulting in :

Now, we restore the table at a particular moment with the CLONE of the table.

CREATE TABLE customer3 CLONE customer
At (timestamp => 'Wed, 02 dec 2020 11:33:00'::TIMESTAMP_NTZ(9));

SELECT * FROM customer3

Resulting in :

3,. Using an Offset to retrieve data

Another option is using an offset. With offset you go back in time from now minus some value in seconds. In the example 1200 seconds is used.

SELECT * FROM customer AT (OFFSET => -1200)

Resulting in :

4. Using an Queryid to retrieve data

It is also query a table at a certain query moment in time. Every query has a query id and you can time travel with this query id.

SELECT * FROM customer AT (statement => '0198a874-00b0-10e1-0000-72e900010322')

Resulting in :

4. Investigating the parameters

Here are some examples that queries the parameters for the time travel parameters.

SHOW Parameters;

SHOW Parameters in database ADVENTUREWORKS;

SHOW Parameters in WAREHOUSE COMPUTE_WH;

SHOW Parameters in account;

SHOW Parameters LIKE 'DATA%' IN ACCOUNT

SHOW Parameters LIKE 'DATA%'  in database ADVENTUREWORKS;

5. Setting the time travel parameter

Next, here are some examples on setting the time travel properties : DATA_RETENTION_TIME_IN_DAYS

ALTER DATABASE ADVENTUREWORKS SET DATA_RETENTION_TIME_IN_DAYS = 2;

ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 2;

Final thoughts

This blogpost is about timetravelling. I've gathered some examples that may come in handy for using in Snowflake projects.

Hennie