Systems and records
Dataset uniqueness audit: duplicate records or legitimate repeat events?
By SourceX Editorial · Updated
10 sections
Short answer
A duplicate is one real-world event recorded more than once; a legitimate repeat is a second event that really happened. A dataset uniqueness audit tells them apart by comparing source identifiers, timestamps, actors and outcomes, not whole rows, then classing each candidate pair before anything is removed. Delete only proven duplicates, keep repeats with a link to the earlier event, and report counts per class.
Checklist
Dataset uniqueness audit
Key takeaways
- Decide what counts as one event before comparing anything; the comparison key decides the answer.
- Exports, retries, migrations and re-entry create duplicates; service callbacks, recurring charges and reopened cases are real repeats.
- Class every candidate pair, and keep an unresolved class rather than forcing a guess.
- Deleting real repeats is as damaging as keeping duplicates, because repeats are often the signal a buyer wants.
- Duplicates across training and evaluation splits can inflate results even when every file opens.
- Report the rate per class with the method, so a buyer can accept the delivery on evidence.
How do you tell a duplicate record from a legitimate repeat event?
Ask whether two records describe the same occurrence or two occurrences. If the source system's event ID, the time, the actor and the outcome all point to one occurrence, it is a duplicate. If any of them shows a second occurrence, such as a new work order, a later timestamp from a different person or a different outcome, it is a repeat and should stay.
Row equality is a poor test in both directions. Two rows of one payment can differ because an export added a load timestamp, so a full-row comparison misses the duplicate. Two genuinely separate monthly charges for the same customer and amount can be identical apart from a date, so a comparison that ignores the date deletes real history.
A uniqueness audit therefore starts with a definition, not a script: for each record type, write down what makes one event one event in this business. Then compare candidates on those fields, classify each pair and remove only the classes you can prove.
Where do duplicates come from in operational exports?
Many duplicates in business records are produced by the plumbing, not by the business: retries, overlapping exports, migrations and people entering the same thing twice. Knowing the mechanism tells you which fields will betray it.
Delivery systems are a common source. Apache Kafka's design documentation describes at-least-once delivery as messages that are never lost but may be redelivered, and explains that a producer resending after a missing acknowledgment can write the same message to the log again. Stripe's webhook documentation says endpoints might occasionally receive the same event more than once and recommends guarding against them by logging the event IDs already processed. CRMs add a human source: in Salesforce, matching rules identify potential duplicates and duplicate rules decide the action, which can alert the user and still allow the save.
| Mechanism | How it shows up | Tell-tale fields | Typical systems |
|---|---|---|---|
| Retry or redelivery | Same event ID ingested twice, seconds or minutes apart | Identical source event ID, different ingestion time | Event streams, webhooks, integration platforms |
| Overlapping export windows | Records at the edge of two date ranges appear in both files | Identical source ID and update time, different file name | Scheduled CSV or API exports |
| Migration or merger of systems | The same customer or ticket exists under an old and a new ID | Legacy ID field, creation date equal to migration date | CRM, help desk or ERP replacements |
| Manual re-entry | A person creates a second record for the same contact or job | Different IDs, same name, contact details and date | Salesforce, HubSpot, field service apps |
| Offline sync conflicts | A mobile app uploads one visit twice after reconnecting | Different IDs, same device, technician and minute | Field service and inspection apps |
| Join fan-out | A one-to-many join repeats parent records | Same parent ID repeated with different child IDs | Prepared extracts and reporting views |
Which repeats are real and must stay in the dataset?
Keep any repeat that records a second occurrence the business would count, bill, staff or measure. In operational data these are often the most valuable records, because repeats carry outcomes: a callback shows the first fix failed, and a reopened ticket shows the first answer did not hold.
| Pattern | Why it looks like a duplicate | Evidence that it is real | How to keep it |
|---|---|---|---|
| Repeat service visit or callback | Same site, same asset, same complaint within days | New work order ID, different arrival time or technician | Keep both; link the second to the first as a callback |
| Recurring charge or subscription invoice | Same customer, amount and description every period | Distinct invoice IDs and period dates | Keep all; never dedupe on amount and customer alone |
| Reopened support case | Same case number appears with two closed events | Status history shows closed, reopened, closed | Keep the history; mark reopen events |
| Correction or supersession | Two versions of one document or order | Version number or amended-from reference | Keep the latest as current and the earlier as superseded |
| Many customers asking the same thing | Near-identical text across tickets | Different requesters, accounts and times | Keep all; flag templated text for the buyer |
The duplicate classification and review guide
Assign every candidate pair to exactly one of these seven classes. The classes separate what can be removed automatically from what needs a person, and the unresolved class keeps doubtful pairs visible instead of quietly deleting them.
| Class | Definition | Test | Default action | Reviewer |
|---|---|---|---|---|
| D1 exact copy | Byte-identical record delivered more than once | Hash of the normalized record matches | Remove all but one automatically | None; report the count |
| D2 delivery duplicate | Same source event ingested twice | Same source event ID; only load metadata differs | Keep the earliest ingestion; log the rest | Data engineer spot check |
| D3 entity duplicate | One real customer, asset or contact under two IDs | Probabilistic match on names, contacts and addresses | Map both IDs to one entity key; do not delete events | System owner |
| D4 capture duplicate | One real event entered twice under different IDs | Same actor, time, location and outcome within a tight window | Keep one, record the merge, keep the other ID as an alias | Process owner sample review |
| R1 legitimate repeat | A second occurrence of the same kind of event | Distinct source ID and at least one distinguishing time, actor or outcome | Keep both; link to the prior event | None unless the rate looks abnormal |
| R2 related revision | A correction, reopen or new version of one event | Explicit reference to the earlier record or version | Keep both; mark which is current | None |
| U unresolved | Evidence does not settle the pair | Any pair failing all tests above | Keep both; flag in the exception log | Process owner before delivery |
Why the comparison key decides the answer
The fields you compare define what a duplicate is, so choose them per record type and write them down. Tools apply whatever definition you give them. The pandas duplicated method, for example, compares all columns unless you pass a subset and by default marks every occurrence except the first, which means a load timestamp column hides retry duplicates while a subset that drops the date merges real recurring events.
For entity duplicates, where no shared key exists, use a probabilistic linkage approach rather than exact rules. Splink's guide to the Fellegi-Sunter model explains that the prior probability two records match depends on the number of records and the number of duplicates, and its term frequency guide shows that agreement on a common value is weaker evidence than agreement on a rare one. In practice, two service records at the same common street name prove little; the same rare asset serial number proves a lot.
Time windows need the same care. A tight window catches retries and double entry, while a wide window starts catching legitimate repeats. Set the window from the mechanism you are hunting, such as minutes for retries and one visit duration for double-entered jobs, never from the size of the duplicate count you hope to see.
In what order should the audit steps run?
Checklist · 8 items
- Reconcile scope first: record counts per month and per type in the extract should match the source system's own reports before any deduplication.
- Write the event definition for each record type: the fields that make one occurrence one occurrence.
- Remove D1 exact copies by hashing normalized records, and record the count.
- Find D2 delivery duplicates on source event IDs, ignoring ingestion and file metadata.
- Generate candidate pairs for D3 and D4 within the agreed time windows and blocking keys.
- Review a random sample of candidate pairs by hand, assign classes and measure how often the automatic rule agrees with the reviewer.
- Apply actions per class, keeping aliases and links rather than silently dropping IDs.
- Publish an audit note with counts per class, the comparison fields, windows, sample agreement rate and the unresolved exception log.
0 of 8 ticked. Tick each item as you go.
Which defect can change a buyer's acceptance even when every file opens?
Duplicates that straddle the buyer's training and evaluation splits, and legitimate repeats that were deleted, are two defects that can change an acceptance decision. Neither stops a file from loading, and both change what a model learns or how well it appears to perform.
Repeated content also shapes model behavior. Lee and colleagues found a single 61-word sentence repeated more than 60,000 times in the C4 corpus, and reported that models trained on deduplicated data emitted memorized text about ten times less often. Operational records carry the same risk in smaller form: templated replies and auto-generated notes repeat thousands of times, and a buyer needs to know where they are.
The reverse error is quieter. A supplier that deduplicates service visits on site and complaint alone removes callbacks, and the dataset then understates repeat failures, the very outcome a buyer may want to predict. Report both the duplicates removed and the repeats kept, so the buyer can see which way the audit leaned.
Worked example (illustrative): repeat visits in a field service export
A heating and cooling contractor exports three years of work orders. All counts and percentages here are illustrative.
The first pass finds 1,200 exact copies, all at the boundaries of monthly export files, and removes them as D1. The source event ID pass finds 300 more records that differ only in an ingestion column added by an integration tool; they become D2. Candidate pairs at the same site within seven days number about 4,000. A sample review of 200 shows that most carry distinct work order IDs, different arrival times and a callback flag or a technician note referencing the first visit, so they are R1 repeats and stay, linked to the first visit.
The exception is a cluster of pairs with different work order IDs but the same technician, the same minute and identical parts lines. They trace to a week when the mobile app's offline sync uploaded jobs twice. By ID they look like repeat visits; by actor, time and outcome they are one visit, so they become D4, merged with the second ID kept as an alias. About 40 pairs where the timecard was missing stay U and go to the service manager before delivery.
Cases the audit should hand to the system owner
Stop applying rules and ask the person who runs the source system when the evidence needed to classify a pair is not in the extract.
- A migration merged or re-keyed records and the mapping table is missing.
- The unresolved class exceeds what the buyer's acceptance terms allow.
- Sample review disagrees with the automatic rule often enough that the rule cannot be trusted.
- Duplicates cluster in one period or region, suggesting a process change rather than noise.
- Removing a class would change counts that also appear in financial or regulatory reports.
- Entity duplicates involve people, where a wrong merge links two individuals' histories.
How uniqueness findings travel into a licensing review
When a business licenses operational records through SourceX, data preparation is part of the work alongside sourcing, rights review and delivery, and an audit note like the one above gives that preparation a documented starting point. Because buyers weigh years of steady history and record counts in the tens of thousands or more from one system, removing real repeats can shrink a dataset that would otherwise qualify.
Eligibility is checked first through a brief company assessment with nothing uploaded; the uniqueness audit comes later, on the records actually offered.
Frequently asked questions
What is a dataset uniqueness audit?
It is a documented check that each real-world event or entity appears once in a dataset, or appears more than once only when it truly happened more than once. The output is a count per duplicate and repeat class with the method used, not just a cleaned file.
Should I deduplicate on all columns?
Usually not. Metadata such as load times hides duplicates, while dropping dates can merge real recurring events. Compare on the fields that define one occurrence for that record type, and write them down.
Are repeat customer visits duplicates?
No, if each visit has its own work order or a different time, technician or outcome. Keep them and link each one to the earlier visit, because repeat visits often carry the outcome a buyer cares about.
How do I handle the same customer under two IDs?
Treat it as an entity duplicate: map both IDs to one entity key and keep all the events. Deleting one ID's records would remove real history that happened under that ID.
What should I tell a buyer about duplicates?
Give counts per class, the comparison fields and time windows, the sample review agreement rate and the list of unresolved pairs. That lets the buyer decide acceptance on evidence rather than on a single duplicate percentage.
Sources
- pandas duplicated compares all columns unless a subset is given and by default marks all but the first occurrence. Source
- At-least-once delivery can redeliver messages, and a producer resend can write a message to the log again unless the idempotent producer deduplicates it. Source
- Webhook endpoints can receive the same event more than once; track processed event IDs rather than timestamps. Source
- Salesforce matching rules identify potential duplicates and duplicate rules decide whether to alert, allow or block. Source
- In the Fellegi-Sunter model, the prior probability of a match depends on the number of records and duplicates. Source
- Agreement on a common value carries less match weight than agreement on a rare value. Source
- C4 contained a 61-word sentence repeated over 60,000 times, and deduplicated models emitted memorized text about ten times less often. Source
See if your company qualifies
A short company assessment. No data uploads are needed.