Time Travel and Zero-Copy Cloning in Snowflake

Time Travel

Snowflake keeps previous versions of changed data for a retention period: 1 day by default, up to 90 days on Enterprise edition.

SELECT * FROM fact_sales AT (OFFSET => -3600);                  -- one hour ago
SELECT * FROM fact_sales BEFORE (STATEMENT => '01b2…query_id');  -- before a bad statement
UNDROP TABLE fact_sales;                                         -- restore a dropped table

Recovering from a bad load

CREATE OR REPLACE TABLE fact_sales_restored CLONE fact_sales
  BEFORE (STATEMENT => '01b2…query_id');
ALTER TABLE fact_sales SWAP WITH fact_sales_restored;

Find the query id of the bad statement in QUERY_HISTORY, clone the table as it was just before it, and swap.

Zero-copy cloning

CREATE DATABASE analytics_dev CLONE analytics;

A clone shares the original's storage. Only data changed afterwards takes new storage. That makes it cheap to give each developer or CI run a full copy of production structure and data.

Costs and limits

• Retention costs storage: churned data is kept for the whole retention period, plus 7 days of Fail-safe that only Snowflake support can recover.
• Transient and temporary tables have at most 1 day of Time Travel and no Fail-safe. Use them for staging data you can rebuild.