Zero Copy Cloning

Snowflake · Storage & Optimization

Theory

CREATE ... CLONE creates a copy of a table, schema or database in seconds without copying data. The clone is a new object whose metadata points to the same micro-partitions as the source. From then on the two are independent: when either one inserts, updates or deletes rows, new micro-partitions are written for that object only, and only those changed partitions add storage.

You can also clone from a point in the past with Time Travel, for example CREATE TABLE orders_before_fix CLONE orders AT(OFFSET => -3600). Cloning a database or schema clones the objects inside it, which makes it the standard way to create a dev or test copy of production.

Why we use it

Copying large tables the normal way takes time and doubles storage. Cloning gives teams full-size, isolated copies for testing, debugging or backups almost instantly and at almost no extra storage cost.

When to use it

Use clones for dev and test environments, safe experiments before risky changes, and point-in-time snapshots. Do not treat a clone as an off-site backup: it lives in the same account. Use replication for that.

How CLONE Works

Creates a full logical copy of a database, schema, or table in seconds by pointing the new object at the same underlying micro-partitions — no data is physically duplicated.

Why It's Essentially Free

A clone costs nothing extra in storage until it diverges from the source — once rows are added, changed, or deleted in either copy, only the changed data consumes new storage.

Common Use Cases

The standard way to spin up a dev or test copy of production data instantly, or to snapshot a table before running a risky migration so you can compare or roll back.

How it works

  1. Need a copy of orders for testing
  2. CREATE TABLE orders_dev CLONE orders
  3. Clone shares the source's micro-partitions; no data copied
  4. Is the clone or source modified? Yes: New partitions are written for that object only. No: Both keep sharing storage.
  5. Drop the clone when done; shared data stays with the source

Example

CREATE TABLE orders_backup CLONE orders;
CREATE SCHEMA dev_schema CLONE learn;
CREATE DATABASE test_db CLONE practice_db;

CREATE TABLE orders_before_migration CLONE orders AT(OFFSET => -86400);

UPDATE orders_backup SET amount = 0 WHERE order_id = 1;
-- The original 'orders' table is unaffected

SELECT TABLE_NAME, ACTIVE_BYTES, TIME_TRAVEL_BYTES, RETAINED_FOR_CLONE_BYTES
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS;

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic