What is Snowflake Zero Copy Cloning?

Zero Copy Cloning is a feature within Snowflake that allows the user to create copies of data, without physically duplicating the data within the database. Conventionally if users needed to access data at a given point in time, they would create multiple development environments. Having multiple development environments can be both costly and time consuming for businesses, especially on larger databases.

Snowflake’s Zero Copy Cloning is an innovative feature offered which clones the data, without massive delays, and without any additional cost. Storage fees are not charged since data is not physically copied. The time required for Zero Copy Cloning is significantly reduced compared to creating a copy of a table from an existing table.

How does it work?

Micro-partitions are units of storage in Snowflake, which contain the data stored in tables, each time changes to the table are made, the structure of the micro-partitions change. Without getting much into the metadata component, Snowflake’s Zero Copy Cloning is a metadata operation since it uses the existing micro-partitions to clone the data. It is a quick operation since the data already exists and the clone references the same micro-partitions as the original table. Cloning is possible for the following elements: databases, schemas, tables, streams, stages, file formats, sequences etc.

Understanding your Zero Copy Cloning Procedure

When data is cloned, changes made to either the original source table or the clone, will only be effected on the side that it is made. In other words, making a change on one, does not automatically result in a change to the other. However it is important to note here that when changes are made, the metadata changes and that means there will be additional costs incurred for storage since new micro-partitions will be created. Also of Snowflake’s Time Travel is activated, based on the type of account, then the data can be restored in accordance to the Time Travel period (operations such as Insert, Update and Delete).

Privileges are not inherited in the cloned objects if for example one table is cloned. If an entire database or schema is cloned, then the cloned object will inherit these permissions.

Syntax

CREATE TABLE clone_table_name CLONE source_table_name;

Clone_table_name: Refers to the name given to the table that is going to be cloned and created.

Source_table_name: Is the source table that you wish to base your newly cloned table on.

SQL

DROP DATABASE CLONE_TEST_DB;

-- create a sample database
CREATE OR REPLACE DATABASE CLONE_TEST_DB;
USE DATABASE CLONE_TEST_DB;

-- create a table based on a table from another schema.
CREATE TABLE CLONE_TEST_DB.PUBLIC.ORDERS
AS SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF10.ORDERS;

-- counting the number of records
SELECT COUNT(*) FROM CLONE_TEST_DB.PUBLIC.ORDERS;

-- CLONE OPERATION
CREATE TABLE ORDERS_CLONE_TABLE CLONE CLONE_TEST_DB.PUBLIC.ORDERS;

-- counting the number of records in the cloned table
SELECT COUNT (*) FROM CLONE_TEST_DB.PUBLIC.ORDERS_CLONE_TABLE;

Demonstration

To better understand this concept of Zero-Copy Cloning, we will look at a practical demonstration. We will see how the SQL Query is written, what the clone operation entails and view the results.

In this example we looked at Snowflake's Zero Copy Cloning feature, understood how it works and possible use-cases for it. We hope that this demonstration equips you with the skills and confidence to clone your data, saving you time in the process.