Photo by Zdeněk Macháček on Unsplash

On Snowflake’s zero-copy clones

Snowflake ships with many useful features with my top 4 being

• UNDROP — revives dropped table,
• ability to choose compute on per-query basis,
• unused compute auto-suspension,
• zero-copy clones of the objects.

In this article we will take a closer look at the last feature on that list. To give you some context, just imagine facing a problem of running relatively risky experiment against production data. You were asked to clone a database with hundreds of terabytes of data (the change is going to affect many tables). I can feel the pain. And imagine doing this over and over again if the experiment didn’t work out well. If only there was a way of taking a fast snapshot of the database…

Fortunately for us, more and more data warehouses support zero-copy clones and that’s exactly what we will be looking into.

What’s a zero-copy clone?

Let’s first explore the idea of copy-on-write. In programming, sometimes there is no use to eagerly copy the data. All read-only operations working with the same data (possibly in parallel) can refer to the same memory address safely. When the operation wants to mutate the data, it can just copy it and work with a clone. In general, usually there is no point in copying the whole dataset if the mutation is limited to a certain, well defined part. For instance, if you worked with whole a database and mutated a single row in one of the tables, the system may copy just a smallest possible physical unit of data that stores that row.

Zero-copy clone is built on this idea. Creating a clone does not copy the data — there is no need to. Instead, you can think of this clone as a point-in-time snapshot. Both tables point to the same physical files on the object storage and, as these objects are immutable, operations on any of those tables would create new objects with data that has changed since making a clone. This works in both directions — changes to original table have no effect on the clone and vice versa. A clone is essentially a metadata copy and it doesn’t even involve a Snowflake warehouse (compute).

Note that this idea can apply to tables, schemas and databases. In Snowflake, you may also zero-clone stages, sequences, streams, tasks and pipes. You can read about the limitations here.

Snowflake isn’t the only warehouse that supports zero-copy clones. BigQuery has both Table Clones and Table Snapshots (read-only), Databricks has shallow clones which are somewhat similar concept but are meant to be short-living unlike Snowflake clones.

Micropartitions

The smallest unit of physical storage in Snowflake is a micro-partition. It contains 50 to 500 MB of uncompressed data (though the object itself will always be compressed). This means that 50 TB table will be represented by about 50 * 1024 ^ 2 / ((50 + 500) / 2 MB) ~= 190 651 pointers.

As you may know, data in Snowflake is stored in a columnar format. For each micropartition

• Snowflake stores data of certain set of rows stored as columns,
• each column may be compressed using different algorithm (moreover, a single column may be compressed differently in different micropartitions. In extreme cases, some micropatitions may store just a constant value for all rows, some may store unique values for each row etc.),
• Snowflake stores metainformation about columns value ranges for better partition pruning.

Micropartitions doesn’t have any predefined partition key (unlike static partitioning as introduced by Hive). Instead, micropartitions are created based on event ingestion order to Snowflake. This may look suboptimal at a glance but in reality it works really well. By storing metadata for pruning and keeping the files relatively small, this works extremely well in practice.

How Snowflake zero-copy clone works?

Now that you are aware of micropartitions you should understand that a clone is just a copy of metadata describing micropartitions of the original table. DML operations on original and cloned tables would either tweak metadata (these are separate) or create new micropartitons (these won’t be shared).

You might be wondering how does this work in conjunction with data retention. Zero-copy clones seem conneted to snapshots and point-in-time travel capabilities of Snowflake. Enterprise edition allows for time travel for up to 90 days, afterwards Snowflake will vacuum unused micropartitions. Fortunately, there is caveat to this — if a micro-partition is referenced and still used by a clone, it won’t be removed. Nonetheless, the data will not be available for original table anymore.

Zero copy clone storage costs

Let’s imagine you have a table with 100 micropartitions and you clone it. As the data is not copied, you will pay only for the 100 micropartitions. If you then make modifications to original table that created 5 new micropartitions, you have 105 micropartitions in total and that’s what you pay for.

If you then made the same modifications to the cloned table, this would create new, completely separatate 5 micropartitions. You’d have to pay for them too. So the total cost of data in original table would correspond to 105 micropartitions and 5 in cloned table — 110 in total.

To reduce costs in this trivial scenario, you could drop existing clone and replace it with a new one — landing at total cost of 105 micropartitions.

Common zero-copy patterns

Dev / staging / QA based on production data

In certain types of projects, for faster iterations, you may consider setting up your dev / staging or QA environments based on production data with zero-copy clones. By coping the whole database, you can

• develop new data products (e.g. in form of tables or views) and promoting them to other environments with zero-copy clones,
• easily keep it in sync with production data with periodical clones back from production.

Setting up DEV enviroment is a matter of executing CREATE DATABASE DEV CLONE PROD .

Let’s imagine your team created YEARLY_ORDER_SUMMARY view in DEV.FINANCE schema. You can promote it into staging with CREATE TABLE STG.FINANCE CLONE DEV.FINANCE . The same rule applies to other environments.

With certain data, this procedure will require a proper strategies in place to work with PII and other sensitive information. Snowflake supports masking policies that are useful in scenarios like this.

Ad-hoc bug fix in production / setting up experimental environment

Sometimes engineers are forced to fix things in production. It isn’t any different for the data world. To limit the risk with zero-copy you can follow this framework

1. Zero-copy clone production object (table, schema, database — aim to choose the smallest unit that is necessary),
2. Alter the data,
3. Verify the data in cloned object,
4. Replace production data with zero-copy back to original object.

This could look as follows

-- clone
CREATE TABLE ORDERS_FIX CLONE ORDERS COPY GRANTS;
-- fix (assuming the column was inflated by VAT)
UPDATE TABLE ORDERS_FIX SET AMOUNT_NO_VAT = AMOUNT_NO_VAT / VAT;
-- verify (should return 0)
SELECT count(1) FROM ORDERS 
 WHERE AMOUNT_VAT <> AMOUNT_NO_VAT * VAT
-- replace production table
CREATE OR REPLACE TABLE ORDERS CLONE OREDRS_FIX COPY GRANTS;

Useful commands

1. By default cloning a table or a view doesn’t clone their grants. In order to clone a table with the grants add COPY GRANTS option to the command:

CREATE OR REPLACE TABLE [cloned table] CLONE [orignal table] COPY GRANTS

Note: cloning schema and database does clone grants.

2. One of the useful patterns while working with zero-copy clones are bug fixes to production database. Let’s say you copied your ORDERS table as ORDERS_CLONE and applied necessary modifications. There are two ways for you to zero-copy replace production table

CREATE OR REPLACE TABLE ORDERS CLONE ORDERS_CLONE [COPY GRANTS]
ALTER TABLE ORDERS SWAP WITH ORDERS_CLONE

The first option indeed does a zero-copy clone and drops existing ORDERS table. You can either use COPY GRANTS options to use the grants of the cloned table or you should add appropriate grants after the table was created. Note that you probably don’t need the original table anymore so the drop is not that scary. Anyways, Snowflake supports UNDROP command in case you realized that you indeed need it.

Alternatively, you can use SWAP WITH command. Essentially what it does are two ALTER TABLE RENAME TO statements executed in one transaction. Thus, no tables will be dropped and the grants of clone would become the grants of production table automagically.

Conclusion

We explored zero-copy clones in Snowflake. You should now be familiar with basic use cases for this operation. Although I used Snowflake as an example, the concept applies to many other projects out in the data space.

We also peeked at internals of Snowflake and you should be more aware of how the data is physically organized in the warehouse with micropartitions.

As you can see, the superior performance of zero-copy clones does not come from supernatural powers of the Snowflake team, rather it is based on well-known principle of copy-on-writes.

Be aware of grants associated with both cloned and swapped tables — remember it is on you to explicitly do something if working with view and table clones.