Skip to content

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

This page covers dataset uniqueness audit: duplicate records or legitimate repeat events.Illustrative

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.

Where do duplicates come from in operational exports?
MechanismHow it shows upTell-tale fieldsTypical systems
Retry or redeliverySame event ID ingested twice, seconds or minutes apartIdentical source event ID, different ingestion timeEvent streams, webhooks, integration platforms
Overlapping export windowsRecords at the edge of two date ranges appear in both filesIdentical source ID and update time, different file nameScheduled CSV or API exports
Migration or merger of systemsThe same customer or ticket exists under an old and a new IDLegacy ID field, creation date equal to migration dateCRM, help desk or ERP replacements
Manual re-entryA person creates a second record for the same contact or jobDifferent IDs, same name, contact details and dateSalesforce, HubSpot, field service apps
Offline sync conflictsA mobile app uploads one visit twice after reconnectingDifferent IDs, same device, technician and minuteField service and inspection apps
Join fan-outA one-to-many join repeats parent recordsSame parent ID repeated with different child IDsPrepared 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.

Which repeats are real and must stay in the dataset?
PatternWhy it looks like a duplicateEvidence that it is realHow to keep it
Repeat service visit or callbackSame site, same asset, same complaint within daysNew work order ID, different arrival time or technicianKeep both; link the second to the first as a callback
Recurring charge or subscription invoiceSame customer, amount and description every periodDistinct invoice IDs and period datesKeep all; never dedupe on amount and customer alone
Reopened support caseSame case number appears with two closed eventsStatus history shows closed, reopened, closedKeep the history; mark reopen events
Correction or supersessionTwo versions of one document or orderVersion number or amended-from referenceKeep the latest as current and the earlier as superseded
Many customers asking the same thingNear-identical text across ticketsDifferent requesters, accounts and timesKeep 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.

The duplicate classification and review guide
ClassDefinitionTestDefault actionReviewer
D1 exact copyByte-identical record delivered more than onceHash of the normalized record matchesRemove all but one automaticallyNone; report the count
D2 delivery duplicateSame source event ingested twiceSame source event ID; only load metadata differsKeep the earliest ingestion; log the restData engineer spot check
D3 entity duplicateOne real customer, asset or contact under two IDsProbabilistic match on names, contacts and addressesMap both IDs to one entity key; do not delete eventsSystem owner
D4 capture duplicateOne real event entered twice under different IDsSame actor, time, location and outcome within a tight windowKeep one, record the merge, keep the other ID as an aliasProcess owner sample review
R1 legitimate repeatA second occurrence of the same kind of eventDistinct source ID and at least one distinguishing time, actor or outcomeKeep both; link to the prior eventNone unless the rate looks abnormal
R2 related revisionA correction, reopen or new version of one eventExplicit reference to the earlier record or versionKeep both; mark which is currentNone
U unresolvedEvidence does not settle the pairAny pair failing all tests aboveKeep both; flag in the exception logProcess 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

  1. Reconcile scope first: record counts per month and per type in the extract should match the source system's own reports before any deduplication.
  2. Write the event definition for each record type: the fields that make one occurrence one occurrence.
  3. Remove D1 exact copies by hashing normalized records, and record the count.
  4. Find D2 delivery duplicates on source event IDs, ignoring ingestion and file metadata.
  5. Generate candidate pairs for D3 and D4 within the agreed time windows and blocking keys.
  6. Review a random sample of candidate pairs by hand, assign classes and measure how often the automatic rule agrees with the reviewer.
  7. Apply actions per class, keeping aliases and links rather than silently dropping IDs.
  8. 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.

See if your company qualifies