# Time Travel and Zero-Copy Cloning in Snowflake

> Query a table as it was an hour ago, undo a bad load, and clone a production database for testing without copying data.

- Source: https://akshaythakare.com/notes/snowflake/time-travel-and-zero-copy-clone
- Topic: Snowflake
- Level: intermediate
- Reading time: 2 min
- Tags: snowflake, time-travel, cloning, recovery
- Updated: 2026-10-11

## Time Travel

Snowflake keeps previous versions of changed data for a retention period: 1 day by default, up to 90 days on Enterprise edition.

```sql
SELECT * FROM fact_sales AT (OFFSET => -3600);                  -- one hour ago
SELECT * FROM fact_sales BEFORE (STATEMENT => '01b2…query_id');  -- before a bad statement
UNDROP TABLE fact_sales;                                         -- restore a dropped table
```

## Recovering from a bad load

```sql
CREATE OR REPLACE TABLE fact_sales_restored CLONE fact_sales
  BEFORE (STATEMENT => '01b2…query_id');
ALTER TABLE fact_sales SWAP WITH fact_sales_restored;
```

Find the query id of the bad statement in `QUERY_HISTORY`, clone the table as it was just before it, and swap.

## Zero-copy cloning

```sql
CREATE DATABASE analytics_dev CLONE analytics;
```

A clone shares the original's storage. Only data changed afterwards takes new storage. That makes it cheap to give each developer or CI run a full copy of production structure and data.

## Costs and limits

- Retention costs storage: churned data is kept for the whole retention period, plus 7 days of **Fail-safe** that only Snowflake support can recover.
- Transient and temporary tables have at most 1 day of Time Travel and no Fail-safe. Use them for staging data you can rebuild.

> **Warning: Time Travel is not a backup strategy**
>
> It protects against mistakes within the retention window. Long-term recovery and compliance need replication or exports.