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;

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.