How far back can Snowflake Time Travel go?

One day by default on Standard edition, and up to 90 days on Enterprise for permanent objects, set per object with `data_retention_time_in_days`. Transient and temporary tables max out at one day, which is a common surprise when someone needs to recover one.

What is zero-copy cloning?

Creating a new table, schema or database that shares the original's existing micro-partitions instead of duplicating them. The clone is instant and adds no storage; you only pay for partitions that change afterwards in either copy.

What is the difference between Time Travel and Fail-safe?

Time Travel is yours — you query it, clone from it, and control its length. Fail-safe is a further 7 days that only Snowflake Support can restore from, is not queryable, and cannot be configured. Never treat Fail-safe as a backup strategy.

Does a clone cost storage?

Not at creation. Both copies reference the same micro-partitions, and storage grows only as rows change in one of them. A cloned 10 TB database for testing starts free and costs only the difference your tests write.

Time Travel and Zero-Copy Cloning

Two features fall out of Snowflake’s immutable storage: it can show you a table as it was at any point in the retention window, and it can make a copy of a database that costs nothing until you change it. Both are things a traditional warehouse simply cannot do.

The mistake

select order_id, status, amount from orders order by order_id limit 3;

+----------+-----------+--------+
| ORDER_ID | STATUS    | AMOUNT |
|----------+-----------+--------|
|     1001 | completed |  25.50 |
|     1002 | completed |  12.00 |
|     1003 | returned  |  40.00 |
+----------+-----------+--------+
3 Row(s) produced. Time Elapsed: 0.402s

update orders set amount = amount * 100;      -- meant to convert pence, ran on pounds

+------------------------+-------------------------------------+
| number of rows updated | number of multi-joined rows updated |
|------------------------+-------------------------------------|
|                      5 |                                   0 |
+------------------------+-------------------------------------+
1 Row(s) produced. Time Elapsed: 0.688s

+----------+-----------+---------+
| ORDER_ID | STATUS    | AMOUNT  |
|----------+-----------+---------|
|     1001 | completed | 2550.00 |
|     1002 | completed | 1200.00 |
|     1003 | returned  | 4000.00 |
+----------+-----------+---------+

No transaction to roll back — it was committed. In most warehouses this is a restore from backup.

Querying the past

select order_id, status, amount
from orders before (statement => '01b2c3d4-0000-a1b2-0000-c3d400001a2b')
order by order_id limit 3;

+----------+-----------+--------+
| ORDER_ID | STATUS    | AMOUNT |
|----------+-----------+--------|
|     1001 | completed |  25.50 |
|     1002 | completed |  12.00 |
|     1003 | returned  |  40.00 |
+----------+-----------+--------+
3 Row(s) produced. Time Elapsed: 0.911s

The original values, from a normal SELECT. Three ways to specify the point:

select * from orders at (offset => -60 * 10);                    -- 10 minutes ago
select * from orders at (timestamp => '2026-09-09 10:00:00'::timestamp_ltz);
select * from orders before (statement => '01b2c3d4-...');       -- just before that query

before (statement => ...) is the precise one — it means “immediately before that statement ran”, so you do not have to guess a timestamp. Find the query id in the history:

select query_id, query_text, start_time
from table(information_schema.query_history())
where query_text ilike 'update orders%'
order by start_time desc limit 1;

+--------------------------------------+---------------------------------+-------------------------------+
| QUERY_ID                             | QUERY_TEXT                      | START_TIME                    |
|--------------------------------------+---------------------------------+-------------------------------|
| 01b2c3d4-0000-a1b2-0000-c3d400001a2b | update orders set amount = a... | 2026-09-09 10:31:02.114 -0700 |
+--------------------------------------+---------------------------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.402s

Repairing

The safe repair is a clone of the old state, verified, then a swap:

create or replace table orders_recovered clone orders
    before (statement => '01b2c3d4-0000-a1b2-0000-c3d400001a2b');

select sum(amount) from orders_recovered;

+-------------------------------------------------+
| status                                          |
|-------------------------------------------------|
| Table ORDERS_RECOVERED successfully created.    |
+-------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s

+-------------+
| SUM(AMOUNT) |
|-------------|
|      149.45 |
+-------------+
1 Row(s) produced. Time Elapsed: 0.402s

alter table orders swap with orders_recovered;
drop table orders_recovered;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

SWAP WITH exchanges the two tables atomically — names, and any grants that follow the name. No window where the table is missing, which create or replace ... as select would give you.

UNDROP

drop table orders;
select count(*) from orders;

002003 (42S02): SQL compilation error:
Object 'ORDERS' does not exist or not authorized.

undrop table orders;
select count(*) from orders;

+---------------------------------------------+
| status                                      |
|---------------------------------------------|
| Table ORDERS successfully restored.         |
+---------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s

+----------+
| COUNT(*) |
|----------|
|        5 |
+----------+
1 Row(s) produced. Time Elapsed: 0.402s

UNDROP works on schemas and databases too. The catch: it restores the most recently dropped object of that name, and create or replace table counts as a drop — so a replace-then-undrop sequence may not restore what you expect.

Retention

show parameters like 'data_retention_time_in_days' in table orders;

+-----------------------------+-------+---------+---------+
| key                         | value | default | level   |
|-----------------------------+-------+---------+---------|
| DATA_RETENTION_TIME_IN_DAYS | 1     | 1       | TABLE   |
+-----------------------------+-------+---------+---------+
1 Row(s) produced. Time Elapsed: 0.194s

alter table orders set data_retention_time_in_days = 30;

Edition / type	Max retention
Standard	1 day
Enterprise, permanent objects	90 days
Transient and temporary tables	1 day, always

Retention costs storage — Snowflake keeps the changed micro-partitions for the whole window — so 90 days on a table rewritten nightly can multiply its storage bill. Set it high on dimension tables that change slowly, low on a staging table rebuilt every hour.

After Time Travel expires, Fail-safe holds another 7 days for permanent tables. It is not queryable, not configurable, and recoverable only by raising a support case. Treat it as a last resort, never as your retention plan.

Zero-copy clones

create database bookshop_dev clone bookshop;

+---------------------------------------------+
| status                                      |
|---------------------------------------------|
| Database BOOKSHOP_DEV successfully created. |
+---------------------------------------------+
1 Row(s) produced. Time Elapsed: 3.884s

Under four seconds for the whole database, and it would be about the same for 10 TB — the clone points at the same micro-partitions rather than copying them. Storage confirms it:

select
    table_catalog,
    round(sum(active_bytes) / power(1024, 3), 2) as gb
from snowflake.account_usage.table_storage_metrics
where table_catalog in ('BOOKSHOP', 'BOOKSHOP_DEV')
group by 1;

+---------------+--------+
| TABLE_CATALOG | GB     |
|---------------+--------|
| BOOKSHOP      | 412.60 |
| BOOKSHOP_DEV  |   0.00 |
+---------------+--------+
2 Row(s) produced. Time Elapsed: 1.204s

The clone is independent from the moment it exists. Writes to either side create new micro-partitions belonging only to that side:

use database bookshop_dev;
delete from raw.orders where status = 'pending';
select count(*) from bookshop.raw.orders;

+------------------------+
| number of rows deleted |
|------------------------|
|                      1 |
+------------------------+

+----------+
| COUNT(*) |
|----------|
|        5 |
+----------+

Production still has five rows. This is what makes cloning the standard way to build environments: a full-size dev database, created in seconds, that costs only what your changes write.

create database bookshop_ci clone bookshop
    at (offset => -60 * 60 * 24);        -- yesterday's state, for a reproducible test run

+--------------------------------------------+
| status                                     |
|--------------------------------------------|
| Database BOOKSHOP_CI successfully created. |
+--------------------------------------------+
1 Row(s) produced. Time Elapsed: 4.102s

Clones inherit privileges on the objects inside but not on the clone itself, so grant access to the new database explicitly. And they do not clone external tables or internal stages — a pipeline that reads from a stage needs that part recreated.

Practice

1. Make a bad update, then query the table as it was before it.

select sum(amount) from orders;
select sum(amount) from orders at (offset => -300);

+-------------+
| SUM(AMOUNT) |
|-------------|
|    14945.00 |
+-------------+

+-------------+
| SUM(AMOUNT) |
|-------------|
|      149.45 |
+-------------+

Two SELECTs against the same table, five minutes apart in logical time. Running the comparison before repairing tells you the blast radius of the mistake.

2. Drop a table and undrop it.

002003 (42S02): SQL compilation error:
Object 'CUSTOMERS' does not exist or not authorized.

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table CUSTOMERS successfully restored. |
+-------------------------------------+

Note it restores grants as well as data. A restore via create table as select from Time Travel would not — the new object starts with no privileges, and everything downstream breaks with permission errors.

3. Clone a database and confirm it costs no storage.

+---------------+--------+
| TABLE_CATALOG | GB     |
|---------------+--------|
| BOOKSHOP      | 412.60 |
| BOOKSHOP_DEV  |   0.00 |
+---------------+--------+

Zero at creation. Watch the same query a week into development and the clone’s number grows only by what changed — usually a rounding error against the original.

4. Set retention to 0 on a table, then try to time travel.

alter table orders set data_retention_time_in_days = 0;
select * from orders at (offset => -60);

000707 (02000): Time travel data is not available for table ORDERS.
The requested time is either beyond the allowed time travel period or before the
object creation time.

Retention zero disables Time Travel entirely — no UNDROP either. It is a legitimate saving on a large table rebuilt from source every run, and a serious mistake anywhere else.

Next: semi-structured data — VARIANT, JSON, and FLATTEN.