CODEWITHSUNDEEP
snowflake-cloud-data-platform
My80
My80

Reputation: 169

Snowflake time travel

I would need to create tables with use of snowflake time travel but not to drop tables and create new objects. I'm looking for most efficient way to do that.

Example:

CREATE OR REPLACE TABLE_SCHEMA.TABLE_NAME AS SELECT * FROM TABLE_SCHEMA.TABLE_NAME at(timestamp => '2020-11-01 07:00:00'::timestamp);

In snowflake documentation I found out that by running a CREATE OR REPLACE, the original table is dropped and replaced with a new table. There will be no time-travel data from before the replace table was issued. If I need to remove the data from a table and retain the data for time-travel purposes, I can use TRUNCATE TABLE statement. My question is shoud I use TRUNCATE or CREATE OR REPLACE? Thank you in advance.

Upvotes: 3

Views: 3772

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

The simplest approach is INSERT OVERWIRTE INTO:

INSERT OVERWRITE INTO TABLE_SCHEMA.TABLE_NAME 
AS 
SELECT * 
FROM TABLE_SCHEMA.TABLE_NAME at(timestamp => '2020-11-01 07:00:00'::timestamp);

I would also like to know how overite effects time travel...historical data?

It is still possible to access data before INSERT OVERWRITE as long as it is within data retention period.

CREATE OR REPLACE TABLE TAB(id INT) AS SELECT 1 UNION SELECT 2;

-- original table
SELECT * FROM TAB;
-- 1
-- 2

UPDATE TAB SET id = id * 10;
SET queryupdate = LAST_QUERY_ID();

-- after update
SELECT * FROM TAB;
-- 10
-- 20

-- restoring state before update
INSERT OVERWRITE INTO TAB SELECT * FROM TAB BEFORE(STATEMENT => $queryupdate);
SET queryinsertoverwrite = LAST_QUERY_ID();

-- current state
SELECT * FROM TAB;
-- 1
-- 2

-- state before update
SELECT * FROM TAB BEFORE(STATEMENT => $queryupdate);
-- 1
-- 2

-- state before insert overwrite
SELECT * FROM TAB BEFORE(STATEMENT => $queryinsertoverwrite);
-- 10
-- 20

Upvotes: 4

Related Questions