Time Travel & Restore

Goal: recover from a bad write by rolling the table back to a known-good Delta version. Understand how vacuum interacts with restore so you don’t accidentally lock yourself out of recovery.

Why this matters: Delta’s commit log makes every write reversible — until vacuum reclaims the old file versions past retention. Knowing that relationship is what turns a panic into a one-line fix.

Shape:

   write → write → write    (versions advance monotonically)
       │       │       │
       ▼       ▼       ▼
   ┌──────────────────────┐
   │  Delta commit log    │
   └──────────────────────┘
              │
              ├─► RESTORE … TO VERSION AS OF K
              │     → commits a NEW version N+1 whose file set = state at K
              │     → the un-drained WAL is WIPED (post-K writes discarded)
              │
              └─► VacuumTable (files older than retention, floor 168h)
                    → permanently removes files for old versions
                    → restoring past them then fails loudly

The surface, honestly

RESTORE is SQL, not an RPC and not a REST route. It is DDL-class: it lowers to the same compactor route TRUNCATE uses and rides Tables.Execute, so it works on every wire that carries SQL — the Postgres door, dodil data sql, the tables HTTP door.

RESTORE [TABLE] <table> TO VERSION   AS OF <n>
RESTORE [TABLE] <table> TO TIMESTAMP AS OF '<rfc3339-with-offset>'

There is no commit-log read API. The plane exposes no History RPC, no SHOW HISTORY statement, and no /history HTTP route — the Delta log is on object storage but nothing serves it as rows. In practice you find a restore target from what you already know: the version a DDL statement returned, the version reported by OptimizeTable / VacuumTable, your own application’s audit trail, or TIMESTAMP AS OF with a wall-clock instant from your logs. Write down the version before a risky migration — that is the whole recovery plan.

Read-side time travel is likewise narrow: only the GetRow RPC takes an as_of_version, and only for a primary-key point lookup. There is no SELECT … VERSION AS OF clause.

Prerequisites

• A bucket + table with at least a few writes — we’ll reuse the events table from the Manual Table recipe.
• A connection on any SQL-carrying wire. See Connect & wire adapters.

Setting the scene — what we’ll roll back

Say the last known-good state of events was Delta version 5. Now someone runs a destructive UPDATE with a too-broad predicate:

-- OOPS — meant `id = 99`, but the predicate matched everything
UPDATE events SET event_type = 'archived' WHERE id > 0;

Every row now says event_type = 'archived'. We want the previous state back.

Note the WHERE is not optional here — UPDATE and DELETE are refused without one. That guard stops the worst mistakes, but it can’t stop a bad predicate, which is what RESTORE is for.

1. Restore by version

RESTORE TABLE events TO VERSION AS OF 5;

conn.execute("RESTORE TABLE events TO VERSION AS OF 5")

dodil data sql -b kb-prod "RESTORE TABLE events TO VERSION AS OF 5"

The response is a DDL summary — the table name and the new version the restore committed:

{
  "statement_kind": "restore",
  "table_name": "events",
  "version": 8
}

Three things are true about that, and all three matter:

1. A restore is a forward commit. Delta reconstructs the target version’s file set with add/remove actions rather than rewinding the log. So the log is preserved, version numbers keep climbing, and a restore is itself restorable — you can undo an undo.
2. It is destructive to the tail, deliberately. Rows written after the target version are gone. The plane also wipes the table’s un-drained write-ahead log, because otherwise the next read or drain would fold those post-target writes back in and silently resurrect exactly what you were undoing.
3. Secondary indexes are not rolled back. An index is a derived table with its own history and no counterpart to the base’s restored version. The statement returns a warning saying so; read-time revalidation and backfill reconcile them.

If the WAL wipe fails, the statement returns a loud warning — “RESTORE committed, but the WAL wipe reported … rows written after the restored version may resurrect on the next drain; re-run the RESTORE”. Re-run it.

RESTORE is refused up front if the table doesn’t exist, and refused if the target version is beyond the current head — you can only restore to a version that has already committed.

Restore by timestamp

If you don’t have a clean version number but know the wall-clock time:

RESTORE TABLE events TO TIMESTAMP AS OF '2026-05-27T08:00:00Z';

This resolves to the last version committed at or before that instant. The timestamp must carry an offset (…Z or …+02:00) — a naive local timestamp has no defined meaning across a multi-region fleet, and guessing one is how you restore to the wrong hour. VERSION AS OF and TIMESTAMP AS OF are mutually exclusive; pass exactly one.

2. Verify the restore

SELECT event_type, COUNT(*) AS n
FROM events
GROUP BY event_type
ORDER BY n DESC;

Expect the original distribution (click, purchase, signup) — not a table full of archived. Reads are read-your-writes, so there is nothing to wait for: the restore’s Delta commit is visible immediately and the stale overlay was invalidated as part of it.

3. The retention window that bounds recovery

VacuumTable permanently deletes Delta data files that are no longer referenced by the current version and are older than the retention window. Once a version’s underlying files are gone you can no longer restore to it — the restore fails loudly rather than committing a table with dangling paths.

There is no retention-check bypass. VacuumRequest has exactly four fields — db_id, table, dry_run, retention_hours — and the plane passes enforce_retention: true. Delta’s --disable-retention-check escape hatch is not exposed on any wire or in the CLI. If you were relying on one, you were relying on something that does not exist.

The practical consequence: your restore window is your vacuum retention, and the background maintenance loop is already vacuuming whether or not you ever run it by hand. A bad write you discover on day 9 cannot be restored past a 7-day window. Recovery beyond that needs a separate story — a materialized snapshot, a mirrored table, or an audit log.

4. Vacuum safely — dry run first

Always dry-run before vacuuming a table you might need to restore:

dodil data table vacuum events --bucket kb-prod --dry-run -o json

{
  "table_name": "events",
  "version": 8,
  "dry_run": true,
  "files_deleted": 42
}

files_deleted under dry_run is the count that would be removed. The response carries the count only — the plane does not return the file paths.

Then the real run, optionally with a longer window:

# Default (168h) retention
dodil data table vacuum events --bucket kb-prod
 
# 30-day retention — a month-long restore window
dodil data table vacuum events --bucket kb-prod --retention-hours 720

--retention-hours below 168 is clamped to 168; there is no way to go under it.

5. Pinning a restore point

Since retention is the only thing standing between you and an unrecoverable table, pin the snapshots you actually care about as independent tables before a risky change:

CREATE TABLE events_pre_migration AS SELECT * FROM events;

The copy is an ordinary Delta table with its own history and its own retention clock — vacuum on events cannot touch it. Restoring from it is a normal INSERT … SELECT or CREATE OR REPLACE TABLE, not a RESTORE. See CTAS & Materialize.

6. Per-scenario restore patterns

Common gotchas

See also

• Maintenance — API Reference — the OptimizeTable / VacuumTable / Compact surface
• CTAS & Materialize — pin restore points as independent tables
• Manual Table — the schema this recipe restores
• SQL Compatibility — the full statement surface, and the honest refusal list