The Duplicate Files That Weren't Duplicates
A checksum is excellent at answering one question: have these exact bytes arrived before? I wrote about that in Checksum, Not Filename, and I still prefer it to trusting a name someone else chose.
But two files can disagree at the byte level while containing the same transactions. Or one can contain half the other’s transactions. Or they can contain the same business keys with different numbers of repeated lines.
The checksum has done its job. Now we need another question.
Same keys, different contents
Imagine three delivery files. For clarity, each letter below stands for a normalized business-transaction key, not a literal row:
File A: x, x, yFile B: y, x, xFile C: x, y, yIf row order is irrelevant, A and B have identical multisets: two occurrences of x and one of y. C has the same distinct key set, {x, y}, but a different count for each key. A comparison that only checks distinct keys declares all three equal. A byte checksum declares all three different if order or serialization differs.
Neither answer is necessarily wrong. They answer different questions.
Key-set equality is weaker than content equality when repeated transactions matter.
This gets worse with partial overlap. If A contains {x, y} and D contains {x, z}, the files share one key. Is that a duplicate delivery? Probably not. Are there repeated business transactions worth investigating? Possibly. If the system labels both files simply duplicate=true, someone downstream will eventually interpret that label more strongly than the evidence supports.
I needed relationships, not a yes/no flag
A more useful report distinguishes:
- Identical business contents: same normalized key set and the same count for each key, regardless of order.
- Same keys, different counts: all keys match, but multiplicities differ.
- Containment: every distinct key from one file is present in the other, with possible extra keys on one side.
- Partial overlap: some, but not all, keys are shared.
- Disjoint: no shared keys.
For partial overlap, I want each file’s denominator shown separately. If A has two rows and B has a thousand, one shared row is 50% of A but only 0.1% of B. A single overlap percentage hides which side is mostly covered.
There’s another trap: joining raw rows on business key before aggregating. If A has three copies of x and B has two, a naive join produces six pairs. You haven’t found six duplicated transactions. You’ve manufactured a many-to-many product.
Count occurrences per file and key first, then compare those counts:
WITH keys AS ( SELECT file_id, business_key, COUNT(*) AS occurrences FROM normalized_rows GROUP BY file_id, business_key)SELECT a.business_key, a.occurrences AS rows_in_a, b.occurrences AS rows_in_bFROM keys aJOIN keys b ON a.business_key = b.business_keyWHERE a.file_id = 'A' AND b.file_id = 'B';This is deliberately a teaching-sized query, not a full pairwise comparison engine. At scale you’d restrict candidate file pairs by source, date, or another safe partition before joining keys.
A business key is a policy too
What goes into that key? Invoice number alone is often too weak. Including an import timestamp makes repeat deliveries look unique. Including a row position makes reordering change identity. Excluding a source identifier can make unrelated partners appear to overlap.
The key should represent the transaction sameness you care about, not every available column. Sometimes you need more than one profile: strict content equality for delivery auditing, and a narrower business-key profile for detecting potential repeats.
I wouldn’t auto-delete a file just because its business-key profile overlaps another. A correction, split delivery, or legitimate resubmission can look similar. The overlap report is evidence for review and targeted rules. It is not itself a delete instruction.
The distinction is now one I try to keep explicit: byte identity prevents repeat delivery; semantic overlap finds relationships between transactions. They belong to different stages of the conversation, and usually different stages of the pipeline.
More on Data engineering The Rows That Never Reached the Reject Table →