Time Travel & Zero-Copy Cloning: Data Restoration Guide

In the fast-paced world of data engineering, mistakes happen. A developer might accidentally drop a table, a faulty ETL job might corrupt data, or a user might update rows without a WHERE clause. Traditional databases often require restoring from backups, which can take hours and involve significant downtime. Snowflake, a cloud-native data warehouse, offers two powerful features to handle such scenarios: Time Travel and Zero-Copy Cloning.

Time Travel enables you to access and restore historical data at any point within a defined retention period (1 to 90 days, depending on your Snowflake edition). This means you can query data as it existed minutes, hours, or days ago, or even clone a table from a specific point in time. Zero-Copy Cloning, on the other hand, allows you to create an instant copy of a database, schema, or table without duplicating the underlying data. The clone shares the same storage until modifications are made, making it incredibly storage-efficient.

Together, these features provide a safety net for data recovery, enable rapid development and testing with production data, and simplify auditing and compliance. In this tutorial, you'll learn how to leverage Time Travel and Zero-Copy Cloning in Snowflake with practical SQL examples, understand their limitations, and see how they can save the day in a real-world production incident. By the end, you'll be equipped to implement these features in your own Snowflake environment.

Understanding Time Travel

Time Travel is a Snowflake feature that allows you to access historical data within a defined retention period. The retention period depends on your Snowflake edition: Standard (1 day), Enterprise (up to 90 days), and Business Critical (up to 90 days). You can query data as it existed at a specific timestamp, offset, or before a statement.

To use Time Travel, you use the AT or BEFORE clause in your SQL queries. The syntax is:

SELECT ... FROM <table> AT (TIMESTAMP => '<timestamp>'::TIMESTAMP) SELECT ... FROM <table> AT (OFFSET => -<seconds>) SELECT ... FROM <table> BEFORE (STATEMENT => '<query_id>')

For example, to see a table as it was 30 minutes ago:

SELECT FROM orders AT (OFFSET => -6030);

You can also use Time Travel to clone a table from a past state:

CREATE OR REPLACE TABLE orders_restored CLONE orders AT (OFFSET => -60*30);

This creates a new table that contains the data as it was 30 minutes ago. The clone is a full, independent table that you can modify without affecting the original.

Time Travel is also used for undropping objects. If you accidentally drop a table, you can restore it with:

UNDROP TABLE orders;

This command recovers the table from the Time Travel retention period. However, if the table was dropped and then another table with the same name was created, you may need to rename the existing table first.

-- Query table as of 1 hour ago
SELECT * FROM orders AT (OFFSET => -3600);

-- Query table before a specific statement (query ID)
SELECT * FROM orders BEFORE (STATEMENT => '0192a3b4-5c6d-7e8f-9a0b-1c2d3e4f5g6h');

-- Clone table from 30 minutes ago
CREATE OR REPLACE TABLE orders_restored CLONE orders AT (OFFSET => -1800);

-- Undrop a table
UNDROP TABLE orders;

-- Show Time Travel retention for a table
SHOW TABLES LIKE 'orders';

Zero-Copy Cloning: Instant Copies Without Storage Overhead

Zero-Copy Cloning is a Snowflake feature that creates an instant copy of a database, schema, or table. The clone shares the same underlying storage as the original until modifications are made. This means you can create clones almost instantly and without incurring additional storage costs until you start changing data.

To create a clone, use the CLONE keyword:

CREATE DATABASE dev_db CLONE prod_db; CREATE SCHEMA dev_schema CLONE prod_schema; CREATE TABLE dev_table CLONE prod_table;

You can also clone from a Time Travel point:

CREATE TABLE orders_backup CLONE orders AT (OFFSET => -3600);

This combines both features: you get an instant copy of the table as it was one hour ago.

-- Clone entire database
CREATE DATABASE prod_clone CLONE prod_db;

-- Clone a schema
CREATE SCHEMA analytics_clone CLONE analytics;

-- Clone a table
CREATE TABLE orders_clone CLONE orders;

-- Clone from a specific time
CREATE TABLE orders_snapshot CLONE orders AT (TIMESTAMP => '2025-03-15 10:00:00'::TIMESTAMP);

-- Verify storage savings (query INFORMATION_SCHEMA)
SELECT table_name, bytes / 1024/1024/1024 AS gb
FROM information_schema.tables
WHERE table_name IN ('ORDERS', 'ORDERS_CLONE');

Restoring Data with Time Travel and Cloning

When data is accidentally deleted or corrupted, you can combine Time Travel and Zero-Copy Cloning to restore it. The typical workflow is:

1. Identify the point in time before the incident.
2. Query the table at that point to verify the data is correct.
3. Create a clone of the table at that point.
4. Replace the current table with the clone.

For example, suppose a DELETE statement removed all rows from the 'orders' table 15 minutes ago. To restore:

-- Step 1: Verify data exists at a point before the delete SELECT COUNT() FROM orders AT (OFFSET => -6015);

-- Step 2: Create a clone from that point CREATE OR REPLACE TABLE orders_restore CLONE orders AT (OFFSET => -60*15);

-- Step 3: Swap tables (or rename) ALTER TABLE orders RENAME TO orders_bad; ALTER TABLE orders_restore RENAME TO orders;

-- Optionally, drop the bad table DROP TABLE orders_bad;

If the table was dropped entirely, use UNDROP:

UNDROP TABLE orders;

If the table was dropped and a new table with the same name was created, you need to drop or rename the new table first, then UNDROP the old one.

For more complex scenarios, like restoring specific rows, you can use a MERGE statement:

MERGE INTO orders AS target USING (SELECT FROM orders AT (OFFSET => -6015)) AS source ON target.order_id = source.order_id WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...;

This allows you to selectively restore missing or corrupted rows.

-- Scenario: orders table has been corrupted by a bad update
-- Step 1: Check current state
SELECT COUNT(*) FROM orders;

-- Step 2: Check historical state (10 minutes ago)
SELECT COUNT(*) FROM orders AT (OFFSET => -600);

-- Step 3: Create a clone of the good state
CREATE TABLE orders_good CLONE orders AT (OFFSET => -600);

-- Step 4: Replace the corrupted table
ALTER TABLE orders RENAME TO orders_corrupt;
ALTER TABLE orders_good RENAME TO orders;

-- Step 5: Clean up
DROP TABLE orders_corrupt;

Time Travel and Cloning for Auditing and Compliance

Time Travel and Zero-Copy Cloning are powerful tools for auditing and compliance. You can use them to: - Track changes over time by querying historical states. - Create periodic snapshots of data for regulatory retention. - Investigate who changed what and when (using QUERY_HISTORY).

For example, to see the state of a table at the end of each month, you can create a clone:

CREATE TABLE orders_end_of_month CLONE orders AT (TIMESTAMP => '2025-01-31 23:59:59'::TIMESTAMP);

You can also use Time Travel to compare current data with a previous state to identify changes:

SELECT FROM orders MINUS SELECT FROM orders AT (OFFSET => -86400);

This returns rows that are new or changed in the last day.

For compliance, you might need to retain data for a specific period. Snowflake's Time Travel retention can be set to up to 90 days. For longer retention, you can export data to external storage or use Snowflake's Fail-safe (7 days, not queryable).

Additionally, you can use the QUERY_HISTORY view to see which queries modified data. For example:

SELECT query_id, query_text, start_time, user_name FROM snowflake.account_usage.query_history WHERE query_text ILIKE '%DELETE FROM orders%' ORDER BY start_time DESC;

This helps identify the exact query that caused data loss.

-- Compare current data with yesterday's data
SELECT * FROM orders
MINUS
SELECT * FROM orders AT (OFFSET => -86400);

-- Find queries that deleted from orders
SELECT query_id, query_text, start_time, user_name
FROM snowflake.account_usage.query_history
WHERE query_text ILIKE '%DELETE FROM orders%'
  AND start_time >= CURRENT_TIMESTAMP - INTERVAL '7 days'
ORDER BY start_time DESC;

-- Create a monthly snapshot
CREATE TABLE orders_2025_01 CLONE orders AT (TIMESTAMP => '2025-01-31 23:59:59'::TIMESTAMP);

Best Practices and Limitations

While Time Travel and Zero-Copy Cloning are powerful, they have limitations and best practices you should follow.

Limitations: - Time Travel retention is limited to 90 days maximum (Enterprise edition). Data beyond that is not recoverable via Time Travel. - Time Travel does not work on external tables (data stored outside Snowflake). - Cloning a table that has a large number of micro-partitions may take slightly longer, but still much faster than a full copy. - You cannot clone a table across regions or accounts without using data sharing. - Time Travel queries consume compute credits; they are not free.

Best Practices: 1. Set appropriate data retention for each table based on business needs. Use ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = N. 2. Use Zero-Copy Cloning for development and testing instead of copying data manually. 3. Before running destructive operations (DELETE, DROP, UPDATE without WHERE), create a clone as a backup. 4. Monitor Time Travel storage usage. Time Travel data is stored and billed separately. 5. Use the UNDROP command as a first resort for dropped objects. 6. Document your recovery procedures and test them regularly. 7. Use roles and permissions to restrict who can drop or truncate tables.

Cost Considerations: - Time Travel storage: The historical data is stored as separate micro-partitions. The cost is proportional to the amount of change over time. - Cloning: No additional storage until changes are made. However, querying clones consumes compute. - Use the ACCOUNT_USAGE views to track storage and compute costs.

-- Set retention for critical table
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- Create a backup clone before risky operation
CREATE TABLE orders_before_update CLONE orders;

-- Perform risky operation
UPDATE orders SET status = 'archived' WHERE date < '2020-01-01';

-- If something goes wrong, restore from clone
-- (swap tables or merge)

-- Monitor Time Travel storage
SELECT * FROM snowflake.account_usage.table_storage_metrics
WHERE table_name = 'ORDERS';

Advanced Use Cases: Time Travel with Streams and Tasks

Snowflake Streams capture change data capture (CDC) on tables. Combined with Time Travel, you can reprocess changes from a specific point in time. For example, if a stream missed some changes due to a bug, you can recreate the stream from a historical point.

However, streams are typically append-only and track changes after creation. To reprocess historical changes, you can use a combination of Time Travel and a temporary table.

Another advanced use case is using Time Travel with Snowflake Tasks to automate data recovery. For example, you can create a task that runs every hour to check for data anomalies and restore from a known good state if needed.

Example: Create a task that clones a table every day at midnight for backup:

CREATE TASK daily_backup WAREHOUSE = my_wh SCHEDULE = '1440 minute' AS CREATE OR REPLACE TABLE orders_backup CLONE orders;

This creates a daily snapshot. You can then use Time Travel to go back to any of these snapshots.

You can also use Time Travel to implement a 'soft delete' pattern. Instead of physically deleting rows, you can mark them as deleted and use Time Travel to query the original state if needed.

Finally, Time Travel can be used for debugging ETL pipelines. If a pipeline produces unexpected results, you can query the source tables at the time the pipeline ran to see what data was processed.

-- Create a daily backup task
CREATE TASK daily_backup
  WAREHOUSE = my_wh
  SCHEDULE = '1440 minute'
AS
  CREATE OR REPLACE TABLE orders_backup CLONE orders;

-- Start the task
ALTER TASK daily_backup RESUME;

-- Use Time Travel to debug ETL: compare source at pipeline time
SELECT * FROM source_table AT (TIMESTAMP => '2025-03-20 02:00:00'::TIMESTAMP)
MINUS
SELECT * FROM target_table;

-- Soft delete pattern: instead of DELETE, set a flag
UPDATE orders SET is_deleted = TRUE WHERE order_id = 123;
-- To see original data, query before the update
SELECT * FROM orders AT (OFFSET => -60) WHERE order_id = 123;