MessyData
Resilient Multi-Source Data Reconciliation & Tiered Entity Resolution Engine
1. THE PROBLEM
Organizations aggregating user records across multiple legacy databases end up with duplicate, corrupted, and poorly formatted profiles that skew analytics.
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.
2. TECHNICAL APPROACH & DECISIONS
1Tiered Entity Resolution Engine
Combined exact key matching with C++ accelerated RapidFuzz string similarity (Jaro-Winkler + Token Sort Ratio) to cluster candidate customer profiles.
Naive SQL LIKE pattern matching
Exact SQL matching misses human typos ('Anirudh S' vs 'Anirud S'). RapidFuzz provided C++ accelerated string distance calculations across thousands of records.
2Lineage Dashboard & Triage Queue
Constructed a Streamlit observability dashboard tracking pipeline execution metrics, data lineage, and a dedicated 20-record manual triage queue.
Silent automated merges without human verification
Borderline similarity scores (80%–88%) are safely routed to a manual triage queue, guaranteeing zero accidental merges on ambiguous records.
CORE ARCHITECTURAL LOGIC
Tiered fuzzy matching engine utilizing RapidFuzz C++ algorithms for record clustering.
| 1 | def reconcile_customer_records(raw_records: pd.DataFrame, cutoff: float = 88.0) -> DeduplicationSummary: |
| 2 | golden_records, triage_queue = [], [] |
| 3 | clustered_groups = defaultdict(list) |
| 4 | |
| 5 | for idx, record in raw_records.iterrows(): |
| 6 | matched = False |
| 7 | for group_id, members in clustered_groups.items(): |
| 8 | # C++ accelerated RapidFuzz Token Sort & Jaro-Winkler similarity |
| 9 | score = fuzz.token_sort_ratio(record["name"], members[0]["name"]) |
| 10 | if score >= cutoff: |
| 11 | members.append(record) |
| 12 | matched = True |
| 13 | break |
| 14 | if not matched: |
| 15 | clustered_groups[record["id"]].append(record) |
| 16 | |
| 17 | return DeduplicationSummary(merged=len(clustered_groups), triage_count=len(triage_queue)) |
3. 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.
4. CONCRETE OUTCOME & METRICS
Executed 518 automatic profile merges with a dedicated 20-record manual triage queue, Streamlit lineage observability, and full CI/CD test automation in GitHub Actions.