Snowflake Zero-Copy Cloning

Zero-copy cloning in Snowflake creates instant, space-efficient copies of databases, schemas, and tables without duplicating the underlying data.

Architecture Diagram 2: Clone vs Copy Performance Comparison

How Zero-Copy Cloning Works

The Cloning Process

-- Clone a table (instant operation)
CREATE TABLE orders_clone CLONE orders;

-- Clone with specific time travel
CREATE TABLE orders_yesterday CLONE orders
  AT (OFFSET => -86400);  -- 24 hours ago

-- Clone entire schema
CREATE SCHEMA analytics_clone CLONE production.analytics;

-- Clone database
CREATE DATABASE dev_clone CLONE production.analytics_db;

Clone vs. Copy Comparison

Aspect	CLONE	CTAS/COPY
Speed	Instant	Proportional to data size
Storage	Near-zero additional	Full data copy
Independence	Shared until modified	Fully independent
Cost	Minimal	Full storage cost
Use Case	Testing, dev, QA	Permanent copies

Clone Behavior

Initial State (Zero-Copy)

When first created, the clone shares all micro-partitions with the source:

-- Check clone size (initially zero)
SELECT
  table_name,
  table_size_bytes,
  cloning_expression_id
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE table_name IN ('ORDERS', 'ORDERS_CLONE');

Modified State (Copy-on-Write)

When data is modified in either table, Snowflake creates new micro-partitions:

-- Insert data into clone
INSERT INTO orders_clone (order_id, amount)
SELECT MAX(order_id) + 1, 100.00 FROM orders_clone;

-- Now clone has its own micro-partitions
SELECT
  table_name,
  table_size_bytes,
  clustering_depth,
  total_rows
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE table_name IN ('ORDERS', 'ORDERS_CLONE');

Use Cases for Zero-Copy Cloning

Development and Testing

-- Create test environment from production
CREATE DATABASE dev_environment CLONE production;

-- Modify clone without affecting production
ALTER TABLE dev_environment.orders
  ADD COLUMN test_column VARCHAR(100);

-- Test new queries on clone
SELECT * FROM dev_environment.orders
WHERE order_date >= '2024-01-01';

Data Backup and Recovery

-- Create backup before ETL process
CREATE TABLE orders_backup CLONE orders;

-- Run ETL process
BEGIN TRANSACTION;
  DELETE FROM orders WHERE order_date < '2023-01-01';
  INSERT INTO orders SELECT * FROM staging_orders;
COMMIT;

-- Recovery if needed
CREATE TABLE orders_recovered CLONE orders_backup;

Reporting Snapshots

-- Create monthly reporting snapshot
CREATE TABLE january_snapshot CLONE orders
  AT (TIMESTAMP => '2024-01-31 23:59:59'::TIMESTAMP);

-- Create rolling 30-day snapshot
CREATE TABLE rolling_snapshot CLONE orders
  AT (OFFSET => -2592000);  -- 30 days in seconds

Clone Operations

Cloning with Filtering

-- Clone with row filtering
CREATE TABLE recent_orders CLONE orders
  WHERE order_date >= '2024-01-01';

-- Clone with column selection
CREATE TABLE order_summary CLONE orders
  (order_id, customer_id, order_date, amount);

Cloning Across Databases

-- Clone from one database to another
CREATE DATABASE backup_analytics CLONE analytics_db;

-- Clone schema across databases
CREATE SCHEMA backup_analytics.orders_schema
  CLONE analytics_db.orders_schema;

Cloning Views

-- Clone a view (creates new view with same definition)
CREATE VIEW orders_view_clone CLONE orders_view;

-- Clone materialized view
CREATE MATERIALIZED VIEW mv_clone CLONE orders_mv;

Monitoring Clone Usage

-- Monitor clone storage impact
SELECT
  t.table_schema,
  t.table_name,
  t.clone_group_id,
  t.cloning_expression_id,
  m.table_size_bytes,
  m.total_rows,
  m.micro_partitions_count
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.TABLE_STORAGE_METRICS m
  ON t.table_name = m.table_name
WHERE t.clone_group_id IS NOT NULL;

-- Compare source vs clone storage
SELECT
  table_name,
  table_size_bytes,
  table_size_bytes / 1024 / 1024 AS size_mb,
  total_rows
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE table_name IN ('ORDERS', 'ORDERS_CLONE')
ORDER BY table_name;

Clone Maintenance

Detaching Clones

-- Detach clone from source (makes it independent)
ALTER TABLE orders_clone UNSET CLONE;

-- Or create independent copy
CREATE TABLE orders_independent AS SELECT * FROM orders_clone;

Dropping Clones

-- Drop clone (frees shared micro-partition references)
DROP TABLE orders_clone;

-- Drop all clones of a source
DROP ALL TABLES LIKE '%_clone' FROM SCHEMA analytics;

Cost Implications

Operation	Storage Cost	Compute Cost
Create Clone	Zero	Minimal
Query Clone	Shared	Normal
Modify Clone	New micro-partitions	Normal
Drop Clone	Free	Minimal

Summary

Key Takeaways

Zero-copy cloning creates instant, space-efficient table copies without duplicating underlying data.

Clones share micro-partitions with source until modified, using copy-on-write semantics.

Storage costs only accrue when data is modified — clone creation is free.

Clone Group ID tracks shared micro-partitions across all clones in a group.

Common Use Cases

Development and Testing — Create test environments from production without storage overhead
Data Backup — Create instant backups before ETL processes
Reporting Snapshots — Generate monthly or rolling snapshots for historical analysis
QA Environments — Provide testers with production-like data
Data Recovery — Quickly restore from accidental modifications

Snowflake Zero-Copy Cloning

Snowflake Zero-Copy Cloning

Architecture Diagram 2: Clone vs Copy Performance Comparison

How Zero-Copy Cloning Works

The Cloning Process

Clone vs. Copy Comparison

Clone Behavior

Initial State (Zero-Copy)

Modified State (Copy-on-Write)

Use Cases for Zero-Copy Cloning

Development and Testing

Data Backup and Recovery

Reporting Snapshots

Clone Operations

Cloning with Filtering

Cloning Across Databases

Cloning Views

Monitoring Clone Usage

Clone Maintenance

Detaching Clones

Dropping Clones

Cost Implications

Summary

Key Takeaways

Common Use Cases

See Also

Need Expert Snowflake Help?