Lab 12 · Master-data dedup with embeddings + LLM adjudication

Farhan owns vendor master data for a manufacturer whose SAP system has been fed by three acquisitions, two data-entry teams, and fifteen years of "just create a new vendor, procurement is waiting." Duplicate vendors are not cosmetic: they split spend history, defeat payment controls, and let one blocked vendor keep operating under a second number. The modern pipeline — embedding similarity for recall, LLM adjudication for judgment, human approval for accountability — works, but only when each layer does its own job. The lab's grading makes that concrete: a false merge (two real, distinct vendors collapsed) costs more than a missed duplicate, exactly as it does in production.

Time & tier

~90 minutes hands-on · Core tier (maps to module M7 · AI-Accelerated ERP Development, Testing & Migration).

Setup

Python with sentence-transformers, DuckDB optional, any AI chat for adjudication. Generate the vendor file and the hidden key with make_vendors.py — run it once and do not open answer_key.csv until step 7:

import csv, random
random.seed(99)
base = [(f"B{i:03d}", n) for i, n in enumerate([
  "Sharma Steel Industries","Kaveri Polymers","Lakshmi Precision Tools",
  "Bharat Bearings","Deccan Chemical Works","Ganga Fasteners",
  "Mysore Paints","Arvind Packaging","Nilgiri Rubber Company",
  "Patel Wire Products","Sundaram Castings","Malwa Alloys"])]
variants = {
  "B000": ["Shri Sharma Steel Industries Pvt Ltd","Sharma Steel Inds."],
  "B001": ["Kaveri Polymers Private Limited"],
  "B002": ["Laxmi Precision Tools","Lakshmi Precision Tools (unit II)"],
  "B003": ["Bharat Bearings & Co","Bharath Bearings"],
  "B004": ["Deccan Chemicals"],
  "B006": ["Mysore Paints and Varnish Ltd"],
  "B008": ["Nilgiri Rubber Co. (formerly Blue Hills Rubber)"],
  "B010": ["Sundaram Castings Division of TVS Group"],
}
rows, key, vid = [], [], 1000
def add(name, city, cluster):
    global vid; vid += 1
    rows.append([f"V{vid}", name, city]); key.append([f"V{vid}", cluster])
for bid, name in base:
    add(name, random.choice(["Pune","Chennai","Indore"]), bid)
    for v in variants.get(bid, []):
        add(v, random.choice(["Pune","Chennai","Indore"]), bid)
# distractors: similar names that are genuinely DIFFERENT companies
add("Sharma Steel Traders", "Delhi", "D1")      # trader, not the mill
add("Kaveri Plastics", "Chennai", "D2")
add("Bharat Bearings Services", "Mumbai", "D3") # service firm
for i in range(60):                              # unique filler vendors
    add(f"{random.choice(['Om','Sri','New','United'])} "
        f"{random.choice(['Textiles','Forgings','Electricals','Ceramics'])} {i}",
        random.choice(["Surat","Nagpur","Kochi"]), f"U{i}")
random.shuffle(rows)
with open("vendors.csv","w",newline="") as f:
    csv.writer(f).writerows([["vendor_id","name","city"]] + rows)
with open("answer_key.csv","w",newline="") as f:
    csv.writer(f).writerows([["vendor_id","cluster"]] + key)
print(len(rows), "vendors written; answer_key.csv is OFF LIMITS until scoring")

Guided steps

1. 1.

   Generate candidate pairs with embeddings. Encode every vendor name (append the city — decide now whether city should influence similarity, and write your reasoning down; there is a defensible case each way) and emit all pairs above a low threshold, pairs.py:

   Copy

   from sentence_transformers import SentenceTransformer
   import csv, numpy as np, itertools
   model = SentenceTransformer("all-MiniLM-L6-v2")
   rows = list(csv.DictReader(open("vendors.csv")))
   vecs = model.encode([r["name"] for r in rows], normalize_embeddings=True)
   with open("candidates.csv","w",newline="") as f:
       w = csv.writer(f); w.writerow(["id_a","name_a","id_b","name_b","sim"])
       for i, j in itertools.combinations(range(len(rows)), 2):
           s = float(np.dot(vecs[i], vecs[j]))
           if s > 0.60:
               w.writerow([rows[i]["vendor_id"], rows[i]["name"],
                           rows[j]["vendor_id"], rows[j]["name"], round(s,3)])
   print("candidate pairs written")
   

   Expected: a few dozen pairs. The 0.60 threshold is deliberately generous — embeddings are the recall layer; their job is to never miss a real duplicate, and precision is someone else's job.

2. 2.

   Triage the pairs into three bands. Sort by similarity and split: high (≥0.90 — near-certain), borderline (0.72–0.90 — the judgment zone), low (<0.72 — near-certain distinct, but skim for surprises like transliterations, which score lower than humans expect: check that Lakshmi/Laxmi and Bharat/Bharath pairs landed somewhere in your candidates at all; if not, your threshold or text prep needs revisiting before proceeding). Record the band counts.

3. 3.

   Adjudicate the borderline band with the LLM — reason codes mandatory. Feed borderline pairs in batches of ten with this contract: "For each pair, verdict MERGE / DISTINCT / NEEDS-EVIDENCE with a reason code: TRANSLIT (spelling/transliteration variant), LEGALFORM (Pvt Ltd / & Co suffix variants), ABBREV, MERGED-ENTITY (formerly-known-as / division-of), SAME-NAME-DIFF-BUSINESS (similar name, different line of business), INSUFFICIENT. One sentence of evidence per verdict, citing only the given fields." Save the full transcript as adjudication.md. Expected behavior worth watching: the model should mark the distractors (Sharma Steel Traders vs Sharma Steel Industries) as DISTINCT or NEEDS-EVIDENCE — a steel trader and a steel mill are different businesses with similar names; if it merges them confidently, you have a live example for step 4.

4. 4.

   Human review — you own every verdict. Walk all adjudications plus the high band (yes, the high band too: near-certain pairs still contain the unit II case — is a second unit of the same company a duplicate vendor or a legitimate separate ordering address? That is a business rule, not a similarity score; record your ruling and its rationale). Overrule the LLM where its evidence is thin, and mark every NEEDS-EVIDENCE pair with what evidence would decide it (GSTIN, bank account, address). Produce merge_list.csv: vendor_id_keep, vendor_id_merge, reason_code, approved_by.

5. 5.

   Define survivorship rules. For each approved merge cluster: which record survives (rule, not vibes — e.g., most-recently-transacted vendor_id survives; here, lowest vendor_id as proxy), what happens to the loser's open POs and payment history (re-point, never delete), and which fields merge (longest legal name wins; conflicting cities escalate). Write survivorship.md — five rules, each one sentence.

6. 6.

   Build clusters from your merge list. Union-find over your approved pairs to produce my_clusters.csv: vendor_id, my_cluster. Every unmerged vendor is its own cluster. (Fifteen lines of Python; ask your assistant to draft it, then check it handles chains — A=B, B=C must land A,B,C together.)

7. 7.

   Score against the hidden key. Now open answer_key.csv. Compute pairwise precision and recall, score.py: truth pairs = all same-cluster pairs in the key (clusters B000–B010; every D*/U* vendor is a singleton); your pairs = same-cluster pairs from my_clusters.csv. Precision = correct pairs / your pairs; recall = correct pairs / truth pairs. Expected for a careful run: precision 1.0 (no distractor merged, no filler merged) with recall 0.75–1.0 depending on your transliteration catches and your unit II ruling — and if your unit II ruling differed from the key, document it as a defensible business-rule deviation, which the rubric treats differently from a miss.

8. 8.

   Write the closing memo. dedup-memo.md: band counts, adjudication stats (verdicts by reason code, your overrules), the score with a per-error post-mortem (each false merge and each miss, one line on which layer failed — embedding recall, LLM judgment, or human review), and the paragraph you would send procurement: what gets merged this cycle, what waits for GSTIN evidence, and why nothing was auto-merged.

Verify

• •
  candidates.csv, adjudication.md (with reason codes on every verdict), merge_list.csv (with approver), survivorship.md, my_clusters.csv, and the score all exist.
• •
  No merge in merge_list.csv lacks a reason code, and every NEEDS-EVIDENCE pair names its deciding evidence.
• •
  The three distractors are not merged with their look-alikes.
• •
  Precision and recall are computed pairwise against the key, and every error has a post-mortem line.
• •
  The memo states explicitly that no pair was merged without human approval.

Self-grading rubric (pass = 8/12): candidate generation with recall-oriented threshold and the transliteration check (0–2); adjudication contract enforced — reason codes and evidence on all verdicts (0–2); human review shows real overrules or a written basis for accepting all (0–2); survivorship rules complete including open-PO handling (0–2); precision = 1.0 (0–2, minus 1 per false merge); recall ≥ 0.75 or documented defensible deviations (0–2).

Stuck?

• The transliteration pairs (Laxmi/Lakshmi, Bharat/Bharath) are missing from candidatesMiniLM handles these but scores them lower than suffix variants; drop the threshold to 0.55 and re-run rather than raising it later, because a duplicate that never becomes a candidate is invisible to every downstream layer.
• Union-find collapses too mucha chained false merge propagates: one bad A=B verdict can glue two real clusters. If precision drops mysteriously, print each final cluster's members and find the bridging pair.
• Your recall is low but every miss is a unit II-style judgment callthat is not a failure; document each as a business-rule deviation with rationale and score it per the rubric's deviation clause.