Restore a table to a previous version

Rolls a table back to the state it had at an earlier snapshot or point in time, by recreating it from a time-travel read of itself. History is preserved: the restore is recorded as a new snapshot (with a commit message noting the restore), so nothing is rewritten or lost and you can still time-travel to any snapshot, including those after the restore point.

Usage

restore_table_version(
  table_name,
  version = NULL,
  timestamp = NULL,
  author = NULL,
  commit_message = NULL,
  conn = NULL
)

Arguments

table_name

The name of the table to restore

version

Optional snapshot id to restore to (see list_table_snapshots())

timestamp

Optional timestamp to restore to (POSIXct, converted to UTC, or character already in UTC)

author

Optional author to record on the restore snapshot, for the audit trail. Defaults to the ducklake.author option when it is set.

commit_message

Optional commit message for the restore snapshot. Defaults to a message noting the restore point (e.g. "Restored my_table to snapshot 5").

conn

Optional DuckDB connection object. If not provided, uses the default ducklake connection.

Value

Invisibly returns TRUE on success

Details

You must specify either version or timestamp, but not both.

Under the hood this reads SELECT * FROM t AT (VERSION => n) into a temporary DuckDB table, then drops and recreates t from it inside a transaction. Because the restore creates a new snapshot, it is itself reversible with another restore_table_version() call.

The restore commits for itself, with its own author and message, so it cannot join a transaction that is already open and says so before it touches anything. To restore to a version and change it in one snapshot, pipe get_ducklake_table_version() into replace_table() inside with_transaction().

The restored table gets a new table id, and DuckLake keeps comments, partition keys, sort order, and table-scoped options against the id, so they are captured beforehand and put back: comments and keys for the columns the restored version still has, inside the restore transaction; table-scoped options right after it commits, as a small follow-up snapshot, since DuckLake cannot set options on a table created in the open transaction.

See also

get_ducklake_table_version(), get_ducklake_table_asof(), list_table_snapshots()

Other time travel: get_ducklake_table_asof(), get_ducklake_table_version(), get_table_changes(), list_table_snapshots(), plot_snapshots(), plot_table_changes(), view_table_changes()

Examples

lake_dir <- tempfile("restore_lake_")
dir.create(lake_dir)
attach_ducklake("restore_lake", lake_path = lake_dir)
create_table(data.frame(id = 1:3, amount = c(10, 20, 30)), "orders")

rows_delete(
  get_ducklake_table("orders"),
  data.frame(id = 1L),
  by = "id"
)
snapshots <- list_table_snapshots("orders")
first_version <- snapshots$snapshot_id[1]

# Roll the table back to its first snapshot
restore_table_version("orders", version = first_version)
#> Committed snapshot 3: Restored orders to snapshot 1

# Record who performed the restore in the audit trail
restore_table_version(
  "orders",
  version = first_version,
  author = "Data Steward",
  commit_message = "Roll back erroneous bulk update"
)
#> Committed snapshot 4 (Data Steward): Roll back erroneous bulk update

detach_ducklake("restore_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)