Recurring CSV imports rarely fail because the file is unreadable. The harder problem is deciding whether a row from the new file represents an existing entity when column names, formatting, identifiers, and data quality vary. This checklist provides a repeatable workflow for schema mapping, normalization, candidate generation, fuzzy matching, exception review, and import sign-off. Use it before monthly uploads, vendor migrations, or reconciliation work so safe updates are separated from records that require investigation.
Overview
Matching records across CSV files is an entity resolution problem, not simply a spreadsheet lookup. A source file may call a customer field client_name, while the destination expects full_name. One system may store an address in a single column; another may split it into street, unit, city, and postal code. Phone numbers may include extensions, names may change after an update, and an external identifier may be missing from only some rows.
A reliable record linkage workflow separates five decisions:
- Schema mapping: Which source columns correspond to which destination fields?
- Normalization: How should values be standardized before comparison?
- Candidate generation: Which existing records are plausible matches?
- Scoring and review: How much evidence is enough to accept, reject, or investigate a match?
- Import control: Which changes can be applied safely, and how will they be audited or reversed?
Do not begin by applying one fuzzy threshold to every column. A one-character difference in a product code can be more important than a larger difference in a person’s name, while an address may legitimately vary because of abbreviations or unit formatting. Match decisions should account for field meaning, data quality, and the consequences of a false positive.
For a broader merge-and-review model, see Entity Resolution Workflow: Match, Review, and Merge Duplicate Records Safely. If addresses are central to the import, the guide to fuzzy address matching covers buildings, units, and postal addresses in more detail.
Checklist by scenario
Scenario 1: A recurring file from the same system
Start by comparing this file with the previous successful import, not just with the destination schema.
- Record the file name, arrival date, source system, delimiter, encoding, and expected row count.
- Confirm whether the file is a full snapshot, an incremental export, or a correction file.
- Compare headers and flag renamed, added, removed, or reordered columns.
- Check whether identifiers remain stable and unique. Never assume a column is a reliable key because it is named
id. - Measure missing values, duplicate source rows, and unexpected changes in data types or formatting.
- Preserve the original file and create a run identifier for the import.
Create a field-mapping table before matching:
| Source field | Destination field | Comparison rule | Required? | Notes |
|---|---|---|---|---|
| customer_name | full_name | Casefold, trim, compare tokens | Yes | Keep original value for display |
| phone_number | phone | Remove formatting; preserve country context | No | Do not compare extensions as the main number |
| postal_address | address | Normalize abbreviations and components | No | Review unit conflicts |
| account_ref | external_id | Exact match only when validated | Preferred | Check uniqueness first |
Scenario 2: Column names or layouts changed
Separate schema mapping from entity matching. First determine what each column means. A column containing a display label should not be treated as a unique identifier, and a combined name field should not be compared directly with a surname field.
- Inspect representative values, not only header names.
- Document transformations such as splitting a full name or combining address components.
- Mark ambiguous fields as unresolved instead of guessing.
- Run validation rules after mapping: dates should parse as dates, postal codes should fit the expected format, and identifiers should meet expected length or character rules.
Scenario 3: The source has no dependable identifier
Use a two-stage process. First generate candidates with inexpensive blocking keys, such as normalized email, phone suffix, postal code plus surname initial, or a shared address token. Then compare candidates using several fields.
Useful comparison signals can include exact or near-exact name similarity, address similarity, phone agreement, email agreement, organization, and date-of-birth or account metadata where the use is appropriate and permitted. Missing fields should contribute no evidence; they should not be treated as disagreement.
Algorithms such as Levenshtein distance, Jaro-Winkler, and trigram similarity can support field-level comparisons, but the final decision should be based on the combination of signals and the risk of an incorrect merge.
Scenario 4: A vendor migration or one-time backfill
Expect more exceptions than in a recurring export. Build a sample set containing obvious matches, obvious non-matches, and difficult cases before processing the full file. Use that set to test normalization, candidate rules, and confidence bands.
- Auto-accept: Strong evidence, no conflicting high-value fields, and a clear destination record.
- Review: Plausible match with missing, conflicting, or weak evidence.
- Reject or create: No suitable candidate, or evidence indicates a genuinely new entity.
Keep the original source row, selected candidate, field-level scores, decision, reviewer, and timestamp. This makes exception review reproducible instead of turning it into an undocumented spreadsheet exercise.
What to double-check
Normalization before similarity
Use a documented normalization pipeline and retain both raw and normalized values. Common steps include trimming whitespace, case folding, Unicode normalization, standardizing punctuation, converting known abbreviations, and separating phone extensions. Apply language-appropriate rules for names and addresses; aggressive transliteration or token removal can erase useful distinctions.
Normalization should reduce irrelevant variation, not make different entities look identical. For example, removing apartment or unit information may improve recall but can create dangerous address matches in multi-unit buildings. Address-specific guidance is available in Fuzzy Address Matching: How to Match Buildings, Units, and Postal Addresses Safely.
Conflicting evidence
A high name similarity does not override a conflicting unique identifier. Likewise, an exact email match may be unsafe if the source field is shared, recycled, or poorly validated. Define conflict rules explicitly. A practical model is to assign each field a reliability level, calculate field-level similarities, and then apply blocking and veto rules for critical conflicts.
Thresholds and review capacity
Thresholds are operating decisions, not universal constants. A lower threshold may find more true matches but create more manual work and false positives. A higher threshold may protect against incorrect merges while leaving more records unresolved. Test thresholds on labeled examples and inspect the records near each boundary. The fuzzy matching thresholds guide provides a framework for tuning and validating these bands.
Import behavior
Confirm whether a match will update an existing record, append a new record, or do nothing. Check whether blank source values overwrite populated destination values. Define how deletions, changed identifiers, duplicate source rows, and records with multiple plausible candidates are handled. Preview the resulting changes before committing them.
Common mistakes
- Matching on one noisy field: A similar name alone is rarely sufficient for safe entity matching.
- Using fuzzy matching for identifiers: Product codes, account numbers, and regulated identifiers generally need exact or tightly constrained comparison. See Fuzzy Search for Product Codes and SKUs for typo-handling considerations.
- Comparing unnormalized values: Punctuation, casing, whitespace, and formatting differences can hide useful similarity.
- Ignoring one-to-many matches: If several destination records fit one source row, automatically choosing the top score can create a silent data error.
- Overwriting with blanks: Treat missing input as missing evidence unless the source explicitly represents deletion.
- Skipping a review queue: Ambiguous records are not failures. They are a normal output of responsible record linkage.
- Failing to preserve evidence: Without raw rows, normalized values, scores, and decisions, it is difficult to explain or undo an import.
- Optimizing only for match rate: A high number of accepted matches is not useful if it includes incorrect merges. Evaluate precision, coverage, and review volume together using a representative test set.
If your workflow combines keyword and meaning-based retrieval rather than structured record linkage, first clarify whether you need fuzzy, semantic, or hybrid search. The comparison in Fuzzy Search vs Semantic Search vs Hybrid Search can help keep those use cases separate.
When to revisit
Revisit the workflow before each seasonal planning cycle, recurring import, or major reconciliation. It also needs review when a source system changes its export, a vendor introduces new identifiers, a country or language is added, or the destination schema changes.
Use this practical sign-off checklist at the end of every run:
- Confirm the file type, encoding, delimiter, row count, and import mode.
- Approve the schema mapping and record any changes from the prior run.
- Run normalization and inspect representative raw-versus-normalized values.
- Check identifier uniqueness, duplicate source rows, and required-field completeness.
- Generate candidates using documented blocking rules.
- Review accepted, ambiguous, rejected, and new-record bands separately.
- Investigate high-impact conflicts and all one-to-many matches.
- Preview updates, especially blank overwrites and identifier changes.
- Record the run ID, ruleset version, reviewer decisions, and final counts.
- Confirm that the import can be rolled back or corrected from the preserved source and audit data.
Update the rules when exception patterns change, not merely when a file fails. A new abbreviation, identifier format, language, or vendor layout can alter the balance between exact matching, fuzzy matching, and manual review. Treat every import as a measured data-quality process: preserve evidence, make uncertainty visible, and change thresholds only after reviewing real examples.
