Cloning

CTAS | Deep Clone | Shallow Clone¶

Create Table As Select (CTAS)¶

CREATE TABLE dev.bronze.sales_ctas 
AS 
SELECT * FROM dev.bronze.sales_managed

A new physical location is created and data is now stored in that for the new table.

-- location is different from original table, the physical location is changed.
desc extended dev.bronze.sales_ctas

Deep Clone¶

CREATE TABLE dev.bronze.sales_deep_clone DEEP CLONE dev.bronze.sales_managed;
select * from dev.bronze.sales_deep_clone;
desc extended dev.bronze.sales_deep_clone;

This copies both table data and metadata.

The data is created in new locaiton.

Both delta logs and data copied over

We can see latest version of source from where data from table is copied.

Shallow Clone¶

The table is created at a new location

Only Delta Log copied over the physical data stored at original location.

Inserting Data into original table and see if it reflects in Shallow Clone¶

select * from dev.bronze.sales_shallow_clone

Insert data into main table

Requery shallow clone

We can see only one row the 2^nd row inserted is not visible.

Shallow Clone points to the source version of original table when created and does not update the data.

When we do the vice versa and insert new record in shallow table, it does not impact the original table. The new data is stored in the shallow table's own location.

Only when we VACUUM the main table, all records from shallow table also gets deleted.