From Time Travel to Lateral Temporal Tables in DuckDB

DuckDB and DuckLake make it pleasantly simple to query an older version of a table when you already know the snapshot number.¹1What “time travel” means here. A versioned catalog such as DuckLake records a consistent snapshot whenever changes are committed. A time-travel reference queries the table’s data and schema as they existed at an earlier snapshot or timestamp, without restoring or copying the database. The timestamp can be absolute or relative, such as “one week ago.” See DuckLake’s time-travel documentation.

-- Select an exact snapshot.
SELECT *
FROM lake.events AT (VERSION => 42);

-- Select the table state visible at a point in time.
SELECT *
FROM lake.events AT (
    TIMESTAMP => now() - INTERVAL '1 week'
);

The awkward part is that snapshot numbers rarely begin life as literals. They live in queryable snapshot metadata. I might want the latest snapshot using schema version 1, the final snapshot produced by yesterday’s pipeline, or the last snapshot before a deployment. DuckLake exposes enough information to ask those questions, but today I have to ask one question, take the answer back into my application, and manufacture another query:

SELECT max(snapshot_id)
FROM ducklake_snapshots('lake')
WHERE schema_version = 1;

-- Application reads 42, quotes it safely, and submits another statement.
SELECT *
FROM lake.events AT (VERSION => 42);

The workaround functions, but it pushes relational logic into application code, introduces a quoting boundary, and prevents the snapshot lookup from composing with the historical read. The query I want to write is the obvious one:

SELECT *
FROM lake.events AT (
    VERSION => (
        SELECT max(snapshot_id)
        FROM ducklake_snapshots('lake')
        WHERE schema_version = 1
    )
);

So I opened DuckDB PR #25665 and implemented it.

The implementation works. It parses an uncorrelated scalar subquery inside AT, evaluates that selector once, preserves the resulting DuckDB type, substitutes the value into the AT expression, and then binds the historical table. I tested it against a live DuckLake catalog using snapshots on either side of an ALTER TABLE ... ADD COLUMN. Selecting the older snapshot returned the older schema; selecting the latest snapshot returned the new column.

The current PR is staying a draft while the execution model is reconsidered.

That is not a retreat from the syntax—it may be the most interesting part. It is an acknowledgment that implementing the idea correctly reaches much further into DuckDB’s query lifecycle than the query suggests.

The binder needs an answer before the executor exists

Database execution diagrams usually look like this:

A hard-coded AT value fits this pipeline. During binding, DuckDB evaluates the constant, asks the catalog for the table at that version, and receives the table’s columns and types.

A dynamic AT selector interrupts the usual query pipeline

new selector work during binding main-query execution

ordinary query

SQL text

↓

parse

↓

bind names and types

↓

build and optimize plan

↓

execute

query with a dynamic AT selector

parse

↓

bind + plan + run snapshot selector

↓

obtain one typed value

execution result required to continue binding

↓

bind historical table

↓

plan + run main query

This introduces an execution-assisted binding phase: part of the statement must run before DuckDB can bind the rest.

The reason is schema evolution. Imagine that the table looked like this at three versions:

version 10: id INTEGER
version 20: id INTEGER, note VARCHAR
version 30: id BIGINT, note VARCHAR

The result of the snapshot selector determines whether note exists and whether id is an INTEGER or a BIGINT. DuckDB cannot build a typed physical plan and defer that discovery until ordinary execution.

What the prototype does

The draft implementation treats each dynamic AT expression as a statement-local pre-bind selector:

main SelectStatement
├── selector 0: SELECT max(snapshot_id) ...
├── selector 1: SELECT ...
└── query tree containing AT placeholders 0 and 1

Before binding the main statement, DuckDB executes each selector as a materialized one-column, one-row query. It takes the resulting Value and replaces the corresponding AT expression with a typed constant. The normal catalog and extension interfaces do not have to change; DuckLake still receives the concrete version or timestamp it expects.

That narrow design bought several useful properties:

• The subquery runs exactly once, even if the table produces millions of rows.
• A timestamp remains a timestamp and a version remains an integer; there is no string interpolation.
• Multiple AT selectors can be resolved before the main statement binds.
• The state belongs to the parsed statement rather than a connection-global variable.
• Existing time-travel catalogs do not need a new runtime scan interface.

It also exposed three design requirements that a production implementation needs to address.

Requirement 1: Binding should describe a query, not run it

Binding normally resolves names and types, checks semantics, and determines the shape of a result without running the query itself. It is expected to be comparatively fast, and the same statement may be bound more than once as a client inspects it, prepares it, or asks DuckDB to produce a plan. Binding can consult catalogs and invoke extension bind logic, but executing an arbitrary relational subquery is a different kind of work with a different lifecycle.

That distinction appears directly in DuckDB’s APIs. BindStatement returns a StatementSignature: result column names, result types, statement properties, and parameter types. ExtractPlan is similarly a plan-only API. A caller using either API does not expect the operation to advance a sequence, perform an expensive remote scan, or cause some other query side effect.

But the signature and plan can depend on the historical schema, and the historical schema depends on the selector result. There are only two honest choices:

1. Execute the selector while producing metadata.
2. Reject the construct in metadata-only APIs.

The prototype initially chose the first option so every binding entry point would behave the same. That experiment clarified the contract: it made BindStatement and ExtractPlan surprisingly impure, while Prepare rejected the construct because it could not promise a stable reusable plan. The more coherent initial contract is that dynamic AT is available through actual query-execution paths, while Prepare, BindStatement and ExtractPlan reject it.

That is a limitation, but it preserves an important boundary. Binding should not quietly run nextval(), execute a remote selector, or invoke some other side-effecting function merely because it needs to discover a schema.

Requirement 2: Typed handoff needs more than MultiStatement

DuckDB already has a feature that runs work before a main query: dynamic PIVOT.

When the pivot values come from data, the PEG transformer generates something conceptually similar to this:

CREATE TEMPORARY TYPE __pivot_enum_<uuid> AS ENUM (
    SELECT DISTINCT pivot_column::VARCHAR
    FROM source
    ORDER BY 1
);

-- The rewritten PIVOT refers to that generated enum.
SELECT ...;

The parser wraps those internal statements in a MultiStatement. The statement preprocessor unwraps them, and the normal query path executes them in order.

This works because PIVOT uses the catalog as its mailbox. The first statement creates a named enum; the rewritten query knows that name and can bind against it. MultiStatement itself does not pass the result of one query into an expression in the next statement.

For AT, we would need a typed handoff:

selector result: Value(TIMESTAMP '2026-09-01 12:00:00')
                              ↓
             this exact AT expression in the next AST

A hidden DuckDB variable could approximate that handoff. Generate a unique variable, assign the scalar subquery to it, and rewrite AT to call getvariable(), which becomes a constant during binding. But then the variable must be cleaned up after normal completion, an error, cancellation, or abandonment of a streaming result. It is connection-visible state, and putting cleanup after a SELECT changes streaming and materialization behavior.

There is a second surprise: a MultiStatement containing a SELECT is deliberately not wrapped in one implicit transaction. Dynamic PIVOT’s preparation statement and its final query can therefore be separate autocommit transactions. Reusing that mechanism would not solve the most important correctness issue in the AT prototype.

Requirement 3: The selector and scan need one transaction

In the draft implementation, an autocommit query resolves the selector in one transaction, then binds and scans the selected snapshot in another:

The easy implementation leaves a transactional gap

transaction 1

BEGIN

→

bind selector

→

execute selector

→

COMMIT

catalog state can change here

transaction 2

BEGIN

→

bind snapshot 42

→

scan

→

COMMIT

An explicit BEGIN causes both operations to reuse the caller’s transaction, but autocommit is the normal case. Between those two transactions another writer can commit, a retention process can expire a snapshot, or catalog state can otherwise move.

Snapshot identifiers are intended to be durable references, which reduces the practical race, but it does not erase it. The semantically correct lifecycle keeps selector evaluation, binding, planning, and the historical scan in one query-owned transaction, committing only when the result completes.

That transaction must remain open for a pending or streaming result. The obstacle is ownership: DuckDB’s existing ClientContext controls query setup, profiling, interruption, result state, the connection’s active_query, and autocommit. Calling it again for the selector would either compete with the main query for that state or commit too early. A production design therefore needs a subordinate pre-bind executor that borrows the main query’s transaction without becoming a second query owner.

The syntax points somewhere larger

Avoiding the application round-trip is useful on its own: it keeps snapshot selection declarative, type-safe, and available to clients that cannot manufacture a second SQL statement. But allowing expressions in temporal table references points to something more powerful than selecting one snapshot once.

Consider this:

SELECT
    o.order_id,
    o.created_at,
    p.price
FROM orders o
CROSS JOIN LATERAL prices
    AT (TIMESTAMP => o.created_at) p
WHERE p.product_id = o.product_id;

This would read, for every order, the version of prices that existed when the order was created.

That is a lateral temporal table reference.²2A quick LATERAL refresher. A LATERAL item on the right can use columns produced by relations to its left. Think of it as a loop: for each left-hand row, evaluate the right-hand relation using values from that row. Unlike a scalar correlated subquery, the right side can return multiple columns and rows. DuckDB can often infer the dependency, but spelling out LATERAL makes it visible. See DuckDB’s FROM and JOIN documentation. It could express several families of queries that are awkward today:

• Join an event to the dimension values that were in force at event time.
• Reproduce a report against the exact table states visible to a historical pipeline run.
• Backtest a model without accidentally reading future corrections.
• Compare business rules or reference data across releases.
• Audit what a user or process could have observed at a particular moment.
• Walk a snapshot timeline and calculate properties of each historical table state.

This is much broader than looking up max(snapshot_id). It makes table history relational.

The same syntax hides two execution models

The syntax makes the progression look small:

-- Once per statement
events AT (VERSION => (SELECT max(snapshot_id) FROM snapshots))

-- Once per outer row
events AT (VERSION => outer_row.snapshot_id)

The execution difference is enormous.

An uncorrelated selector produces one value before the main table binds. A correlated selector produces a different value for every outer row. It cannot be replaced with one constant; it needs a parameterized physical scan.

That does not mean the first lateral implementation has to reconcile every schema the table has ever had. Data usually changes much more often than schema: a table may accumulate hundreds or thousands of snapshots while retaining the same columns and types. Those stable-schema stretches already cover valuable temporal joins—matching events to the reference data, prices, permissions, or model inputs that existed when each event occurred.

A practical first contract could bind one schema and require every selected snapshot to use it. A catalog that tracks schema versions can advertise this capability or reject a row when its snapshot crosses a schema boundary. That would unlock lateral time travel across the common case without pretending schema evolution is solved.

Schema evolution still matters, and it makes the general correlated case harder. If one outer row selects version 10 and another selects version 20, what is the schema of events in the plan?

There are several defensible policies, but DuckDB would need to define one:

• Require every selected version to have an identical schema.
• Bind against the current schema and return NULL for columns that did not exist yet.
• Construct a union-by-name schema and cast compatible historical columns.
• Require the query to declare its expected schema.
• Return each historical row inside a stable envelope containing a VARIANT payload.
• Allow a catalog to advertise a stable logical schema independent of its physical snapshots.

VARIANT helps with heterogeneous values, but it is not magic. The relation still needs a fixed set of top-level columns and a defined way to represent missing, renamed, and incompatible fields.

A staged path forward

The temporal-table idea can advance in layers without asking the first PR to promise everything.

1. Constant temporal references

This is the model DuckDB has today:

FROM events AT (VERSION => 42)

The binder knows the version and schema immediately.

2. Uncorrelated scalar selectors

Execute one scalar selector in the same transaction as the main query, then bind the historical table. Initially support this only through execution APIs and reject it from preparation and metadata-only APIs. That delivers immediate composability without requiring correlated scans or a cross-snapshot schema policy.

FROM events AT (
    VERSION => (
        SELECT max(snapshot_id)
        FROM ducklake_snapshots('lake')
        WHERE commit_message LIKE 'validated:%'
    )
)

The missing piece is a first-class transaction-scoped pre-bind phase, not more grammar.

3. Runtime temporal expressions for stable-schema catalogs

Let a catalog declare that changing the version changes the visible data but not the logical output schema—or validate that each requested snapshot belongs to the schema version used during binding. DuckDB could then bind one parameterized scan and feed it a version or timestamp for each lateral input row.

FROM snapshots s
CROSS JOIN LATERAL events
    AT (VERSION => s.snapshot_id)

Catalogs without that capability would reject the correlated form clearly, and a selected snapshot that crosses a schema boundary would produce an explicit error. This narrower contract captures the large same-schema opportunity while leaving cross-schema reconciliation for a later layer.

4. Explicit schema-evolution modes

If users really want one query spanning incompatible historical schemas, make the policy visible rather than guessing. A future syntax might choose the current schema, union schemas by name, or request a VARIANT envelope. The exact spelling matters less than making the contract explicit.

Is the architecture worth the cost?

Judged only as a way to avoid one application round-trip, the implementation cost looks disproportionate. But that framing is too narrow. Across DuckLake, Iceberg, and Delta-shaped workloads, composable temporal references would enable a class of relational questions that are difficult to express today. The same machinery can support snapshot selection now and lateral temporal joins later—and the lateral case can deliver substantial value across same-schema snapshots before DuckDB supports every form of schema evolution.

Time travel is already an established capability across versioned table formats: DuckLake, Apache Iceberg, Delta Lake, and Apache Hudi all retain the history needed to address earlier table states. Database engines expose related ideas through different syntax: Snowflake has AT and BEFORE, BigQuery has FOR SYSTEM_TIME AS OF, SQL Server has FOR SYSTEM_TIME, and Oracle has AS OF Flashback Query. The storage models, schema rules, and retention windows differ, but the abstraction is familiar: address a table in the past.

What remains uncommon is composition. BigQuery requires a constant timestamp expression and explicitly rejects subqueries and correlated references; Snowflake also requires a constant expression; Delta Lake accepts versions and timestamp expressions but not subqueries. Dynamic AT is therefore more than DuckLake convenience syntax. It is a way for DuckDB to push time travel beyond the usual literal-or-parameter boundary.

The need already appears in real workflows. A recent DuckLake time-travel bug report uses exactly the two-step max(snapshot_id) followed by the interpolated AT (VERSION => ...) pattern. A separate historical recovery discussion describes finding the correct snapshot in DuckLake’s audit trail but having to reconstruct dropped tables manually. These are different tasks with the same missing operation: compose snapshot metadata with a historical table reference.

That is the case for doing the work. Dynamic temporal references are more than a convenience; they are a path to making table history composable in SQL. Uncorrelated selectors are the smallest useful step, removing application glue now while laying the groundwork for lateral temporal joins—the capability large enough to justify the architecture.

What the prototype establishes

PR #25665 has already established the important parts. The PEG grammar and statement AST can represent the idea, and a selector’s typed result can drive DuckLake reads across schema changes. Just as importantly, the prototype bounds the remaining design work:

• Metadata-only APIs must remain free of arbitrary query execution.
• Prepared statements need an explicit story for snapshot-dependent schemas.
• The selector and historical scan need one transaction.
• A generic pre-bind mechanism needs statement-local typed handoff, not hidden connection state.
• Lateral temporal scans need a fixed-schema contract with the catalog.

Takeaways: make table history relational

The prototype leads to five conclusions:

• Snapshot IDs and timestamps are data. When they already live in queryable metadata, users should not have to move them through application code to construct a second statement.
• An uncorrelated scalar selector is the right first step. Evaluate it once, preserve its type, and use the result to bind the historical table in the same transaction.
• The larger payoff is lateral temporal joins, especially across the many data snapshots that share one schema.
• Schema evolution needs an explicit contract, but the hardest cross-schema case should not block useful same-schema temporal queries.
• The missing engine primitive is transaction-scoped pre-bind execution—not arbitrary SQL hidden inside binding and not connection-visible variables used as a mailbox.

The current prototype should not merge with its transaction gap. But the lesson is not that dynamic temporal references are too difficult or too niche. The grammar works, the user-facing model is natural, real workflows already expose the missing composition, and the remaining engineering problems now have clear boundaries.

SQL earns its value through composition. Time travel should not stop at AT (VERSION => 42) when the right version can itself be found with SQL. Snapshot metadata is data. Historical tables are relations. DuckDB should let them meet in one query.