Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 38 ยท Architecture

Time Travel, Fail-safe and Cloning

Query and restore past data with Snowflake Time Travel, understand the 7-day Fail-safe, and create zero-copy clones for testing. Includes UNDROP and retention costs.

Because Snowflake never edits micro-partitions in place, old versions of your data still exist for a while after a change. Three features build on that: Time Travel lets you query or restore past states, Fail-safe is a last-resort recovery window, and cloning makes instant copies. Together they turn "I deleted the wrong rows" from a disaster into a one-line fix.

Time Travel

Time Travel keeps the previous state of tables, schemas and databases for the data retention period. It is 1 day by default on all editions, and up to 90 days on Enterprise and above for permanent objects (transient and temporary tables allow 0 or 1 day).

SQL
-- Snowflake SQL (read-only)
-- The table as it was 10 minutes ago
SELECT * FROM shop.raw.orders AT (OFFSET => -60*10);

-- The table at an exact time
SELECT * FROM shop.raw.orders AT (TIMESTAMP => '2025-06-01 09:00:00'::TIMESTAMP_LTZ);

-- The table just before a specific statement ran
SELECT * FROM shop.raw.orders BEFORE (STATEMENT => '01b2c3d4-0000-1234-0000-000000000000');

AT includes changes made at that moment; BEFORE shows the state just prior. The statement ID comes from Query History, which makes BEFORE (STATEMENT => ...) perfect for undoing a bad DELETE.

Fixing a mistake

Suppose someone ran DELETE FROM orders WHERE region = 'EU' by accident. Recover with a clone from just before the statement, or insert the lost rows back:

SQL
-- Snowflake SQL (read-only)
INSERT INTO shop.raw.orders
SELECT * FROM shop.raw.orders BEFORE (STATEMENT => '<the delete query id>')
WHERE region = 'EU';

You can also restore a dropped object, as long as it is inside the retention period:

SQL
-- Snowflake SQL (read-only)
DROP TABLE shop.raw.orders;
UNDROP TABLE shop.raw.orders;

Here is a runnable model of the same recovery idea: keep a history table of deleted rows (Snowflake does this for you through immutable partitions) and restore from it.

SQL Stand-in: restoring accidentally deleted rows ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Setting retention

SQL
-- Snowflake SQL (read-only)
ALTER TABLE shop.raw.orders SET DATA_RETENTION_TIME_IN_DAYS = 14;
ALTER DATABASE shop SET DATA_RETENTION_TIME_IN_DAYS = 7;   -- default for objects inside
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN TABLE shop.raw.orders;

Cost: all the old partitions kept for Time Travel count as storage. A table you rewrite every day with a 90-day retention can cost many times its visible size. Use long retention only for important tables and choose transient tables with 0 days for staging data you can reload.

Fail-safe

After Time Travel ends, permanent tables enter a 7-day Fail-safe. You cannot query or restore it yourself; only Snowflake support can recover data in this window, as a last resort for catastrophic failures. It is billed as storage. Transient and temporary tables have no Fail-safe. Total protection for a permanent table is therefore retention plus 7 days.

flowchart LR
  A["Data changed or dropped"] --> B["Time Travel<br/>1-90 days<br/>you restore"]
  B --> C["Fail-safe<br/>7 days<br/>Snowflake support only"]
  C --> D["Permanently deleted"]

Time Travel is self-service recovery; Fail-safe is disaster recovery.

Zero-copy cloning

CLONE creates a new object that shares the same micro-partitions as the source at that moment. No data is copied, so cloning a multi-terabyte database takes seconds and uses no extra storage initially. From then on, the two objects diverge independently: changes to one do not affect the other, and storage grows only by the changed partitions.

SQL
-- Snowflake SQL (read-only)
CREATE TABLE orders_test CLONE shop.raw.orders;
CREATE SCHEMA analytics_dev CLONE shop.analytics;
CREATE DATABASE shop_qa CLONE shop;

-- Clone as of the past: combine with Time Travel
CREATE TABLE orders_yesterday CLONE shop.raw.orders AT (OFFSET => -86400);

Common uses:

  • a full copy of production for testing, in seconds, at almost no cost,
  • a snapshot before a risky migration,
  • a safe sandbox for analysts,
  • reproducible datasets for machine-learning experiments.

Clones inherit the privileges of child objects but not those on the clone target itself; grants on the cloned container must be set up again.

The idea (a copy that shares until changed) looks like this in a stand-in: a table of "partitions" with a pointer table per clone. Updating the clone writes a new partition and repoints only the clone:

SQL Stand-in: a clone shares partitions until it changes

Press Run (or Ctrl + Enter) to see the result here.

The original still points at partition 2; the clone points at the new one; partition 1 is shared. That is exactly how real storage is shared.

Limits and good habits

  • Time Travel applies to tables, schemas and databases, not to external tables or the contents of stages.
  • Dropping a table and creating one with the same name hides the old one; UNDROP then needs the old table renamed first.
  • Drop clones you no longer need: they pin old partitions in storage.
  • Treat Time Travel as safety net, not backup. For compliance, keep separate retention or replication.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the default Time Travel retention on a Standard edition account?

    All editions default to 1 day. Enterprise and above can raise permanent objects to 90.
  2. 2. Who can recover data during the 7-day Fail-safe?

    Fail-safe is not self-service. Snowflake support may recover data as a last resort.
  3. 3. What does CREATE TABLE t2 CLONE t1 do immediately?

    A clone shares storage with its source until either one is changed.
  4. 4. A DELETE removed the wrong rows 2 hours ago. Retention is 1 day. Best fix?

    Time Travel with BEFORE (STATEMENT => id) lets you read the earlier state and restore from it.
  5. 5. Why can long retention on a frequently rewritten table be expensive?

    Every rewrite leaves old partitions that stay for the retention period.

Summary

  • Time Travel lets you query, clone or undrop past data within the retention period (1 day default, up to 90 on Enterprise).
  • Fail-safe adds 7 days of Snowflake-only recovery for permanent tables.
  • Cloning shares micro-partitions, so copies are instant and initially free.
  • Retention and clones affect storage cost; tune retention per table.
  • Chapter 2 is done: you know the layers, storage, compute, metadata, caching and recovery.

Next: Stages and File Formats