Case study
Audit-Grade ETL Pipeline
A pipeline that turns inconsistent, multi-source financial documents into a reconciled, categorized ledger - and refuses to load anything that doesn't tie out to the penny.
- Python 3.10+
- SQLite
- mypy (strict)
- ruff
- pytest
- openpyxl
- GitHub Actions
What it is
A working, tested command-line pipeline that ingests messy financial statements from multiple institutions and produces a reconciled, categorized, queryable ledger plus an audit-ready workbook. Each institution gets its own parser; nothing loads unless it reconciles to the penny; ingestion is idempotent; categorization is versioned and never destroys human review; and sensitive output is redacted with a verification step. The patterns transfer to any setting where messy documents must become data you can defend.
Why it exists
The hard part of this work is not parsing - it is trust. Can you re-run it safely? Does the data tie out? If the rules change, can you explain what moved? If you hand a deliverable to someone who will scrutinize it, does every number trace back to a source? Wiring a regex to a CSV is a demo. This repo is a set of answered questions about correctness under audit, and each answer is written up as an ADR.
Three ideas do most of the work
Reconciliation is a gate, not a report
A parser can't produce a loadable result unless the transactions it parsed tie out to the statement's own stated totals - the balance identity, the credit total, and the debit total, all exact to the cent. A mismatch raises. So a parser bug on a document it has never seen surfaces as a loud failure on the check it broke, never as quietly wrong data.
Re-runs are always safe
Ingestion is keyed by the SHA-256 of the source bytes, so the same file dropped in twice is processed once. Migrations record the versions they've applied and skip them. Categorization protects locked rows and never downgrades. Whatever happened last time, you can just run it again.
Every figure traces back
Money stays in integer cents from parse to report. The categorizer stamps a classifier version on every row, manual review is recorded and protected, and deliverables recompute from the ledger instead of hard-coding numbers. Provenance is preserved end to end, because the deliverable has to survive scrutiny.
How a statement becomes a ledger
One path, with correctness enforced at the gate. Full detail in ARCHITECTURE.md and the eight ADRs.
- 1
Source documents
bank statement, savings statement, CSV export - all synthetic fixtures
- 2
Registry: sniff + dispatch
routes each document to its institution parser
- 3
Institution parser
one StatementExtractor interface per statement layout
- 4
Penny-exact reconciliation gate
ties out to the stated totals, or fails loud
- 5
Idempotent ingest → SQLite
SHA-256 content key; migrated schema; single-transaction load
- 6
Versioned categorizer → workbook
no-downgrade + manual locks; an audit-ready deliverable
What's inside
Money & domain
Integer-cents money with exact parsing and formatting, plus the Statement and Transaction models. A lost penny is a failed audit, so a dollar figure never touches a float.
Extraction
Per-institution parsers behind one StatementExtractor interface, a registry that sniffs and dispatches, and the reconciliation gate that refuses to load anything that doesn't tie out.
Persistence
A connection factory and idempotent, versioned migrations that record what they've applied and skip it on the next run - the schema has a single source of truth.
Ingestion
SHA-256 content keying for idempotent, single-transaction loads, so a corpus can be re-ingested safely and a duplicate file lands exactly once.
Categorization
An ordered, first-match-wins rule engine with account-aware and date-aware rules, stamping a classifier version on every row and never reclassifying a locked row or downgrading a real category to Uncategorized.
Reconciliation
Independent two-ledger matching to the penny, for tying a derived ledger back against a source of record and catching a drift that a single view would hide.
Redaction
Config-driven redaction that returns a manifest of exactly what was removed, gated by a self-test that proves it strips what it must and preserves what it must before it ever touches a real document.
Cite-check
Verification of a generated document's claims against a locked source of truth, so a number that no longer ties out is caught before the document ships.
By the numbers
The suite is deterministic and offline - no network, no clock, no randomness - and CI runs ruff, mypy (strict), and pytest across Python 3.10 to 3.12. Money is integer cents from parse to report, and reconciliation is exact to the penny.
Explore the code
The repository is fully documented - an architecture overview, eight ADRs, a pipeline and data-model write-up, and a diagram.