# Time travel and snapshots

Every commit to an Iceberg table makes a snapshot: the table as it was right after that commit. An insert, a delete, an overwrite, a compaction by maintenance: each is one snapshot. While a snapshot is still in the table's metadata you can read the table as it was then, or make that snapshot the current one again (a rollback). That is time travel. How far back it reaches is set per catalog.

## How long snapshots are kept

Table maintenance runs every 6 hours and expires old snapshots. What it keeps depends on the catalog's snapshot window, snapshot_retention_days:

| catalog setting | what maintenance keeps, per table |
|---|---|
| not set (the default) | at most 20 snapshots, none older than 7 days, whichever is tighter |
| N days (1 to 90) | every snapshot younger than N days, at most 500 per table (the newest 500) |

The current snapshot is never expired, and neither is the head of any branch or tag.

The default is tight on a busy table. A table that commits every minute keeps only its last 20 commits after a maintenance run, so its undo window can be as short as 20 minutes. If you need to read or roll back further, set a window.

Set the window (admins only, because retained snapshots are storage):

```
lhbox catalog update analytics --snapshot-retention-days 30
lhbox catalog update analytics --snapshot-retention-days default
```

The API form is PATCH /v1/warehouses/{id} with {"snapshot_retention_days": 30}, or null for the default (see the API reference (https://lakehousebox.com/docs/api/)). lhbox catalog get analytics and GET /v1/warehouses/{id} show the value and a snapshot_retention block that spells out the policy in force.

Things to know about the window:

- It takes effect at the next maintenance run, within 6 hours, for every table in the catalog.

- A longer window keeps snapshots from then on. It cannot bring back snapshots already expired.

- Every retained snapshot keeps the data files it references. A table that maintenance compacts daily and that you keep for 30 days can hold up to 30 generations of its files, and all of them count against your organisation's storage.

- The 500-snapshot cap exists because every Iceberg commit gets slower as a table's snapshot count grows. Batch your writes (see performance (https://lakehousebox.com/docs/performance/)).

Plan limits. lhbox usage --full lists snapshot_retention_days (7) and max_snapshots_per_table (20). They are the default policy above. Maintenance applies them by expiring snapshots; nothing is ever refused because of them.

Unreferenced files. Maintenance also deletes files that no snapshot in the table's metadata references (left over from failed or abandoned writes). It only deletes such a file once it is older than 72 hours, so a writer still in the middle of a commit is not affected. A file that a kept snapshot needs is never deleted, however old it is.

## See a table's snapshots

- CLI: lhbox table get analytics.events.clicks prints the table's snapshot count and row count.

- API: GET /v1/table?warehouse=analytics&namespace=events&name=clicks&snapshots=20 adds snapshot_log, the last N snapshots (0 to 50), newest first: snapshot_id, committed_at, operation, added_records, deleted_records, total_records.

- MCP: the describe_table tool returns the last 10 snapshots by default (up to 50) as recent_snapshots.

- Account page: pick a table in the explorer and press Snapshots. It lists up to 50, newest first, with snapshot id, sequence number and commit time.

- DuckDB: SELECT * FROM iceberg_snapshots('analytics.events.clicks');

Snapshot ids are 64-bit integers. Keep them as strings in JSON tools that would round them.

## Read an older version

Connect each engine as shown in engines (https://lakehousebox.com/docs/engines/). Then:

PyIceberg 0.12

```
t = catalog.load_table(("events", "clicks"))
for s in t.metadata.snapshots:
    print(s.snapshot_id, s.timestamp_ms, s.summary)
old = t.scan(snapshot_id=<snapshot id>).to_arrow()
```

Spark 3.5 with Iceberg 1.11

```
SELECT * FROM analytics.events.clicks VERSION AS OF <snapshot id>;
SELECT * FROM analytics.events.clicks TIMESTAMP AS OF '2026-09-28 12:00:00';
SELECT * FROM analytics.events.clicks.snapshots;
```

This is Iceberg's documented Spark syntax; unlike the DuckDB and PyIceberg forms, it has not been run against LakehouseBox yet.

DuckDB 1.5.5 (with the catalog attached as analytics)

```
SELECT * FROM analytics.events.clicks AT (VERSION => <snapshot id>);
SELECT * FROM analytics.events.clicks AT (TIMESTAMP => TIMESTAMP '2026-09-28 12:00:00');
```

Both forms, and iceberg_snapshots, were run against LakehouseBox on 2026-09-29 (DuckDB 1.5.5): a table with two snapshots read 3 rows at the current one and 2 rows at the older one, by id and by time.

## Roll a table back

A rollback makes an older snapshot the current one. It is a metadata commit: it writes no data and deletes nothing. The newer snapshots stay in the metadata and expire later like any other. You need write access to the catalog.

PyIceberg 0.12

```
t.manage_snapshots().rollback_to_snapshot(<snapshot id>).commit()
```

rollback_to_snapshot accepts only an ancestor of the current snapshot. Run against LakehouseBox on 2026-09-29 (PyIceberg 0.12), with scan(snapshot_id=…) above: after the rollback the older snapshot was current again and the table read its 2 rows.

Spark has the procedure CALL analytics.system.rollback_to_snapshot('events.clicks', <snapshot id>). It needs Iceberg's SQL extensions (spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions), which the recipe on the engines page does not set. We have not recorded a run of it yet.

DuckDB has no rollback. Use PyIceberg or Spark.

Other writers can commit at the same time as your rollback, and so can maintenance. Iceberg commits are optimistic: if the catalog answers 409, reload the table and try again.

## What time travel is not

- Not a backup. Snapshots live in the same store as the table. They protect you from a bad write, not from losing the table.

- Not an undo for a dropped table. lhbox table delete drops the table and its data files, with every snapshot, and cannot be undone.

- Not how a deleted catalog comes back. Deleting a catalog starts a grace period (7 days on the service). During it the catalog, its tables and their snapshots are kept, and an admin can bring it back with lhbox catalog restore <name>. The delete answer gives the exact date as purge_after. After it, or with --purge, the catalog is deleted for good. See the CLI reference (https://lakehousebox.com/docs/cli/).

- Not a way to recover expired snapshots. Once maintenance has expired a snapshot, it is gone.

---
HTML version: https://lakehousebox.com/docs/time-travel/ · every page: https://lakehousebox.com/llms.txt · everything in one file: https://lakehousebox.com/llms-full.txt