What is Time Travel

Snowflake’s time travel is a function that allows users of the platform to access historical data (data that has been updated or deleted). When the allowed Time Travel period has expired, the data enters a state referred to as “Fail-Safe” where the data is only recoverable by Snowflake.

Time Travel Benefits

Allows the user to restore tables, schemas and databases which may have been deleted.

Users can duplicate (create clones) and back up data that has changed or been deleted.

Analyse data usage over specified periods of time.

Accessing Time Travel

Time Travel can be executed by using the AT | BEFORE keywords in the SELECT and CREATE…CLONE statements. The UNDROP command can be used to restore dropped tables, schemas and databases.

This function can be used to pinpoint historical data by using the following parameters:

TIMESTAMP

SELECT * 
  FROM my_table 
  AT(TIMESTAMP => 'Mon, 07 August 2023 16:20:00 -0700'::timestamp_tz); 

SQL Sample:

-- create a databse
CREATE DATABASE abc_traders_timestamp;
USE DATABASE abc_traders_timestamp;

-- create a schema
CREATE TABLE customer_copy AS 
SELECT * 
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER 
  LIMIT 50;

-- query the data
SELECT * 
  FROM ABC_TRADERS_TIMESTAMP.PUBLIC.CUSTOMER_COPY;

-- incorrectly update the data
UPDATE ABC_TRADERS_TIMESTAMP.PUBLIC.CUSTOMER_COPY
  SET C_NATIONKEY = 17;

-- query the data after incorrectly updating it it
SELECT * 
  FROM ABC_TRADERS_TIMESTAMP.PUBLIC.CUSTOMER_COPY; 

-- time travel (current timestamp - number of minutes)
SELECT *
  FROM ABC_TRADERS_TIMESTAMP.PUBLIC.CUSTOMER_COPY
  BEFORE (TIMESTAMP => '2023-08-15 02:23:42.906 -0700' ::timestamp_tz )
  LIMIT 50;

-- query the current timestamp to use time travel
SELECT CURRENT_TIMESTAMP;
--before 2023-08-15 02:23:42.906 -0700
--after 2023-08-15 02:24:11.718 -0700

Video Example:

OFFSET (time difference in seconds from the present time).

SELECT * 
  FROM my_table 
  AT(OFFSET => -60*5);

SQL Sample:

-- create a databse
CREATE DATABASE abc_traders_offset;
USE DATABASE abc_traders_offset;

-- create a schema
CREATE TABLE customer_copy AS 
SELECT * 
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER 
  LIMIT 50;

-- query the data
SELECT * 
  FROM ABC_TRADERS_OFFSET.PUBLIC.CUSTOMER_COPY;

-- incorrectly update the data
UPDATE ABC_TRADERS_OFFSET.PUBLIC.CUSTOMER_COPY
  SET C_NATIONKEY = 2;

-- query the data after incorrectly updating it it
SELECT * 
  FROM ABC_TRADERS_OFFSET.PUBLIC.CUSTOMER_COPY; 

-- time travel (current timestamp - number of minutes)
SELECT *
  FROM ABC_TRADERS_OFFSET.PUBLIC.CUSTOMER_COPY
  AT (OFFSET => -60*0.5)
  LIMIT 50;

Video Example:

STATEMENT (Query ID - an identifier for the statement).

SELECT * 
  FROM my_table 
  BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

SQL Sample:

-- create a databse
CREATE DATABASE abc_traders_query_id;
USE DATABASE abc_traders_query_id;

-- create a schema
CREATE TABLE customer_copy AS 
SELECT * 
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER 
  LIMIT 50;
 
-- query the data
SELECT * 
  FROM ABC_TRADERS_QUERY_ID.PUBLIC.CUSTOMER_COPY;
 
-- incorrectly update the data
UPDATE ABC_TRADERS_QUERY_ID.PUBLIC.CUSTOMER_COPY
  SET C_NATIONKEY = 17;

-- query the data after incorrectly updating it it
SELECT * 
  FROM ABC_TRADERS_QUERY_ID.PUBLIC.CUSTOMER_COPY; 

-- time travel (current timestamp - number of minutes)
SELECT *
  FROM ABC_TRADERS_QUERY_ID.PUBLIC.CUSTOMER_COPY
  BEFORE (STATEMENT => '01ae521b-0202-bc2b-0000-0001d202865d')
  LIMIT 50;

Video Example:

Time Travel Data Availability Period

The period that the historical data will be available for depends on the Snowflake Edition type. A Snowflake Standard Edition has a Time Travel period of 1 day. Enterprise Edition has a 90 day Time Travel period.