What this project is about
Daily transaction files are often delivered more than once, corrected after delivery or mixed with malformed rows. A loader that only inserts valid records can silently duplicate business events or conceal why totals changed. ReliableETL addresses this through file fingerprints, transaction identifiers, rejected-row records and durable run status in a compact Python and SQLite pipeline.
ETL means extracting source data, transforming it into an agreed representation and loading it into a destination. This project concentrates on the reliability boundary of that process rather than orchestration features. Its command-line workflow, SQL tables and runbook make a batch's outcome explainable. It is a local ingestion demonstrator, without evidence of a scheduled production feed or distributed processing system.
How it works
- Daily CSV batch
- Validation + quarantine
- Atomic facts + run audit
Separate file identity from business identity
The loader calculates a SHA-256 fingerprint from the complete file bytes. If that content has already succeeded, another invocation returns SKIPPED even when the file has a new name. A unique transaction identifier supplies a second boundary across different files. Corrected or consolidated content can therefore be a new batch without allowing previously accepted business transactions to be inserted twice.
Preserve row-level rejection evidence
Required fields, ISO dates, positive quantities and non-negative prices form the input contract. Valid rows are normalised and their amounts calculated with decimal arithmetic. Invalid rows retain their raw values, source line number, run ID and reason codes in a rejected-record table. Duplicate transaction IDs are also quarantined. Several problems can be recorded for one row, helping an operator correct the source rather than guess what disappeared.
Commit data and successful counts together
A RUNNING audit record is committed before row processing. Facts, rejects and the final SUCCEEDED counts then share one database transaction. An ordinary processing exception rolls these changes back and updates the durable run record to FAILED. A missing required header is treated as a structural batch failure, while an invalid individual row can be rejected without preventing the rest of a well-formed file from loading.
The important design choices
Use two replay controls
Combine content fingerprints with a unique business transaction key.
The trade-offA renamed file and a repeated transaction in changed content are both detectable. However, a legitimate correction to an already accepted transaction needs a separate amendment policy; the current loader rejects that identifier instead of updating history.
Distinguish invalid rows from unusable files
Quarantine row errors, but fail the batch when the required schema is absent.
The trade-offA feed can make useful progress while accounting for bad records, but a technically successful batch can still contain many rejects. Operational quality thresholds and escalation rules remain a separate policy.
Correcting a rejected row without duplicating earlier facts
A file contains valid transactions plus one row with an invalid quantity. An operator later sends changed content containing the correction and the original valid rows.
- The first load stores valid facts and preserves the invalid row with its reason under a successful, reconciled run.
- Changed file bytes produce a new fingerprint, allowing the corrected batch to enter the normal processing path.
- Previously accepted transaction identifiers are rejected as duplicates, while a valid correction for a previously rejected identifier can load.
What this showsThe operator can trace both attempts without loading the previously accepted transactions twice or editing reporting tables manually.
Illustrative example, not a production incident.
How it was tested—and what it shows
The checks
Tests exercise multiple validation reasons, successful ingestion and repeated-file skipping, duplicate business-key rejection and structural failure without partial facts. They check database state and the successful-run identity read = accepted + rejected. The runbook and operations SQL explain how to inspect failures and reasons. These checks support the supplied sequential workflow; they do not establish recovery from every process termination or concurrent submission.
What to take from the results
The result is an auditable ingest path where successful counts and retained rejects have an explicit relationship. A notable boundary is that file hashes are unique for every stored run status. Re-submitting unchanged content after a FAILED or abandoned RUNNING record is therefore not a supported recovery path. Automatic stale-run recovery and an intentional retry model need to be designed before unattended operation.
What to keep in mind
- The workflow is deliberately small and local.
- Its correctness depends on the chosen transaction keys and validation rules.
Technical notes & source
Built with
Python · SQLite · SQL · pytest
Terms used in this project
- ETL
- Extract data, transform it into the required form and load it into a destination.
- Atomic commit
- Save related changes together, or save none of them.