Interview/Databricks

Databricks interview questions: Time travel: reading the commit log backwards

Interactive Databricks interview questions on Time travel: reading the commit log backwards. Practice with the matching lesson. Read an old version from the commit log, RESTORE as a new forward commit, and the VACUUM that ends time travel.

Lesson · Simulation

An analyst overwrote a gold table at 2pm and finance needs this morning's numbers. What do you type?

Answer it out loud, then reveal. Play steps through like the simulators.

Production scenario

Time travel: reading the commit log backwards

RESTORE failed the morning after a storage-cleanup VACUUM

Symptoms

  • DESCRIBE HISTORY still lists version 940 but VERSION AS OF 940 throws a missing-file error
  • A VACUUM step was added to the nightly workflow last week
  • Storage footprint dropped sharply on the same night

All questions on this page

Indexed as FAQ. Open any item if you prefer a list to Play.

Showing 10 of 10 questions.

beginner

An analyst overwrote a gold table at 2pm and finance needs this morning's numbers. What do you type?

Time travel: reading the commit log backwards · tap to open the answer

Say this first: Read the older version: SELECT * FROM gold.revenue VERSION AS OF 812, or TIMESTAMP AS OF '2026-09-12 09:00:00'.

Why: Every Delta write appends an ordered JSON commit to _delta_log, and the superseded data files are still referenced by earlier versions, so reading an old version is a metadata read against a different file list — nothing is restored and nothing is written. DESCRIBE HISTORY gold.revenue lists versions, timestamps, and the operation behind each commit so you can pick the right one.

Common mistake: Assuming the data is gone and asking the platform team for a bucket-level restore.

Follow-up they will ask: Does that query change the table in any way?

Lesson · Simulation

intermediate

A MERGE with a bad join condition mangled the table and you want version 812 back. Do you INSERT OVERWRITE from VERSION AS OF 812?

Time travel: reading the commit log backwards · tap to open the answer

Say this first: Use RESTORE TABLE t TO VERSION AS OF 812 — it writes a new forward commit, so the rollback itself is auditable.

Why: RESTORE does not rewind the log; it adds a new version whose file list matches the target version, so DESCRIBE HISTORY shows a RESTORE operation and downstream readers just see another new version. Hand-rolling it with INSERT OVERWRITE loses that audit trail and splits the read and write into steps that can interleave with other writers.

Common mistake: Trying to delete JSON files out of _delta_log to 'undo' a commit.

Follow-up they will ask: What happens to a structured streaming reader on that table after a RESTORE?

Lesson · Simulation

senior

Rolling back a table worked on Tuesday; on Wednesday the same trick threw a file-not-found. What changed in between?

Time travel: reading the commit log backwards · tap to open the answer

Say this first: VACUUM physically deleted the data files those older versions point at, so the commits still exist but the Parquet does not.

Why: VACUUM removes files that the current version no longer references once they are older than delta.deletedFileRetentionDuration, and those unreferenced files are exactly what time travel reads. Commit history can outlive the data because delta.logRetentionDuration is a separate knob, so DESCRIBE HISTORY may still list a version you can no longer query.

Senior depth: Two independent dials. delta.deletedFileRetentionDuration decides how long VACUUM leaves unreferenced data files alone — restorability actually depends on that one. delta.logRetentionDuration decides how long the commit metadata survives. Set the file dial from the recovery requirement, accept that you pay storage for every superseded file for that long, and stop calling it a backup: a dropped table or a deleted storage prefix takes the log with it.

Common mistake: Saying 'time travel goes back 30 days' as if it were a fixed guarantee instead of a function of retention and VACUUM.

Follow-up they will ask: Which property do you raise if legal wants 60 days of restorability, and what does it cost?

Lesson · Simulation

architect

A junior ran VACUUM sales RETAIN 0 HOURS to reclaim storage while the hourly MERGE was running. What is the blast radius?

Time travel: reading the commit log backwards · tap to open the answer

Say this first: In-flight readers and the concurrent writer can fail on missing files, and every earlier version becomes unreadable — that retention floor exists to protect concurrent operations.

Why: Delta refuses a retention below the configured threshold unless someone sets spark.databricks.delta.retentionDurationCheck.enabled to false, precisely because a query that resolved its file list minutes ago may still be reading files VACUUM just deleted. Once it lands, only the current version is guaranteed readable, so RESTORE and VERSION AS OF for anything earlier are gone, along with change-feed and streaming replays that needed those files.

Senior depth: The retention check is a guardrail, not a nuisance — anyone disabling it should be able to name the longest-running reader on that table. I would VACUUM on a schedule with retention at or above the longest read plus the recovery window, keep it out of the write window on hot tables, and get real durability from something VACUUM cannot touch: cloud storage versioning and periodic DEEP CLONE snapshots to a separate location.

Common mistake: Disabling the retention check as a habit because the error message was in the way.

Follow-up they will ask: How do you get the storage savings safely instead?

Lesson · Simulation

beginner

TIMESTAMP AS OF '2026-09-01' fails saying the timestamp is before the earliest version, but the table is two years old. Why?

Time travel: reading the commit log backwards · tap to open the answer

Say this first: Timestamp travel resolves against the commits the log still retains, and those older commits were already expired by log cleanup.

Remember these

  • Timestamps resolve to the latest commit at or before the requested time
  • Expired commits give a resolution error, not a missing-file error
  • VACUUMed files give a missing-file error on a commit that still exists
  • DESCRIBE HISTORY tells you the real oldest version available

Why: A timestamp query resolves to the latest commit at or before the time you asked for, using the commit timestamps recorded in _delta_log. delta.logRetentionDuration governs how long those commits survive cleanup, so a table can hold two years of data while its earliest known version is recent. The opposite failure also exists and looks different: the commit is still listed but the Parquet it references was removed by VACUUM, which gives a missing-file error rather than a resolution error. DESCRIBE HISTORY is the first command either way, because it tells you the oldest version and timestamp you can actually ask for. The data's age is never the time-travel window; retention and VACUUM are.

DESCRIBE HISTORY ops.silver.orders;
SELECT * FROM ops.silver.orders TIMESTAMP AS OF '2026-09-01 00:00:00';

Common mistake: Treating the table's age as the time-travel window.

Follow-up they will ask: Which retention property would you change, and what would it not fix?

Lesson · Simulation

intermediate

Row counts dropped four percent overnight and nine commits landed. Find the bad one without guessing.

Time travel: reading the commit log backwards · tap to open the answer

Say this first: Read operationMetrics per commit in DESCRIBE HISTORY to find where rows disappeared, then diff that version against its parent with two AS OF reads.

Remember these

  • operationMetrics identifies the destructive commit from metadata only
  • EXCEPT between two AS OF versions shows the exact rows involved
  • Both steps are reads, safe to run against live production
  • RESTORE reverts everything after the bad commit; a MERGE does not

Why: Every commit records its operation and operationMetrics — numOutputRows, numTargetRowsDeleted, numFilesAdded and removed — so a DELETE or a MERGE that removed rows is identifiable from metadata alone, without scanning any data. Narrow to the commit where the count changes, then run an EXCEPT between VERSION AS OF n-1 and VERSION AS OF n to see the actual rows involved. Both of those are reads, so this is safe to do on a production table that is still being written. Only after you know which commit and which rows do you choose a remedy, because the choice matters: RESTORE reverts the whole table and throws away the eight good commits that landed after the bad one, whereas a targeted MERGE from the AS OF read repairs just the affected rows. Writing the hypothesis, the evidence, and then the change is the sequence interviewers are listening for.

SELECT * FROM ops.silver.orders VERSION AS OF 940
EXCEPT
SELECT * FROM ops.silver.orders VERSION AS OF 941;

Common mistake: Running RESTORE to the last known-good version and silently discarding the good commits that followed.

Follow-up they will ask: When is a targeted MERGE the better fix than RESTORE?

Lesson · Simulation

advanced

A structured streaming job reads this table and you need to RESTORE it. What happens to the stream?

Time travel: reading the commit log backwards · tap to open the answer

Say this first: RESTORE commits a non-append data change, so the Delta streaming source fails unless you restart it with skipChangeCommits or ignoreChanges and an explicit starting version.

Remember these

  • RESTORE removes and re-adds files, which is not an append
  • skipChangeCommits ignores the commit; ignoreChanges re-emits rewritten files
  • Either option requires an idempotent sink, normally a MERGE on a key
  • Stop the stream, restore, then restart with an explicit startingVersion

Why: A Delta streaming source expects commits to add files it can deliver incrementally; a RESTORE commit removes and re-adds files, which is a data change the source cannot express as new rows. By default it errors out rather than silently emitting something wrong. skipChangeCommits ignores such commits entirely, and ignoreChanges lets rewritten files through as if they were new data — both change your semantics, so the sink must be idempotent, which in practice means a MERGE on a business key rather than an append. The safe operational sequence is to stop the stream, perform the restore, choose the restart position with startingVersion, and verify the sink deduplicates. Doing the restore first and discovering the stream's behaviour afterwards is how a rollback turns into a duplicate-row incident.

spark.readStream.format("delta")
  .option("skipChangeCommits", "true")
  .option("startingVersion", 941)
  .table("ops.silver.orders")

Senior depth: I plan a restore on a streamed table as a coordinated operation, not a SQL statement. Stop the consumers, restore, pick the restart version deliberately, and confirm each sink converges on re-delivery before resuming. The underlying requirement is idempotent sinks, because every incremental option here eventually re-delivers something — so I would rather pay for MERGE semantics downstream than rely on exactly-once behaviour that a single rollback breaks.

Common mistake: Restoring a source table mid-day and assuming downstream streams simply carry on.

Follow-up they will ask: What makes the sink safe to re-emit rows into?

Lesson · Simulation

advanced

Legal wants a point-in-time copy of a 40 TB gold table kept for a year. Time travel or something else?

Time travel: reading the commit log backwards · tap to open the answer

Say this first: CLONE — shallow for a cheap short-lived snapshot, deep when the copy has to survive VACUUM and a possible DROP on the source.

Remember these

  • Shallow clone references the source's files and dies when VACUUM removes them
  • Deep clone copies files and survives source retention and DROP
  • CLONE accepts VERSION AS OF or TIMESTAMP AS OF to pin the snapshot
  • Deep clone can be re-run incrementally for recurring archives

Why: A shallow clone copies only metadata and points at the source table's existing data files, so it is near-instant and costs almost no storage — and it stops working the moment VACUUM on the source removes files the clone still references. A deep clone copies the data files into its own table and location, so it is independent of the source's retention, its VACUUM schedule, and a DROP. That independence is exactly what a one-year legal hold requires, which is why this is the moment to say out loud that time travel is not a backup. You can pin the snapshot precisely with VERSION AS OF or TIMESTAMP AS OF in the clone statement, and a deep clone can be re-run incrementally, copying only what changed, which makes it usable as a recurring archive rather than a one-off. The price is a second copy of the data and a lifecycle of its own.

CREATE TABLE finance.archive.orders_2026q3
  DEEP CLONE finance.gold.orders VERSION AS OF 940;

Senior depth: The distinction I insist on is retention versus durability. Time travel and shallow clones are retention features inside one table's storage, governed by VACUUM, and they vanish with the table. Deep clones, cross-account copies, and storage replication are durability, because they survive the failure modes that take the original with it. Legal holds and audit requirements are durability questions, so they get deep clones in a separate location with their own lifecycle policy, and I write the recovery horizon down rather than inferring it from a table property.

Common mistake: Taking a shallow clone for an archive and losing it the first time the source is vacuumed.

Follow-up they will ask: What is a shallow clone genuinely good for, then?

Lesson · Simulation

senior

People keep saying 'we have time travel, we don't need backups'. Dismantle it.

Time travel: reading the commit log backwards · tap to open the answer

Say this first: Time travel lives inside the same table in the same storage under the same credentials, so a DROP, a deleted container, or a region loss takes the history with it.

Remember these

  • History is stored in the same prefix under the same credentials as the table
  • DROP, VACUUM, and storage loss each end time travel
  • Protection is bounded by a retention window you pay storage for
  • Durability means a copy the same failure cannot reach

Why: The commit log and the superseded data files are objects in the same prefix as the live table, so every failure that affects the table affects its history identically. It protects you from one thing — a bad write — and only inside a retention window you are paying storage for. Drop a managed table and the log goes with it; run VACUUM and earlier versions stop being readable; lose the storage account and there was never a second copy. Real durability needs something the same failure cannot reach: deep clones in a different location or account, cloud storage versioning or cross-region replication, and the ability to replay from bronze. The framing that lands in an interview is that time travel is undo and a backup is a second copy, and the two are not substitutes.

Senior depth: I ask the question that settles it: name the failure you are protecting against, then say which mechanism covers it. Bad write inside the window is time travel. Bad write outside the window is a bronze replay. Dropped table is UNDROP if you are fast, otherwise a deep clone. Lost storage account or region is replication, and nothing in Delta helps. Once that table of failures and mechanisms exists, nobody argues that one feature covers all four, and the VACUUM schedule becomes a recovery decision rather than a storage-cleanup chore.

Common mistake: Quoting a retention number as an RPO without checking when VACUUM actually runs.

Follow-up they will ask: What is your RPO for a dropped managed table right now?

Lesson · Simulation

architect

Set the retention policy for a 500-table lakehouse. What do you actually decide, and what does each choice cost?

Time travel: reading the commit log backwards · tap to open the answer

Say this first: Set delta.deletedFileRetentionDuration per tier from the recovery requirement, keep delta.logRetentionDuration at least as long, and make VACUUM a scheduled, audited job.

Remember these

  • Restorability depends on file retention plus the VACUUM schedule
  • Log retention only governs whether a version can still be named
  • Default deleted-file retention is 7 days; raise it deliberately per tier
  • Retention must exceed the longest-running reader on the table

Why: Restorability is a function of the data-file retention — which defaults to 7 days — and of when VACUUM last ran; the log retention only governs whether the commit metadata still exists to name a version, which is why shortening one without the other leaves you with a window narrower than you think. So the decision is per tier rather than global: gold tables with an audit obligation get a long file retention plus deep-clone snapshots, while high-churn bronze and streaming tables get a short one because every superseded file is billed storage until VACUUM removes it. VACUUM then has to actually run on a schedule, because an unrun VACUUM makes the retention setting theoretical and lets storage grow without bound. Keep the retention above your longest-running reader so VACUUM can never delete files out from under an executing query, and treat disabling the retention-duration check as an incident rather than a workaround. The deliverable is a written recovery horizon per tier, not a number someone remembers.

ALTER TABLE finance.gold.orders SET TBLPROPERTIES (
  'delta.deletedFileRetentionDuration' = 'interval 30 days',
  'delta.logRetentionDuration' = 'interval 30 days');

Senior depth: I turn it into three tiers with explicit numbers and an owner, because a per-table conversation across 500 tables never finishes. Tier one is audited gold: long file retention plus periodic deep clones to a separate location. Tier two is operational silver and gold: retention sized to cover a weekend plus a replay window. Tier three is high-churn bronze and streaming sinks: short retention, because replay comes from the source rather than from versions. Then VACUUM runs as a governed job with its retention derived from the tier, and I audit for tables whose properties do not match their tier, so the policy is enforced rather than documented.

Common mistake: Raising retention uniformly across every table and discovering the storage bill on the high-churn streaming ones.

Follow-up they will ask: How do you tell whether VACUUM has ever run on a given table?

Lesson · Simulation