Lesson 2.4 — Time Travel & Zero-Copy Cloning

These two features are called out by name in the JD and are unique enough to Snowflake that interviewers use them as a litmus test for real Snowflake experience. Master them here — both are 20 minutes of concept, 10 minutes of hands-on.

Time Travel

Time Travel lets you query a table as it was at a point in the recent past:

-- As of 10 minutes ago
SELECT * FROM meter_reads AT(OFFSET => -60*10);

-- As of a specific timestamp
SELECT * FROM meter_reads AT(TIMESTAMP => '2026-04-20 14:30:00'::TIMESTAMP_NTZ);

-- BEFORE a specific statement ID (everything up to, but not including, the mistake)
SELECT * FROM meter_reads BEFORE(STATEMENT => '01ab...-3333');

Retention windows

-- Enterprise: extend retention on a specific table
ALTER TABLE meter_reads SET DATA_RETENTION_TIME_IN_DAYS = 30;

Fail-safe — what Time Travel is NOT

Fail-safe is a 7-day grace period after Time Travel expires. It's not user-accessible — only Snowflake Support can recover data from Fail-safe. You pay for its storage, can't disable it on permanent tables. Transient and temporary tables skip Fail-safe (saves cost but you have no second chance).

Four practical use cases

1. "Undo" a bad UPDATE/DELETE. Before: BEGIN; DELETE FROM meter_reads WHERE ...; COMMIT; — oops. After: INSERT INTO meter_reads SELECT * FROM meter_reads AT(OFFSET => -60);.
2. Investigate a pipeline run. "What did the Matillion job see yesterday at 2am?" → AT(TIMESTAMP).
3. Reproducible reports. Regulator asks for the Q3 numbers as-of-last-week → AT a known timestamp.
4. Diffing. SELECT * FROM t MINUS SELECT * FROM t AT(OFFSET => -3600); shows what changed in the last hour.

Zero-Copy Cloning

Zero-Copy Clone creates a new object (table, schema, or database) that shares underlying storage with the source. Near-instant, no initial storage cost. New storage is consumed only when either side changes.

-- Clone a database
CREATE DATABASE LEARN_DB_DEV CLONE LEARN_DB;

-- Clone with time travel: "as it was 1 hour ago"
CREATE TABLE meter_reads_1h_ago CLONE meter_reads AT(OFFSET => -3600);

-- Clone a schema
CREATE SCHEMA UTILITY_SANDBOX CLONE UTILITY;

What changes; what doesn't

Four practical use cases

1. Dev environments from prod. CREATE DATABASE prod_clone CLONE PROD;. Instant, full production data, free (until you change things). Drop when done.
2. Per-PR CI environments. In your Jenkins pipeline: clone, apply migrations, test, drop. (See Exercise 6.1.)
3. Point-in-time snapshots before risky operations. CREATE TABLE meter_reads_safety CLONE meter_reads; → do the thing → if OK, drop the clone; if not, swap.
4. Experiments. Hand a cloned database to a data scientist for ML feature engineering without fear of corrupting prod.

The Zero-Copy-Clone + Time-Travel combo (the showstopper)

Together they give you something almost no other database has:

-- "Give me a full copy of the production database as it was last Tuesday at 3pm,
--  in under a second, using no new storage until I change it."
CREATE DATABASE analysis_as_of_tue_3pm
  CLONE PROD
  AT(TIMESTAMP => '2026-04-15 15:00:00'::TIMESTAMP_NTZ);

In an old MPP, this is a multi-hour, multi-TB copy. In Snowflake, it's the syntax above.