Automating Financial Reconciliation with n8n and Python

Why reconciliation breaks at scale

Reconciliation is deceptively simple: match two sides, explain the difference. At one client it is a spreadsheet. At forty clients, with different file formats, cut-off times and edge cases, it becomes an engineering problem.

The failure mode is never the matching logic. It is the twenty one-off scripts nobody owns.

The architecture

1. Ingest — n8n pulls source data on schedule (SFTP, API, portal via Playwright).
2. Normalise — a Python layer coerces every source into one canonical schema.
3. Validate — SQL assertions run before matching: row counts, control totals, date ranges, duplicate keys.
4. Match — parameterised matching rules per client, stored as config not code.
5. Report — results land in PostgreSQL and surface in Metabase.

-- control-total assertion that runs before every match
select
  source,
  count(*)                as rows_in,
  sum(amount)             as total_in,
  count(*) filter (where amount is null) as null_amounts
from staging.transactions
where batch_id = :batch_id
group by source;

What actually moved the needle

• Making validation fail loudly before matching, not after.
• Storing client rules as configuration, so a new client is a row, not a repo.
• Treating the automation layer as a product with owners, tests and versioning.

Result: 35% less manual processing time and 99%+ sustained accuracy.