Reconcile years of dual writes after partial failures were normalized

• Build a field-level authority matrix and identify which operational edits were legitimate business state versus derived presentation. Define acceptable repair windows, customer notification thresholds, and whether historical intent can be reconstructed.

• Assume hundreds of millions of profiles and a comparison job that must not overload either store. Start with stratified samples and change recency cohorts before scheduling a full scan.

• Detect and classify every divergence, avoid overwriting newer valid data, make repairs idempotent and reversible, and preserve an audit trail. Stop the divergence source before large-scale repair.

• Create a canonical change log or database outbox from the chosen authority. A reconciliation pipeline joins versioned snapshots, classifies differences by field and cause, generates repair proposals, and routes high-risk cases to review.

• Compare normalized records with version, last writer, source event, and checksum. Repair commands include expected current version and provenance so conditional writes fail rather than clobber concurrent edits.

• The system of record owns business fields; search becomes a derived projection. Legitimate search-only edits are migrated into explicit source commands or a separate authority before reindexing.

• Run repairs in rate-limited batches, checkpoint results, and retain before-images for reversal. If mismatch rate or customer errors exceed a threshold, stop the affected class without halting safe categories.

• Reconciliation workers receive least-privilege field access, repair logs redact sensitive values, and human review is tenant-scoped. Historical operational tools must be disabled or redirected to the canonical write API.

• Track mismatch rate by field, age, writer, tenant, and cause; repair success, conditional conflicts, recurrence, and serving impact. A zero scan mismatch is not enough—ongoing change-log reconciliation proves drift stays closed.

• Use hashes and range summaries to skip equal partitions, compare on replicas, and prioritize high-value or high-risk tenants. Retain evidence long enough for audit without duplicating complete sensitive records in logs.

• Blind canonical overwrite is fast but can destroy legitimate noncanonical edits. Field-aware classification takes longer but turns an ambiguous cleanup into a controlled business migration.

• Freeze new search-only edits, add versions and provenance, deploy outbox-driven projection, then reconcile recent and high-risk cohorts before full history. Remove the dual-write path only after recurrence metrics stay at zero.

• Staff candidates do not simply say “rebuild the index.” They establish authority, account for valid exceptions, use conditional repair, and close the architectural cause that made divergence invisible.

Interviewer evaluation rubric