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.

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();