Cloning, also referred to as “zero-copy cloning” creates a copy of a database, schema or table. A snapshot of data present in the source object is taken when the clone is created, and is made available to the cloned object. The cloned object is writable, and is independent of the clone source. That is, changes made to either the source object or the clone object are not part of the other.
Syntax:
Clone a database and all objects within the database at its current state:
- CREATE DATABASE testdb_clone CLONE testdb;
Clone a schema and all objects within the schema at its current state:
- CREATE SCHEMA testschema_clone CLONE testschema;
Clone a table at its current state:
- CREATE TABLE testtable_clone CLONE testtable;
Before get to know more about cloning, we need to understand how to calculate table size in snowflake. Click Here to know more about table size calculation.
Example:
In this explanation we are going to take table TRIPS as an example.
Table row count: 61468359. Refer below screenshot
Storage occupied by Table TRIPS in GB: 1.8 GB (Approx.)
Now we will see two different methods to understand how Zero Copy cloning works,
Scenario 1: (Copy Method)
Table Script:
CREATE OR REPLACE TABLE TRIPS_NORMALCOPY AS SELECT * FROM TRIPS;
Table Script:
SELECT COUNT(*) FROM TRIPS_NORMALCOPY;
Total space occupied by table TRIPS_NORMALCOPY :
Scenario 2: (Cloning Method)
Table Script:
CREATE TABLE TRIPS_ZEROCOPYCLONING CLONE TRIPS;
Table Script:
SELECT COUNT(*) FROM TRIPS_ZEROCOPYCLONING;
Total storage occupied by table TRIPS_ZEROCOPYCLONING:
Result:
In both the methods we could see no of rows copied/cloned is same and difference in storage occupied by the table. Table (TRIPS_ZEROCOPYCLONING) occupied 0 bytes of storage which we cloned from main table TRIPS.
Points to Remember:
To create a clone, your current role must have the following privilege(s) on the source object:
Tables: SELECT
Pipes, Streams, Tasks: OWNERSHIP
Other objects: USAGE
Advantages of zero copy cloning in Snowflake ?
Here are some of the benefits of zero copy cloning:
- Zero Copy cloning is super fast, often referred to “Fast Clone” in Snowflake
- Zero Copy Cloning saves you in Storage costs — Any DML update to the cloned table will result in creation of new partition which result in storage cost for new added partition.
We can leverage time travel with cloning which we will see in future post :)
References:-