Snowflake
Zero-Copy Cloning
Instantly create full database, schema, or table copies without duplicating storage.
Cloning a production database in Snowflake takes seconds and costs nothing, regardless of size.
It's like giving someone a symbolic link to your entire file system that only starts consuming extra disk space once they begin editing files — until then, it's free to create.
Key Concepts
1
CREATE DATABASE dev CLONE prod;2
That is a full, independent, writable copy. A 50 TB source makes no difference to how long it takes.
3
Why it is instant. Micro-partitions are immutable, so two tables can safely point at the same files.
The clone copies metadata — the list of which partitions make up the table — not the data.
4
prod.orders -> partitions 1, 2, 3, 4
dev.orders -> partitions 1, 2, 3, 4 (same files)5
Storage grows only where they diverge. Change a row in dev and Snowflake writes a new partition that
only dev points at. Everything unchanged is still shared:
dev
6
dev.orders -> partitions 1, 2, 3, 4' (4' is new, the rest shared)7
So a development copy of a 50 TB warehouse costs the size of the changes you make to it, not 50 TB.
8
What this replaces. Building a realistic test environment normally means an export, a transfer, a load,
and a scheduled refresh — hours of pipeline, and a copy that is always stale.
9
Here it is one statement, it is current as of this second, and it can be thrown away and recreated every
morning.
10
The uses worth naming in an interview.Dev and test from real data, refreshed daily and dropped afterwards.
11
A safety net before a risky migration. Clone the table, run the change, and if it goes wrong the clone is
the original.
12
Reproducing a bug, by combining cloning with Time Travel:
13
CREATE TABLE debug CLONE orders BEFORE(STATEMENT => '01a...');14
That gives you the exact state before the offending statement, as a real table you can query and modify.
15
What does not come with the clone. The data and structure do. Grants on the cloned objects generally do
not — access has to be granted again, which people discover when the new environment is unreachable. And a
clone of a table with a clustering key may be reclustered independently, which is a separate cost.
16
It is a snapshot, not a mirror. Once created, the clone is independent. Later changes to production do
not appear in it. For live shared access to current data, that is data sharing, not cloning.
17
What the interviewer is probing.1. "What makes it zero copy?" Probing: the mechanism. Stalls: "It is fast." Moves up: the
clone shares the same micro-partitions by metadata reference; storage is only consumed as either
side changes.
18
2. "What is the main use case?" Probing: the practical value. Stalls: "Backups." Moves up:
instant full-size dev, test and staging environments from production, and a safe snapshot before a
risky migration.
19
3. "What is not cloned?" Probing: the detail. Stalls: "Everything is." Moves up: external
tables' data, internal stage contents, and some object-level grants depending on the object type.
20
4. "Does a clone diverge from the source?" Probing: the semantics. Stalls: "It stays in
sync." Moves up: yes — it is a point-in-time independent object; later changes to either side do
not affect the other.