Time Travel & Data Recovery

-- Query data as of a specific timestamp
SELECT *
FROM prod.analytics.daily_metrics
AT (TIMESTAMP => '2026-03-10 08:00:00'::TIMESTAMP);

-- Query data as of N seconds ago
SELECT *
FROM prod.analytics.daily_metrics
AT (OFFSET => -3600);  -- 1 hour ago

-- Query a specific version (from QUERY_ID)
SELECT *
FROM prod.analytics.daily_metrics
BEFORE (STATEMENT => '01abc-def-ghi');

-- Oops! Someone ran DELETE without WHERE
-- Recover the data:
CREATE TABLE prod.analytics.daily_metrics_recovered
CLONE prod.analytics.daily_metrics
AT (TIMESTAMP => '2026-03-10 07:00:00'::TIMESTAMP);

-- Verify recovered data, then swap
ALTER TABLE prod.analytics.daily_metrics
SWAP WITH prod.analytics.daily_metrics_recovered;

-- Oops! Someone dropped the table
-- Undrop it:
UNDROP TABLE prod.analytics.daily_metrics;

-- Also works for schemas and databases
UNDROP SCHEMA prod.analytics;
UNDROP DATABASE prod;

-- Find what changed between two timestamps
SELECT
  'deleted' AS change_type, old.*
FROM prod.analytics.daily_metrics
  AT (TIMESTAMP => '2026-03-10'::TIMESTAMP) old
LEFT JOIN prod.analytics.daily_metrics current
  ON old.metric_date = current.metric_date
WHERE current.metric_date IS NULL

UNION ALL

SELECT
  'added' AS change_type, current.*
FROM prod.analytics.daily_metrics current
LEFT JOIN prod.analytics.daily_metrics
  AT (TIMESTAMP => '2026-03-10'::TIMESTAMP) old
  ON current.metric_date = old.metric_date
WHERE old.metric_date IS NULL;

-- Set retention period (1-90 days, default 1)
ALTER TABLE prod.analytics.daily_metrics
SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- Check current retention
SHOW TABLES LIKE 'daily_metrics' IN SCHEMA prod.analytics;