MessyData
Resilient Multi-Source Data Reconciliation & Tiered Entity Resolution Engine
// 01. THE PROBLEM
Organizations aggregating user records across multiple legacy databases end up with duplicate, corrupted, and poorly formatted profiles that skew analytics.
// 02. SYSTEM ARCHITECTURE & PIPELINE FLOW
Multi-Source Extraction & Normalization
Ingests raw records from PostgreSQL, REST APIs, and varied CSV encodings into normalized data schemas.
Exact Key & RapidFuzz Similarity Matching
Executes exact key matching followed by C++ accelerated Jaro-Winkler string distance scoring.
Golden Record Synthesis & Auto Merges
Clusters candidate profiles above 88% confidence and writes unified golden customer records.
Streamlit Lineage & Manual Triage Queue
Routes borderline matches (80%-88%) to a 20-record manual triage queue with full lineage tracking.
// 03. TECHNICAL APPROACH & DECISION MATRIX
01Tiered Entity Resolution Engine
Exact Key Matching + C++ RapidFuzz (Jaro-Winkler + Token Sort)
Exact SQL matching misses human typos. RapidFuzz provided C++ accelerated string distance calculations across thousands of records in milliseconds.
Naive SQL LIKE Pattern Matching
SQL LIKE clauses failed on transposed names, missing middle initials, and varied phone formats.
02Lineage Dashboard & Triage Queue
Streamlit Observability Dashboard + Manual Triage Queue
Borderline similarity scores (80%–88%) are safely routed to a manual triage queue, guaranteeing zero accidental merges on ambiguous records.
Silent Automated Merges Without Threshold Gates
Auto-merging below 88% risk corrupting distinct customer accounts with similar names.
// 04. CORE ARCHITECTURAL LOGIC
Tiered fuzzy matching engine utilizing RapidFuzz C++ algorithms for record clustering.
1234567891011121314151617def reconcile_customer_records(raw_records: pd.DataFrame, cutoff: float = 88.0) -> DeduplicationSummary: golden_records, triage_queue = [], [] clustered_groups = defaultdict(list) for idx, record in raw_records.iterrows(): matched = False for group_id, members in clustered_groups.items(): # C++ accelerated RapidFuzz Token Sort & Jaro-Winkler similarity score = fuzz.token_sort_ratio(record["name"], members[0]["name"]) if score >= cutoff: members.append(record) matched = True break if not matched: clustered_groups[record["id"]].append(record) return DeduplicationSummary(merged=len(clustered_groups), triage_count=len(triage_queue))
// 05. TRADE-OFFS & HONEST REFLECTION
Setting conservative fuzzy matching cutoffs (88%) required maintaining a manual triage queue for edge cases, but guaranteed 100% data integrity for golden records.