I spent a quarter working on transporter records for a logistics customer: missing identifiers, duplicate companies, and fuzzy matching across inconsistent names. The work affected every downstream system that joined against that table.
The customer's demand data depended on transporter status: who was active, who was verified, and which rows represented the same company. Identifiers were missing, names appeared under several spellings, and one transporter could exist as four records with different pieces of history. Dashboards and reports still rendered, but their totals depended on which duplicate a join happened to find.
Dirty reference data often produces plausible outputs rather than errors. Analyses, automations, and decisions inherit the duplicated or missing records, and the discrepancy may be several joins away from its source by the time someone notices it.
- Data Cascades in High-Stakes AIDocuments how neglected data problems propagate through high-stakes ML systems and become harder to diagnose downstream.
- Data-Centric Artificial IntelligenceSurveys data-centric methods that improve system performance by changing datasets rather than model architecture.
- Data Cleaning and Machine Learning: A Systematic Literature ReviewCatalogs cleaning tasks that show up in real ML systems: label cleaning, entity matching, outlier detection, imputation, and more.
Identity evidence
A similar name is the start of a question
Inspect a pair of invented source records, then follow three evidence-dependent identity decisions.
Scenario
Invented transporter names, IDs and review outcomes. Similar names nominate candidates; they never establish identity.
Sequence
- Read the source recordsCurrent
- Separate evidence from resemblanceUpcoming
- Record the decisionUpcoming
- Update downstream referencesUpcoming
Read the source records
The dispatch sheet has Mango Road Transport, an example registry ID and active operating status. The CRM has Mango Rd. Transport, no registry ID and inactive operating status. Preserve both source rows. Missing identity evidence and contradictory status observations are separate problems.
Read the full explanation
Needs review
Invented transporter names, IDs and review outcomes. Similar names nominate candidates; they never establish identity.
- Read the source records. The dispatch sheet has Mango Road Transport, an example registry ID and active operating status. The CRM has Mango Rd. Transport, no registry ID and inactive operating status. Preserve both source rows. Missing identity evidence and contradictory status observations are separate problems.
- Separate evidence from resemblance. There is no verified identity link. The name resemblance, missing registry ID and status conflict remain visible for review.
- Record the decision. Hold the candidate pair for review. Do not invent a canonical identity while the evidence is incomplete.
- Update downstream references. Both source records stay intact. Downstream consumers can see the unresolved pair instead of a false single identity.
Confirmed merge
Invented transporter names, IDs and review outcomes. Similar names nominate candidates; they never establish identity.
- Read the source records. The dispatch sheet has Mango Road Transport, an example registry ID and active operating status. The CRM has Mango Rd. Transport, no registry ID and inactive operating status. Preserve both source rows. Missing identity evidence and contradictory status observations are separate problems.
- Separate evidence from resemblance. For this scenario, an illustrative operator confirmation links the two source IDs to one business. The name similarity only proposed the pair; it did not authorize the merge.
- Record the decision. Merge into entity-A, retaining both names and source IDs as aliases. The active/inactive observations remain in the decision record; merging identity does not automatically settle operating status or identify which observation is current.
- Update downstream references. Both source IDs point to entity-A. Keep the evidence and before-state so an incorrect merge can be reversed.
Keep separate
Invented transporter names, IDs and review outcomes. Similar names nominate candidates; they never establish identity.
- Read the source records. The dispatch sheet has Mango Road Transport, an example registry ID and active operating status. The CRM has Mango Rd. Transport, no registry ID and inactive operating status. Preserve both source rows. Missing identity evidence and contradictory status observations are separate problems.
- Separate evidence from resemblance. For this scenario, an illustrative operator check identifies different businesses. Their similar names remain searchable without collapsing their identities.
- Record the decision. Keep entity-A and entity-B separate. Record the negative match so the same resemblance does not keep producing the same suggestion.
- Update downstream references. The sheet points to entity-A; the CRM row points to entity-B. Each business keeps its own history.
Needs review
Invented transporter names, IDs and review outcomes. Similar names nominate candidates; they never establish identity.
- Read the source records. The dispatch sheet has Mango Road Transport, an example registry ID and active operating status. The CRM has Mango Rd. Transport, no registry ID and inactive operating status. Preserve both source rows. Missing identity evidence and contradictory status observations are separate problems.
- Separate evidence from resemblance. There is no verified identity link. The name resemblance, missing registry ID and status conflict remain visible for review.
- Record the decision. Hold the candidate pair for review. Do not invent a canonical identity while the evidence is incomplete.
- Update downstream references. Both source records stay intact. Downstream consumers can see the unresolved pair instead of a false single identity.
Confirmed merge
Invented transporter names, IDs and review outcomes. Similar names nominate candidates; they never establish identity.
- Read the source records. The dispatch sheet has Mango Road Transport, an example registry ID and active operating status. The CRM has Mango Rd. Transport, no registry ID and inactive operating status. Preserve both source rows. Missing identity evidence and contradictory status observations are separate problems.
- Separate evidence from resemblance. For this scenario, an illustrative operator confirmation links the two source IDs to one business. The name similarity only proposed the pair; it did not authorize the merge.
- Record the decision. Merge into entity-A, retaining both names and source IDs as aliases. The active/inactive observations remain in the decision record; merging identity does not automatically settle operating status or identify which observation is current.
- Update downstream references. Both source IDs point to entity-A. Keep the evidence and before-state so an incorrect merge can be reversed.
Keep separate
Invented transporter names, IDs and review outcomes. Similar names nominate candidates; they never establish identity.
- Read the source records. The dispatch sheet has Mango Road Transport, an example registry ID and active operating status. The CRM has Mango Rd. Transport, no registry ID and inactive operating status. Preserve both source rows. Missing identity evidence and contradictory status observations are separate problems.
- Separate evidence from resemblance. For this scenario, an illustrative operator check identifies different businesses. Their similar names remain searchable without collapsing their identities.
- Record the decision. Keep entity-A and entity-B separate. Record the negative match so the same resemblance does not keep producing the same suggestion.
- Update downstream references. The sheet points to entity-A; the CRM row points to entity-B. Each business keeps its own history.
Tune resemblance. Keep identity accountable.
Adjust the candidate threshold, inspect each source record, and add one explicit piece of identity evidence.
Each bar shows name similarity; its fine marker is your review threshold. Solid graph paths are supported identity links, dashed paths need review, and crossed paths have conflicting registry IDs. Below-threshold records have no path. Select a numbered record to read its evidence.
- Registry
- Missing. Cannot establish an identity match.
- Operating status
- unknown. No status observation; identity confirmation does not fill this gap.
- Decision
- review
- Reason
- No registry ID or operating-status observation. A similar name proposes a review; it does not prove identity.
Invented records and decisions, not project outcomes. Name score = multiset Sørensen–Dice similarity of character pairs after lowercasing, punctuation removal, and “rd” → “road”. Verified IDs take precedence over this review threshold. Identity merges preserve separate status claims; status prediction cannot prove two businesses are the same.
The cleanup
- Thorough EDA before touching anything. You cannot fix data you haven't actually looked at, and the distributions surprised me more than once.
- Fuzzy matching to resolve name variants, with thresholds tuned by reading the borderline cases myself rather than picking a similarity score that felt right.
- Duplicate analysis to collapse the multi-entity transporters back into single records.
- An XGBoost-ensemble classifier to predict transporter status where ground truth was missing. Useful, but I want to be precise about its place: the model existed to serve the cleanup. The cleanup was the product.
I never published an accuracy headline for this work. The recorded outcome was qualitative: downstream systems could use the transporter table without repeatedly resolving the same identity conflicts.
Value that compounds
The demand pipeline became more trustworthy without a change to its own code because it read the same corrected transporter table. The cleanup affected each report and workflow that depended on those canonical records.
Model changes could not repair joins against duplicated companies or missing identifiers. We had to correct the reference layer first, then evaluate the classifier and downstream pipeline on top of it.
Before adding a more capable model to a data system, I now inspect the identifiers and reference tables it will depend on. Errors there survive model upgrades and propagate into every feature that reads them.