Take a sports federation that undertakes the deduplication of its members’ data: the file contains 20,000 records, entered each season by the clubs. The same person may appear in it several times: registered with two successive clubs, recorded under the name they use day to day and then under their birth name, or entered with a typing error.
Deduplicating consists of identifying the records that refer to the same entity, deciding which to merge, then preventing duplicates from coming back. We propose carrying out the operation in this order: set the scope and the matching rule, normalise the values, detect candidate pairs, have doubtful cases reviewed, merge according to a survivorship rule, prevent at data entry. The risk to keep in view is the merging in error of two distinct entities, which is hard to undo if the original records have not been kept.
Data deduplication starts with a scope and a matching rule
The scope says which entity is being deduplicated, in which file and for which uses. In the starting scenario, the entity is the person: two licences taken out in the same season in two disciplines remain two licences, attached to a single record.
The matching rule says under what conditions two records refer to the same entity. It separates the attributes that identify (a national licence number if one exists, failing that the surname, first name and date of birth taken together) from those that support without proving, because they change: address, email, club. It lists the edge cases in advance: namesakes born on the same day, twins at the same address, change of name.
This rule is a business decision. It falls to the data owner, a role described on our data governance page, and is written down before any processing.
When the records describe natural persons, the GDPR, Regulation (EU) 2016/679, is to be reread: its Article 4(2) cites “alignment or combination” among the operations that constitute processing. We advise involving the data protection officer from this stage, if the organisation has appointed one.
When the same entities are created in several applications, the subject becomes that of master data management, described in our entry; this article confines itself to a single file.
Normalise values before comparing them
Two values identical to a reader may differ for a machine: capital letters, accents, spaces, punctuation, order of surname and first name, abbreviations, date or phone number formats. Normalisation brings them to a common form.
The documentation of the OpenRefine tool describes its “fingerprint” method as follows: removal of leading and trailing spaces, conversion to lower case, removal of punctuation, conversion of accented characters, sorting of words. “Cruise, Tom” and “Tom Cruise” then receive the same key. The same text points out the trade-off: converting accents can bring together two distinct names.
The original value is kept in a separate column, with the normalised form used only for comparison. Placeholder values, such as a default date of birth or the entry “UNKNOWN”, are neutralised before comparison, failing which they match one another.
Detection combines exact matching, fuzzy matching and blocking
Exact matching compares a reliable identifier or a normalised key. It carries little exposure to false matches and misses variants: two transposed letters separate two records.
Fuzzy matching tolerates a gap between similar strings. It relies on the number of edits separating two strings, or on a phonetic key that brings together spellings pronounced in the same way. The tolerance is adjustable: too wide, it brings together distinct people.
Comparing each record with all the others quickly becomes impractical. For n records, the number of pairs is n × (n - 1) / 2, that is 199,990,000 pairs for 20,000 records. Blocking keys limit comparisons to records that share a value, for example the year of birth or the first letters of the surname. Several keys are applied in succession, so that an error on one does not hide a pair.
Finally, a similarity score combines the comparisons, attribute by attribute. Two thresholds divide it into three zones: duplicates considered certain, cases to examine, distinct records. These thresholds are a convention specific to each file, tuned on a sample of pairs judged by hand.
According to its documentation, OpenRefine offers key-based methods and distance-based methods, with blocking. The Python library Splink presents itself, in its own documentation, as a tool for probabilistic record linkage without a unique identifier, in which blocking rules reduce the number of pairs compared. These two examples of tools do not amount to a recommendation.
Doubtful cases go to a designated person
In the intermediate zone, each pair receives the decision of a person who knows the data: the file’s data steward, or the contact at the club that entered the record. This decision takes one of three forms: same person, distinct people, undetermined for lack of evidence.
The two possible errors do not have the same cost. A duplicate left in place can be counted and dealt with later. A merge in error mixes the history of two people, and one of them loses their record. The thresholds are therefore set on the side of caution, by decision of the owner.
Each decision is recorded: a pair judged distinct goes into an exclusion list, so as not to come back at the next detection run. If doubtful cases exceed what the reviewers can handle, the rule is reworked, by adding an attribute or a key.
Merging follows a survivorship rule and keeps a trace
The survivorship rule, also set by the owner, says which value the retained record keeps for each attribute: the most recent for an address, the one from the source judged reliable for a date of birth, the filled-in value when the other is empty.
Merging then attaches to the retained record everything that pointed to the absorbed records: licences, results, payments. A mapping table links old and new identifiers, for extracts and downstream systems. The original records are archived for a period set in advance, so that a merge in error can be undone. A trial on a copy, counting the attached objects before and after, precedes the real operation.
For personal data, Article 5(1)(d) of the GDPR requires data that are “accurate and, where necessary, kept up to date”. We draw a reading from this, distinct from the text: a survivorship rule that keeps an outdated address, or a merge in error, leaves inaccurate data in the file. The archiving period for absorbed records and the classification of the operation are a matter for the organisation, with its data protection officer.
Preventing duplicates from forming again at data entry
Duplicates may come back if data entry remains unchanged. Prevention reapplies the matching rule at the moment of creation: the search for similar records runs before saving, with the same normalisation, and suggests the existing records. The operator can override it for a namesake, stating the reason. File imports go through the same check.
This lever belongs to the source application: our guide to improving data quality lists the duplicate search among the data entry rules. Our data quality page describes the framework: rules, checks and, when several systems create the same entities, master data management.
The effect is measured on records created after implementation, by a duplicate rate calculated separately from the existing stock, according to the conventions of our article on data quality metrics.