What you will be able to do
- Create a zero-copy clone of a database, schema or table and explain what it costs in storage
- Predict which child objects a database or schema clone brings along, and what state they arrive in
- Work out which grants a clone inherits, with and without COPY GRANTS
- Avoid the DML and DDL conflicts that make a clone fail or come out inconsistent
Key concept
Zero-copy clone — A clone is a new, writable object built from the source's existing data without copying it. It is fully independent: changes made to the clone never reach the source, and changes to the source never reach the clone. This is what makes it a cheap and safe base for a development environment.
1.Cloning objects with CREATE … CLONE
In Snowflake, the quickest way to give a team a development copy of production is to clone it. CREATE <object> … CLONE is the normal CREATE command for an object with the CLONE keyword added. Its main use is creating zero-copy clones of databases, schemas and tables. It can also clone other schema objects, including external stages, file formats, sequences and database roles. Databases, schemas and non-temporary tables also accept an AT | BEFORE clause, so you can clone them as they were at a point in the past.
CREATE [ OR REPLACE ] { DATABASE | SCHEMA } [ IF NOT EXISTS ] <object_name>
CLONE <source_object_name>
[ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
[ IGNORE TABLES WITH INSUFFICIENT DATA RETENTION ]
[ IGNORE HYBRID TABLES ]
[ INCLUDE INTERNAL STAGES ]
...Two properties make clones a good fit for dev and test work. The first is cost. A cloned database, schema or table adds nothing to storage until someone changes existing data or adds new data in it. Examples are adding, deleting or modifying rows in a cloned table, or creating a new populated table in a cloned schema. The second is isolation. A clone is independent, so changes made to the source or the clone don't show up in the other. Developers can run destructive tests on the clone and production is never touched.
Cloning a large object takes time. A clone with no AT | BEFORE clause still gives you a consistent snapshot, because Snowflake sets the AT point to the timestamp when the statement started. The clone also takes the source's name and structure as of that point.
Checkpoint 1 of 5· Exam question
A data engineering team wants to spin up a full-size copy of a 40 TB production database for analysts to test transformation logic against realistic data, without waiting for a bulk export/import and without doubling storage costs on day one. Which approach satisfies this requirement?
Correct answer: A — Run `CREATE DATABASE dev_db CLONE prod_db;` to create a zero-copy clone sharing the source's micro-partitions until either side is modified.
- A. Cloning a database is a metadata-only operation: the new database references the same micro-partitions as the source, so it is created almost instantly and does not double storage until DML diverges the two copies. This is the mechanism designed exactly for this scenario.
- B. Exporting and reloading every table through a stage moves the full 40 TB through unload and load jobs, which is far slower than a metadata-only clone and immediately consumes new storage for the reload.
- C. Granting direct read access to production does not give analysts an isolated environment to test transformation logic in; any writes or schema experiments they run would affect the live production objects.
- D. There is no `CREATE DATABASE ... AS SELECT` construct in Snowflake, and even a row-by-row materialization would allocate new storage for every row immediately rather than sharing the source's micro-partitions.
Checkpoint 2 of 5· Check yourself
A developer clones the production table ORDERS to ORDERS_DEV, then deletes half the rows in ORDERS_DEV. What happens to ORDERS?
A clone is writable and independent of its source, so DML on one is never reflected in the other.
“Changes made to the source or clone aren’t reflected in the other object.”Source: docs.snowflake.com
2.What a database or schema clone brings along, and in what state
Database and schema clones are recursive. Cloning a database clones all its schemas and the objects inside them. Two limits apply. First, the clone includes only the objects that the cloning role has the right privileges on. Second, some object types are never cloned: external tables are not cloned at all, and hybrid tables can be cloned with a database but not with a schema. Named internal stages are cloned only if you add INCLUDE INTERNAL STAGES. Each table's stage is cloned, but it comes over empty. Cloning an external stage has no effect on the cloud storage it points to.
Objects that would otherwise start running are made safe on the way in. A clone made from production won't start loading data or firing schedules by itself:
| Object in source | State in the clone |
|---|---|
| Task | Suspended by default; resume individually with ALTER TASK … RESUME |
| Alert | Suspended by default; resume with ALTER ALERT … RESUME |
| Pipe with AUTO_INGEST = FALSE | Paused by default |
| Pipe with AUTO_INGEST = TRUE | STOPPED_CLONED state; doesn't collect event notifications for newly staged files |
| Pipe that references an internal stage | Not cloned |
| Stream | Unconsumed records are inaccessible in the clone |
Tables have their own details. A cloned table copies the source's structure, data and clustering key. However, the new table starts with Automatic Clustering suspended, even if it is running on the source. A cloned table also doesn't get the source's load history, so files already loaded into the source can be loaded into the clone again. Storage lifecycle policies are not attached automatically. Masking and row access policies are carried over: the cloned table maps to the same policies, or to the cloned copies if the policies live in the same cloned schema or database.
Checkpoint 3 of 5· Fill the gap
EVENTS_DEV was cloned from a table that has an active clustering key. Which keyword turns on automatic reclustering for the clone?
ALTER TABLE <name> ? RECLUSTERThe clone keeps the clustering key, but Automatic Clustering starts suspended. ALTER TABLE … RESUME RECLUSTER turns it on.
Source: docs.snowflake.com3.Permission inheritance: which grants follow a clone
Grants are where most clone surprises come from, and the rules depend on what you clone. When you clone a database or schema, the clone inherits all the grants on the cloned child objects. A role that could SELECT from production tables can SELECT from the cloned tables. The container itself is the exception: the cloned database or schema doesn't get the privileges granted on the source database or schema. Those have to be granted again.
Most CREATE <object> … CLONE statements for individual objects copy no grants. Commands that support COPY GRANTS, such as CREATE TABLE and CREATE VIEW, let you choose to copy them. With COPY GRANTS, every privilege except OWNERSHIP is copied from the source table to the new table. There is a trade-off with future grants, though. With COPY GRANTS, the clone gets the explicit privileges on the original table but not the schema's future grants. Without it, the clone gets no explicit privileges but does get the schema's future grants. In every other case, you grant privileges to the clone with GRANT <privileges> … TO ROLE.
Pipes are a special case. When you clone a database or schema that contains pipes, the role that creates the clone owns the cloned pipes. To copy the grants as well, including pipe ownership, add COPY GRANTS when cloning the database or schema. Cloning also needs privileges on the source: SELECT for a table, USAGE for a database, and OWNERSHIP for alerts, pipes, streams and tasks.
Checkpoint 4 of 5· Match them up
Match each cloning scenario to the grants the clone ends up with
Tap a term, then the definition that fits it.
Child objects inherit grants but the container does not. For a single table, COPY GRANTS picks explicit privileges over future grants, and leaving it out does the opposite.
“The clone of the container itself (database or schema) doesn’t inherit the privileges granted on the source container.”Source: docs.snowflake.com
4.Keeping a clone consistent while the source is busy
Cloning is fast but not instantaneous, and production keeps changing while it runs. Snowflake tries to clone each table as it was when the operation started. If a table's retention time is 0, the history needed to rebuild that starting state can be purged by DML that runs during the clone. The clone then fails with 000707 (02000): None: Data is not available. The docs give two fixes. One is to avoid DML on the source until the clone finishes. The other is to set DATA_RETENTION_TIME_IN_DAYS=1 on the affected tables before cloning and set it back afterwards. You can also set the cloned tables to 0 so dev DML doesn't build up Time Travel storage.
DDL causes a different kind of conflict. DDL statements are atomic and are not part of the clone's transaction. Snowflake also doesn't record which names existed when the clone started. Renaming, dropping or recreating child objects while a clone runs can therefore fail with errors like Object 'T_SALES' already exists. Wait until the clone finishes before renaming an object to the name of a dropped one.
References to other objects also need a look before developers start writing to the clone. If you clone only a table, any column default that uses a sequence still points at the source sequence. The same is true when the sequence is in a different schema. Inserts in dev would then draw values from the production sequence. Foreign keys behave the same way: they point at the cloned parent only if both tables were cloned together in the same database or schema. To repoint a sequence default:
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DEFAULT <new_sequence>.nextval;Checkpoint 5 of 5· Check yourself
Every table in a schema has DATA_RETENTION_TIME_IN_DAYS = 0. Cloning the schema keeps failing with "Data is not available" because ETL keeps writing to it. ETL cannot be paused. What is the recommended fix?
With retention at 0, history from DML during the clone is purged before the clone can use it. A 1-day retention keeps that history long enough for the clone to finish.
“prior to starting cloning, set DATA_RETENTION_TIME_IN_DAYS=1 for all tables in the schema”Source: docs.snowflake.com
Sources2
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Cloning a database also copies the grants on the database itself, so consumers can use the clone right away.Why is that wrong?
Child objects inherit their grants, but the cloned database or schema doesn't get the privileges granted on the source container. Those must be granted again.
Covered in Permission inheritance: which grants follow a clone
2.Adding COPY GRANTS gives a cloned table both the source's explicit grants and the schema's future grants.Why is that wrong?
With COPY GRANTS, the clone gets the explicit privileges but not the future grants. Without it, the clone gets only the future grants.
Covered in Permission inheritance: which grants follow a clone
3.A cloned table keeps reclustering just like its source, because it inherited the clustering key.Why is that wrong?
The clustering key is copied, but Automatic Clustering starts suspended on the clone until you resume it.
Covered in What a database or schema clone brings along, and in what state
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“This command is primarily used for creating zero-copy clones of databases, schemas, and tables.”
↩︎ Cloning objects with CREATE … CLONE“a clone does not contribute to the overall data storage for the object until operations are performed on the clone”
↩︎ Cloning objects with CREATE … CLONE“Cloning includes only the objects on which the role that creates the clone has appropriate privileges.”
↩︎ What a database or schema clone brings along, and in what state“the following object types are not cloned: External tables”
↩︎ What a database or schema clone brings along, and in what state“A cloned table does not include the load history of the source table.”
↩︎ What a database or schema clone brings along, and in what state“but does inherit any future grants defined for the object type in the schema”
↩︎ Permission inheritance: which grants follow a clone“A clone is writable and is independent of its source.”
↩︎ Key concept“the new object inherits any explicit access privileges granted on the original table but does not inherit any future grants”
↩︎ Exam trap 2“the new table starts with Automatic Clustering suspended – even if Automatic Clustering is not suspended for the source table.”
↩︎ Exam trap 3“Changes made to the source or clone aren’t reflected in the other object.”
↩︎ Checkpoint - 2.
“the cloning operation internally sets the AT clause value as the timestamp when the statement was initiated”
↩︎ Cloning objects with CREATE … CLONE“cloning an external stage has no impact on the referenced cloud storage.”
↩︎ What a database or schema clone brings along, and in what state“CREATE <object> … CLONE statements for most objects do not copy grants on the source object to the object clone.”
↩︎ Permission inheritance: which grants follow a clone“the create operation copies all privileges, except OWNERSHIP, from the source table to the new table”
↩︎ Permission inheritance: which grants follow a clone“the role that creates the clone takes ownership of the cloned pipe”
↩︎ Permission inheritance: which grants follow a clone“Refrain, if possible, from executing DML transactions on the source object (or any of its children) until after the cloning operation completes.”
↩︎ Keeping a clone consistent while the source is busy“DDL statements that rename (or drop and recreate) source child objects compete with any in-progress cloning operations and can cause name conflicts.”
↩︎ Keeping a clone consistent while the source is busy“Or if you clone just the table itself, the cloned table references the source sequence.”
↩︎ Keeping a clone consistent while the source is busy“the clone inherits all granted privileges on the clones of all child objects contained in the source object”
↩︎ Exam trap 1“When a database or schema that contains tasks is cloned, the tasks in the clone are suspended by default.”
↩︎ Prediction“The clone of the container itself (database or schema) doesn’t inherit the privileges granted on the source container.”
↩︎ Checkpoint“prior to starting cloning, set DATA_RETENTION_TIME_IN_DAYS=1 for all tables in the schema”
↩︎ Checkpoint