Timetravel in Snowflake

Introduction

Timetravel has become a common functionality in Cloud dataplatforms like Snowflake, Databricks and Microsoft Fabric. It is quite easy to use and it can save you when you have done something that not  is smart to do;-). For instance, when you delete data in a table accidently, you didn't want to delete.

Snowflake has 3 options :

• TIMESTAMP 
• OFFSET
• STATEMENT

With all of these options you can use AT and BEFORE.

What are objects that can be used for timetravel ? :

• TABLE.
• SCHEMA.
• DATABASE.

So I've conducted a couple experiments with Timetravel, in which I explore the different options.

Setup the experiment

First a small setup of the lab situation. This script creates a database with a table and inserts some data in the table. There are two insert statements and between them we want to time travel to.

//============================================================================
// Set context
//============================================================================

ALTER WAREHOUSE IF EXISTS COMPUTE_WH RESUME IF SUSPENDED;

USE ROLE SYSADMIN;
USE WAREHOUSE Compute_WH;

//============================================================================
// Setup database
//============================================================================
CREATE OR REPLACE DATABASE TIMETRAVEL_TIMESTAMP;

USE TIMETRAVEL_TIMESTAMP;

//============================================================================
// setup tables
//============================================================================
CREATE OR REPLACE TABLE CUSTOMER (ID INT, NAME VARCHAR);

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

SELECT * FROM CUSTOMER ;

--Record the time and use this Later
SELECT CURRENT_TIMESTAMP();
--2023-12-14 04:00:36.407 -0800       

--Record the UTC time and use this Later
SELECT CONVERT_TIMEZONE( 'America/Los_Angeles' , 'UTC' , CURRENT_TIMESTAMP())
--2023-12-14 12:00:25.925


-- Wait for 5 a 10  seconds
INSERT INTO CUSTOMER VALUES (4, 'Frank');

SELECT * FROM CUSTOMER ;

Results in :

So, first we have three rows with data and then we have 4 rows at the end of the script.

Timestamp with AT

The first item I want to explain is the AT. The AT keyword specifies that the request is inclusive of any changes made by a statement or transaction with timestamp equal to the specified parameter.

Time travel based on the UTC time

If you don't specify anything, timetravel is based on UTC time.

SELECT * 
FROM CUSTOMER AT (TIMESTAMP => '2023-12-14 12:00:25.925'::timestamp);           --UTC Time

Results in : 

Time travel based on the Local time zone time (LA time)

If you want to timetravel based on your local timezone, you have to specify the LTZ in the timestamp cast.

SELECT * 
FROM CUSTOMER AT (TIMESTAMP => '2023-12-14 04:00:36.407 -0800'::timestamp_ltz); --LA time

Results in :

Time travel based on the Local time zone time (LA time) without timezone info

You don't need to specify the timezone information in the timestamp. In this experiment I removed the  -800. 

-- LA time without timezone information
SELECT * 
FROM CUSTOMER AT (TIMESTAMP => '2023-12-14 12:00:25.925'::timestamp_ltz); 

Results in 

Timestamp with BEFORE

Another option is using the BEFORE with Timetravel.The BEFORE keyword specifies that the request refers to a point immediately preceding the specified parameter.

Here is an example between the two INSERT statements :

--AT  UTC Time
SELECT * 
FROM CUSTOMER BEFORE (TIMESTAMP => '2023-12-14 12:00:25.925'::timestamp);

And this results in :

Here another example (5 minutes later) :

--AT  UTC Time (5 minutes later)
SELECT * 
FROM CUSTOMER BEFORE (TIMESTAMP => '2023-12-14 12:05:25.925'::timestamp);

And this results in :

OFFSET (with AT)

OFFSET is another option with timetravel. Here you can timetravel in a relative manner from the current timestamp. Here is an example:

SELECT * FROM CUSTOMER AT (OFFSET => -60*37) AS T 

Results in  :

STATEMENT (with BEFORE)

STATEMENT is the 3rd option you have with Timetravel. You have to use the id of the query in this options. You can find them in the query history. I've included a couple of steps in this experiment.

//============================================================================
// TIMETRAVEL with STATEMENT
//============================================================================
-- Before the CREATE STATEMENT
select * from CUSTOMER before(statement => '01b0fb67-0103-36c3-0000-72e9003dc0c6');
--Statement 01b0fb67-0103-36c3-0000-72e9003dc0c6 cannot be used to specify time for time travel query.

-- Statement Query id at the CREATE statement
select * from CUSTOMER before(statement => '01b0fb70-0103-36c3-0000-72e9003dc0de');
--Statement 01b0fb70-0103-36c3-0000-72e9003dc0de cannot be used to specify time for time travel query.

-- Statement Query id after the CREATE Statement and at the insert statement
select * from CUSTOMER before(statement => '01b0fb70-0103-36c2-0000-72e9003dd10a');
--Query produced no results

This results in :

Here is the example 

-- Statement Query id after the INSERT Statement
select * from CUSTOMER before(statement => '01b0fb70-0103-36c3-0000-72e9003dc0ea');
--Query Produces rows

Resulting in :

Undrop a object

Yet another interesting option is to use UNDROP statement. If you accidently remove a table you can undrop the table with the UNDROP statement.

DROP TABLE CUSTOMER;

Resulting in :

With this example you can undrop the table :

UNDROP TABLE CUSTOMER;

Resulting in :

Experiment 2 with undrop

And also a nice feature is the shifting (move aside) of a (new) version of a table to another place (rename) and then get the old version of a table back. 

CREATE OR REPLACE TABLE CUSTOMER (ID INT, NAME VARCHAR);

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

DROP TABLE CUSTOMER;

CREATE OR REPLACE TABLE CUSTOMER (ID INT, NAME VARCHAR, BLA INT);

INSERT INTO CUSTOMER VALUES (1, 'Hennie', 100), (2, 'Peter', 200), (3, 'Karen', 300) ;

SELECT * FROM CUSTOMER

-- Show the history of the table
SHOW TABLES HISTORY;

-- Now rename the table
ALTER TABLE CUSTOMER RENAME TO CUSTOMER_Current;

-- Undrop the table
UNDROP TABLE CUSTOMER; 

SELECT * FROM CUSTOMER;

Results in :

Create a clone with timetravel

And the last experiment I want to show is the Clone. So if you want to restore a previous version of a table, you can use the clone option. It just makes a copy of the table (or another object).

CREATE OR REPLACE TABLE CUSTOMER_CLONE 
CLONE CUSTOMER AT (TIMESTAMP => '2023-12-14 12:00:25.925'::timestamp);

SELECT * FROM CUSTOMER_CLONE;

Results in :

Final Thoughts

I wanted to created a complete list of timetravel options and experiments in this blogpost. Let me know what you think.

Hennie