◐ ai-teacher

❄️ Warehouse & Modeling · Chapter 9

Time travel, zero-copy clones, streams and tasks

Immutable micro-partitions give Snowflake four superpowers for free: query the past, undo a bad DELETE, copy a 20 TB database in seconds, and know exactly which rows changed since you last looked.

Study this lesson free in the app →

The basics

Because Snowflake never edits micro-partitions in place — every change writes new files and keeps the old ones for a while — it can offer four features almost for free:

Why they matter: they replace a pile of external machinery — backup jobs, test-environment copies, CDC tooling, a scheduler for simple incremental loads.

The mental model: a notebook written in pen where you never erase, only add new pages. Time travel is flipping back. A clone is a second bookmark into the same pages. A stream is a sticky note saying "read up to here".

Words you must know 4

retention period
how long old versions are kept
fail-safe
a further 7-day recovery window only Snowflake support can use
offset
a stream's bookmark
task graph
tasks chained with AFTER

Explained

Thursday, 4:47 pm. An engineer at Meghalaya Cabs runs:

DELETE FROM fact_rides WHERE city_key = 7;   -- meant: in the dev schema

31 million Guwahati rides, gone from production.

4:52 pm. One statement later, they're back — no backup restore, no ticket, no data copied:

INSERT INTO fact_rides
SELECT * FROM fact_rides BEFORE(STATEMENT => '01b2c3d4-...')
WHERE city_key = 7;

Time travel and fail-safe, precisely

Retention is set by DATA_RETENTION_TIME_IN_DAYS on the account, database, schema or table:

During retention you can:

After retention, permanent tables enter fail-safe for 7 more days. You cannot query it; only Snowflake support can recover from it, as a disaster-recovery last resort. Transient and temporary tables have no fail-safe — which is why staging tables are often transient: cheaper storage, accepted risk.

Cost: every micro-partition replaced by DML stays billable through retention + fail-safe. A table rewritten daily with 90-day retention can store ~90× its live size.

Time travel and clones in practice

-- What did yesterday's dashboard see?
SELECT city_key, SUM(fare_inr)
FROM   fact_rides AT(TIMESTAMP => '2026-11-04 09:00:00 +0530'::timestamp_tz)
GROUP  BY 1;

-- State 1 hour ago
SELECT COUNT(*) FROM dim_driver AT(OFFSET => -3600);

-- Undo a dropped table
DROP TABLE dim_zone;
UNDROP TABLE dim_zone;

-- Restore a table to just before a bad MERGE
CREATE OR REPLACE TABLE fact_rides_restored
  CLONE fact_rides BEFORE(STATEMENT => '01b2c3d4-0000-...');
ALTER TABLE fact_rides SWAP WITH fact_rides_restored;

-- A full production copy for testing a migration, in seconds
CREATE DATABASE analytics_dev CLONE analytics;

-- Longer retention only where it earns its storage
ALTER TABLE fact_rides SET DATA_RETENTION_TIME_IN_DAYS = 14;
CREATE TRANSIENT TABLE stg_rides_raw (...);   -- no fail-safe, cheap

SWAP WITH exchanges two tables atomically, so readers never see a half-restored table.

What a clone shares

CREATE DATABASE analytics_dev CLONE analytics;    (20 TB, ~3 s)

analytics      → [MP-1] [MP-2] [MP-3] … [MP-96000]
analytics_dev  → [MP-1] [MP-2] [MP-3] … [MP-96000]   same files
                                                     new storage: 0

UPDATE analytics_dev.marts.fact_rides SET … WHERE city_key = 7;

analytics      → [MP-1] [MP-2 ] [MP-3] …
analytics_dev  → [MP-1] [MP-2'] [MP-3] …   only MP-2' is new storage

The clone is a fully independent, writable object. Changes on either side never affect the other — they just stop sharing those files.

Stream + task = incremental pipeline in pure SQL

CREATE STREAM stg_rides_s ON TABLE stg_rides;

CREATE TASK load_fact_rides
  WAREHOUSE = elt_wh
  SCHEDULE  = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('STG_RIDES_S')     -- no data → no warehouse
AS
MERGE INTO fact_rides f
USING (SELECT * FROM stg_rides_s
       WHERE METADATA$ACTION = 'INSERT') s        -- inserts + new side of updates
ON f.ride_id = s.ride_id
WHEN MATCHED THEN UPDATE SET f.fare_inr = s.fare_inr
WHEN NOT MATCHED THEN INSERT (ride_id, driver_key, fare_inr)
                      VALUES (s.ride_id, s.driver_key, s.fare_inr);

-- Downstream step runs only after the root task finishes
CREATE TASK refresh_driver_daily
  WAREHOUSE = elt_wh
  AFTER load_fact_rides
AS CALL sp_refresh_driver_daily();

-- Tasks are created SUSPENDED: resume children first, then the root
ALTER TASK refresh_driver_daily RESUME;
ALTER TASK load_fact_rides RESUME;

Omit WAREHOUSE to make it a serverless task — Snowflake sizes compute itself and bills it as serverless credits.

At production scale

Gotchas

Old files are kept, so the past is queryable. Time travel = flip back; clone = second pointer; stream = bookmark; task = standing instruction.

Retention 1 day default (Enterprise up to 90) → then 7 days fail-safe, support-only.

Streams move only on committed DML. Tasks start suspended.

Words to know

Time travelQuerying, cloning or restoring data as of a past point within the object's data retention period.
Fail-safeA 7-day, non-queryable recovery period after time travel for permanent tables, usable only by Snowflake support.
Zero-copy cloneA metadata-only copy of a table, schema or database that shares micro-partitions with the source until either changes.
StreamAn object that tracks an offset on a table and exposes row-level changes since that offset, with change metadata columns.
Stream offsetThe table version a stream has consumed up to; it advances only when the stream is read in a committed DML statement.
TaskA Snowflake object that runs SQL on a schedule or after a predecessor task, optionally gated by a WHEN condition.
Transient tableA table with at most 1 day of time travel and no fail-safe, used for re-creatable data to save storage.

In two minutes

Test yourself

1. What is the default data retention (time travel) period for a Snowflake table?

  1. 7 days
  2. 1 day
  3. 90 days
  4. 0 days
Show the answer

1 day — Default DATA_RETENTION_TIME_IN_DAYS is 1. Enterprise edition can raise it to 90 for permanent tables; 7 days is fail-safe, a different thing.

2. Who can recover data from fail-safe?

  1. Any role with OWNERSHIP on the table
  2. ACCOUNTADMIN with an AT clause
  3. Only Snowflake support
  4. Nobody — fail-safe data is deleted immediately
Show the answer

Only Snowflake support — Fail-safe is a disaster-recovery window operated by Snowflake. It isn't queryable, and transient/temporary tables don't have it.

3. What makes a zero-copy clone 'zero-copy'?

  1. It copies data in compressed form
  2. It creates new metadata pointing at the same micro-partitions; storage is only used when either side changes
  3. It's a read-only view
  4. It streams data lazily from the source on each query, so it breaks if the source is dropped
Show the answer

It creates new metadata pointing at the same micro-partitions; storage is only used when either side changes — Cloning duplicates metadata only. The clone is fully writable and independent; diverging changes create new micro-partitions for that side alone.

4. An analyst runs SELECT * FROM rides_s (a stream) three times during debugging. What happens to the offset?

  1. It advances each time, so the second and third SELECT return nothing
  2. It advances after the third read
  3. It resets to the table's creation
  4. It doesn't move — only a committed DML statement reading the stream advances it
Show the answer

It doesn't move — only a committed DML statement reading the stream advances it — Plain queries are side-effect free. The offset moves only when the stream is consumed in a DML transaction that commits.

5. A row is updated in the source table. How does it appear in a standard stream (if it existed before the offset)?

  1. As a DELETE row and an INSERT row, both with METADATA$ISUPDATE = TRUE
  2. As one UPDATE row
  3. Not at all — streams only track inserts
  4. As two INSERT rows
Show the answer

As a DELETE row and an INSERT row, both with METADATA$ISUPDATE = TRUE — Streams represent an update as the old row (DELETE) and the new row (INSERT), flagged with ISUPDATE = TRUE. Append-only streams ignore updates and deletes.

6. You created a task with CREATE TASK … SCHEDULE = '5 MINUTE'. Nothing has run after an hour. Most likely why?

  1. The schedule syntax needs cron
  2. Tasks run only on Enterprise edition
  3. Tasks are created suspended; it needs ALTER TASK … RESUME
  4. The warehouse is too small
Show the answer

Tasks are created suspended; it needs ALTER TASK … RESUME — Every new task starts suspended. In a task graph, resume the children before the root.

There are 9 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.

Learn it so it stays. Read it, test yourself, then recall it on two different days. The app schedules that for you, free.

Start this lesson →