What is Time Travel in Snowflake and how does it work (retention period, UNDROP, AT/BEFORE)?

Time Travel is Snowflake's ability to query, clone, or restore data as it existed at any point within a configurable retention window. When a row is deleted or updated, Snowflake does not immediately remove the old micro-partitions — it retains them for the duration of the Time Travel period. The original data is accessible via special SQL syntax.

The AT clause queries data as it existed at a specific moment using a timestamp, a negative offset in seconds from now, or a statement ID. The BEFORE clause returns the state just before a named statement executed — useful for recovering from a mistaken DML. After the retention window expires, data moves to the Fail-safe tier.

Retention period: Standard edition = 1 day. Enterprise+ = configurable per object up to 90 days (set via DATA_RETENTION_TIME_IN_DAYS). Longer retention increases storage costs since old micro-partitions are retained. Transient and temporary tables have 0-day Fail-safe and max 1-day Time Travel.

-- Query orders as they existed one hour ago
SELECT * FROM orders AT (OFFSET => -3600);

-- Restore data before an accidental DELETE (use the query_id of the DELETE statement)
SELECT * FROM orders BEFORE (STATEMENT => '01ab23cd-...');

-- Clone a table to its state from yesterday as a point-in-time backup
CREATE TABLE orders_backup
  CLONE orders AT (TIMESTAMP => '2024-06-01 09:00:00'::TIMESTAMP_NTZ);

-- Recover a dropped table (must be within retention window)
UNDROP TABLE orders;