Feature of Snowflake: Zero-Copy Cloning

Zero Copy Cloning in Snowflake:

Snowflakes have some advanced feature like Time Travel, zero-copy cloning and others, those features make things for us Loat easier.

Create copies of a database, schema, or a table for that usually we copied complete structure, metadata, primary keys, schema everything but in snowflake it is very easy using clone command.

We can copy all the data, metadata, structure with just one single command. It is helping when we are developing, and we quickly want to create a clone of database or table.

Why this is called as zero copying cloning?

Cloned objects are independent from original table, so we can update, and it will affect only in clone table, but it will increase some cost. But for creating any clone for object so it will not cost us, and it is cost efficient.

Easy to copy all metadata and improved storage management.

Cost efficient

Purpose:

Purpose for creating a clone for any database or table is like creating a backup for development and it also work with time travel so we care restore data.

Command:

          CREATE TABLE <table_name>

          CLONE <source_table_name>

Additional Rules:

1.Any structure of the object and metadata is inherited like clustering keys, comments.

2.Data storage object (permanent & transient)

Databases, schemas, tables

3.Also clone configuration objects

Stages, File Formats, Task

Let us illustrate this with an example

Step 1: we have one table with some 1000 rows, and we have some development around that table, and we can not take risk to loss data so we can create a clone of that table.

Step 2: let’s update one of the columns from the clone table but it will not affect the original table. If we create a clone table, it will take extra cost from us, but we update anything in the clone table then it will charge extra cost for space.

We cannot create a clone for temporary table with regular clone command we have to add temporary in the command.

 

create temporary table <table_name>

clone < source_table_name>

 

 

we also clone the complete database, schemas and stages in snowflake using clone command.

Step3:  let’s see how we combined time travel and cloning together which is also very useful in snowflake.

We created one table TEST_DATA with some dummy data. If we update any thing in the table and we want to get back our original data by using time travel various methods we can time travel and see original data and if we want to restore that data so we can create a clone table for that, below screenshots shows two methods of time travel feature of snowflake with cloning feature.

 

1.cloning using OFFSET method of time travel.

 

2.cloning using QUERY ID method of time travel.

If we update clone table with some wrong data, so we can also travel back and restore original data and create new clone and store that as well.

 

 

 

 

Swapping Table:

Swapping table is very similar feature like cloning. This is very useful if we want to take development table or development database into production, so we have multiple ways to doing this for example just create a clone or copy of the development table and renamed this to production table, but in snowflake we have much easier method that is swapping the table it is just similar to cloning. Basically, we are just swapping the metadata.

For swapping the table we just use this command

 ALTER TABLE <table_name>

SWAP WITH <target_table_name>

ALTER SCHEMA <schema_name>

SWAP WITH <target_schema_name>

Step4:here we have two tables if we delete some rows in the dev table and we have to get back all data in that dev table then by using swap command we can get back all data into our dev table from prod table

In this way, you can clone the table and use with Time Travel feature as well.

Final Reflections

This brings us to the conclusion about the Snowflake zero copy cloning. This article has taught us what zero copy cloning is and how to use it in Snowflake. Additionally, I have demonstrated to you how to use zero copy cloning with time travel feature of snowflake at table levels. I hope you gained an overview of one of Snowflake’s most significant features.

Please share your thoughts and suggestions in the space below, and I’ll do my best to respond to all of them as time allows.

Refer to the official Snowflake documentation here if you want to learn more.

for more such blogs click here

Happy Reading!!!!