BadgeprintingEvent registration · badge automation

Deduplicating Attendees Across Multiple Registration Sources

Symptom Statement

One human registers twice — once when the CRM export lands, once when they buy their own ticket through the form — and the pipeline treats them as two people. Two confirmation emails go out, two badge jobs hit the print floor, and at check-in the attendee stands there holding one lanyard while the desk stares at a second unclaimed badge with a slightly different spelling of their name. The records are obvious duplicates to a human and invisible to the system: Renée O'Brien from the CRM versus renee obrien from the checkout form, [email protected] versus [email protected] , the same person with a trailing space and a capital letter. This page addresses that exact symptom — near-duplicate attendee records that never collide on a match key — and it is the detection-side failure within the attendee identity resolution stage, which owns how partial records from every source are collapsed into one canonical identity. The tells are consistent: badge counts exceeding confirmed headcount, a check-in list with visually adjacent near-twins, and a manual “merge these two” ticket queue that grows every time two sources import the same cohort.

Why two records escape dedup, and the normalize-then-block-then-score fix that catches them The top row shows the broken path: a CRM record with mixed-case email and a diacritic name, and a form record with a trailing space and a lowercase email, are compared by raw exact-email equality, which returns not equal, so two badges print. The bottom row shows the fix: both records pass through a normalize step that casefolds and trims the email and folds diacritics from the name, producing an identical match key; a blocking step groups them by that key; and a fuzzy-score step confirms the pair is above the duplicate threshold, so a single badge prints. BROKEN · exact-email compare CRM record [email protected] Renée O'Brien Form record [email protected]␣ renee obrien a.email == b.email False → 2 badges duplicate FIXED · normalize → block → score Normalize casefold · trim · fold Block on key [email protected] Score ≥ 0.90 same key + name 1 badge

Root Cause Analysis

Four concrete, independent failures let one human become two records. An incident is usually one of them, occasionally two stacked, so triage means naming which before changing thresholds.

  • No canonical match key. Records are compared on whatever field arrived — raw email here, first + last name there — so there is no single normalized value both sources are forced to agree on. Two representations of one human never land in the same bucket, and dedup silently never fires.
  • Normalization gaps in casing, diacritics, and whitespace. Exact string equality treats [email protected], [email protected], and [email protected] (trailing space) as three distinct people, and Renée as a different human from Renee. Every unfolded difference forks the key.
  • A race between concurrent source imports. The CRM batch and the ticketing webhook process the same cohort at the same time. Both check “does this attendee exist?”, both see nothing, and both insert — a check-then-insert race that manufactures duplicates even when the match keys are correct.
  • Over-eager exact-email-only dedup. Deduping on email alone and nothing else looks safe until a typo variant ([email protected] vs [email protected]) or a shared family inbox slips through, because email equality is neither necessary nor sufficient for identity on its own.

Symptom-to-Resolution Matrix

No Canonical Match Key

Symptoms

  • The same attendee appears once per source with no field the dedup step actually compares in common.
  • Merges only ever happen by hand, never automatically.

Root cause. Nothing forces the two records onto a shared, normalized value, so they never collide.

Fix

  1. Derive one deterministic match key per record from stable fields: normalized email as primary, a folded name|org signature as secondary.
  2. Compute the key at ingestion, before insert, and persist it as an indexed column so lookups are O(1).
  3. Compare on the key, never on a raw source field.

Normalization Gaps

Symptoms

  • Near-twins differ only by case, a diacritic, or a trailing space.
  • Dedup catches identical strings but misses obvious human duplicates.

Root cause. Raw equality preserves differences that carry no identity meaning.

Fix

  1. Casefold and strip() the email before it becomes a key: email.strip().casefold().
  2. Fold diacritics with Unicode NFKD decomposition and drop combining marks so Renée and Renee converge.
  3. Collapse internal whitespace runs to a single space before building the name signature.

Concurrent Import Race

Symptoms

  • Duplicates appear only when two sources import the same cohort in the same window.
  • The keys are correct, yet two rows still exist.

Root cause. A check-then-insert race: both importers read “absent” before either writes.

Fix

  1. Claim the match key atomically before insert with Redis SET ... NX: the loser of the race must merge into the winner, not insert.
  2. Back the claim with a database UNIQUE constraint on the match-key column so a lost race fails loudly instead of duplicating.
  3. On a unique-violation, catch it and route the record to the existing canonical row rather than raising.

Over-Eager Exact-Email Dedup

Symptoms

  • Typo-variant emails (crop for corp) escape dedup.
  • A shared household inbox wrongly collapses two different people.

Root cause. Email equality alone is treated as identity, which it is not.

Fix

  1. Require a second signal: a fuzzy name-plus-org score must agree before an email-key match auto-merges.
  2. Score typo variants with a string-similarity ratio and route the middle band to review instead of merging blindly.
  3. Never collapse two records on email alone when their names diverge beyond the threshold.

Minimal Working Implementation

A self-contained deduper: normalize into a canonical match key, block by that key so comparison stays near-linear, score each in-block pair with a blended email-and-name ratio, and assert the near-duplicates collapse while a genuine second human does not.

PYTHON
from __future__ import annotations

import unicodedata
from collections import defaultdict
from dataclasses import dataclass, field
from difflib import SequenceMatcher

DUPLICATE_THRESHOLD = 0.90  # >= is an auto-detected duplicate


def normalize_email(raw: str) -> str:
    """Casefold + trim so casing and stray whitespace never fork the key."""
    return raw.strip().casefold()


def fold_name(raw: str) -> str:
    """Strip diacritics, collapse whitespace, casefold — a stable name signature."""
    decomposed = unicodedata.normalize("NFKD", raw)
    ascii_only = "".join(ch for ch in decomposed if not unicodedata.combining(ch))
    # Drop apostrophes and punctuation that vary between sources (O'Brien vs OBrien).
    cleaned = "".join(ch for ch in ascii_only if ch.isalnum() or ch.isspace())
    return " ".join(cleaned.split()).casefold()


@dataclass
class Record:
    source: str
    record_id: str
    email: str
    full_name: str
    org: str = ""

    @property
    def match_key(self) -> str:
        return normalize_email(self.email)

    @property
    def name_sig(self) -> str:
        return f"{fold_name(self.full_name)}|{fold_name(self.org)}"


def block(records: list[Record]) -> dict[str, list[Record]]:
    """Group records that share a normalized email key; only same-block pairs compare."""
    buckets: dict[str, list[Record]] = defaultdict(list)
    for r in records:
        buckets[r.match_key].append(r)
    return {k: v for k, v in buckets.items() if len(v) > 1}


def pair_score(a: Record, b: Record) -> float:
    """1.0 when email and name both agree; degrade toward the name similarity."""
    email_signal = 1.0 if a.match_key == b.match_key else \
        SequenceMatcher(None, a.match_key, b.match_key).ratio()
    name_signal = SequenceMatcher(None, a.name_sig, b.name_sig).ratio()
    return 0.5 * email_signal + 0.5 * name_signal


@dataclass
class DedupResult:
    duplicates: list[tuple[str, str]] = field(default_factory=list)
    distinct: list[tuple[str, str]] = field(default_factory=list)


def deduplicate(records: list[Record]) -> DedupResult:
    result = DedupResult()
    for group in block(records).values():
        for i in range(len(group)):
            for j in range(i + 1, len(group)):
                a, b = group[i], group[j]
                pair = (a.record_id, b.record_id)
                if pair_score(a, b) >= DUPLICATE_THRESHOLD:
                    result.duplicates.append(pair)
                else:
                    result.distinct.append(pair)
    return result


if __name__ == "__main__":
    records = [
        Record("crm", "crm-1", "[email protected]", "Renée O'Brien", "Corp"),
        Record("form", "form-1", "[email protected] ", "renee obrien", "Corp"),
        # A different human sharing a household inbox must NOT collapse.
        Record("form", "form-2", "[email protected]", "Sam Rivera", "Corp"),
    ]
    out = deduplicate(records)
    # The casing/diacritic/whitespace near-twins are caught as one duplicate pair.
    assert ("crm-1", "form-1") in out.duplicates, "near-duplicate must be detected"
    # Same inbox, different name -> flagged distinct, never force-merged on email.
    assert ("crm-1", "form-2") in out.distinct
    assert ("form-1", "form-2") in out.distinct
    print("OK duplicates:", out.duplicates)
    print("OK distinct:", out.distinct)

The assert block is the fix’s own regression test: two records that differ only by casing, a diacritic, and a trailing space are detected as a duplicate, while a third record that shares the household inbox but carries a different name is held distinct — proving the deduper catches real twins without collapsing two people onto one email. Detection is where this page stops; once a duplicate pair is confirmed, folding them into one survivor without losing anything is the concern of merging attendee records without losing session history.

Memory & Performance Constraints

Dedup is CPU-bound on string similarity and memory-bound on the blocking buckets, not I/O-bound. These constraints bite when two sources import a full cohort at once.

Component Constraint Mitigation
In-block comparison Within one email bucket, pairs are still O(k²) if a shared inbox is huge Cap block size; spill oversized buckets to review rather than scoring every pair
Blocking buckets The defaultdict holds the whole import batch in memory Stream imports in bounded batches; drop a bucket once its pairs are scored
SequenceMatcher (GIL) The similarity ratio is a tight CPU loop holding the GIL Short-circuit on an exact key match before scoring; scale with process-per-core workers, not threads
Match-key index A non-indexed key column turns every lookup into a table scan Add a UNIQUE index on the normalized match key so claims and lookups stay O(1)

Incident Triage & Rollback

Fast path when duplicate badges surface — target under fifteen minutes to containment. Every step is non-destructive until the final rollback.

  1. Confirm it is a detection miss. Count records sharing a normalized key that were never linked: psql -c "SELECT match_key, COUNT(*) FROM attendees GROUP BY match_key HAVING COUNT(*) > 1;". Rows here are duplicates the deduper should have caught.
  2. Check for a normalization regression. Diff a known twin pair’s stored match_key values: if they differ by case, a diacritic, or whitespace, a normalization change forked the keys. redis-cli GET normalize:version against the deployed tag.
  3. Check for an import race. redis-cli --scan --pattern "claim:attendee:*" | wc -l against expected headcount; a runaway count with duplicate rows means the SET NX claim or the UNIQUE constraint is missing or was dropped in a migration.
  4. Contain the source. Pause the offending importer at the gate: redis-cli SET ingress:pause:<source> 1 EX 900, so clean sources keep flowing while duplicates stop being minted.

Rollback. If a normalization or threshold change caused the regression, git revert HEAD~1 --no-edit && docker compose up -d --build to restore the prior normalize_email/fold_name and DUPLICATE_THRESHOLD. Because detection is read-only until a merge is applied, reverting is always safe — it can only change which pairs are flagged, never destroy a record.

Post-rollback validation. Re-run the deduper over the affected window and confirm the known twins now collide:

BASH
python -m identity.dedup --since "1 hour ago" --dry-run
# expect the previously-missed pairs to appear under detected duplicates, and
psql -c "SELECT COUNT(*) FROM attendees a JOIN attendees b USING (match_key) \
         WHERE a.id < b.id AND a.canonical_id IS DISTINCT FROM b.canonical_id;"  # trending to 0

Frequently Asked Questions

Should I dedup on email alone if every attendee has a verified email? No. Email is the strongest single signal but it is neither necessary nor sufficient: typo variants (crop for corp) escape it, and a shared household or team inbox collapses two genuinely different people. Use the normalized email as the blocking key, then require a second agreeing signal — a fuzzy name-and-org score — before you treat the pair as one human. Email gets them into the same bucket; the name score confirms they belong there.

Why normalize before storing the key instead of at compare time? Because a persisted, indexed normalized key makes the claim atomic and the lookup O(1), and it closes the import race: two concurrent importers both resolve to the identical key and contend for the same SET NX claim, so one wins and the other merges. Normalizing only at compare time leaves the race open and forces a full scan on every dedup pass.

How do I keep a threshold change from silently merging different people? Roll the threshold as a versioned, logged value and run any change in dry-run first, comparing detected-duplicate counts before and after. A drop in the auto-merge floor should be staged through the review band, not shipped straight to auto-merge, so a regression surfaces as extra review volume rather than as wrongly fused attendees.

Related pages

Related Merging Attendee Records Without Losing Session History