Time travel support

[westonpace]

Several databases and data lakes support time travel which allows historical versions of a table to be queried. Regrettably, syntax and capabilities do not seem to be terribly consistent across the various implementations.

Querying Historical Times

This option lets a database be queried as it existed at a certain point in time. (Question: Is this "last version prior to the given time"? or is it an error if no snapshot exists with that exact time?)

Databricks

SELECT * FROM people10m TIMESTAMP AS OF '2018-10-18T22:15:12.013Z';

Snowflake

SELECT * FROM my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 09:20:00 -0700'::TIMESTAMP_LTZ);

Cockroach

SELECT * FROM t AS OF SYSTEM TIME '2016-06-15 12:45:00'

DuckLake

SELECT * FROM tbl AT (TIMESTAMP => now() - INTERVAL '1 week');

Querying Specific versions

This options lets a specific "version" of a database to be queried. What exactly this means seems to depend on the database implementation in question.

Databricks

SELECT * FROM people10m VERSION AS OF 123;

Snowflake

Note: unclear if this is the same thing (specific versions) or different

SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

Cockroach

Unsupported

DuckLake

SELECT * FROM tbl AT (VERSION => 3);

[JamesVorder]

I've started taking a look at this, and adding support for these seems doable. The functionality can be bolted onto maybe_parse_table_version. I can start by adding support for Databricks time travel, and get a PR together for that on its own to keep it small.