Duplicate Detection
Find repeated rows and repeated business events before one transaction becomes two reports, three arguments, and a long afternoon.
Duplicate detection finds repeated records before an ETL workflow loads them. It can compare full rows, selected business fields, or calculated matching keys so repeated source records do not inflate totals, duplicate transactions, or overwrite the wrong target data.
Duplicate detection looks for repeated business events
Not every duplicate has the same technical key. Two rows may have different row IDs but still describe the same order, payment, shipment, or customer update. That is where duplicate detection earns its tea.
The goal is simple: compare the fields that identify the business event, then decide what should happen when two or more records look the same. The workflow should make that decision before the target system has to deal with the mess.
Match on the fields that prove a duplicate
Duplicate detection works best when the matching rule reflects the data. Exact full-row matching is useful, but it is not always enough. In many real feeds, the same transaction arrives with a different timestamp, batch ID, or comment field.
Exact row match
Compare every relevant field when the source file should not contain repeated rows at all.
Business key match
Compare values such as customer, invoice number, date, amount, and reference number.
Normalised text match
Trim spaces, standardise case, and remove harmless formatting differences before comparing text fields.
Windowed match
Look for repeated events inside a time window when timestamps are close but not identical.
The workflow should separate duplicates from clean rows
The safest duplicate detection pattern is to route clean rows forward and duplicate candidates somewhere visible. That may be a rejected-records table, an exception file, or a review queue.
Do not delete duplicates silently unless the rule is approved and boringly obvious. Duplicate records are like relatives at Christmas: sometimes expected, sometimes accidental, and occasionally expensive.
If the business rule says “keep the newest record,” then use a timestamp and log the rows that were suppressed. If the rule says “manual review,” keep all conflicting rows together so someone can see the full story.
Check duplicates before and against the target
There are two useful checks. First, look for duplicates inside the current batch. Second, compare incoming records with data already loaded into the target system.
The first check catches repeated rows in today’s file. The second catches records that were loaded yesterday and sent again today. That second case is common with partner feeds, retry jobs, and exports named `latest.csv`, a filename with all the reliability of a weather forecast.
Related guides include uniqueness validation, file duplication validation, and 22 data quality checks.
When duplicate detection is not worth automating
If you are checking a small one-off file by hand, a quick sort or database query may be enough. Do not build machinery where a five-minute inspection solves the problem.
Automate duplicate detection when the feed runs repeatedly, the data affects totals, or the same mistake could reach customers, stock, finance, or reporting. That is where repeatable validation saves time and prevents awkward meetings.
Video
Duplicate detection questions
What is duplicate detection in ETL?
Duplicate detection finds repeated records before data is loaded. It can compare complete rows, selected business fields, or calculated keys such as customer, date, amount, and reference number.
How is duplicate detection different from uniqueness validation?
Uniqueness validation checks fields that must appear once, such as primary keys or invoice numbers. Duplicate detection is broader and can identify repeated business events even when each row has a different technical ID.
Should duplicate rows be deleted automatically?
Not always. Some duplicates are genuine repeated events. The safest pattern is to reject, log, or route duplicates for review unless the business has approved a clear deduplication rule.
Which fields should be used for duplicate detection?
Use the fields that identify the business event, not only the database key. Common examples include customer ID, transaction date, invoice number, amount, product code, and source reference.
Find duplicates before they become totals
Define the match rule, test it on a sample, route duplicate candidates clearly, and load only records that pass. The database will thank you by not lying with confidence.
Duplicate detection is not about deleting rows. It is about proving which rows deserve to load.