This blog pertains to Cloning feature in Snowflake, and I will explain you all the things you need to know about these features with practical example. So let’s get started.

Zero Copy Clone

Cloning also Snowflake as Zero Copy Clone in Snowflake. It used to create a copy of a Table or Schema or a Database. In most database, in order to make a copy of the data, we first need to create all the required structure and then insert the data into these structures. Not so in Snowflake in which cloning makes a copy of the objects at the metadata level.

A snapshot of the data of the source object is taken and is made available to the clone object. The cloned object can exist independent of the original object and both can be modified independent of each other.

Since the cloning of object is metadata operation only, the actual data is not duplicated. Therefore providing savings on the storage cost. This can provide a huge benefit.

The cloned object and actual object shares the same data, as we make changes to the data that sharing will reduce and Snowflake will maintain separate files for the changed data for both the object. This allows us to make changes to the cloned object and the original object independent of each other.

Sample code:

USE DATABASE my_db;

SELECT COUNT(*) FROM sales;

SELECT * FROM sales LIMIT 20;

--Clone sales table
CREATE TRANSIENT TABLE sales_copy CLONE sales;

SELECT COUNT(*) FROM sales_copy;

SELECT * FROM sales_copy LIMIT 20;

--Update data in sales_copy table
UPDATE sales_copy SET Company = 'Knoldus Inc' WHERE Name = 'Sarfaraz';

--Check data from sales_copy table
SELECT * FROM sales_copy WHERE Name = 'Sarfaraz';

--Check data from the sales table
SELECT * FROM sales WHERE Name = 'Safaraz';

--Dropping sales_copy table
DROP TABLE sales_copy;

--Check data from the sales table
SELECT COUNT(*) FROM sales;

--Clone Public Schema
CREATE SCHEMA PUBLIC_COPY CLONE PUBLIC;

This feature of Snowflake is very powerful as it allows to create copies of table or schema or databases and use them for testing and development purposes without incurring any additional storage.

Zero Copy Cloning with Time Travel

Cloning can also be combined with Time Travelto allow some interesting use. This is a very powerful feature of Snowflake which can create a table or database with data as they existed in the past.

Sample code: (Check the blog on Loading Bulk Data into Snowflake and Time Travel to understand the code of loading data into Snowflake table and Time Travel, as I am not repeating here)

#analytics #aws #big data and fast data #cloud #data analysis