Snowflake features · 8 min read

Snowflake's Time Travel

Query, clone and restore data as it was in the past, using the AT and BEFORE keywords and UNDROP.

What is Time Travel?

Snowflake's Time Travel lets users of the platform access historical data: data that has been updated or deleted. When the allowed Time Travel period has expired, the data enters a state called Fail-safe, where only Snowflake can recover it.

Time Travel benefits

  • Restore tables, schemas and databases that may have been deleted.
  • Duplicate (clone) and back up data that has changed or been deleted.
  • Analyse data usage over specified periods of time.

Accessing Time Travel

Time Travel is used through the AT | BEFORE keywords in SELECT and CREATE … CLONE statements. The UNDROP command restores dropped tables, schemas and databases.

You can pinpoint historical data with the following parameters:

TIMESTAMP

Query the data as it was at a specific date and time.

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

SQL sample

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

-- create a table
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
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: Time Travel with TIMESTAMPOpens on YouTube in a new tab

OFFSET

The time difference, in seconds, from the present time.

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

SQL sample

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

-- create a table
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
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: Time Travel with OFFSETOpens on YouTube in a new tab

STATEMENT

The query ID: an identifier for the statement.

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

SQL sample

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

-- create a table
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
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 with STATEMENTOpens on YouTube in a new tab

Time Travel data availability period

How long historical data stays available depends on your Snowflake edition:

Standard Edition1 day
Enterprise Edition90 days

Need help getting more from Snowflake?

We can help you set up Snowflake and use it well.

Request a Demo