We are leveraging Snowflake as the warehouse platform. While loading data to Snowflake , we had some drop create logics for populating the data in snowflake. Instead of using temporary or transient table we accidentally leveraged permanent tables which resulted in huge storage sizes as part of time travel storage(close to 140 tb).
Since we have already dropped the permanent tables these are not listing the snowflake schema. Wanted to check if there is any way we can change the retention period for already dropped tables so that we can release the time travel storage used by these dropped tables.
Many thanks in advance,
Prasanth
If you did not change the default Time Travel retention time, it defaults to one day. If so, you will see the data start to clear out in eight days (1 day Time Travel + 7 days Fail Safe). If you didn't alter it, you may want to just let it age until cleared automatically. If you altered the duration, you can try this to change the retention time. It should work.
If you have new tables with the old dropped table names, you'll need to temporarily rename the new table. Please refer to this KB article for details: KB Article for Time Travel on dropped tables.
After temporarily renaming your new tables, undrop your old tables. You can then set their retention to 0 (be 100% you want this), and then drop the table.
undrop MY_TABLE;
alter table MY_TABLE set DATA_RETENTION_TIME_IN_DAYS = 0;
drop MY_TABLE;
Of course when you re-drop your old tables, you can rename your new transient tables to their proper names. While this should work, you will not see the Time Travel data go away immediately. A background service will delete them at some point.