What is zero-copy cloning in Snowflake, and how would you use it?

Detailed explanation

Snowflake never edits micro-partitions in place. A table is a list of micro-partitions in metadata, so a clone is simply a new list pointing at the same files. Copy-on-write follows:

• Rows changed in the clone are written to new micro-partitions billed to the clone.
• Rows changed in the source replace micro-partitions there; the old ones cannot be purged while the clone references them, and show up as RETAINED_FOR_CLONE_BYTES on the source.

What happens to other parts:

Item	In the clone
Data	Shared until either side changes
Grants	A cloned database or schema keeps grants on child objects; a cloned table needs COPY GRANTS to keep its own
Tasks	Cloned suspended
Pipes	Only pipes on external stages are cloned
Streams	Cloned, but unconsumed changes from before the clone are not readable
Load history	Not copied: a COPY into the clone may reload files the source already loaded

Example

-- A production-sized environment for a feature branch, in seconds
CREATE DATABASE dev_feature_123 CLONE prod;

-- A safety net before a risky migration
CREATE TABLE analytics.orders_pre_migration CLONE analytics.orders;
ALTER TABLE analytics.orders ADD COLUMN channel VARCHAR;
-- ... migrate, validate, then drop the safety copy

-- Blue-green rebuild: build the new version from a clone, validate, swap atomically
CREATE TABLE analytics.dim_customer_next CLONE analytics.dim_customer;
-- ... rebuild dim_customer_next ...
ALTER TABLE analytics.dim_customer SWAP WITH analytics.dim_customer_next;

-- Restore the state just before a bad statement
CREATE TABLE analytics.orders_restore
  CLONE analytics.orders BEFORE (STATEMENT => '<query id of the bad MERGE>');

Trade-offs and pitfalls

• Clones are not free forever: as the source changes, the clone holds retired micro-partitions; after months it can cost nearly a full copy. Drop or refresh dev clones on a schedule.
• Dev clones and fully qualified names: a resumed task in a cloned database that writes to prod.schema.table writes to production. Use relative names or check before resuming.
• Not cross-account: clones live in the same account; use replication or data sharing to reach others.
• Point-in-time clones need Time Travel to still cover that point.

Common mistakes

1. Thinking a clone copies data (and costs full storage on creation).
2. Expecting a cloned table to keep its grants without COPY GRANTS.
3. Forgetting about old clones in storage reviews.