
Query a table at a specific timestamp (time travel)
Source:R/time_travel.R
get_ducklake_table_asof.RdRetrieves data from a DuckLake table as it existed at a specific point in time using DuckLake's AT (TIMESTAMP => ...) syntax.
Arguments
- table_name
The name of the table to query
- timestamp
A POSIXct timestamp (converted to UTC, which is how DuckLake records snapshot times) or character string in ISO 8601 format already in UTC (e.g., "2024-01-15 10:30:00")
- conn
Optional DuckDB connection object. If not provided, uses the default ducklake connection.
Value
A lazy table (class tbl_ducklake) that works with dplyr verbs.
Like get_ducklake_table(), collecting it restores stored column
labels: the ones in force at that snapshot, which a later
set_column_comments() may have changed. A table written from the read
(create_table(), replace_table()) takes the present-day comments,
as restore_table_version() does.
Details
DuckLake supports time-travel queries, allowing you to query historical data
as it existed at a specific timestamp. This uses the syntax:
SELECT * FROM table AT (TIMESTAMP => 'timestamp')
This is useful for:
Auditing changes over time
Recovering accidentally deleted or modified data
Comparing data states across different time points
Regulatory compliance and data lineage documentation
The timestamp resolves to the latest snapshot taken at or before it, a
snapshot's own time included, so the times from
list_table_snapshots() can be passed as they are. A timestamp
before the table's first snapshot is an error, and so is one in the
future, which DuckLake refuses; ducklake checks for that before the
query runs.
Examples
lake_dir <- tempfile("asof_lake_")
dir.create(lake_dir)
attach_ducklake("asof_lake", lake_path = lake_dir)
create_table(data.frame(id = 1:3, amount = c(10, 20, 30)), "orders")
rows_insert(
get_ducklake_table("orders"),
data.frame(id = 4L, amount = 40),
by = "id"
)
# Query data as of the first snapshot's own time
snapshots <- list_table_snapshots("orders")
get_ducklake_table_asof("orders", snapshots$snapshot_time[1]) |>
dplyr::summarise(total = sum(amount)) |>
dplyr::collect()
#> # A tibble: 1 × 1
#> total
#> <dbl>
#> 1 60
detach_ducklake("asof_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)