UNDROP recovers a table created with no retention time

user_453238 0
Viewed 100

I create a table with explicit DATA_RETENTION_TIME_IN_DAYS zero, i.e. no time travel. UNDROP on a dropped table should not restore the table. However, it does!

create or replace table t1 (col1 string)
  DATA_RETENTION_TIME_IN_DAYS=0; -- yes, no time travel please
insert into t1 values ('abc');
drop table t1;
undrop table t1; -- hey, this works!
select * from t1; -- this returns my table with the row inserted!
snowflake-cloud-data-platform
1 Answers

DROP TABLE will remove any explicit DATA_RETENTION_TIME_IN_DAYS you have set. The dropped table will now inherit whatever similar parameter value you have in the parent schema, database, or account. This is usually 1 by default. Or, in my case, it was 5 at the database level. Run show tables history like 't1'.

This means you've automatically got time travel, and all your data will now be saved, only when the table is dropped, with no other previous history data.

If you intend to drop tables and to rely on UNDROP, you should rather set the retention time parameter at the schema or database level:

create or replace database db1
  DATA_RETENTION_TIME_IN_DAYS=0; -- no time travel for any table here!
create or replace table t1 (col1 string); -- inherited, as zero
insert into t1 values ('abc');
drop table t1;
undrop table t1; -- now this fails, as expected

UNDROP fails now, as expected, with "Table T1 does not exist or was purged". Because t1 still inherits retention time 0 from its parent database.

Later Edit: if your database has no time travel, but your table has, expect to lose not just all historical data when you drop the table, but all table data as well. Which can not be restored.

user_4532380
Related