Prompt and scope
A batch job backfills 90 days of order dimensions and its transformation logic is later found to be wrong. Streaming writes continued during the run. Design how to locate the bad snapshot, reproduce affected queries, isolate a fix, handle commit conflicts, and set retention. The core skills are table-format versioning, lineage, and recoverable operations, so this belongs to data engineering.
What the interviewer evaluates
The answer should explain that a snapshot is a metadata pointer, not a simple file copy, and that time travel depends on retention. Cover concurrent commits, rollback effects, downstream read consistency, snapshot expiration, and orphan-file cleanup. “Restore yesterday” alone does not demonstrate safety.
Questions to clarify first
- Which catalog, engine, and commit lock are used, and how are snapshot IDs audited?
- Which partitions, snapshots, and downstream tables were affected?
- Did streaming jobs commit during the bad backfill, and can they be paused or replayed from a snapshot?
- Is the business asking for a table rollback, partition rewrite, or a replacement table for validation?
- How long are snapshots and data files retained, and could cleanup remove recovery evidence?
30-second answer framework
“I first identify the backfill commit from table history and job logs, then compare its snapshot with the parent using time-travel queries to bound affected partitions. I isolate correct inputs, rerun validation, and choose a partition rewrite, a repair snapshot, or a rollback only when no later valid commits must be kept. Before changing the pointer I check concurrent commits and readers; after it I rebuild downstream tables. Delay snapshot expiry and orphan cleanup until the recovery window closes, and monitor snapshot, file, and data-quality metrics.”
Step-by-step solution
Read table history, the snapshot list, and commit summaries. Record snapshot IDs, commit times, run IDs, partition ranges, and input versions. Do not infer the target from wall-clock time alone because concurrent commits can interleave. Compare the bad snapshot with its parent in metadata and data files, then use job logs, quality alerts, and partition statistics to bound impact.
Use time-travel queries to reproduce the same business metrics before and after the bad write. A snapshot gives a consistent read view, but it does not retain infinite history; finish queries before expiry or export validation samples and metadata references when necessary.
If streaming writes are still active, pause the conflicting partitions or create an isolated branch or temporary table. Read correct inputs as of the appropriate snapshot, fix the transformation, and commit the repair only after checking that the parent snapshot is still the expected version. On a conflict, reread the latest snapshot and recompute instead of force-overwriting valid writes.
A table-wide rollback is appropriate only when no later valid commit must be preserved and readers accept a temporary regression. More often, rewrite affected partitions or publish a repaired table and switch downstream references. Moving the metadata pointer does not repair data already materialized into other tables.
After the repair, rerun uniqueness, counts, amounts, latency, and business reconciliation checks. Compare the bad snapshot, repair snapshot, and raw events. Recompute downstream tables from the repair version and record the new snapshot and code version so the run is reproducible.
Retention must cover backfill, review, downstream recomputation, and audit deadlines. Expiring snapshots too early can make time travel fail. Only files no longer referenced by retained snapshots should be removed after confirming no readers need them; orphan cleanup must not delete files still referenced by an uncommitted or concurrent job.
Monitor snapshot age, commit conflicts, rollback count, orphan-file count, expiration failures, partition quality, downstream recomputation delay, and reconciliation gaps. Write snapshot IDs, run IDs, code versions, and input partitions to an audit table so a result can be traced back to its commit.
Model high-quality answer
“I preserve table history, snapshot IDs, and the run ID, identify the backfill commit, and use time-travel queries between its parent and child to bound affected partitions. If streaming writes continue, I pause the conflict range or write the repair to an isolated table, validate the parent snapshot at commit time, and reread on conflict instead of force-overwriting.
If no later valid commit must be retained, I can roll back the metadata pointer; otherwise I rewrite partitions and publish a repair snapshot. Rollback does not repair downstream materialized tables, so I recompute them from the repair version and rerun quality and reconciliation checks. Snapshot expiry and orphan cleanup wait beyond the audit window, and every snapshot, code version, and input range is recorded.”
Common mistakes
- Guessing a snapshot from its timestamp → concurrent commits can interleave → use history, parentage, and run IDs.
- Treating a snapshot as a file backup → rollback may not fix downstream tables → inventory metadata pointers and derived data.
- Force-overwriting the latest snapshot → lose valid concurrent writes → check the parent and retry conflicts.
- Cleaning old snapshots immediately → time travel and audit evidence disappear → retain a recovery window.
- Validating only sample rows → aggregate errors remain → check partitions, metrics, uniqueness, and reconciliation.
- Rolling back only the main table → downstream results stay wrong → recompute derived tables from the repair version.
- Assuming orphan cleanup is harmless → concurrent jobs may still reference files → clean with commit and reader state.
- Omitting code and input versions → repair cannot be reproduced → link snapshots, runs, and versions in audit data.
Follow-up questions and responses
Follow-up 1: When is a table rollback better than a partition rewrite?
Only when no valid commit after the rollback point must be kept and readers accept a temporary regression. Otherwise rewrite partitions or publish a repaired table.
Follow-up 2: Why can time travel fail?
The target snapshot may have expired, or its files may have been removed. Retention must cover investigation and recomputation, and cleanup must be monitored.
Follow-up 3: How do you avoid overwriting new streaming data?
Limit the repair to affected partitions, pause conflicting writers or isolate the work, validate the parent snapshot, and recompute from the latest version after a conflict.
Follow-up 4: Does rollback undo downstream events?
No. It changes the table’s metadata pointer. Sent events, materialized tables, and external effects need separate compensation or recomputation.
Follow-up 5: How does a snapshot differ from a full backup?
A snapshot is usually a metadata view of a file set and depends on file retention. A full backup also requires cross-storage copies, catalog state, and a recovery procedure.
Follow-up 6: How do you prove the backfill repair is correct?
Pin the input snapshot and code version, rerun partitions, compare raw events and business metrics, check uniqueness and amounts, reconcile, and retain the repair snapshot for review.
Follow-up 7: Why do commit conflicts matter?
Iceberg commits update from a parent snapshot. Ignoring conflicts and forcing a write can discard another job’s valid commit; conflict should trigger reread, recomputation, or a human decision.