The most expensive data quality failure in revenue systems is also the most banal: the same invoice, imported twice. The duplication poisons everything downstream with polite plausibility — collection totals inflate by exactly the amount of the duplicate, efficiency KPIs improve on paper, the ministry's reconciliation disagrees with yours by a number nobody can see — and because every individual record looks perfectly valid, no row-level validation will ever catch it. Duplicate detection at import time is the only interception point that works: every incoming row is checked against pre-fetched system indexes and against the current run itself, duplicates are counted and skipped — never written twice — and the import's error report says precisely how many rows were recognized as already-known, which is the number that keeps your totals honest.
Why "we will clean it later" never cleans it #
Post-import deduplication is one of those plans that sounds reasonable and fails structurally. Once duplicates are written, the ledger contains two records that are both "real" in every field — same invoice number, same client, same amount — and distinguishing the original from the copy requires a decision procedure (which import, which timestamp, which authority) that nobody wants to own retrospectively, especially after payments have been matched against one of them. The writes are also not reversible without collateral: soft-deleting the "wrong" duplicate after a settlement batch has touched it creates an audit story that takes a week to explain. Import-time interception inverts the economics: the check costs milliseconds inside a pipeline that is already touching every row, the skip decision is mechanical (this row exists), and the evidence is captured while the context is fresh — which is why counted-and-skipped beats found-and-fixed every time it is measured.
- Pre-fetched indexes: the incoming sheet is checked against system indexes loaded before the run — no per-row database round trips.
- In-run self-checking: the same sheet imported twice in one run catches its own duplicates — the classic double-submission.
- Counted and skipped: duplicates appear in the import report as a number, so totals reconcile against the source sheet.
- Invalid-invoice detection alongside: rows conflicting with stored invoices (changed due date, changed amount) are flagged per row, audited — not silently updated.
For teams migrating legacy ledgers, the duplicate detector is also an honest mirror: the first import of an old system routinely finds that five to eight percent of historical records were already duplicated in the source — a discovery better made in the import report, with counts and skips, than in the ministry's year-end reconciliation, with questions. Treat the deduplication numbers from your first runs as a data-quality baseline for the migration itself; the second run's duplicate rate tells you whether the source cleanup is working, and the third run's near-zero rate is the migration's real completion certificate.