Katkat
Back

Reconcile Records Across Sources

RampSoftware Engineer · Technical Screen
HardSoftware EngineerTechnical ScreenSoftware Engineering Fundamentals

Last updated

This question evaluates entity resolution, record linkage, data normalization, conflict resolution, and data quality competencies within the Software Engineering Fundamentals domain by probing how to identify, group, and produce canonical person records from multiple noisy sources.

In an AI-assisted ("AI coding") interview round, you are asked to reconcile person records across multiple data sources. Each source contains records with fields such as name, email, phone, and address. Across and within sources, records may have missing values, inconsistent formatting (casing, punctuation, abbreviations), exact and near duplicates, and directly conflicting information.

Design and implement a reconciliation approach that:

  1. decides whether two records refer to the same real-world person,
  2. groups all records that refer to the same person,
  3. produces a canonical merged record for each group, and
  4. leaves genuinely unmatched records separate (a group of one is fine).

Be explicit about (a) field normalization, (b) the matching rules or confidence scoring that drive the same-person decision, (c) conflict resolution when grouped records disagree on a field, and (d) how you would validate the quality of the matches. Because this is an AI-assisted round, you should also be ready to explain how you used the AI tool, what you verified by hand, and where you chose not to trust it.

Constraints & Assumptions

State your own, but a reasonable baseline:

  • Input is a list of records, each tagged with a source (and ideally a last_updated timestamp). No globally trusted primary key exists across sources — that's why reconciliation is needed.
  • Fields are free-text and dirty: emails may differ only in case, phones may have varied separators/country codes, addresses use inconsistent abbreviations, names include nicknames/initials/middle names.
  • False positives (merging two different people) are more costly than false negatives (leaving one person split), unless told otherwise — over-merging silently corrupts data and is hard to undo.
  • Dataset is large enough that an all-pairs comparison is unacceptable; assume you must reason about throughput, not just correctness on the toy example.

Clarifying Questions to Ask

  • What is the entity and the cost asymmetry — is a wrong merge worse than a missed merge, and is a manual-review band for borderline cases acceptable?
  • Are any sources authoritative (e.g. a verified/KYC source) whose values should win on conflict, and is there a reliable recency signal (last_updated)?
  • What scale are we targeting (thousands vs. tens of millions of records), and does it run batch (one pass over a snapshot) or incrementally as new records stream in?
  • Which fields are reliable identifiers in this domain — is email/phone unique per person, or do families/companies share them? Are there fields not shown (DOB, SSN, account id) we can use?
  • Is there a ground-truth / labeled set to measure precision and recall, or do we need to bootstrap one?

Hints

Hint 1

Where to start: Don't jump to matching. First write a deterministic **normalization** layer per field (lowercase + trim email; strip non-digits and apply a country/E.164 convention to phone; expand street abbreviations and uppercase ZIP in address; tokenize and case-fold names). Matching quality is mostly won or lost here.

Hint 2

Avoid the $O(n^2)$ trap: Comparing every pair is $O(n^2)$. Use **blocking / candidate generation**: only compare records that share a cheap key (normalized email, normalized phone, or a weaker composite like `last_name + ZIP`). This is the single biggest scalability lever.

Hint 3

Turning pairwise matches into groups: A pairwise "same person" decision is not transitive by default. Model records as nodes and confident matches as edges, then take **connected components** (union-find / DSU, or BFS/DFS over the graph) so A–B and B–C pull A and C into one group.

Hint 4

Conflict resolution & evaluation: For the canonical record, decide a **precedence policy** per field (trusted/verified source wins, else most-recent non-null, else majority vote) and keep **provenance**. To validate, you need a **labeled set** and the right metric — think precision vs. recall and which error is more expensive here.

Follow-up questions

  • How would you convert this from a **batch** job into an **incremental** one, where a new record must be matched against millions of existing records without re-running everything?
  • Your blocking key misses some true matches (e.g. a typo'd email). How would you **recover recall** without falling back to all-pairs — fuzzy blocking, multiple blocking passes, or similarity (Levenshtein / Jaro-Winkler / token-set) on candidate pairs?
  • If two **high-confidence** sources disagree on a field and there's no recency signal, what do you put in the canonical record, and how do you make that decision auditable and reversible?
  • How would you detect and stop **runaway over-merging**, where weak transitive edges collapse many distinct people into one giant group?