Time travel (data versioning)

What is time travel?

Time travel lets you query a table as it looked at an earlier version, or at an earlier moment in time. Your query runs against last week's state, or the version from just before a bad load, while the live table stays untouched. It is a read-only look at the past, not an edit.

The feature belongs to the open table formats behind a lakehouse, mainly Delta Lake and Apache Iceberg, and to cloud data warehouses such as Snowflake and BigQuery. Each one keeps more than the latest state of a table: a run of older versions you can point a query at.

Think of the version history in a shared document. What you open is the latest text, but you can scroll back to how it read three days ago without anyone saving that copy by hand.

How it works: a log plus old files, not a saved snapshot

Time travel is not a stack of full copies. Each write creates a new version, but neighbouring versions share almost all of their files.

On a Delta Lake or Iceberg table the data sits in immutable Parquet files. A write never edits a file in place. It adds new files and records that some old files are no longer part of the current table. A separate transaction log tracks which files belong to which version, and that same log is what gives these formats their ACID transactions.

• Delta Lake writes the log to a _delta_log folder. Each commit is a numbered version: 0, 1, 2, and so on. To read version 42, the engine loads the files the log ties to that commit.

• Apache Iceberg does the same with snapshots. Every write ends in a new snapshot with its own id, and each snapshot is a complete, readable version of the table.

Because neighbouring versions differ only by the files that actually changed, keeping history stays cheap. Snowflake and BigQuery use the same idea inside their storage but hide the files, so you get the versions without managing Parquet yourself.

The syntax, engine by engine

On Delta Lake, Iceberg, and Microsoft Fabric you read a version or a timestamp directly:

SELECT * FROM sales VERSION AS OF 42
SELECT * FROM sales TIMESTAMP AS OF '2026-06-30'

Run DESCRIBE HISTORY sales first to list the versions and timestamps that exist. On Iceberg, VERSION AS OF takes a snapshot id, or a branch or tag name, rather than a running counter.

Snowflake uses an AT or BEFORE clause on the table. SELECT * FROM sales AT(OFFSET => -3600) reads it as of one hour ago, and SELECT * FROM sales BEFORE(STATEMENT => '01a2b3') reads it as it was just before a specific statement ran, which is what you want after a bad write. BigQuery uses FOR SYSTEM_TIME AS OF with a timestamp.

What you use it for

1. Recover from a bad write. A pipeline loaded a corrupt file. Read the previous version to confirm it is clean, then set the table back to it.

2. Reproduce a report. A number that was right on Monday looks wrong today. Run the same query against Monday's version and see exactly what moved.

3. Audit what changed. Compare two versions of one table to see which rows were added, updated, or removed, and when.

4. Pin an analysis. Point a calculation at a fixed version so the result stays identical even as the source table keeps growing.

Time travel itself only reads. To make an old version current you run a separate command, such as RESTORE on Delta Lake. The safe pattern is to travel to the old version first, confirm it is right, then restore.

How far back you can go: retention and cleanup

Every version you can still query means its files are still on storage, and more history costs more storage. So each engine prunes old versions on a schedule, and that retention window, not the history list, is the real limit on how far back you can travel.

• Delta Lake. VACUUM deletes data files that are no longer part of the current table and older than the retention threshold, seven days by default. Once a file is gone you cannot travel to a version that needed it, even though the version still shows in the history. The log is kept longer, 30 days by default, but that will not restore files VACUUM has removed.

• Apache Iceberg. An expire-snapshots action does the same job, dropping old snapshots and the files only they referenced.

• Snowflake. The retention window defaults to one day. Standard edition tops out there; Enterprise edition and above allow up to 90 days for permanent tables. Query a point outside the window and it fails.

• BigQuery. The time travel window is seven days by default and can be set anywhere from two to seven days.

Compaction is the sibling process that quietly interacts with all of this. Rewriting many small files into fewer large ones, with OPTIMIZE on Delta Lake or a rewrite on Iceberg, is itself a write, so it creates a new version and leaves the pre-compaction files behind until VACUUM or expire-snapshots clears them. Compact hard and then vacuum aggressively and you can lose older time travel points you assumed were still there.

One limit is worth stating plainly: time travel is not a long-term backup. Its window is short and meant for recent history, so to keep a state for years, make a real copy, for example with a CREATE TABLE AS SELECT. Set VACUUM or the Snowflake window shorter than an audit or a restore needs and the files vanish early.