Course contents

SnowPro Core COF-C03 Study GuidePart 5: Data protection and sharingZero-Copy Cloning

Zero-Copy Cloning

Zero-Copy Cloning

Definition

Cloning creates a new database, schema, or table that initially shares the exact same micro-partitions as its source, a metadata-only operation with no data physically copied. Storage cost stays near zero until either the source or the clone is modified, at which point copy-on-write creates new partitions only for the changed data. If the source table changes, the old partitions that a clone still uses stay billed to the source table.

Zero-copy clone of table PROD.SALES. Left, at creation: PROD.SALES and the clone DEV.SALES both point at micro-partitions MP1, MP2 and MP3; nothing is copied and extra storage is zero. Right, after an UPDATE on DEV.SALES that changes rows in MP3: Snowflake writes a new partition MP3 prime for the clone only. PROD.SALES still points at MP1, MP2, MP3; DEV.SALES points at MP1, MP2, MP3 prime. Only MP3 prime is new storage, billed to the clone.AT CREATIONAFTER UPDATE ON DEV.SALESTABLEPROD.SALESsourceMP1MP2MP3CLONEDEV.SALESCREATE … CLONEExtra storage: 0 bytesTABLEPROD.SALESunchangedMP1MP2MP3MP3′newCLONEDEV.SALESafter the UPDATEExtra storage: MP3′ only, billed to DEV.SALES

AT CREATION

TABLEPROD.SALESsource

MP1

MP2

MP3

CLONEDEV.SALESCREATE … CLONE

Extra storage: 0 bytes

AFTER UPDATE ON DEV.SALES

TABLEPROD.SALESunchanged

MP1

MP2

MP3

MP3′new

CLONEDEV.SALESafter the UPDATE

Extra storage: MP3′ only, billed to DEV.SALES

Figure 5.3 A clone copies pointers, not data. Left: at creation both tables point at the same micro-partitions. Right: after an UPDATE on the clone, only the rewritten partition (MP3′) is new storage, billed to the clone.
CREATE TABLE sales_dev CLONE sales_prod;
CREATE SCHEMA dev_schema CLONE prod_schema;
CREATE DATABASE dev_db CLONE prod_db;

-- Combine with Time Travel: clone as of an hour ago
CREATE TABLE sales_recovered CLONE sales_prod AT(OFFSET => -3600);

What does and doesn't carry over

Carries into the cloneDoes NOT carry into the clone
Data (shared micro-partitions)Privileges granted on the database or schema itself
Table structure, constraintsLoad history metadata (files can be reloaded via COPY without being skipped)
Privileges on child objects inside a cloned database/schemaPipes on an internal stage aren't cloned. Pipes on an external stage are cloned but start stopped, and you restart them with ALTER PIPE ... RESUME
Clone ownership → the role that ran the CLONEInternal named stages, unless INCLUDE INTERNAL STAGES is specified, and the files in table stages (a cloned table's stage starts empty)

The privilege rule is the easiest one to get wrong: clone a database, and grants on tables inside it come along, but a GRANT USAGE ON DATABASE prod_db TO ROLE analyst does not automatically apply to the clone. After a database clone, an administrator re-grants access on the new database itself; grants on its schemas and tables come along. After a schema clone, the same applies to the new schema. A single cloned table gets no grants unless you add COPY GRANTS, which puts the source table's grants (all but OWNERSHIP) on the clone.

Exam trap · reloading into a clone

A table is cloned from a source that already had files loaded via COPY INTO. Running the identical COPY INTO against the clone loads those same files again successfully, no skip, because load history metadata isn't copied. This can silently duplicate data if a team assumes clones inherit dedup history the way they inherit rows.

Production ScenarioIsmaFrost · release testing

Friday, 17:00: CREATE DATABASE release_test CLONE prod_db; on a 40 TB production database. It completes in seconds and the storage bill barely moves, because both databases point at the same micro-partitions. The team runs a weekend of destructive migration tests against the clone, and only the partitions their tests rewrite get materialized as new storage.

Monday: DROP DATABASE release_test; and the extra storage evaporates. Total cost: two days of the diff, not two copies of 40 TB. The one re-grant they script every time: USAGE on the cloned database, because grants on the container itself don't survive the clone (schema and table grants do).