Snowflake Zero-Copy Cloning

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

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

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