Storage and Data Protection · Topic 3 of 4

Zero-copy Cloning and Permission Inheritance

Clone Snowflake databases, schemas and tables without copying data, and learn which objects clone and how privileges, tasks and streams behave.

  • 5 min
In a nutshell
  • Clones share micro-partitions, are independent afterwards, and can use AT or BEFORE within retention.
  • Tasks clone suspended; internal named stages are not cloned; load history is not copied.
  • Clone of a container keeps child grants but not the container's own grants; COPY GRANTS copies a table's grants.

What it is

Cloning creates a copy of a database, schema or table in seconds, without copying the stored data. The clone points at the same micro-partitions as the source, so it uses no extra storage at first. This is called a zero-copy clone.

The two objects are then independent. When either one changes, Snowflake writes new micro-partitions for that object only, and storage cost grows only by the changed data.

Source tablepartitions A B CCLONEClonesame A B C, metadata onlyDML on cloneClone gets Dsource unchanged

How it works

Basic syntax

CREATE DATABASE dev_db CLONE prod_db;
CREATE SCHEMA   dev_db.sales CLONE prod_db.sales;
CREATE TABLE    orders_copy CLONE orders;
CREATE TABLE    orders_before CLONE orders AT (OFFSET => -3600);

Adding AT or BEFORE clones the object as it was at a point inside the Time Travel retention period. This is how you rebuild a past state, for example the table before a bad load. The point in time must be after the object was created and inside retention, or the statement fails.

What can be cloned

ClonedNotes
Databases, schemas, tablesCloning a container clones everything inside it
StreamsUnconsumed changes of the original stream are not available in the clone
TasksCloned in a suspended state, so nothing runs by surprise
Views, sequences, file formats, external stagesCloned with the container
PipesOnly pipes that refer to an external stage

Things to remember:

  • Internal named stages are not cloned, so the files in them are not copied.
  • A transient table can only be cloned as transient or temporary. A permanent table can be cloned to a permanent, transient or temporary table.
  • Load history of a table is not copied. Files already loaded into the source could load again into the clone, so be careful with COPY and pipes after cloning.
  • Cloning works on the data at the moment of the command, or at the AT or BEFORE point, and is not affected by changes made while it runs.
  • The cloned storage is shared, so the original micro-partitions stay until every clone that uses them is gone, including their Time Travel and Fail-safe periods.

Permission inheritance

A clone does not inherit privileges the way people expect, so learn the two rules.

What you clonePrivileges on the clone itselfPrivileges on child objects
Table, with plain CLONENone. The role that ran the command owns itNot applicable
Table, with COPY GRANTSCopied from the source tableNot applicable
Database or schemaNone from the source. The role that ran the command owns itKept, child objects keep the privileges granted on the source child objects
CREATE TABLE orders_copy CLONE orders COPY GRANTS;
  • The cloned container is new, so you must grant access to it yourself.
  • Existing grants on the child tables and views come along, which can expose cloned data to roles you did not intend to give access to the new database.
  • Future grants on the target schema or database apply to the new objects created by the clone, as they do for any new object.
  • Cloning needs the right source privileges: SELECT on a table, USAGE on most other objects, and ownership for pipes, streams and tasks.
Watch out

Cloning a database that has broad grants on its tables gives the same roles access to the clone's tables, even though the new database itself has no grants. Check access on the clone before you share it.

Using Time Travel and cloning to create development environments

A clone of production is a full size, realistic development or test environment that costs almost nothing to create.

Production databaselive dataCLONEDev databasezero copydevelopers changeDev changesisolatedthrow away or promoteDrop clonestorage freed

Typical pattern:

  • Clone production into dev or test on a schedule, or per feature branch, and drop it when finished.
  • Add AT (TIMESTAMP => ...) to reproduce a bug using the data as it was when the problem occurred.
  • Clone into a transient database to avoid paying for Fail-safe on throwaway data.
  • Run a task or script to suspend or resume cloned tasks, and re-grant access to the right roles.
  • Mask or replace sensitive columns in the clone if developers should not see production values.

Because the clone is writable, developers can load, update and delete freely without touching production.

NeedUse
Fast private copy for testingCREATE ... CLONE
Copy of data at a past pointCLONE ... AT or BEFORE
Separate permanent copy that never shares storageCREATE TABLE ... AS SELECT
Copy to another account or regionReplication or data sharing, not cloning

Common mistakes

Thinking a clone costs the same as the source. It costs almost nothing until data changes.

Cloning across accounts. Cloning stays inside one account; use sharing or replication for others.

Expecting grants on a cloned database. The container has none, but its child objects keep theirs.

Forgetting cloned tasks are suspended. Resume them if the clone needs to run its pipeline.

Expecting stage files. Internal named stages are not cloned.

What to remember for the exam

  • Clones share micro-partitions, are independent afterwards, and can use AT or BEFORE within retention.
  • Tasks clone suspended; internal named stages are not cloned; load history is not copied.
  • Clone of a container keeps child grants but not the container's own grants; COPY GRANTS copies a table's grants.
  • Clones suit development and test environments, ideally transient to avoid Fail-safe cost.

Check yourself

  • What storage does a new clone of a 10 TB table use?

    Almost none at first, because it shares the source's micro-partitions and only changed data creates new storage.

  • What state are tasks in after cloning a schema?

    Suspended, so you must resume them.

  • Which clone option copies the source table's privileges?

    COPY GRANTS.

Topic 3 of 4 in Storage and Data Protection

Test yourself on Storage and Data Protection

The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.