Snowflake Cloning

Developers need realistic data to test changes. Copying a terabyte table takes time and doubles storage cost. Cloning solves both problems. A clone creates a full, independent copy of a database, schema, or table in seconds, and it adds almost no storage at the start.

The Idea in Everyday Language

Picture a shared photo album. Making a physical duplicate means printing every photo again. Cloning works like making a second album that points to the same photos. Both albums show identical pictures at the moment of creation. Adding a new photo to one album leaves the other untouched. Only new or changed photos need fresh paper.

HOW A CLONE SHARES STORAGE
==========================

AT CLONE TIME
  ORDERS (source)   --> [P1] [P2] [P3] [P4]
  ORDERS_DEV (clone) --> [P1] [P2] [P3] [P4]   same files, no extra storage

AFTER CHANGES
  ORDERS (source)    --> [P1] [P2] [P3] [P4] [P5 new]
  ORDERS_DEV (clone) --> [P1] [P2'] [P3] [P4]   P2' is a changed copy
                         shared: P1, P3, P4
                         extra storage: P2' only

Cloning Commands

-- Clone one table
CREATE TABLE orders_dev CLONE orders;

-- Clone a schema with everything inside it
CREATE SCHEMA sales_test CLONE sales;

-- Clone a whole database
CREATE DATABASE analytics_dev CLONE analytics_prod;

-- Clone data as it looked yesterday (combines with Time Travel)
CREATE TABLE orders_yesterday CLONE orders
  AT (OFFSET => -86400);

-- Clone data from just before a bad statement ran
CREATE TABLE orders_restored CLONE orders
  BEFORE (STATEMENT => '01b2c3d4-0000-1111-2222-333344445555');

The last two commands pair cloning with Time Travel. They rebuild a table as it existed in the past and make recovery from mistakes fast.

What Clones Cost

A clone consumes storage only for data that changes after creation. A 10 TB database cloned for testing starts at nearly zero extra cost. Heavy updates on either side create new micro-partitions, and those partitions add storage. Drop clones when testing ends.

Rules to Remember

  • A clone is independent. Later changes to the source never appear in the clone, and the reverse holds too.
  • The new top-level object needs fresh grants. Child objects inside a cloned database or schema keep their privileges.
  • A cloned table has no load history, so a COPY INTO may reload files that the source already loaded.
  • Time Travel history of the source does not carry over; the clone starts a new history.
  • Data inside the retention window of the source may remain in storage until the clone releases it.

Popular Uses

USE CASE                 HOW CLONING HELPS
-----------------------  -------------------------------------------
Development and testing  Developers work on production-sized data safely
Release rehearsal        Run a deployment script on a clone first
Backup before changes    Snapshot a table before a risky update
Analytics sandbox        Analysts experiment without touching live data
Recover from mistakes    Clone from a point before the error

A Safe Deployment Routine

CREATE DATABASE analytics_stage CLONE analytics_prod;

-- Run the new transformation scripts against analytics_stage
-- Compare results with production
SELECT COUNT(*) FROM analytics_stage.public.orders;
SELECT COUNT(*) FROM analytics_prod.public.orders;

-- Swap names when satisfied
ALTER DATABASE analytics_prod RENAME TO analytics_old;
ALTER DATABASE analytics_stage RENAME TO analytics_prod;

The swap finishes instantly, and the old database remains available as a rollback option.

Cloning Compared With Other Copy Methods

METHOD                   SPEED       EXTRA STORAGE      INDEPENDENT
-----------------------  ----------  -----------------  -----------
CREATE TABLE AS SELECT   Minutes+    Full copy          Yes
Clone                    Seconds     Only changed data  Yes
Share                    Instant     None               No (read only)

A Practical Scenario

A retail company wants to test a new pricing calculation on its full sales history. Copying the database would take hours and double the storage bill. A developer clones the production database, runs the new calculation on the clone, and compares the results with the live numbers. Errors surface before any customer sees a wrong price. The developer drops the clone at the end of the day, and the extra storage cost stays close to zero.

Frequently Asked Questions

Can I clone a table from last week?

Cloning with AT or BEFORE works inside the Time Travel retention period of the source table. Older states are not available.

Does cloning lock the source table?

Cloning does not block normal reads and writes on the source, and the clone captures a consistent point in time.

Cloning and Governance

Clones inherit the data of the source, including sensitive values. Treat a clone of production as production data. Grant access only to approved roles, apply the same masking policies, and drop the clone when the test ends. Many teams build a small routine that clones, masks, and shares an environment with developers on request.

Naming Habits

Clear names prevent confusion. A name such as orders_dev_2026_10 tells every colleague the purpose and the creation month. Comments on the object add the owner and the planned drop date, which keeps storage tidy.

Key Points

  • Cloning copies metadata, so databases, schemas, and tables clone in seconds.
  • Source and clone share storage until either side changes data.
  • Clones are fully independent after creation.
  • Clone with AT or BEFORE to rebuild past states using Time Travel.
  • New top-level clones need fresh grants.
  • Drop unused clones to release storage.
Previous lesson
Back to course
Next lesson