Part 168: Snowflake Time Travel & Zero-Copy Cloning

Querying historical data states and cloning production petabytes instantly without incurring storage costs.

1. Snowflake Time Travel

Allows querying, restoring, and cloning data as it existed at any historical point in time (up to 90 days in Enterprise edition):

-- Query table exactly as it existed 1 hour ago:
SELECT * FROM customer_balance 
AT(OFFSET => -60*60);

-- Query table right before a catastrophic accidental update query ID ran:
SELECT * FROM accounts 
BEFORE(STATEMENT => '01a5b823-0001-20e4-0000-00018a729e92');

-- Restore table back to its previous state:
CREATE OR REPLACE TABLE accounts AS
SELECT * FROM accounts BEFORE(STATEMENT => '01a5b823-0001-20e4-0000-00018a729e92');

-- Accidentally dropped a table? Restore it with zero downtime:
UNDROP TABLE accounts;

2. Zero-Copy Cloning

Creates an exact copy of a table, schema, or entire database in 2 seconds with $0 additional storage cost: - It simply duplicates metadata pointers to existing immutable micro-partitions. - Uses Copy-on-Write: Storage costs are only incurred for new or updated rows in the clone.

-- Clone entire production database for staging test environment:
CREATE DATABASE staging_db CLONE production_db;

-- Clone table at a specific historical point in time:
CREATE TABLE dev_orders CLONE orders AT(TIMESTAMP => '2026-09-01 00:00:00'::timestamp_tz);

Key Takeaways

• Time Travel and Zero-Copy Cloning make staging environments instant and accidental data drops completely recoverable.

In Part 169, we explore Google BigQuery SQL: Partitioning, Clustering & Cost Optimization!