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
- Clones share micro-partitions, are independent afterwards, and can use
ATorBEFOREwithin 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 GRANTScopies 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.
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
| Cloned | Notes |
|---|---|
| Databases, schemas, tables | Cloning a container clones everything inside it |
| Streams | Unconsumed changes of the original stream are not available in the clone |
| Tasks | Cloned in a suspended state, so nothing runs by surprise |
| Views, sequences, file formats, external stages | Cloned with the container |
| Pipes | Only 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
ATorBEFOREpoint, 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 clone | Privileges on the clone itself | Privileges on child objects |
|---|---|---|
Table, with plain CLONE | None. The role that ran the command owns it | Not applicable |
Table, with COPY GRANTS | Copied from the source table | Not applicable |
| Database or schema | None from the source. The role that ran the command owns it | Kept, 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:
SELECTon a table,USAGEon most other objects, and ownership for pipes, streams and tasks.
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.
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.
| Need | Use |
|---|---|
| Fast private copy for testing | CREATE ... CLONE |
| Copy of data at a past point | CLONE ... AT or BEFORE |
| Separate permanent copy that never shares storage | CREATE TABLE ... AS SELECT |
| Copy to another account or region | Replication 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
ATorBEFOREwithin 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 GRANTScopies 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.