Snowflake SQL Patterns

Time Travel and zero-copy clones

Query or clone a past state, restore a dropped table, and avoid treating AT(TIMESTAMP) as a session-local wall clock.

The problem

Someone ran a bad UPDATE, dropped a table, or you need a realistic copy of production for a migration dry-run. Snowflake Time Travel lets you query, clone, or restore objects as they existed within a retention window. Zero-copy CLONE lets you copy a table, schema, or database without immediately copying storage. Both are easy to use slightly wrong.

A working example

Official Time Travel SQL extensions are AT | BEFORE (on SELECT and on CREATE … CLONE) and UNDROP. AT / BEFORE take one of TIMESTAMP, OFFSET (seconds from now, as -N), or STATEMENT (a query id).

-- Snowflake syntax from official documentation (not executed)

-- 5 minutes ago (OFFSET is in seconds)
SELECT * FROM orders AT (OFFSET => -60 * 5);

-- Explicit timestamp type matters; see the gotcha
SELECT * FROM orders AT (
  TIMESTAMP => '2026-04-25 09:00:00'::TIMESTAMP_LTZ
);

SELECT * FROM orders BEFORE (STATEMENT => 'query-id-here');

UNDROP TABLE orders;

CREATE TABLE orders_clone CLONE orders;

CREATE TABLE orders_yesterday CLONE orders
  AT (OFFSET => -86400);

Official retention: the standard period is 1 day (24 hours) and is on by default. Edition limits from the official Time Travel guide:

Edition Permanent objects Transient / temporary Maximum
Standard 0 or 1 day (default 1) 0 or 1 day (default 1) 1 day
Enterprise and higher 0 to 90 days 0 or 1 day (default 1) 90 days permanent; 1 day transient/temporary

Longer retention uses more storage. Official docs say extended retention is reflected in monthly storage charges. This site does not invent a price.

After the retention window, data moves to Fail-safe and these Time Travel actions are no longer available. Official docs also note that a long-running Time Travel query delays moving the involved objects into Fail-safe until the query completes.

CLONE of a database or schema can skip tables that have already aged out of Time Travel (IGNORE TABLES WITH INSUFFICIENT DATA RETENTION) and can skip hybrid tables (IGNORE HYBRID TABLES). Official docs say a schema clone that includes hybrid tables fails unless you ignore them.

The gotcha

Timestamp type in AT (TIMESTAMP => …). Official CREATE … CLONE documentation: the value must be explicitly cast to TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ. If you do not cast, the timestamp is treated as UTC (equivalent to TIMESTAMP_NTZ). Casting with the TIMESTAMP type may also be treated as TIMESTAMP_NTZ, because official date-time docs say TIMESTAMP defaults to TIMESTAMP_NTZ via TIMESTAMP_TYPE_MAPPING. If you meant “09:00 in the session time zone,” write TIMESTAMP_LTZ (or an unambiguous TIMESTAMP_TZ). See timestamp types.

Restoring deleted rows is not INSERT … SELECT * FROM t AT (…). AT returns the historical snapshot of the whole table. Inserting that snapshot back into the current table will add historical rows on top of rows that still exist, which duplicates survivors. The logical fix — not executed here — is to insert only keys that are present in the snapshot and absent now, or to clone the historical table and swap, or to UNDROP if you dropped the object itself.

A clone at time T does not include objects created after T. Official Time Travel examples show a table created after the clone timestamp missing from the cloned schema.

Retention is not uniform. Transient and temporary tables stay at a 1-day maximum even on Enterprise. Dropped objects keep the retention they had; official docs note that changing a database or schema retention period does not rewrite already-dropped objects inside it.

Clones start cheap because they share storage; they do not stay free if the clone or the source then diverge under DML. Official clone documentation is the place to read current storage behavior — do not budget from a blog memory of “clones are always free.”

When not to use this

Verification

Syntax, retention limits, Fail-safe transition, UNDROP, CLONE options, the uncast timestamp rule, hybrid-table clone behavior, and the effect of changing retention on already-dropped objects are from official Snowflake documentation. Nothing on this page was executed on Snowflake, and no storage-cost figures are given.

Official docs