Snowflake Cloning Facts

Read Time:2 Minute, 26 Second

During this post we are going to discuss some interesting facts about cloning. As we know Snowflake’s Zero Copy Cloning feature offers a fast and straightforward method to duplicate tables, schemas, or entire databases. Importantly, this process incurs no extra costs, as the created copy utilizes the same underlying storage as the original object. Have we considered the repercussions on the underlying CLONE table if we decide to drop the original table? There are some prevalent misconceptions in this regard.

One common assumption is that once the Time Travel and Failsafe period for the original table expires, the respective Clone table would become empty.
Another misconception is that the CLONED table would now possess the micro-partitions of the original table

Interestingly, the answer to the above questions is ‘NO’. The importance of the “RETAINED_FOR_CLONE_BYTES“ column in the TABLE_STORAGE_METRICS View is more pronounced. As per the definition:

RETAINED_FOR_CLONE_BYTES: Bytes owned by (and billed to) this table that are retained after deletion because they are referenced by one or more clones of this table.

Lets try to understand with example:

After dropping the CUST_TABLE, the table had a data retention period of 1 day, so after 24 hours, it transitioned to Failsafe. Confirm the details about my table using the following query:

select * from information_schema.table_storage_metrics where table_name = 'CUST_TABLE';

We are still able to query the data into CLONE table which is as per expected behavior.

Now after the 7 days of FAILSAFE goes over, our original table is not available inside information_schema.table_storage_metrics view.

We conducted parallel verification in the CLONE table, and it continues to retain the records without any impact on the record count; the table was already populated.

This raises the question of how the CLONE table still holds records even when the original table no longer exists in the system. Does this imply that we are no longer incurring storage costs for the CLONED data? Additionally, this presents the clever idea that we can clone large tables, drop the original table, and consequently save on storage costs.

Certainly, Snowflake is intelligent enough to retain information about your cloned table even if you drop the original table. The details of original table are stored in the snowflake.account_usage.table_storage_metrics table, and the RETAINED_FOR_CLONE_BYTES column clearly displays this information.