Handling Inconsistent Data in Legacy Systems

Establish the live position before planning change

Inconsistent data is not the same as invalid data. Validation checks whether a value fits a rule — a postcode in the right format, a date that actually exists. Inconsistency is subtler: the data may pass every validation rule yet still contradict other records, use different formats for the same thing, or carry outdated information that no longer reflects reality.

Legacy systems accumulate inconsistency over years. Staff enter the same customer name in different ways. A company restructures and product codes change format, but old orders keep the original codes. Two departments maintain separate records for the same client, and neither is authoritative. Fields that were optional when the system was built become essential later, leaving historical records with gaps that the new system cannot tolerate.

The practical problem arises at migration. If you move inconsistent data into a new system, the results are predictable: duplicate customer accounts, reports that do not reconcile, and staff who lose trust in the platform within the first week. The question is not whether to address inconsistency, but how to categorise it, decide what matters, and resolve it without halting the migration.

Common patterns of inconsistency

• Format variation: "Ltd", "Limited", and "LTD" in the same company-name field; phone numbers with and without international prefixes; dates in mixed UK and US formats.
• Duplication without exact match: Two customer records for the same organisation with slightly different addresses, created by different teams at different times.
• Conflicting values across tables: A client's primary contact name differs between the accounts table and the orders table.
• Orphaned references: An order line that points to a product code deleted from the product table years ago.
• Historical drift: A supplier's VAT number was correct when first entered but has since changed; the old number remains on historic invoices while current records show the new one.

Recognising these patterns early shapes the entire cleaning strategy. Some inconsistencies are cosmetic and can be normalised automatically. Others require a business decision about which record is correct.

The first step is profiling: running queries against the legacy database to measure the scale and shape of inconsistency. This is not a full validation exercise — that is a separate stage — but a targeted investigation of the specific problems you already suspect. How many distinct formats appear in the company-name field? What proportion of order records reference product codes that no longer exist in the product table? How many clients appear more than once, matched on postcode or partial name?

Profiling produces a count and a sample. The count tells you whether the problem affects five records or five thousand. The sample lets you inspect real examples and decide which category each type of inconsistency falls into.

Categorising inconsistencies for action

Not every inconsistency needs the same treatment. A practical approach sorts them into three groups:

• Auto-correctable: Format differences that can be normalised with rules — trimming whitespace, standardising title case, converting date formats, appending missing country codes to phone numbers. These are low-risk and high-volume.
• Requires business decision: Duplicate customer records where it is unclear which one is the master. Conflicting addresses where the correct one depends on context. These need a person who understands the business relationship, not just the data structure.
• Acceptable as-is or discardable: Historical product codes on closed orders that will never be re-ordered. Very old records that fall outside the retention policy. These can be migrated unchanged or excluded, depending on regulatory and operational requirements.

Resolving conflicts with business rules

When two records contradict each other, a technical team cannot decide which one is right. The business must supply rules. For customer duplicates, a common approach is to nominate a "master" source — for example, the CRM is authoritative over the billing system, or the most recently modified record wins. These rules need to be documented and agreed before cleaning starts, not invented on the spot during a migration weekend.

For historical data where the "correct" value has changed over time, the decision is often about whether to preserve history accurately or overwrite it to match current state. Financial records usually demand preservation: an invoice must show the VAT number that applied at the time, not the current one. Contact details, by contrast, are often updated to the latest known value.

The role of data stewards

Staff who work with the legacy system daily can resolve ambiguities that no automated process can. A data steward in the credit-control team will know that "ABC Ltd" and "A.B.C. Limited" are the same debtor. A product manager will know which old product codes map to current equivalents. Involving these people early — even for short review sessions on sampled records — prevents large numbers of incorrect automated merges.

Assuming the new system will fix the data

A new application can enforce better rules going forward, but it will not repair what it receives. If you migrate a duplicated customer list into a well-structured CRM, you get a well-structured CRM full of duplicated customers. Cleaning must happen before or during migration, not after.

Cleaning without documented decisions

When a team merges or discards records without recording why, two problems follow. First, there is no way to answer a future query about what happened to a specific record. Second, there is no audit trail for compliance purposes. Every batch of changes should have a logged rule: "Records merged where postcode and company name matched after normalisation, with the most recently modified record retained as master."

Underestimating manual review

Auto-correction handles the easy cases. The remaining ambiguous records are often the ones that take the most time, because each one may require looking at order history, correspondence, or asking a colleague. If profiling shows that 15% of customer records fall into the "requires business decision" category, the effort to review those records should be estimated and resourced explicitly, not treated as a minor task.

Not reconciling after cleaning

After a cleaning pass, run the same profiling queries again. The counts should change in the expected direction. If the duplicate count has not moved, the rules were too conservative. If large numbers of records have disappeared, the rules may have been too aggressive. Reconciliation does not guarantee perfection, but it catches obvious errors before the data reaches the new system.

Checks to complete before proceeding

• Have all inconsistency types been profiled with counts and samples?
• Has each type been assigned to an action category — auto-correct, business review, or accept/discard?
• Are the business rules for conflict resolution documented and agreed by the relevant team leads?
• Have data stewards reviewed a representative sample of the ambiguous records?
• Has a reconciliation pass confirmed that cleaning produced the expected change in counts?
• Is there an audit log of what was changed, merged, or excluded, and the rule applied in each case?

Limitations to accept

Complete consistency is an unrealistic goal for a large legacy dataset. The practical aim is to reduce inconsistency to a level where the new system can operate reliably and where remaining issues can be managed through normal business processes. Chasing the last 1% of edge cases often costs more than the value it delivers. Agreeing a threshold with the business — and documenting what known issues remain — is more honest and more manageable than promising a perfectly clean dataset.