Advanced Time Travel in Snowflake

Time Travel in Snowflake enables querying historical data at any point in time, providing powerful capabilities for data recovery, auditing, and temporal analysis.

Architecture Diagram 2: Time Travel Retention Timeline

Time Travel Syntax

Querying at Specific Points

-- Query at specific timestamp
SELECT * FROM orders
  AT (TIMESTAMP => '2024-01-15 10:30:00'::TIMESTAMP);

-- Query at offset (seconds ago)
SELECT * FROM orders
  AT (OFFSET => -3600);  -- 1 hour ago

-- Query at specific statement ID
SELECT * FROM orders
  AT (STATEMENT => '01a1b2c3-0001-0000-0000-000000000000');

Time Travel Clauses

Advanced Time Travel Use Cases

Point-in-Time Recovery

-- Recover dropped table
CREATE TABLE orders_recovered CLONE orders
  AT (TIMESTAMP => '2024-01-15 10:00:00'::TIMESTAMP);

-- Recover from accidental DELETE
BEGIN TRANSACTION;
  DELETE FROM orders WHERE order_date < '2024-01-01';
COMMIT;

-- Restore to before DELETE
CREATE TABLE orders_restored CLONE orders
  AT (STATEMENT => '01a1b2c3-0001-0000-0000-000000000000');

-- Replace current table with restored version
CREATE OR REPLACE TABLE orders AS SELECT * FROM orders_restored;

Data Auditing

-- Track changes over time
SELECT
  'CURRENT' as version,
  COUNT(*) as row_count,
  SUM(amount) as total_amount
FROM orders
UNION ALL
SELECT
  'YESTERDAY' as version,
  COUNT(*) as row_count,
  SUM(amount) as total_amount
FROM orders AT (OFFSET => -86400);

-- Identify data modifications
SELECT
  order_id,
  amount as current_amount,
  amount as historical_amount
FROM orders
  AT (OFFSET => -86400) h
FULL OUTER JOIN orders c ON h.order_id = c.order_id
WHERE h.amount != c.amount OR h.order_id IS NULL OR c.order_id IS NULL;

Temporal Analysis

-- Analyze data drift over time
SELECT
  DATEADD('day', seq, '2024-01-01')::DATE as analysis_date,
  (SELECT COUNT(*) FROM orders AT (TIMESTAMP => analysis_date::TIMESTAMP)) as row_count
FROM TABLE(GENERATOR(ROWCOUNT => 30));

-- Compare metrics across time periods
SELECT
  'Current' as period,
  AVG(amount) as avg_amount,
  STDDEV(amount) as stddev_amount
FROM orders
UNION ALL
SELECT
  '1 Week Ago' as period,
  AVG(amount) as avg_amount,
  STDDEV(amount) as stddev_amount
FROM orders AT (OFFSET => -604800);

Time Travel with Cloning

-- Clone at specific point in time
CREATE TABLE january_snapshot CLONE orders
  AT (TIMESTAMP => '2024-01-31 23:59:59'::TIMESTAMP);

-- Create rolling snapshot
CREATE TABLE rolling_7day CLONE orders
  AT (OFFSET => -604800);

-- Clone with filtering
CREATE TABLE high_value_january CLONE orders
  AT (TIMESTAMP => '2024-01-31'::TIMESTAMP)
  WHERE amount > 1000;

Retention Configuration

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

-- Set schema-level retention
ALTER SCHEMA analytics SET DATA_RETENTION_TIME_IN_DAYS = 14;

-- Set database-level retention
ALTER DATABASE my_db SET DATA_RETENTION_TIME_IN_DAYS = 7;

-- Check current retention
SELECT
  table_name,
  data_retention_time_in_days,
  created_on
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'ANALYTICS';

Fail-safe Period

-- Monitor fail-safe usage
SELECT
  table_name,
  data_retention_time_in_days,
  fail_safe_bytes / 1024 / 1024 AS fail_safe_mb
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE fail_safe_bytes > 0;

Performance Considerations

-- Check time travel query performance
SELECT
  query_id,
  query_text,
  execution_time_ms,
  bytes_scanned
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD('hour', -1, CURRENT_TIMESTAMP())
))
WHERE query_text LIKE '%AT (%'
ORDER BY execution_time_ms DESC;

-- Optimize time travel queries
SELECT /*+ USE_CACHED_RESULT */
  *
FROM orders
  AT (OFFSET => -3600)
WHERE order_date = CURRENT_DATE();

Summary

Key Takeaways

Time Travel enables querying data at any point within the retention period without data movement.

Three query syntaxes available: TIMESTAMP, OFFSET, and STATEMENT for flexible historical access.

Essential for point-in-time recovery — restore tables to any previous state.

Cloning with Time Travel creates instant historical snapshots for analysis.

Fail-safe provides an additional 7-day protection window beyond time travel retention.

Retention Configuration Levels