Time Travel, Fail-Safe & Zero-Copy Cloning

Architecture Diagram 1: Time Travel Data Flow

Architecture Diagram 2: Zero-Copy Cloning Process

Architecture Diagram 3: Time Travel Retention Timeline

Architecture Diagram 4: Zero-Copy Clone Mechanics

Architecture Diagram 5: Data Recovery Workflow

Detailed Explanation

What is Time Travel?

Time Travel enables querying historical data at any point within a configurable retention period (1–90 days on Enterprise Edition). It leverages micro-partition versioning, not separate backups.

How Time Travel Works

• Modified/deleted data is not immediately overwritten
• New micro-partition versions are created while old versions are retained
• Metadata pointers track current and historical versions
• AT/OFFSET/BEFORE clauses reconstruct table state at the requested time
• Optimizer materializes only the specific micro-partitions needed (efficient)

Fail-Safe: Automatic Disaster Recovery

• Data transitions to Fail-Safe after Time Travel window expires
• Permanently purged after 7 days in Fail-Safe
• Designed for worst-case scenarios only

Zero-Copy Cloning: Efficient Data Duplication

• Creates complete copies via metadata pointers (no data duplication)
• 1TB table clone: seconds to create, near-zero storage initially
• Copy-on-write mechanism: only diverged micro-partitions are copied
• Clones are fully independent for DML operations
• Inherit Time Travel retention settings from source

Advanced Use Cases

1. Data Auditing — Clone before/after regulatory changes for compliance snapshots
2. Development Environments — Clone production for realistic dev data without impact
3. Data Science — Use Time Travel to reproduce historical training datasets
4. Regression Testing — Clone, apply changes, compare original vs. modified
5. Data Quality Analysis — Track quality metrics over time via Time Travel

Key Takeaway: Combine cloning + Time Travel for instant rollback, development environments, and regulatory compliance with production data fidelity.

Key Concepts Table

Code Examples

-- Example 1: Time Travel queries with different syntax
-- Query data as of specific timestamp
SELECT * FROM sales_data
AT (TIMESTAMP => '2024-01-15 10:30:00'::TIMESTAMP_TZ);

-- Query data as of offset from current time (in seconds)
SELECT * FROM sales_data
AT (OFFSET => -3600);  -- 1 hour ago

-- Query data before a specific statement
SELECT * FROM sales_data
BEFORE (STATEMENT => '01234567-89ab-cdef-0123-456789abcdef');

-- Example 2: Clone table with Time Travel
CREATE CLONE sales_data_clone
FROM sales_data
AT (TIMESTAMP => '2024-01-15 00:00:00'::TIMESTAMP_TZ);

-- Clone entire schema with specific point-in-time
CREATE SCHEMA analytics_clone
CLONE analytics_prod
AT (OFFSET => -86400);  -- 24 hours ago

-- Example 3: Data recovery using Time Travel
-- Step 1: Identify when data was deleted
SELECT * FROM sales_data
AT (TIMESTAMP => '2024-01-15 10:00:00'::TIMESTAMP_TZ)
EXCEPT
SELECT * FROM sales_data
AT (TIMESTAMP => '2024-01-15 11:00:00'::TIMESTAMP_TZ);

-- Step 2: Recover deleted data
CREATE TABLE sales_data_recovered AS
SELECT * FROM sales_data
AT (TIMESTAMP => '2024-01-15 10:30:00'::TIMESTAMP_TZ);

-- Step 3: Merge recovered data back (if needed)
MERGE INTO sales_data t
USING sales_data_recovered s
ON t.id = s.id AND t.date = s.date
WHEN NOT MATCHED THEN
    INSERT (id, date, amount, region)
    VALUES (s.id, s.date, s.amount, s.region);

-- Example 4: UNDROP operations
-- Restore dropped table
UNDROP TABLE sales_data;

-- Restore dropped schema
UNDROP SCHEMA analytics;

-- Restore dropped database
UNDROP DATABASE warehouse;

-- Example 5: Advanced cloning patterns
-- Clone with explicit warehouse for large operations
CREATE WAREHOUSE clone_wh WAREHOUSE_SIZE = 'xlarge';

USE WAREHOUSE clone_wh;

CREATE CLONE large_fact_table_clone
FROM large_fact_table;

-- Clone with data transformation
CREATE CLONE sales_filtered_clone AS
SELECT * FROM sales_data
WHERE region = 'US'
  AND transaction_date >= '2024-01-01';

-- Example 6: Time Travel data analysis
-- Analyze data changes over time
SELECT 
    'Current' as version,
    COUNT(*) as row_count,
    SUM(amount) as total_amount
FROM sales_data
UNION ALL
SELECT 
    '1 hour ago' as version,
    COUNT(*) as row_count,
    SUM(amount) as total_amount
FROM sales_data AT (OFFSET => -3600)
UNION ALL
SELECT 
    '1 day ago' as version,
    COUNT(*) as row_count,
    SUM(amount) as total_amount
FROM sales_data AT (OFFSET => -86400);

-- Example 7: Automated backup cloning
CREATE OR REPLACE PROCEDURE daily_backup()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    CREATE CLONE sales_backup_
        CLONE sales_data;
    
    -- Drop clones older than 7 days
    EXECUTE IMMEDIATE '
        DROP TABLE IF EXISTS sales_backup_' || 
        TO_CHAR(DATEADD(day, -7, CURRENT_DATE()), 'YYYYMMDD');
    
    RETURN 'Backup completed successfully';
END;
$$;

-- Example 8: Compare table versions
-- Find rows added since specific time
SELECT * FROM sales_data
MINUS
SELECT * FROM sales_data AT (OFFSET => -3600);

-- Find rows deleted since specific time
SELECT * FROM sales_data AT (OFFSET => -3600)
MINUS
SELECT * FROM sales_data;

-- Find rows modified since specific time
SELECT id, amount FROM sales_data
EXCEPT
SELECT id, amount FROM sales_data AT (OFFSET => -3600);

Performance Metrics

Best Practices

1. Set appropriate retention periods: Use 1 day for cost-sensitive workloads, 7 days for regulatory compliance, and 90 days only when required by specific regulations.

2. Implement automated cloning schedules: Create scheduled tasks that clone critical tables daily, providing additional recovery points beyond Time Travel.

3. Use Time Travel for auditing: Query historical data to track data quality trends, identify when anomalies occurred, and validate regulatory compliance.

4. Clone before schema changes: Always clone tables before DDL operations, enabling instant rollback if changes cause issues.

5. Monitor Time Travel storage: Track historical data growth to optimize retention settings and manage storage costs.

6. Document recovery procedures: Create runbooks for common data recovery scenarios, including Time Travel queries and cloning operations.

7. Test recovery processes: Regularly test Time Travel queries and cloning operations to ensure they work as expected during actual emergencies.

8. Use UNDROP for accidental drops: Prefer UNDROP over Time Travel for recently dropped objects, as it's faster and doesn't require data copying.

9. Leverage clones for development: Create development environments by cloning production schemas, ensuring developers have realistic data without impacting production.

10. Monitor Fail-Safe eligibility: Understand that Fail-Safe only applies to dropped objects, not deleted data, and plan recovery strategies accordingly.

See Also

• Snowflake Architecture — Micro-partition versioning internals
• Access Control — Data masking for cloned environments
• Stored Procedures — Automated recovery procedures
• Snowpipe Ingestion — Data loading patterns
• PySpark Iceberg Tables — Time travel with Iceberg
• Delta Lake Fundamentals — Delta Lake time travel comparison