Reading Time: 2 minutes

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 Travel to 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)

ALTER SESSION SET TIMEZONE = 'UTC'

--Select the current timestamp
SELECT CURRENT_TIMESTAMP;

--Copy the timestamp somewhere given by the query
--2020-08-02 11:04:34.068 +0000

USE DATABASE my_db;

SELECT COUNT(*) FROM sales;

--Load new data into sales table using the COPY command
COPY INTO my_db.public.sales FROM @my_db.external_stages.my_ext_stage
FILE_FORMAT = (FORMAT_NAME = 'my_csv_format')
ON_ERROR = 'skip_file';

SELECT COUNT(*) FROM sales;

--Clone sales table before loading new records using the timestamp
CREATE TRANSIENT TABLE sales_DEV CLONE sales BEFORE(TIMESTAMP => '2020-08-02 11:04:34.068'::timestamp)

SELECT COUNT(*) FROM sales_DEV;

--Clone sales table before loading new records using Query ID
CREATE TRANSIENT TABLE sales_DEV1 CLONE sales BEFORE (STATEMENT => '01949934-002f-2c7c-0000-00001df58551');

SELECT COUNT(*) FROM sales_DEV1;

This is all from this blog. Hope you enjoyed the blog and it helped you!! Stay connected for more future blogs. Thank you!!