Does CREATE OR REPLACE statement affect time travel in snowflake?

4
votes

If I create a table in snowflake and then create another one with the same name using CREATE OR REPLACE statement, I am not able to access the content of the first table using time travel.

For example, if I run this code

CREATE TABLE "MY_DB"."MY_SCHEMA"."MY_TABLE" (COL1 VARCHAR, COL2 NUMBER);   
INSERT INTO "MY_DB"."MY_SCHEMA"."MY_TABLE" VALUES ('A',1);

... and then in five minutes run this chunk of code

CREATE OR REPLACE TABLE "MY_DB"."MY_SCHEMA"."MY_TABLE" (COL1 VARCHAR, COL2 NUMBER);
INSERT INTO "MY_DB"."MY_SCHEMA"."MY_TABLE" VALUES ('B',2);
SELECT * FROM "MY_DB"."MY_SCHEMA"."MY_TABLE"
UNION
SELECT * FROM "MY_DB"."MY_SCHEMA"."MY_TABLE" AT (offset => -60*1)

The query only returns the values from the second table. Is this behavior expected? I tried to google this or find clarification in snowflake documentation without any luck...

Thank you

3
snowflake-cloud-data-platform

3 Answers

4
votes

You can also restore the old table by using time travel at the schema level to the time before you ran Create or Replace Table:

create schema "MY_DB"."MY_SCHEMA_RESTORED" clone "MY_DB"."MY_SCHEMA" AT (offset => -60*1)
4
votes

Update: See @peterb answer "restore the old table by using time travel at the schema level"

As Mike says, the previous table was dropped with the CREATE AND REPLACE, so time travel won't be able to find it.

Nevertheless, you can still recover the data by renaming the newer table, and undropping the previous one:

CREATE TABLE "test_travel" (COL1 VARCHAR, COL2 NUMBER);   
INSERT INTO "test_travel" VALUES ('A',1);

CREATE OR REPLACE TABLE "test_travel" (COL1 VARCHAR, COL2 NUMBER);   
INSERT INTO "test_travel" VALUES ('B',2);

ALTER TABLE "test_travel" RENAME TO "test_travel2";

UNDROP TABLE "test_travel";

SELECT *
FROM "test_travel" AT (offset => -60*1)

enter image description here

https://docs.snowflake.com/en/user-guide/data-time-travel.html#restoring-objects

3
votes

Yes - this is expected. By running a CREATE OR REPLACE, you've dropped the original table and replaced it with a new table. There will be no time-travel data from before the replace table was issued.

If you wish to remove the data from a table and retain the data for time-travel purposes, leverage a TRUNCATE TABLE statement, instead.

https://docs.snowflake.com/en/sql-reference/sql/truncate-table.html