Snowflake – Time Travel, Cloning and Undrop

These two features come from the same place — micro-partitions are immutable, so the old ones are still there — and between them they change how you work rather than just what you can recover. Time Travel means a bad UPDATE is a query away from being undone. Cloning means a full copy of production for a development branch costs nothing and takes a second.

Time Travel

Every table has a retention period during which its previous states remain queryable.

Note the default: 1 day, not 90, even on Enterprise. Discovering that after a week-old mistake is a bad way to learn it. Set it deliberately, at the database level so objects inherit it:

ALTER DATABASE analytics SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- Long retention on the tables that are a source of truth...
ALTER TABLE analytics.marts.fact_orders SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- ...and none on the ones rebuilt from scratch every night.
ALTER TABLE analytics.staging.stg_orders SET DATA_RETENTION_TIME_IN_DAYS = 0;

SELECT table_name, retention_time
FROM   analytics.information_schema.tables
WHERE  table_schema = 'MARTS';

Retention is not free: those old micro-partitions occupy storage you pay for. Thirty days on a rapidly-changing large table can hold a surprising amount, which is the trade-off — set it high where the data matters and zero where it does not.

Querying the past

Three clauses, all of which go straight after the table name:

-- As of a point in time.
SELECT * FROM fact_orders AT(TIMESTAMP => '2026-08-21 09:00:00'::TIMESTAMP_NTZ);

-- As of N seconds ago. Negative numbers, always.
SELECT * FROM fact_orders AT(OFFSET => -60 * 30);       -- 30 minutes back

-- Immediately BEFORE a specific statement ran. This is the one you want
-- when you know which statement did the damage.
SELECT * FROM fact_orders BEFORE(STATEMENT => '01a2b3c4-0000-0000-0000-000000000000');

BEFORE(STATEMENT => …) is the precise form, and finding the query id is part of the recovery:

SELECT query_id, query_text, user_name, start_time, rows_updated, rows_deleted
FROM   snowflake.account_usage.query_history
WHERE  query_type IN ('UPDATE', 'DELETE', 'MERGE', 'INSERT')
  AND  query_text ILIKE '%fact_orders%'
  AND  start_time >= DATEADD('hour', -6, CURRENT_TIMESTAMP())
ORDER  BY start_time DESC;

Recovering from a mistake

The instinct is to restore over the top of the damaged table. Resist it — you have exactly one chance to get this right, and overwriting destroys the evidence. Land the recovered state somewhere new first, check it, then swap.

-- 1. Look at what you would restore. Nothing is changed yet.
SELECT COUNT(*) FROM fact_orders BEFORE(STATEMENT => '01a2b3c4-...');

-- 2. Materialise it beside the damaged table.
CREATE TABLE fact_orders_recovered CLONE fact_orders
  BEFORE(STATEMENT => '01a2b3c4-...');

-- 3. Compare before committing to it.
SELECT (SELECT COUNT(*) FROM fact_orders)           AS now,
       (SELECT COUNT(*) FROM fact_orders_recovered) AS recovered;

-- 4. Swap atomically. Both names exist throughout; nothing is dropped.
ALTER TABLE fact_orders SWAP WITH fact_orders_recovered;

-- 5. Only once you are certain.
DROP TABLE fact_orders_recovered;

SWAP WITH exchanges two tables' names and metadata in one atomic operation. It is the safe way to replace a table that queries are running against, and it is equally useful for a nightly rebuild — build the new version beside the old one, swap, drop.

UNDROP

A dropped object is recoverable for its retention period, which makes the usual catastrophe survivable:

DROP TABLE fact_orders;
UNDROP TABLE fact_orders;

UNDROP SCHEMA analytics.marts;
UNDROP DATABASE analytics;

-- Dropped objects, including several generations of the same name.
SHOW TABLES HISTORY LIKE 'fact_orders' IN SCHEMA analytics.marts;

One caveat worth knowing before you need it: UNDROP restores the most recent object of that name. If a table was dropped and a new one created with the same name, you must rename the current one out of the way first, or the UNDROP has nowhere to land.

Fail-safe is not your backup

After Time Travel expires, permanent tables enter a 7-day Fail-safe period. It is easy to mistake this for a second chance. It is not:

• You cannot query it and you cannot restore from it yourself.
• Recovery requires opening a support case, and takes hours to days.
• You pay to store it, for every permanent table, whether or not you ever use it.
• Transient and temporary tables have none at all — which is exactly why lesson 5 recommends transient for anything rebuildable.

Treat Fail-safe as Snowflake's disaster insurance, not as your recovery plan. Your recovery plan is retention set appropriately plus, for anything genuinely irreplaceable, periodic clones.

Zero-copy cloning

A clone is a new object sharing the original's micro-partitions. It is created from metadata alone, so it is instant regardless of size and consumes no storage at creation. Storage accrues only as the two diverge — changed partitions are written for whichever side changed them.

CREATE TABLE    orders_snapshot CLONE analytics.marts.fact_orders;
CREATE SCHEMA   marts_test      CLONE analytics.marts;
CREATE DATABASE analytics_dev   CLONE analytics;

-- Clone as of a point in the past: a snapshot you forgot to take.
CREATE TABLE orders_monday CLONE analytics.marts.fact_orders
  AT(TIMESTAMP => '2026-08-17 00:00:00'::TIMESTAMP_NTZ);

Cloning a database clones its schemas, tables, views, streams, sequences and file formats. Internal named stages are not cloned, and neither is their content — a pipeline that reads from a stage needs that wired up separately in the clone.

Three uses that change how a team works:

• Development against real data. CREATE DATABASE analytics_dev CLONE analytics gives every engineer production data to develop against, at no storage cost, with no possibility of touching production.
• Testing a migration. Clone, run the migration on the clone, inspect, throw the clone away. The rehearsal is free.
• A snapshot before something risky. Clone the table before the bulk update. If it goes wrong, SWAP WITH and it never happened.

Two things to know about permissions. A cloned object does not inherit the source's grants by default — the cloning role owns it, and access has to be granted again. And a clone is a fully independent object from the moment it exists: writing to it never affects the source, and dropping the source never affects the clone.

What retention actually costs

Both features are paid for in storage, and the bill is invisible until you look for it. Every version of a micro-partition kept for Time Travel is storage, and a clone that has diverged substantially from its source is storage too. One view shows all of it:

SELECT table_catalog,
       table_schema,
       table_name,
       ROUND(active_bytes       / POWER(1024, 3), 2) AS active_gb,
       ROUND(time_travel_bytes  / POWER(1024, 3), 2) AS time_travel_gb,
       ROUND(failsafe_bytes     / POWER(1024, 3), 2) AS failsafe_gb,
       ROUND(retained_for_clone_bytes / POWER(1024, 3), 2) AS clone_gb
FROM   snowflake.account_usage.table_storage_metrics
WHERE  deleted = FALSE
ORDER  BY time_travel_bytes DESC
LIMIT  20;

A table where time_travel_gb dwarfs active_gb is one that is being rewritten heavily — a nightly full reload of a large table keeps every night's version for the whole retention window. That is precisely the table that should be transient with retention set to zero, because you can rebuild it from source anyway.

The mirror-image mistake is setting retention to zero on the tables that matter to save a little storage, and then having no way back from a bad merge. The distinction is not size, it is whether the data can be regenerated: retention is for what you cannot rebuild.

Two more limits, so they are not a surprise. Changing retention downward does not immediately purge history — versions already outside the new window age out rather than vanishing. And a clone holds its source's historical partitions alive for as long as the clone exists, which means dropping a clone can reduce storage on a table you did not touch.

Next: streams and tasks, for pipelines that run themselves.