## 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.

```text
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:

```sql
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.

2. Know what does not come along:

```text
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.

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

```sql
-- 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.

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

```text
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.

5. Use time travel with clones for safe experimentation:

```sql
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