Rules in SQL, steps in Python

When I built an automated billing and reconciliation pipeline for a health organization in Puerto Rico, I followed one rule from the start: the matching and reconciliation rules live in SQL, and Python handles everything around them.

It sounds like a small technical choice. It decides who can read the rules, how they get tested and what breaks when something changes.

The problem it solves

The organization billed members every month and reconciled what came in under Puerto Rico Law 95, for individual members and for joint memberships shared by spouses. The work was done by hand, and the numbers had to match exactly.

That kind of process mixes two very different things:

• Rules: which payment belongs to which member, when two records are the same person, what counts as a match.
• Steps: receive the files, clean them, run the checks in the right order, produce the outputs.

When both live in the same script, every change to a rule is a change to the pipeline, and every change to the pipeline can quietly change a rule.

Rules in SQL

The matching rules sit in SQL, next to the data. That has three advantages:

• Readable. A rule written as a query can be read by an analyst or an auditor who does not write Python. They can see the condition, not just trust it.
• Testable. A rule is a query: run it on known data and check the result.
• Auditable. When someone asks why a record was matched, the answer is a query you can run again.

Steps in Python

Python does what it is good at: reading several input files, cleaning them, calling the rules in order and writing the outputs. It knows the sequence, not the business logic.

What it buys you

A rule can change without touching the pipeline, and the pipeline can change without touching a rule. When a regulation or a business case changes, the change happens in one place, and it is easy to see what changed.

It also makes the outputs honest. The process produces the monthly billing, the reconciliation report and a list of records that do not match. Because the rules are explicit, every record on that list has a reason a person can read. And because a user starts the monthly cycle from an application screen and confirms the results along the way, that person stays in control of what gets billed.

When I use it

Whenever the rules matter more than the plumbing: billing, reconciliation, eligibility, anything someone will be asked to explain later. The pipeline can be rewritten many times. The rules are what the business actually agreed on, so they deserve the place where they are easiest to read.