Time Travel and Zero-Copy Clones

Every engineer has run the query. The UPDATE with the WHERE clause you forgot, the DELETE that matched more rows than you meant, the moment the row count comes back wrong and your stomach drops. On most databases the next move is a restore from last night’s backup, a lost afternoon, and an apologetic message in a channel. Snowflake has a different next move, and it takes about ten seconds. It can show you the table as it looked before your mistake, and hand those rows back. The feature next to it lets you copy an entire production database for a new engineer to break, at a storage cost of zero. Both feel like magic. Both are actually the same piece of plumbing — Snowflake keeps its storage as immutable, versioned chunks, and once you know that, the magic turns into arithmetic.

Time Travel: the table remembers

Snowflake keeps old versions of your data around for a window after every change, and lets you query straight into that window. This is Time Travel, and you reach it by hanging one of two clauses off a table reference: AT, which addresses a moment inclusive of the change at that point, and BEFORE, which addresses the state just prior to it. Each takes one of three coordinates — a relative offset, an absolute timestamp, or a specific statement — so there are effectively six ways to name a point in a table’s past. In practice you’ll use three of them constantly.

Query the table as it was a set number of seconds ago with an offset — here, one hour back:

SELECT * FROM orders AT(OFFSET => -3600);

The offset is always negative and always in seconds, so -3600 is “one hour ago,” -86400 is “yesterday at this exact time.” It’s the quickest way in when you don’t need precision, just “before all this went wrong a little while ago.”

Or as of an exact wall-clock moment, which is what you reach for when you know when things were still fine — say a colleague messaged “the numbers looked right at 9am”:

SELECT * FROM orders AT(TIMESTAMP => '2026-07-04 09:00:00'::timestamp);

Or — the one that saves your afternoon — as of the instant before a specific statement ran. This is the surgical option: not “roughly an hour ago,” but “the state of the table immediately before that one query touched it.”

Finding the query id you’re time-travelling to

Here’s the part most tutorials wave past, and it’s the part you actually need: BEFORE(STATEMENT => '...') wants a query id, and a query id is not something you know off the top of your head. It’s a UUID-shaped string like 01af8c3e-0000-d5a2-0000-6f5900a1b2c3 that Snowflake assigns to every statement it runs. So before you can travel to a point before a statement, you have to find that statement. There are two ways, depending on whether the culprit is your own last query or something that ran a while ago.

If you just ran the offending statement yourself, in this same session, the id is one function call away:

SELECT LAST_QUERY_ID();

LAST_QUERY_ID() returns the id of the most recent statement in your session; passing it a negative number walks further back (LAST_QUERY_ID(-2) is the one before that). You can even inline it, which is the fastest possible recovery — no copy-pasting UUIDs:

SELECT * FROM orders BEFORE(STATEMENT => LAST_QUERY_ID(-1));

More often, though, the damage was done a while ago, by someone else, or in a session that’s long closed. Then you go to the query history. Snowflake keeps it in two places. The INFORMATION_SCHEMA.QUERY_HISTORY table function is the low-latency one — it reads recent activity (roughly the last 7 days) with essentially no delay:

SELECT query_id, query_text, start_time, rows_produced
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_text ILIKE '%UPDATE orders%'
ORDER BY start_time DESC;

For anything older, or for account-wide auditing, there’s SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY — a shared view retaining a full year of history. It lags real time by up to about 45 minutes, so it’s the archive, not the live feed:

SELECT query_id, query_text, user_name, start_time
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_type = 'UPDATE'
  AND start_time > DATEADD('hour', -3, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;

Either way, you eyeball the results, find the row whose query_text is your regrettable UPDATE, and copy its query_id. (The Snowsight UI has the same history under Monitoring → Query History, where you can click a query and copy its id straight off the details pane — often the fastest route mid-incident.) Now the surgical time-travel query has a real value to work with:

SELECT * FROM orders BEFORE(STATEMENT => '01af8c3e-0000-d5a2-0000-6f5900a1b2c3');

From “look at the past” to “restore the past”

Reading the past is diagnosis; the cure is writing it back. You ran a bad UPDATE and half the orders table is wrong. You don’t restore from a backup — you overwrite the damage with the pre-damage version, read live out of the table’s own history:

CREATE OR REPLACE TABLE orders AS
  SELECT * FROM orders BEFORE(STATEMENT => '01af8c3e-0000-d5a2-0000-6f5900a1b2c3');

The table is whole again, sourced from a version of itself that existed a moment ago. If only some rows are wrong and you’d rather surgically mend them than rebuild the whole table, the same source works inside a MERGE, so you can reconcile just the affected keys against their past selves. But for a wholesale “undo the last few minutes,” the CREATE OR REPLACE is blunt and effective.

And for the bluntest disaster of all — you dropped the wrong table entirely — there’s a one-liner that needs no timestamp, no query id, nothing:

UNDROP TABLE orders;

The table comes back exactly as it was at the drop, because Snowflake never truly deleted it; it marked it gone and kept the bytes inside the retention window. The same verb works up the hierarchy — UNDROP SCHEMA analytics and UNDROP DATABASE prod restore a whole schema or database and everything under it. There’s one failure mode worth knowing before you need it: UNDROP restores to the original name, so if you dropped orders, then created a new table also called orders, the undrop has nowhere to land and fails with a name-collision error. The fix is to rename the live object out of the way first, then undrop. Snowflake won’t silently clobber the new table to make room for the old one.

How long the past sticks around

That window is governed by DATA_RETENTION_TIME_IN_DAYS, and its default is 1 day — 24 hours of history behind every table. On Standard edition, one day is also the ceiling. On Enterprise edition and above you can set it anywhere from 0 to 90 days. Zero is legal and it means no Time Travel at all — no undrop, no point-in-time query — which is occasionally what you want for a scratch table you never need to recover.

The parameter isn’t only a table setting. It lives at four levels — account, database, schema, and table — and it inherits downward with override. Set it on the account and every object beneath adopts that value unless it declares its own; set it on a database and every schema and table inside inherits that unless they in turn override. So a common shape is a conservative account default with a generous override on the databases that matter:

-- account-wide baseline (run as ACCOUNTADMIN)
ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 1;

-- but the production database keeps a month of history
ALTER DATABASE prod SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- and one especially precious table keeps the full 90
ALTER TABLE prod.sales.orders SET DATA_RETENTION_TIME_IN_DAYS = 90;

There’s a companion parameter, MIN_DATA_RETENTION_TIME_IN_DAYS, settable only at the account level, that acts as a floor. When it’s set, the effective retention for any object is the greater of the object’s DATA_RETENTION_TIME_IN_DAYS and the account minimum — so an admin can guarantee, say, a 7-day safety net that no individual table owner can accidentally drop below, while still letting teams turn up retention on tables that warrant it. It raises the floor; it never lowers a ceiling someone set higher.

One sharp edge to internalize: lowering retention takes effect immediately, and it drops history retroactively. If a table has 30 days of retention and you set it to 1, you don’t keep the old 30 days and start counting down — the 29 days beyond the new window are gone the moment the ALTER commits, moved on toward Fail-safe. Turning retention up, by contrast, only affects data that enters Time Travel from that point forward; it can’t resurrect history that already aged out. So retention is a dial you should set deliberately and rarely turn down in anger.

Fail-safe: the seven days you don’t control

When retention finally expires, the data isn’t instantly gone forever. For permanent tables it slips into Fail-safe — a further 7 days, fixed, during which Snowflake itself can recover it but you cannot. Fail-safe is not configurable: you can’t extend it, shorten it, or turn it off, and unlike Time Travel it’s not something you query. There’s no AT, no BEFORE, no SELECT that reaches into it. Recovery from Fail-safe means opening a support case and asking Snowflake to do it for you, which they’ll do only for genuine “the data is truly gone and it matters” situations — it’s a disaster backstop, not a self-serve second undo.

So the full mental model is a three-stage decay. For your retention window the past is yours: self-serve, one query away. For seven days after that it exists but only Snowflake can reach it. Then it’s truly gone. Everything you’d actually script — the undrop, the point-in-time restore — lives in that first stage, which is exactly why setting retention thoughtfully matters.

Two exceptions are worth carrying around. First, Fail-safe is table-only — it protects table data and nothing else. Second, and more important day to day: transient and temporary tables have no Fail-safe at all, and their Time Travel retention maxes out at 1 day. A TEMPORARY table vanishes with your session; a TRANSIENT table persists but skips the 7-day Fail-safe entirely. That’s not a limitation to route around — it’s the point. Which brings us to why any of this shows up on your bill.

What the past costs

None of this is free in storage, even if cloning is free in copies. Every version Snowflake retains for Time Travel, and every version sitting in Fail-safe, is real bytes it’s holding on your behalf and charging you for. A table that churns heavily — big daily rewrites, frequent full refreshes — can quietly carry several multiples of its live size in historical and Fail-safe versions, especially at 90-day retention.

You don’t have to guess at this; it’s measurable. The TABLE_STORAGE_METRICS view breaks a table’s footprint into exactly these buckets:

SELECT table_name,
       active_bytes,
       time_travel_bytes,
       failsafe_bytes,
       retained_for_clone_bytes
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE table_schema = 'SALES'
ORDER BY failsafe_bytes DESC;

active_bytes is your live data; time_travel_bytes and failsafe_bytes are the history you’re paying to keep recoverable; retained_for_clone_bytes is data kept alive only because a clone still references it (more on that shortly). When a storage bill looks surprising, this view is almost always where the surprise is hiding — and it’s often a high-churn table at long retention, not the live data, doing the damage. The fix is usually to make that table TRANSIENT, or to bring its retention down to something the recovery story actually needs.

That churn deserves a closer look, because it’s the hinge the whole chapter turns on.

Why both are cheap: the same trick

Snowflake stores table data as immutable micro-partitions — small, compressed, columnar chunks, typically tens of megabytes each, that once written are never edited. This is the single design decision behind everything above, and it’s worth sitting with, because it also explains a cost that surprises newcomers.

Because a micro-partition can’t be modified, a change never edits data in place. An UPDATE that touches even one row in a partition can’t patch that partition — it writes a new partition holding the updated version and re-points the table’s metadata at it, leaving the old partition untouched. A DELETE is the same story: the rows don’t get erased from their chunk; a new version of the affected partitions is written without them. This is write amplification, and it’s why a small logical change can produce a large physical write, and why heavy update/delete workloads generate a lot of superseded partitions.

Now the two features fall out for free. Time Travel is just reading the metadata as it pointed last hour — the old partitions the UPDATE superseded are still physically sitting there, so “the table an hour ago” is nothing more than the earlier set of pointers. And Fail-safe’s cost, and Time Travel’s cost, are simply how long Snowflake keeps those superseded partitions around before reclaiming the space. The immutability that makes the past queryable is the same immutability that makes churn expensive: every superseded version is retained, and retention is you telling Snowflake how long to hold them. Micro-partitions, write amplification, Time Travel cost, and Fail-safe cost are four views of one mechanism.

Zero-copy clones: a full copy that copies nothing

The other hat the same trick wears. You need a copy of a production table to experiment against without risking the original. Anywhere else that’s a slow, expensive duplication of every byte. In Snowflake it’s instant and free:

CREATE TABLE orders_dev CLONE orders;

orders_dev is a complete, independent, writable table with every row of orders — created in about the time it took to submit the statement, consuming essentially no new storage. It works at every level of the hierarchy, so you can clone a whole schema or database in one line:

CREATE DATABASE analytics_dev CLONE analytics;

The word “zero-copy” is literal. Nothing was copied. The clone is metadata — a new set of pointers at the same underlying micro-partitions the original already uses, which is exactly why it’s the mirror image of Time Travel. Time Travel is old pointers to old partitions; a clone is a second name’s pointers to current partitions. Neither copies bytes. The clone starts paying for storage only for the parts that diverge — this is copy-on-write. Change a thousand rows in orders_dev and you store just those thousand rows’ worth of new partitions; the untouched 99% stays shared with the parent, owned by neither, billed once. (This is also what retained_for_clone_bytes back in the storage view was tracking: if you drop the parent, its partitions can’t be reclaimed while a clone still points at them.)

The two features compose, which is where it gets genuinely useful. You can clone a table as of a point in the past — freezing an incident’s exact pre-blast state into a named, poke-able copy while production carries on:

CREATE TABLE orders_snapshot CLONE orders
  BEFORE(STATEMENT => '01af8c3e-0000-d5a2-0000-6f5900a1b2c3');

Now you have a free, permanent, independent copy of the world right before the bad statement, to investigate at leisure without pinning anything in production’s retention window.

The caveats a “free copy” hides

Zero-copy cloning is genuinely close to magic, but “an exact copy” oversells it in two ways that will bite you if you don’t know them going in.

The first is grants. By default a clone does not copy the privileges granted on the source. Clone a database whose tables the analytics role could read, and on the clone that role can read nothing — the object exists, but the access map didn’t come with it. For a throwaway dev copy that’s often fine; for a promoted environment it’s a trap. The fix is the COPY GRANTS clause, which carries the source’s privileges onto the clone:

CREATE TABLE orders_dev CLONE orders COPY GRANTS;

The second is what “the data” even means. Cloning duplicates the data and structure, but a table and its schema are more than rows. Some things clone; some pointedly don’t:

• Load history does not clone. The clone has a fresh, empty COPY INTO history, so a file already loaded into the parent can be loaded again into the clone without being rejected as a duplicate. Handy for testing an ingest; dangerous if you assumed dedup carried over.
• External-table files aren’t copied (there’s nothing to copy — they live in your cloud storage), but the external table’s metadata clones, so it keeps pointing at the same underlying files.
• Pipes, streams, and tasks clone with a database or schema, but their state does not. Cloned tasks are created suspended and you must explicitly resume them — which is a safety feature, not a bug, since you rarely want a dev clone’s tasks firing on their own. A cloned stream starts fresh rather than inheriting the parent’s unconsumed offset. Materialized-view and other refresh state likewise resets rather than carrying over.

None of these are reasons to avoid cloning — they’re the fine print that turns “I cloned prod, why is it behaving differently” from a mystery into an expected footnote.

SWAP WITH: promoting a clone into place

Cloning gives you a private copy to build in; SWAP WITH is how you put a finished copy into production atomically. It exchanges the names and metadata of two tables in a single instant — what was orders_new becomes orders, and the old orders becomes orders_new, with no window where the name points at nothing:

ALTER TABLE orders SWAP WITH orders_staging;

This is the backbone of a blue/green table promotion. You build the next version of a table off to the side — clone the current one, rebuild it, reload it, whatever the change requires — validate it in place under a staging name, and when it’s ready, one SWAP WITH cuts production over to it atomically. Readers never see a half-built table; there’s no drop-and-recreate gap. And because the old version is now sitting under the staging name, rollback is just swapping back. Clone to build, swap to promote, swap to undo.

Zoom out and the pattern generalizes. Time Travel and cloning together are how you build the whole supporting cast of a serious data platform without a storage budget to match. A dev or test environment is a CLONE of production you refresh on demand and throw away. A backup is a clone taken BEFORE a risky migration, kept until you’re sure. A pre-deploy checkpoint is a clone you can SWAP back in if the deploy goes wrong. None of them costs a full second copy; all of them fall out of immutable, shared, versioned storage — the same pointer arithmetic, arranged three different ways.

Final thoughts

It’s tempting to file these under nice-to-haves — an undo button and a convenient copy command. They’re bigger than that. Time Travel changes how it feels to run a destructive statement: the query that used to make your stomach drop is now reversible — provided you can name the moment to reverse to, which is why knowing how to fish a query id out of the history is the real skill, not the BEFORE clause itself. Zero-copy cloning changes what a “dev environment” costs: a faithful copy of production stops being a budget line and becomes a throwaway you spin up per experiment and delete without a thought — as long as you remember it didn’t bring its grants, its load history, or its running tasks along for the ride. Both features, and their costs, and their sharp edges, fall directly out of one decision made deep in Snowflake’s engine: never mutate storage in place. Everything else in this chapter is that decision, surfacing all the way up here as the ability to undo yesterday and duplicate everything for free.

Next: A First Analysis on TPCH