Finding Duplicate Records with Machine Learning

Data Quality / Machine Learning / Master Data Management

Why Traditional Corporate Data Validation Rules Are No Longer Sufficient

The quality of corporate information directly impacts the effectiveness of management decisions. Enterprises use ERP for financial operations, CRM for customer interaction, electronic document management systems for contract work, and corporate data warehouses for analytics.

All these systems depend on the accuracy of information. An incorrect product code can lead to an inaccurate display of inventory. A duplicate counterparty can result in incorrect procurement consolidation. An incorrectly defined currency can distort financial metrics.

For many years, enterprises have used traditional Data Quality mechanisms: mandatory field checks, format control, reference data constraints, exact duplicate detection, and conformity rules. For example, a system can check if a counterparty code is filled in, if a date conforms to the established format, or if a product exists in the corporate reference data.

Such checks remain necessary.

However, they have limitations.

Let’s imagine that the CRM stores the following records:

• Alfa Trade LLC
• Alfa-Trade, Limited Liability Company
• Alfa Trade LLC
• Alfa Trade, Kyiv

Do they describe the same legal entity? Simple string comparison will not provide a reliable answer. Even after normalizing names, ambiguities will remain.

Similar difficulties arise when searching for atypical financial transactions, identifying incorrect units of measurement, and evaluating the reliability of new records.

For such tasks, enterprises can use Machine Learning methods, which allow identifying patterns based on historical examples and using them to evaluate new data.

However, machine learning should not completely replace formal quality rules.

The most promising approach is to combine deterministic checks with mathematical models that help find complex and ambiguous cases.

Data Quality as a Continuous Process

One of the common mistakes is considering data quality solely as a cleaning task before migration.

For example, a company is preparing to migrate to a new ERP. The IT team identifies duplicates, corrects directories, and uploads cleaned records.

However, after a few months, incorrect values reappear in the new system. The reason is that the problem was only addressed at the level of the existing dataset, without changing the process of its creation.

Therefore, Data Quality must be an integral part of ongoing corporate information management.

This requires defining quality requirements, responsible parties, verification procedures, and correction protocols.

Key Quality Characteristics

Mathematical models can help assess some of these characteristics. However, not all requirements can be reliably derived from historical data.

For example, the correctness of an identification number should be checked according to established rules, and not solely through statistical prediction.

From traditional Matching to machine learning

Searching for duplicate records is one of the most practical applications of machine learning in Data Quality.

Traditional MDM uses deterministic and probabilistic matching methods.

Deterministic matching

For example, two records have the same verified legal entity registration code.

Under the appropriate conditions, this can be sufficient grounds to confirm their identity.

However, even in such cases, it is necessary to consider the country, object type, and quality of the initial requisites.

Fuzzy Matching

For names, addresses, and contact details, similarity comparison methods are used. For example, Levenshtein distance allows determining the number of editing operations between two strings.

Jaro-Winkler is focused on evaluating the similarity of text values and can be useful for names and titles. Tokenization, abbreviation normalization, and phonetic algorithms can also be applied.

However, no single metric guarantees correct identification.

For example, the names of two different companies can be almost identical.

Machine Learning

Instead of one fixed rule, a set of characteristics for a pair of records can be formulated. This may include similarity of names, matching of addresses, registration codes, phone numbers, and other attributes.

After that, the classification model estimates the probability that the records describe the same object.

The advantage lies in the ability to consider more complex combinations of characteristics.

However, the model requires high-quality training examples with a confirmed matching result.

How to build a duplicate search model

Consider a conditional corporate registry of counterparties that contains records from ERP and CRM.

The company wants to automatically find possible duplicates.

First stage: normalization

Names are converted to a single case. Normalization of spaces, punctuation, and common legal abbreviations is performed.

For example, “LLC” and “Limited Liability Company” can be mapped using a corresponding dictionary.

However, not all legal designations can be indiscriminately removed.

In some cases, they help distinguish between different organizations.

Stage Two: Candidate Generation

A full comparison of each record with all others creates a significant load.

If the database contains a million counterparties, the number of possible unordered pairs is almost 500 billion.

Therefore, Blocking is used – a preliminary selection of potentially similar records. For example, candidates can be grouped by part of the name, region, or other characteristics.

It is important not to create blocks that are too narrow, otherwise some true duplicates will not be compared.

Stage Three: Feature Engineering

Numerical characteristics are calculated for each pair.

For example:

These values are used as input features for the model.

Stage Four: Training

Logistic regression, decision trees, or Random Forest can be used for training.

The choice depends on the number of examples, the nature of the features, and the explainability requirements.

Stage Five: Verification

Results are evaluated on an independent set.

Particular attention should be paid to errors where different legal entities are erroneously merged.

For financial directories, such errors can be significantly more costly than missed duplicates.

Stage Six: Application

The model can distribute pairs into three categories:

• High confidence of a match;
• Ambiguous case for the Data Steward;
• Low probability of a match.

In this case, thresholds should be determined based on testing results and in accordance with business consequences.

Why Precision and Recall are More Important than Overall Accuracy

For a duplicate search task, overall classification accuracy can be misleading.

For example, in a large corporate registry, the vast majority of random pairs belong to different organizations.

A model that almost always predicts ’not a duplicate’ can show very high Accuracy. However, its practical utility will be minimal.

Therefore, it is advisable to use Precision and Recall. Precision shows what proportion of pairs identified as duplicates are truly duplicates.

Recall characterizes what proportion of actual duplicates have been detected. For corporate MDM, the correct balance of these metrics is particularly important.

For instance, if automatic merging can impact contracts and financial history, an enterprise might demand very high Precision. Instead, to form a list of potential duplicates for manual verification, it is advisable to aim for higher Recall.

It is important that the test set reflects real-world difficulties, rather than consisting mainly of obviously different records.

scikit-learn 0.19: Anomaly Detection Tools

scikit-learn 0.19 was released on August 12, 2017. Among the innovations are LocalOutlierFactor for anomaly detection, QuantileTransformer, and extended model evaluation capabilities. A corrective version, 0.19.1, was already available in October.

Local Outlier Factor (LOF) is particularly interesting for Data Quality. The algorithm estimates how much an object differs in local density from neighboring records.

For example, a company can analyze procurement records by transaction amount, quantity of goods, and other normalized characteristics. If a specific record significantly differs from other similar operations, the algorithm can flag it as a potential anomaly.

However, an anomaly does not necessarily mean an error. A large purchase may be related to a real production project.

Therefore, such results should be used as a signal for verification.

Python Example

For a conditional numerical set, you can apply:

from sklearn.neighbors import LocalOutlierFactor
from sklearn.preprocessing import StandardScaler

# X contains the prepared numeric features.
X_scaled = StandardScaler().fit_transform(X)
model = LocalOutlierFactor(n_neighbors=20, contamination=0.02)
labels = model.fit_predict(X_scaled)

This example is compatible with the main scikit-learn API 0.19. It demonstrates anomaly detection within the analyzed dataset.

It should be noted that in this version, LocalOutlierFactor should not be described as a universal ready-made tool for evaluating arbitrary new records via the novelty=True API later. A different algorithm or specially organized retraining may be required for real-time checking of new operations.

Also, the contamination=0.02 value in the example is a conditional parameter, not a statement that 2% of corporate records necessarily contain errors.

What errors can be searched for using statistical methods

Machine learning can be useful for detecting various categories of atypical records.

Unit of measurement errors

For example, most purchases of a certain material are recorded in kilograms, but a single transaction contains an anomalously large value. The reason could be the incorrect use of grams instead of kilograms.

However, the model only identifies atypicality. The correct value needs to be confirmed with primary documents.

Price Discrepancy

For a product, typical price ranges can be analyzed considering the supplier, currency, and date. If a new record differs significantly, it may require verification.

However, the price should not be automatically corrected to the average value. The deviation may be a result of a real discount or a market change.

Incorrect Product Classification

The model can assess the consistency between the product description, its technical specifications, and its category.

For example, equipment with a certain set of features might be mistakenly classified as consumables.

Atypical Attribute Combinations

Sometimes each individual field is valid, but their combination appears contradictory. For example, a certain contract type is used with unusual payment terms.

The model can flag a case for the responsible department. However, the final verification should be based on business rules and documents.

Apache Spark MLlib for Large Enterprise Registries

Not all Data Quality tasks can be effectively performed on a single server. For example, a large group of enterprises may have tens of millions of customer and product records.

Distributed computing can be used for such information. Apache Spark 2.2.0 was released on July 11, 2017. As of November, it remains the current 2.2 branch: the corrective Spark 2.2.1 will only appear on December 1st.

Spark MLlib includes classification, regression, and clustering algorithms, as well as other machine learning tools. DataFrame API and ML Pipelines can be used to build procedures.

Distributed Feature Engineering

For example, an enterprise might normalize product characteristics and generate candidates for matching.

After this, numerical features are calculated.

Model Training

Training can be performed using cluster resources for supported algorithms.

Limitations

Spark does not eliminate the complexity of the duplicate detection task. If each record needs to be compared with every other record, the number of pairs grows quadratically.

Therefore, blocking and other methods for reducing the number of candidates remain necessary. Additionally, not every algorithm available in scikit-learn has a direct equivalent in MLlib.

Therefore, the choice of platform should be determined by the specific task.

TensorFlow 1.4 and Prospects for Complex Models

On November 7, 2017, Google announced the release of TensorFlow 1.4. One of the innovations was the inclusion of Keras in the main package tf.keras, which simplifies the construction of neural network models.

For corporate data, this can be interesting in tasks involving the analysis of text descriptions, complex document characteristics, and other cases where traditional algorithms have limitations. For example, a neural model can explore the similarity of product text descriptions.

However, the presence of TensorFlow does not mean that deep learning is advisable for every Data Quality task. For small tabular datasets, logistic regression, Random Forest, or deterministic rules may be simpler, more transparent, and sufficiently effective.

It is also important to consider the need for training examples and computational resources. Therefore, complex neural models should only be evaluated after establishing a real business advantage over simpler approaches.

Informatica and the Development of Intelligent Data Governance

In 2017, enterprise platform vendors are expanding the use of machine learning in the field of Data Management. In particular, Informatica is developing the CLAIRE concept – metadata and intelligent assistance when working with corporate information resources.

This direction demonstrates the prospect of automating part of the procedures for analysis, matching, and data preparation. However, it is important not to equate the marketing concept of intelligent automation with a fully autonomous system capable of determining the correctness of all corporate records without specialist involvement.

For MDM and Data Quality, approved rules, Data Stewardship, and results control remain necessary. Therefore, intelligent capabilities should be viewed as tools to enhance the efficiency of responsible specialists.

How to Integrate a Model into the Data Quality Process

A separate analytical experiment does not ensure continuous improvement of corporate data. It is necessary to integrate its results into the regular process of creating and verifying records.

For example, during the registration of a new counterparty, the system can perform several stages.

• New record from ERP or CRM
• Verification of mandatory attributes
• Normalization and Candidate Search
• ML Model Similarity Assessment
• Automated Decision or Data Steward
• Corporate Record Approval
• Distribution via MDM and Integrations

Conceptual procedure for intelligent verification of a new counterparty. Automatic merging is permissible only under approved rules and verified model quality.

It is important that the model’s output is not the sole basis for potentially dangerous changes.

For example, automatically merging two counterparties can affect legal documents and financial history.

Therefore, manual verification is required for ambiguous cases.

It is also necessary to store information about the decision.

If the model suggested a merge and the Data Steward rejected it, the corresponding result can be a useful example for further training.

Feedback Loop: Learning from Data Steward Decisions

One promising possibility is to use the results of manual verification to improve the model.

• For example, the model identifies one hundred potential duplicate pairs.
• The Data Steward confirms some and rejects the rest.
• These decisions are stored as training examples.

After accumulating a sufficient number of verified records, the model can be retrained.

However, the quality of the decisions themselves must be considered.

If different specialists use different rules, the training set will contain contradictions.

Therefore, for complex cases, it is advisable to use a standardized verification procedure and the possibility of review.

It is also important not to evaluate the new model solely on the same records on which it was trained.

An independent test set reflecting real-world enterprise scenarios is required.

Why automatic correction is more dangerous than automatic detection

It is necessary to distinguish between two fundamentally different tasks.

The first is to detect a potential error.

The second is to modify an enterprise record.

For the first, machine learning can be useful even with a certain percentage of false positives.

For example, the model generates a list of suspicious product prices for verification.

For the second, significantly higher confidence is required.

If the system automatically changes an incorrect product code, it can affect orders, inventory levels, and financial transactions.

Therefore, it is advisable for the enterprise to implement automation levels.

• For simple formal errors, deterministic rules can be used.
• For complex ambiguous cases — model recommendations.
• For legally or financially significant changes — appropriate approval.

This allows reducing the amount of manual work without uncontrolled transfer of responsibility to a mathematical algorithm.

Practical Scenario: Supplier Register Cleanup

Let’s consider a hypothetical holding company with four subsidiaries.

Each has a separate ERP and its own supplier directory.

During consolidation, it is revealed that one organization may have different names and local codes.

Stage 1. Data Profiling

The team analyzes the directory structure, the number of missing entries, and duplicate identifiers.

Stage 2. Normalization

Names, addresses, and registration details are converted to defined formats.

Stage 3. Blocking

Groups of potentially similar suppliers are formed.

Stage 4. Model Building

A matching classifier is trained based on confirmed examples.

Stage 5. Independent Testing

Precision, Recall, and false positives (erroneous merging of different legal entities) are evaluated.

Stage 6. Data Steward Verification

Ambiguous pairs are transferred to responsible specialists.

Stage 7. Golden Record

For confirmed matches, MDM generates a unified corporate identifier.

Stage 8. Integration with DWH

The analytical warehouse uses consistent keys to consolidate purchases.

Stage 9. Monitoring

The enterprise monitors the emergence of new duplicates and changes in model quality.

The result should be not just a cleaned dataset, but a continuous process of maintaining information consistency.

Data Governance and Responsibility for Intelligent Checks

The use of machine learning does not negate organizational responsibility.

• Data Owner defines quality rules and acceptable data modification methods.
• Data Steward reviews complex cases and confirms the correctness of decisions.
• Data Scientist develops and evaluates the model.
• Data Architect ensures its integration with MDM and corporate information flows.
• The IT department is responsible for reliable execution, security, and logging.

It is important to maintain versions of models and the rules by which decisions were made.

For example, if two counterparties were automatically matched, the enterprise must be able to determine which model and which characteristics were used.

It is also necessary to define a procedure for correcting erroneous merges.

Especially if the corresponding identifiers have already been propagated to several corporate systems.

Intecracy Group Competencies in Intelligent Data Quality

Applying Machine Learning to improve data quality requires a combination of specialized competencies.

Data Management IG specializes in ETL, MDM, Data Quality, DWH, and corporate analytics. These areas are directly related to data profiling, duplicate detection, Golden Record formation, and model result monitoring.

IQusion IT operates in the fields of system integration, electronic document management, and BPM. Such competencies are important for incorporating intelligent checks into the processes of creating and approving corporate records.

SL Global Service specializes in cloud integration, virtualization, and hybrid infrastructure. These areas can be used to host computing resources and combine on-premises and cloud processing environments.

At the same time, quality rules and acceptable automation levels must be approved by the enterprise.

Practical Guidelines for Implementation

Machine learning creates new opportunities for the development of corporate Data Quality.

Classification algorithms can help detect duplicates, and anomaly detection methods can find atypical records and potential information errors.

scikit-learn 0.19, Apache Spark MLlib, and TensorFlow 1.4 provide various technological tools for such tasks.

However, a statistical model is not a universal source of truth.

It requires quality training examples, independent verification, and result control.

It is especially important to distinguish between automatic detection of potential errors and automatic correction of corporate information.

For critical records, Data Stewardship and approved MDM rules must be maintained.

The future of Data Quality lies not in replacing corporate rules with machine learning, but in combining them: formal checks provide defined guarantees, and statistical models help find complex errors that are difficult to describe with ordinary conditions.