📖

Key Terms — Zero-Copy Cloning

—

Zero-Copy Clone

A metadata-only copy of a database, schema, or table that references the same underlying micro-partitions. No data is physically duplicated — instant and free at creation.

—

CREATE ... CLONE

The SQL command to create a zero-copy clone of a database, schema, or table.

—

Clone + Time Travel

Cloning AT or BEFORE a specific point in time, creating a snapshot of the object as it existed in the past.

—

Child Clone

When cloning a database or schema, all child objects (tables, views, stages, etc.) are cloned recursively.

—

Micro-Partition Sharing

Source and clone share the same physical micro-partitions until one side modifies data — then only changed partitions are written independently.

What is Zero-Copy Cloning?

Zero-copy cloning creates an instant, metadata-only copy of a database, schema, or table. No data is physically duplicated — the clone points to the same underlying micro-partitions as the source.

Zero-Copy Cloning Mechanics

Diagram showing: Source table points to micro-partitions MP1, MP2, MP3, MP4. After CREATE TABLE clone CLONE source, the clone table also points to the same MP1, MP2, MP3, MP4. No data copied — both share the same storage. When the clone later modifies data affecting MP2, a new MP2-prime is created for the clone while the source still points to the original MP2. Only the changed partition uses additional storage.

Source and clone sharing micro-partitions, with divergence only on modified partitions

🎯Exam Focus: Zero Storage Cost at Creation

A clone consumes zero additional storage when first created. Storage costs only accrue when data in either the source or clone is modified, because only changed micro-partitions are written independently. This is a favourite exam topic.

Creating Clones

Cloning Syntax

1-- Clone a table
2CREATE TABLE orders_dev CLONE orders;
3
4-- Clone a schema (all child objects cloned recursively)
5CREATE SCHEMA staging_clone CLONE staging;
6
7-- Clone an entire database
8CREATE DATABASE analytics_dev CLONE analytics;
9
10-- Clone with Time Travel — snapshot from the past
11CREATE TABLE orders_backup CLONE orders
12AT (TIMESTAMP => '2024-06-15 09:00:00'::TIMESTAMP_NTZ);
13
14CREATE TABLE orders_before_delete CLONE orders
15BEFORE (STATEMENT => '01a1b2c3-0000-0000-0000-000000000001');
16
17-- Clone at a specific offset
18CREATE TABLE orders_1hr_ago CLONE orders
19AT (OFFSET => -3600);

What Can Be Cloned?

Cloneable vs Non-Cloneable Objects

Feature

Can Be Cloned

Cannot Be Cloned

Tables

Permanent, transient, temporary tables

External tables

Schemas

Yes — all child objects cloned recursively

—

Databases

Yes — all schemas and their children cloned

—

Streams

Yes (but offset is not preserved)

—

Tasks

Yes (cloned in suspended state)

—

Stages

Named internal stages (empty clone)

External stages are referenced, not cloned

Pipes

Yes (cloned in paused state)

—

⚠️Important Clone Behaviours

Internal stages are cloned but empty — files are not copied
Streams are cloned but the offset resets — historical change data is not preserved
Tasks are cloned in a suspended state — they do not automatically start running
Pipes are cloned in a paused state

Clone + Time Travel

You can clone an object as it existed at a past point in time, effectively creating a historical snapshot.

Time Travel Cloning

1-- Recover a dropped table using clone + Time Travel
2-- Step 1: The table was accidentally truncated 30 minutes ago
3CREATE TABLE orders_recovered CLONE orders
4AT (OFFSET => -1800);
5
6-- Step 2: Verify the data
7SELECT COUNT(*) FROM orders_recovered;
8
9-- Step 3: Swap if needed
10ALTER TABLE orders SWAP WITH orders_recovered;

🎯Exam Focus: Clone AT vs UNDROP

Both CLONE AT and UNDROP can recover data. UNDROP restores a dropped object with its original name. CLONE AT creates a new object from a past snapshot — the original remains unchanged. CLONE AT works even if the object was not dropped (e.g., after an accidental UPDATE or TRUNCATE).

Privileges and Cloning

🔑Privilege Behaviour on Clones

Table clones do NOT inherit grants — the cloning role becomes the owner
Schema clones do NOT inherit grants on the schema or its child objects
Database clones do NOT inherit grants on the database, its schemas, or child objects
The role that executes CREATE … CLONE becomes the owner of the clone and all its children
To preserve grants, use CREATE … CLONE … COPY GRANTS (available for databases and schemas)

Cloning with Grants

1-- Clone database and preserve grants
2CREATE DATABASE analytics_dev CLONE analytics COPY GRANTS;
3
4-- Clone schema and preserve grants
5CREATE SCHEMA staging_dev CLONE staging COPY GRANTS;
6
7-- Note: COPY GRANTS is not available for individual table clones

Storage and Billing

Clone Storage Over Time

Timeline diagram: At creation (T0), clone uses 0 additional storage. At T1, source table receives an UPDATE affecting 10% of data — 10% new micro-partitions created for source. At T2, clone table receives a DELETE affecting 5% of data — 5% new micro-partitions created for clone. Total additional storage = only the changed partitions on each side. Shared (unchanged) partitions are counted once in storage billing.

Timeline showing zero initial cost then incremental storage as source and clone diverge

ℹ️Storage Billing for Clones

Snowflake uses a copy-on-write model. Shared micro-partitions are billed once (to the source). When either side modifies data, only the new/changed micro-partitions incur additional storage cost on that side. Time Travel and Fail-safe apply independently to both source and clone.

Common Use Cases

Common Cloning Patterns

Development and testing

Clone production to create a dev/test environment with real data — instantly and at zero initial cost.

SQL

CREATE DATABASE prod_dev CLONE production;

💡Clone regularly to keep dev environments fresh.

Disaster recovery

Clone with Time Travel to recover from accidental data modifications.

SQL

CREATE TABLE orders_recovered CLONE orders
  AT (OFFSET => -3600);

Pre-release snapshots

Clone before a major deployment so you can quickly roll back if needed.

SQL

CREATE SCHEMA release_backup CLONE app_schema;

Data sandboxing

Give analysts a clone to experiment with — modifications do not affect the source.

SQL

CREATE SCHEMA analyst_sandbox CLONE analytics;
GRANT USAGE ON SCHEMA analyst_sandbox TO ROLE analyst_role;

Cheat Sheet

📋

Quick Reference

Zero-Copy Cloning Quick Reference

📋

Key Facts

Storage at creation

Zero — metadata only

Speed

Near-instant regardless of data size

Cloneable

Tables, schemas, databases (and child objects)

Not cloneable

External tables; internal stage files

Grants

NOT inherited by default; use COPY GRANTS

⌨️

Commands

Basic clone

CREATE TABLE t2 CLONE t1

Time Travel clone

CREATE TABLE t2 CLONE t1 AT (OFFSET => -3600)

Clone + grants

CREATE DATABASE d2 CLONE d1 COPY GRANTS— DB/schema only

Swap

ALTER TABLE t1 SWAP WITH t2

Practice Quiz

Data Protection

How much additional storage does a zero-copy clone consume when first created?

Data Protection

When you clone a database, what happens to tasks in the cloned database?

Data Protection

Which statement about clone privileges is correct?

Flashcards

Data Protection

QUESTION

How does zero-copy cloning work at the storage level?

Click to reveal answer

ANSWER

The clone shares the same micro-partitions as the source (metadata-only copy). When either side modifies data, only the changed partitions are written independently (copy-on-write). Zero additional storage at creation.

Click to see question

Data Protection

QUESTION

What objects can be cloned in Snowflake?

Click to reveal answer

ANSWER

Tables (permanent, transient, temporary), schemas (all children recursively), and databases (all schemas and children). Streams, tasks, and pipes are included but with caveats: streams lose offset, tasks are suspended, pipes are paused, internal stages are empty.

Click to see question

Data Protection

QUESTION

How does cloning interact with Time Travel?

Click to reveal answer

ANSWER

You can clone AT or BEFORE a timestamp, statement ID, or offset to create a snapshot from the past. This is useful for recovering from accidental modifications without UNDROP. Time Travel and Fail-safe apply independently to both source and clone after creation.

Click to see question

Data Protection

QUESTION

What happens to grants when you clone a database?

Click to reveal answer

ANSWER

By default, grants are NOT inherited — the cloning role becomes owner of the clone and all children. Use COPY GRANTS to preserve the original grants. COPY GRANTS is available for database and schema clones but not individual table clones.

Click to see question

Data Protection

QUESTION

What is the difference between CLONE AT and UNDROP?

Click to reveal answer

ANSWER

UNDROP restores a dropped object with its original name. CLONE AT creates a new object from a past snapshot — works even if the source was not dropped (e.g., after accidental UPDATE/TRUNCATE). Both leverage Time Travel.

Click to see question

Resources

Next Steps

Reinforce what you just read

Study the All flashcards with spaced repetition to lock it in.

Study flashcards →