Data Engineering / Editorial solution guide
Diagnose duplicate or missing data after a retry
Trace event identity, cursor boundaries and accepted publication before replaying a failed batch. Distinguish attempted work from durably accepted effects.
Read-only knowledge base · No public comments
Recognize the symptoms
A rerun doubles totals, an interrupted page loses records, or a checkpoint reports progress beyond visible target data. A green scheduler attempt does not establish complete delivery. Preserve the affected interval and known expected identities before another rerun changes evidence or adds more duplicate effects.
Understand the likely causes
- Possible causes include attempt IDs used as event identity, timestamp-only pagination, conflicting duplicates hidden by DO NOTHING, progress committed before the effect, or a lost response followed by a new non-idempotent request. Overlap and late corrections can deliberately repeat source records, so duplicate input is not automatically a source defect.
Identify one interval and the last accepted boundary
Record source version, logical interval, cursor before/after, target version, transformation version and attempt identity from existing authorized evidence. Separate an empty successful read from a failed fetch. Determine which effects actually committed and which acknowledgments were lost. Do not reset a shared cursor, delete target partitions or replay the complete history as a first reaction. The correction needs the dataset owner’s scope and a rollback/reconciliation plan, with private payloads excluded from general incident logs.
Count attempts and logical events separately
The complete offline program identifies repeated event labels in an invented delivery list. It does not decide whether repeated content agrees or which sink effects occurred. Compare source identity and content/version, then inspect the sink’s actual unique/update rule. A stable event key can prevent duplicate rows while still hiding a conflicting correction. Preserve attempt evidence for diagnosis, but do not give every retry a new business identity simply to make it insert successfully.
from collections import Counter
deliveries = ['e1', 'e1', 'e2']
counts = Counter(deliveries)
duplicates = sorted(key for key, count in counts.items() if count > 1)
print(f'attempts={len(deliveries)} unique={len(counts)} duplicates={duplicates}')attempts=3 unique=2 duplicates=['e1']
Three attempts contain two identities, with e1 repeated. No source, scheduler or target is queried.
Check every component of a page boundary
A timestamp-only greater-than predicate can skip rows sharing the final timestamp. The second local program contrasts that wrong boundary with a composite timestamp/ID cursor. This demonstrates a controlled ordering rule, not a snapshot guarantee for a changing source. Confirm null policy, tie uniqueness and actual ORDER BY/predicate agreement in the selected engine. Historical hard deletes or backdated updates may require CDC or deeper reconciliation; increasing a short overlap cannot recover arbitrarily old missing changes.
rows = [(10, 1), (10, 2), (11, 3)]
cursor = (10, 1)
wrong = [row[1] for row in rows if row[0] > cursor[0]]
correct = [row[1] for row in rows if row > cursor]
print(f'timestamp_only_ids={wrong}')
print(f'complete_cursor_ids={correct}')timestamp_only_ids=[3]
complete_cursor_ids=[2, 3]
ID2 disappears under the incomplete cursor but remains under the complete declared order.
Repair effects and progress under one declared protocol
If effect and checkpoint share a supported transaction boundary, publish them together. Otherwise use the documented idempotency and reconciliation process rather than assuming a scheduler retry is a distributed transaction. Scope the repair to known input history and accepted target versions. Verify same-identity conflicts explicitly, retain tombstone/version evidence where needed and coordinate downstream replacement so repaired totals are not added again. All examples here are synthetic computations; this guide performs no database repair, cursor change or production replay.
Verify the fix
On an isolated fixture, interrupt before effect, between effect and progress, and after commit before response. Rerun the same logical input and compare exact keys, occurrence counts, content versions and meaningful totals. Confirm the accepted checkpoint matches the target and that consumers replace or update the prior publication as intended. A successful task status alone is insufficient.
Keep it from recurring
- Centralize identity and cursor rules, pin input/transform versions and retain replay history long enough for recovery. Keep reject, empty, transient failure and conflicting duplicate outcomes distinct, and review checkpoint/sink atomicity whenever the destination or connector changes.