File Duplication Validation
Calculate a file checksum, store it in a control table, and stop duplicate files before they create duplicate data.
File duplication validation prevents double data processing by calculating a checksum for each incoming file, storing that checksum in a database, and checking whether the same file was processed before. If the checksum already exists, the workflow should skip, reject, or route the file to an exception path.
Duplicate files create duplicate records
Duplicate file processing is one of those ETL problems that looks harmless until the report totals double. The file arrives twice, the load runs twice, and suddenly sales look excellent for entirely fictional reasons.
The safest pattern is to treat every incoming file as a candidate for validation. Calculate a checksum, compare it with the processing history, and only continue when the file is genuinely new.
A checksum identifies the file contents
A checksum is a value calculated from the file contents. If the file contents are the same, the checksum should be the same. If one byte changes, the checksum changes. That makes it more reliable than checking the file name alone.
File names are useful, but they are not proof. `orders.csv`, `orders_copy.csv`, and `final_orders_really_final.csv` may all contain the same data. The checksum is less impressed by creative naming.
In practice, the workflow should calculate the checksum before the import step. Store it with the file name, full path, file size, processing date, status, and row count. That gives you both duplicate detection and an audit trail.
The control table records processed files
Create a small database table for processed files. It does not need to be complicated. It needs to answer one question quickly: has this file content been processed before?
| Column | Purpose |
|---|---|
| file_checksum | Stores the calculated checksum and should be indexed or unique. |
| file_name | Records the source file name for troubleshooting and audit reports. |
| file_path | Stores where the file came from, such as an SFTP folder or network share. |
| processed_at | Records when the workflow processed or attempted to process the file. |
| status | Shows whether the file was processed, rejected, skipped, or failed. |
| row_count | Stores the number of rows loaded or validated, useful for later checks. |
The workflow checks before it loads
The process should be deliberately boring. Boring is good here. Boring means the same file cannot sneak in twice wearing a different hat.
- Pick up the incoming file from the folder, SFTP server, email attachment, or other source.
- Calculate the checksum before loading the data.
- Search the processed-file control table for the same checksum.
- If the checksum exists, stop the load and log the duplicate.
- If the checksum is new, process the file and write the checksum to the database.
This pattern prevents accidental duplicate processing while keeping the original file untouched. Raw files are still the witness statement, not the editable version of events.
Handle reprocessing with an explicit override
Sometimes a file must be processed again. A target table may have been rebuilt. A mapping may have been corrected. A previous load may have failed halfway through and left the data in an awkward state, like a progress bar stuck at 99 percent.
Do not hide that behaviour. Add an explicit override flag or a controlled reprocessing path. Log who requested it, when it happened, and why the duplicate check was bypassed.
If the file is a one-off import into a temporary table, a full checksum workflow may be more than you need. If the process is scheduled, feeds production tables, or affects finance reports, duplicate validation is worth the few extra minutes.
Video
File duplication validation questions
What is file duplication validation?
File duplication validation checks whether the same source file has already been processed. The usual method is to calculate a file checksum, store that checksum in a control table, and check the table before loading the file again.
Why is a checksum better than using only the file name?
A checksum is based on the file contents, not just the name. That helps catch duplicate files even when someone renames the file, which is exactly the kind of helpful behaviour that makes ETL developers sigh into their tea.
Where should processed file checksums be stored?
Store processed file checksums in a database control table with the file name, checksum, processed date, status, row count, and any error details. This gives the workflow an audit trail and a reliable duplicate check.
Should duplicate files always be rejected?
In scheduled production workflows, duplicate files should usually be skipped, rejected, or sent to an exception path. For manual reprocessing, allow an explicit override so the duplicate load is intentional and logged.
Stop duplicate files before the load
Calculate the checksum first, check the control table second, and only then load the data. That order is what prevents duplicate processing from becoming duplicate reporting.
A duplicate file is not a second business event. It is the same event trying its luck twice.