Time Travel Queries

Introduction

DuckLake’s time travel capabilities provide a powerful audit trail for your data, enabling you to:

• View data as it existed at any specific point in time
• Query specific versions of your tables
• Restore tables to previous states
• Track the complete history of changes
• Meet regulatory and compliance requirements

This functionality is especially valuable in domains where data provenance and reproducibility are critical, such as clinical trials, financial reporting, and scientific research.

Setting Up the Data Lake

We’ll start by creating a new DuckLake and loading the mtcars dataset. We’ll then make several modifications to demonstrate time travel functionality.

# Create or attach to a data lake
attach_ducklake(
  ducklake_name = "time_travel_demo",
  lake_path = vignette_temp_dir
)

# Create initial table with the mtcars dataset
with_transaction(
  create_table(mtcars, "cars"),
  author = "Data Engineer",
  commit_message = "Initial load of mtcars dataset"
)

# Verify the table was created
get_ducklake_table("cars") |>
  select(mpg, cyl, hp, wt) |>
  head()

Making Changes Over Time

Let’s make several changes to our data to create a version history we can explore.

get_ducklake_table("cars") |>
  summarise(
    n_cars = n(),
    avg_mpg = mean(mpg, na.rm = TRUE),
    avg_hp = mean(hp, na.rm = TRUE)
  )

# Update mpg for high-performance cars (5% reduction), in place: the
# filter becomes the WHERE clause of an UPDATE
with_transaction(
  get_ducklake_table("cars") |>
    filter(hp > 200) |>
    mutate(mpg = mpg * 0.95) |>
    ducklake_exec(),
  author = "Data Analyst",
  commit_message = "Adjust MPG for high-performance vehicles"
)

# Check the updated averages
get_ducklake_table("cars") |>
  summarise(
    n_cars = n(),
    avg_mpg = mean(mpg, na.rm = TRUE),
    avg_hp = mean(hp, na.rm = TRUE)
  )

# A new column is a metadata change; filling it is an in-database UPDATE
with_transaction({
  add_table_column("cars", "efficiency_class", "VARCHAR")
  get_ducklake_table("cars") |>
    mutate(
      efficiency_class = case_when(
        mpg >= 25 ~ "High",
        mpg >= 20 ~ "Medium",
        TRUE ~ "Low"
      )
    ) |>
    ducklake_exec()
},
  author = "Data Analyst",
  commit_message = "Add efficiency classification"
)

# View the new classification
get_ducklake_table("cars") |>
  count(efficiency_class) |>
  arrange(desc(n))

with_transaction(
  get_ducklake_table("cars") |>
    mutate(
      efficiency_class = case_when(
        mpg >= 30 ~ "High",
        mpg >= 20 ~ "Medium",
        TRUE ~ "Low"
      )
    ) |>
    ducklake_exec(),
  author = "Senior Analyst",
  commit_message = "Correct efficiency classification thresholds"
)

# View the corrected classification
get_ducklake_table("cars") |>
  count(efficiency_class) |>
  arrange(desc(n))

Exploring Version History

Now that we have a history of changes, let’s explore the time travel functionality.

# View all available versions of the table
snapshots <- list_table_snapshots("cars")
snapshots

# Get version 2 (after MPG adjustment, before classification)
get_ducklake_table_version("cars", version = 2) |>
  select(mpg, cyl, hp, wt) |>
  head()

# Notice: no efficiency_class column yet

# Get version 3 (with initial classification)
get_ducklake_table_version("cars", version = 3) |>
  select(mpg, efficiency_class) |>
  count(efficiency_class)

# Get the timestamp of the second snapshot (the MPG adjustment)
version2_timestamp <- snapshots$snapshot_time[[2]]

# Query data as it existed at that time
# Note: Add 1 second to ensure we query AFTER the snapshot was created
get_ducklake_table_asof("cars", version2_timestamp + 1) |>
  summarise(
    avg_mpg = mean(mpg, na.rm = TRUE)
  )

Comparing Versions

One powerful use case is comparing different versions to understand what changed:

# Get MPG values from version 1 (original) and version 2 (after adjustment)
original <- get_ducklake_table_version("cars", version = 1) |>
  select(mpg) |>
  collect() |>
  mutate(version = "Original")

adjusted <- get_ducklake_table_version("cars", version = 2) |>
  select(mpg) |>
  collect() |>
  mutate(version = "Adjusted")

# Combine and compare
bind_rows(original, adjusted) |>
  group_by(version) |>
  summarise(
    avg_mpg = mean(mpg, na.rm = TRUE),
    min_mpg = min(mpg),
    max_mpg = max(mpg)
  )

That compares two whole versions. For the rows themselves, get_table_changes() lists every row inserted, updated, or deleted between two snapshots, and view_table_changes() opens that feed in an interactive viewer; vignette("visualizing-your-lake") shows both.

Restoring Previous Versions

If we need to undo changes, restore_table_version() rolls a table back to an earlier snapshot in one call:

# Go back to version 2 (before adding classifications)
restore_table_version("cars", version = 2, author = "Senior Analyst")

# Verify the restoration - efficiency_class column should be gone
get_ducklake_table("cars") |> colnames()

You can also restore to a point in time with restore_table_version("cars", timestamp = "2026-07-01 09:00:00"), and pass a custom commit_message if the default (“Restored cars to snapshot 2”) isn’t descriptive enough for your audit trail.

Nothing is lost in a restore: the rollback happens forward, as a new snapshot with its own author and commit message, so the full history, including the states after the restore point, remains available for time travel. That also means a restore is itself reversible with another restore_table_version() call:

list_table_snapshots("cars")

Pinning a Whole Session to a Snapshot

The queries above travel one table at a time. To freeze everything, say to re-run a report exactly as it stood at a submission milestone, attach the lake pinned to a snapshot:

attach_ducklake(
  "cars_milestone",
  lake_path = "~/data/lake",
  snapshot_version = 2
)

Every table then reads as of snapshot 2 with no AT (...) clauses needed, and writes are rejected, so the milestone view can’t drift. A snapshot_time argument does the same for a point in time.

Row Lineage

Every row in a DuckLake table carries two hidden columns: rowid, an identifier assigned when the row was first inserted and kept through updates and compaction, and snapshot_id, the snapshot that wrote the row’s current version. They are how the change feed tells an update apart from a delete followed by an insert. dbplyr does not know about hidden columns, so read them with a SQL query:

conn <- get_ducklake_connection()
tbl(conn, sql("SELECT rowid, snapshot_id, mpg, cyl FROM cars")) |>
  head(5) |>
  collect()

Use Cases for Time Travel

Time travel functionality is particularly valuable for:

1. Regulatory Compliance: Maintain complete audit trails for datasets used in regulatory submissions (e.g., clinical trials, financial reporting)
2. Reproducibility: Recreate analyses exactly as they were run at specific points in time
3. Data Recovery: Restore accidentally modified or deleted data
4. Change Tracking: Understand when and how data quality issues were introduced
5. Reporting: Generate historical reports using data as it existed at specific time points
6. Collaboration: Allow team members to reference specific versions of shared datasets
7. Debugging: Identify when unexpected changes occurred in your data pipeline

Metadata and Audit Information

Each snapshot includes metadata about when it was created and what changes were made. The list_table_snapshots() function provides a complete audit trail:

# Get detailed snapshot history with all metadata
snapshot_history <- list_table_snapshots("cars")
snapshot_history |>
  select(snapshot_id, snapshot_time, author, commit_message)

This complete audit trail ensures that you can always answer questions like:

• What changes were made?
• When were they made?
• What version is the table at?
• What was the data before this change?

You can also access metadata about all tables in the DuckLake:

# View metadata for all tables
all_snapshots <- list_table_snapshots()
all_snapshots |>
  select(snapshot_id, snapshot_time, changes) |>
  head(10)