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:
