Guides · Apache Iceberg

"Iceberg time travel: read a table as it was, or roll it back"

Read an Apache Iceberg table as it was yesterday, or roll it back. The exact SQL for DuckDB, PyIceberg and Spark, and how long snapshots are kept.

2 min read

The short answer. Every write to an Apache Iceberg table makes a snapshot. To read the table as it was, point your engine at an older one:

-- DuckDB 1.5.5+, with the catalog attached as `analytics`
SELECT * FROM analytics.events.clicks AT (TIMESTAMP => TIMESTAMP '2026-11-01 12:00:00');
SELECT * FROM analytics.events.clicks AT (VERSION => 4628871297741230594);

That is all time travel is: a query against a past snapshot. The rest of this guide covers how to find the snapshot you want, how to undo a bad write, and the setting that decides how far back you can go.

1. Find the snapshot

Each snapshot records when it was committed and what it did (an append, a delete, an overwrite, a compaction):

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

On LakehouseBox the same list is in the CLI (lhbox table get), the API (snapshot_log), the account page (Snapshots) and the MCP server's describe_table tool, so an agent can find "the version before this morning's import" by itself. Snapshot ids are 64-bit integers: keep them as strings in tools that would round them.

2. Read it with your engine

engine by snapshot by time
DuckDB 1.5.5+ AT (VERSION => <id>) AT (TIMESTAMP => TIMESTAMP '…')
PyIceberg 0.12 table.scan(snapshot_id=<id>).to_arrow() pick the id from table.metadata.snapshots
Spark 3.5 VERSION AS OF <id> TIMESTAMP AS OF '…'

We ran the DuckDB and PyIceberg forms against LakehouseBox: a table with two snapshots read 3 rows at the current one and 2 rows at the older one, by id and by time. The Spark syntax is Iceberg's documented one and has not been run against LakehouseBox yet.

3. Undo a bad write

A rollback makes an older snapshot the current one again. It is a metadata commit: it writes no data and deletes nothing.

table.manage_snapshots().rollback_to_snapshot(4628871297741230594).commit()

PyIceberg accepts only an ancestor of the current snapshot. DuckDB has no rollback; use PyIceberg, or Spark's rollback_to_snapshot procedure. If another writer commits at the same moment, the catalog answers 409: reload the table and try again.

4. Decide how far back you need to go

Snapshots are kept until table maintenance expires them, and every snapshot you keep also keeps the data files it points to. On LakehouseBox maintenance runs every 6 hours. By default it keeps at most 20 snapshots per table, none older than 7 days, whichever is tighter. A table written every minute can only be undone about 20 minutes back. If you need more, set a window for the catalog:

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

The window can be 1 to 90 days, with up to 500 snapshots per table. It applies from the next maintenance run and cannot bring back snapshots already expired.

What time travel is not

  • Not a backup: snapshots live with the table.
  • Not an undo for a dropped table: dropping a table removes its snapshots too.
  • Not a way back to a snapshot that maintenance has already expired.

For agents, time travel is what makes their writes safe to try: load, check, and if the result is wrong, go back one snapshot.

Explore how it works

Related articleAlways-learning analytics: why agents don't have to start over