Search code examples
snowflake-cloud-data-platform

Snowflake Time Travel appears to work even with retention days = 0

I created a table with retention days = 0, but looks like time travel is able to get the old data.

create table t1 (
    name string
)
data_retention_time_in_days = 0;

insert into t1 values ('ABC');

select * from t1; -- ABC

truncate table t1; -- 01b7710a-0001-3451-0005-d21e0002e6b2

select * from t1; -- empty

-- Error: Time travel data is not available for table T1.
select * from t1 
before (statement => '01b7710a-0001-3451-0005-d21e0002e6b2'); 

-- Time travel should start from this point of time onwards, 
-- and SHOULD NOT be able to get data prior to this point of time.
alter table t1
set data_retention_time_in_days = 10; 


-- HOW IS THIS STEP WORKING?
select * from t1 
before (statement => '01b7710a-0001-3451-0005-d21e0002e6b2'); -- ABC, works!!!

Thanks!

Solution

  • This was most likely because an appropriate amount of time for the data to be moved from time-travel to Fail Safe had not occurred. Note the documentation that states that this process can take some time because it is a background process.

    Per the documentation:

    If the Time Travel retention period is set to 0, any modified or deleted data is moved into Fail-safe (for permanent tables) or deleted (for transient tables) by a background process. This may take a short time to complete. During that time, the TIME_TRAVEL_BYTES in table storage metrics might contain a non-zero value even when the Time Travel retention period is 0 days.

    In conjunction with this statement that shows that increasing the time-travel retains data that has not been moved to fail-safe:

    Increasing Retention Causes the data currently in Time Travel to be retained for the longer time period.

    For example, if you have a table with a 10-day retention period and increase the period to 20 days, data that would have been removed after 10 days is now retained for an additional 10 days before moving into Fail-safe.

    Note that this doesn’t apply to any data that is older than 10 days and has already moved into Fail-safe.