The Missing WAL Window
You are the data engineer on call for this production path. A CDC connector is offline longer than database log retention and cannot resume from its saved offset. What would you investigate, how would you fix it safely, and how would you prove the issue is resolved?
Scenario context
The incident centers on CDC retention and recovery. The current implementation or operating process does not make that contract explicit, so the team needs a diagnosis supported by evidence rather than a tool or configuration guess.
Business requirement
Identify the most likely failure mechanism, propose a reversible production-safe fix, and explain validation, trade-offs, monitoring, and recovery.
Schema
CDC evidence is shown below. Treat it as a production review artifact rather than a toy exercise.Broken logic / code
connector_offset = 'LSN/0A12F880'
oldest_available_wal = 'LSN/0B980010'
connector_status = 'FAILED: requested WAL segment removed'
# Recovery procedure is undefined.Logs / error
[Production review] Scenario 139: The Missing WAL Window
Observed symptom: A CDC connector is offline longer than database log retention and cannot resume from its saved offset. What would you investigate, how would you fix it safely, and how would you prove the issue is resolved?
Core contract at risk: CDC retention and recovery.
Evidence to collect: Track source log retention, connector offset age, replica lag, transaction age, schema versions, duplicate and out-of-order counts. Add source-to-target reconciliation by key and time window. Run planned failover, snapshot, DDL, and replay drills.Actual output
A CDC connector is offline longer than database log retention and cannot resume from its saved offset. What would you investigate, how would you fix it safely, and how would you prove the issue is resolved?Expected output / expected logic
A strong response should define the contract, rank likely causes, propose a safe fix, and prove correctness with monitoring and reconciliation.Your attempt
Write your root-cause analysis
Think before revealing the answer. A partial but honest attempt is better practice than reading the model solution first.
Saved
Interview-style explanation
Now explain your solution as if you are in an interview: symptom, root cause, fix, edge cases, trade-offs, monitoring, and prevention.