Zero-copy cloning in Snowflake - Step-by-Step Execution

Process Flow - Zero-copy cloning

Start: Existing Object

↓

Create Clone

↓

Clone Points to Original Data

↓

Read/Query Clone

↓

If Clone Modified?

No→Use Original Data

Yes↓

Store Changes Separately

↓

Clone Maintains Own Metadata

↓

End: Efficient Storage & Fast Clone Creation

Zero-copy cloning creates a new object that points to the original data without copying it, saving space and time. Changes to the clone are stored separately.

Execution Sample

Snowflake

CREATE TABLE original (id INT, name STRING);
INSERT INTO original VALUES (1, 'Alice');
CREATE TABLE clone CLONE original;
SELECT * FROM clone;
INSERT INTO clone VALUES (2, 'Bob');
SELECT * FROM original;

This code creates a table, clones it without copying data, queries the clone, modifies the clone, and then queries the original to show it is unchanged.

Process Table

Step	Action	Data Stored	Clone Behavior	Result
1	Create original table	Data for Alice inserted	No clone yet	Original table has 1 row
2	Create clone of original	No new data copied	Clone points to original data	Clone appears identical to original
3	Select from clone	Reads original data	No separate storage	Returns 1 row: Alice
4	Insert new row into clone	New data stored separately	Clone diverges from original	Clone has 2 rows: Alice, Bob
5	Select from original	Original data unchanged	No effect from clone insert	Returns 1 row: Alice

💡 Clone creation stops copying data; modifications stored separately to keep original intact

Status Tracker

Variable	Start	After Step 2	After Step 4	Final
original_table_rows	0	1 (Alice)	1 (Alice)	1 (Alice)
clone_table_rows	N/A	1 (Alice, via pointer)	2 (Alice, Bob)	2 (Alice, Bob)
storage_used	0	Data for Alice only	Data for Alice + new Bob row separately	Data for Alice + Bob separately

Key Moments - 3 Insights

Why doesn't creating a clone copy all the data immediately?

Because zero-copy cloning points the clone to the original data without copying it, saving space and time as shown in execution_table step 2.

What happens when we insert new data into the clone?

The new data is stored separately for the clone only, so the original remains unchanged, as seen in execution_table step 4 and 5.

Does querying the clone always read from the original data?

Yes, until the clone is modified. Before modification, it reads original data directly (step 3). After modification, it reads original plus its own changes.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the clone start storing its own data separately?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'Data Stored' and 'Clone Behavior' columns in step 4.

According to variable_tracker, how many rows does the original table have after step 4?

A 2 rows

B 1 row

C 0 rows

D 3 rows

💡 Hint

Look at 'original_table_rows' after step 4 in variable_tracker.

If we inserted data into the original after cloning, what would the clone see?

A The new data immediately

B No new data, clone is isolated

C Only if clone is refreshed manually

D Depends on storage size

💡 Hint

Recall that clone points to original data unless modified, see concept_flow and execution_table.

Concept Snapshot

Zero-copy cloning creates a new object pointing to original data without copying.
Modifications to clone are stored separately.
Original remains unchanged by clone changes.
Clone creation is fast and storage-efficient.
Queries read original data unless clone diverges.

Full Transcript

Zero-copy cloning in Snowflake lets you create a new table or object that points to the original data without copying it. This saves time and storage. When you create a clone, it shares the original data. If you query the clone before changes, it returns the original data. When you insert or update the clone, those changes are stored separately, so the original stays the same. This way, cloning is fast and efficient. The clone maintains its own metadata and diverges only when modified.