snowflake zero copy clone limitations

TL;DR

Zero-copy clones share underlying storage until data diverges, so they are instant and cheap to create. The limitations: clones do not copy external stages, some objects cannot be cloned, and storage bills grow as the clone diverges from the original.

snowflake zero copy clone limitations

Use this when

• You want cheap dev/test copies of production data
• A clone is missing something you expected
• Clone storage costs surprise you

Not for this skill when

• Queries spill to remote disk
• You are laying out BigQuery tables
• You are configuring dbt snapshots

Steps

1. Create the clone. This is the easy part:

CREATE TABLE dev_orders CLONE prod.orders;
-- or whole schemas / databases:
CREATE SCHEMA dev CLONE prod;

Expected output: an instant clone regardless of table size. No data is copied; the clone references the same micro-partitions.

1. Know what does not come along:

Not cloned: external stages, pipes, streams (mostly), tasks,
sequences current values, some grants.
Cloned: tables, views, and their data at clone time.

Expected output: correct expectations. The classic surprise is cloning a schema and finding the pipes and tasks missing; recreate those objects separately.

1. Understand the storage billing. Clones are cheap until they diverge:

-- check how much unique storage a clone owns:
SELECT table_name, bytes
FROM snowflake.account_usage.table_storage_metrics
WHERE table_name = 'DEV_ORDERS';

Expected output: bytes unique to the clone. Shared micro-partitions are billed once; as you update the clone, new micro-partitions are written and billed to it. A heavily-modified "dev copy" can cost nearly as much as a real copy.

1. Clones are point-in-time and independent afterward:

The clone is a snapshot at creation. Later changes to prod do not
appear in the clone, and changes to the clone never affect prod.
There is no ongoing link; re-clone for a fresh copy.

Expected output: correct mental model. Teams expecting a live replica are disappointed; teams wanting isolated test data are delighted.

1. Use time travel with clones for safe experimentation:

CREATE TABLE experiment CLONE prod.orders AT (OFFSET => -3600);

Expected output: a clone of the table as it was an hour ago. Combine with UNDROP for recovery workflows: clone first, experiment, drop the clone when done.

Variant phrasings

snowflake clone table limitations

External objects, pipes, tasks, and streams do not clone (step 2). Data and structure do.

snowflake zero copy clone storage cost

Shared until divergence (step 3). Monitor table_storage_metrics if clones live long.

snowflake clone database for testing

CREATE DATABASE test CLONE prod gives a full isolated copy instantly. Recreate the non-cloned objects (stages, pipes, tasks) in the test database.

Why it happens

Snowflake stores tables as immutable micro-partitions with metadata pointers. A clone just copies the pointers, so creation is O(metadata) regardless of data size. Divergence writes new micro-partitions, which is when storage actually grows.

Edge cases

• Cloning is blocked while the source has an active exclusive lock in some cases; retry or clone at a quieter moment.
• Fail-safe storage (7 days, non-configurable) applies to clones too and is billed.
• Clones of transient tables are transient; clones of permanent tables are permanent.
• Cross-region/account cloning (replication) is a different feature with real data movement and real cost.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_HLyl6kSoYHZGHgLW3O2mfw