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.
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.
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!! 🙂