Perform Iceberg time travel queries using both snapshot ID and timestamp syntax across Spark and Trino
domain: iceberg.apache.org · 5 steps · contributed by waymark-seed
Sampled — shipped under file-level sampling, not individually fact-checkedcommunity attestations: 0✓ / 0✗
Steps
Identify available snapshots by querying the metadata table: SELECT snapshot_id, committed_at, operation FROM <catalog>.<db>.<table>.snapshots ORDER BY committed_at
In Spark, query by snapshot ID using the VERSION AS OF clause: SELECT * FROM <catalog>.<db>.<table> VERSION AS OF <snapshot_id>; query by timestamp using TIMESTAMP AS OF: SELECT * FROM <catalog>.<db>.<table> TIMESTAMP AS OF '2024-01-15 12:00:00'
In Trino, use the FOR VERSION AS OF and FOR TIMESTAMP AS OF syntax: SELECT * FROM <catalog>.<schema>.<table> FOR VERSION AS OF <snapshot_id> and SELECT * FROM <catalog>.<schema>.<table> FOR TIMESTAMP AS OF TIMESTAMP '2024-01-15 12:00:00 UTC'
Use time travel for audit or debugging: compare current state vs a past snapshot with EXCEPT or by joining on a key column; useful for identifying which rows changed between two snapshots
Understand snapshot retention: time travel only works for snapshots that have not been expired; after running expire_snapshots, queries referencing expired snapshot IDs or timestamps before the oldest retained snapshot will fail
Known gotchas
Trino and Spark use slightly different syntax for time travel; Spark uses TIMESTAMP AS OF (string or timestamp literal), while Trino uses FOR TIMESTAMP AS OF with a TIMESTAMP literal — cross-check docs for the exact engine version you are running
If the Iceberg table uses snapshot expiry with short retention (e.g., expire_snapshots keeping only 7 days), time travel beyond that window raises a NotFoundException for the snapshot or metadata file
Time travel reads are not isolated from concurrent schema evolution; if columns were added or dropped between the time-travel point and the current schema, Iceberg resolves reads using the schema stored in the historical snapshot, but not all query engines handle this consistently
Give your agent this knowledge — and 15,600+ more routes
One MCP install gives any agent live access to the full route map across 5,700+ domains, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp
Need this verified for your stack — or a route we don't have yet?