Mastering Zero-Copy Cloning for Effortless Data Environments in Snowflake

Thank you for reading my latest article Mastering Zero-Copy Cloning for Effortless Data Environments in Snowflake.

Here at LinkedIn I regularly write about modern data platforms and technology trends. To read my future articles simply join my network here or click ‘Follow’. Also feel free to connect with me via YouTube.

Introduction

This excerpt is taken from my top-rated SnowPro Core Study Guide, where I discuss the powerful benefits of zero-copy cloning in Snowflake, a game-changing feature for creating fully functional data environments instantly, without duplicating data.

Zero-copy cloning, or simply cloning, allows you to create a copy of nearly any database object along with its data quickly and easily and then transfer it into another environment. For example, you can create a fully working test database, complete with data, in minutes, at zero cost!

The main reason for this is that there is no physical movement of data in the background. It’s a metadata operation, meaning when you clone an object it creates a copy of the object with a pointer back to the original data. If a query changes the original data that the clone was based on after you created it, it will not affect the data in the clone. Similarly, you are free to manipulate the data in the clone and this won’t affect the original object it was based on. It will, however, start to incur storage costs based on the data you have changed.

The primary benefit of cloning is that it facilitates the creation of development or test environments as part of an overall development lifecycle approach. Traditionally, you’d have had to create the new database environment or object before writing some code to copy the data physically into the new environment. You would also have needed to devise a process to keep the data up to date.

Are you starting to see how much easier and quicker the same process might be in Snowflake using cloning?

Which objects can be cloned?

These objects can be cloned in Snowflake:

• Databases
• Schemas
• Tables
• Streams — Any unconsumed records in the stream are not available for consumption in the clone. The stream begins again at the time/point the clone was created.
• External named stages — This has no impact on the contents of the external storage location. It is simply the pointer to the storage location that is cloned.
• File formats
• Sequences — When a table with a column with a default sequence is cloned, the cloned table still references the original sequence object. You can instruct the table to use a new sequence by running the following command:

ALTER TABLE <table_name>

ALTER COLUMN <column_name> SET DEFAULT <new_sequence>.nextval;

• Tasks — Tasks in the clone are suspended by default, and you must execute the ALTER TASK…RESUME statement.
• Pipes — A database or schema clone includes only pipe objects that reference external stages (Amazon S3, Google Cloud Storage, or Microsoft Azure).

These objects cannot be cloned:

• Internal (Snowflake) stages
• Pipes — Internal (Snowflake) pipes are not cloned. A cloned pipe is paused by default.
• Views — You cannot clone a view directly. A view will be cloned if it is contained within a database or schema that you are cloning.

You must take note of which objects can and cannot be cloned. It is highly likely you will get some questions about cloning, so make sure you understand the basic concepts well.

To stay up to date with the latest business and tech trends in data and analytics, make sure to subscribe to my newsletter, follow me on LinkedIn, and YouTube, and, if you’re interested in taking a deeper dive into Snowflake check out my books ‘Mastering Snowflake Solutions’ and ‘SnowPro Core Certification Study Guide’.

About Adam Morton

Adam Morton is an experienced data leader and author in the field of data and analytics with a passion for delivering tangible business value. Over the past two decades Adam has accumulated a wealth of valuable, real-world experiences designing and implementing enterprise-wide data strategies, advanced data and analytics solutions as well as building high-performing data teams across the UK, Europe, and Australia.

Adam’s continued commitment to the data and analytics community has seen him formally recognised as an international leader in his field when he was awarded a Global Talent Visa by the Australian Government in 2019.

Today, Adam is dedicated to helping his clients to overcome challenges with data while extracting the most value from their data and analytics implementations. You can find out more information by visiting his website here.

He has also developed a signature training program that includes an intensive online curriculum, weekly live consulting Q&A calls with Adam, and an exclusive mastermind of supportive data and analytics professionals helping you to become an expert in Snowflake. If you’re interested in finding out more, check out the latest Mastering Snowflake details.