Does the bill match
the service delivered?
A SQL reconciliation that turns disconnected order, activation, and billing extracts into an actionable exception queue—with a clear business meaning for every discrepancy.
These are synthetic review amounts, not confirmed losses, refunds, or savings. S014 has conflicting invoice values and is excluded from dollar totals. Findings overlap; exception amounts must not be added together.
Investigate the right source first.
Billing operations needs to know which charges deserve review. Provisioning needs to know which orders lack delivery evidence. Data engineering needs to distinguish a replayed extract from a second customer charge.
The model compares September base charges with agreed plans only when CRM and provisioning support a reliable expectation. Conflicting records remain visible and are quarantined from uncertain comparisons.
Controlled scenario. Twenty fictional service orders, all due September 1, plus one orphan billed ID. Flat $50 monthly plans, no mid-month changes, proration, credits, taxes, or usage fees.
Two clocks. Service cutoff: September 30, 23:59:59 UTC. Evidence received through October 3, 00:00:00 UTC is included. Later evidence cannot silently change this run.
Preserve raw evidence
Orders, activation snapshots, and invoice lines. Keep duplicate rows.
Freeze & classify
Apply both clocks. Collapse exact replays. Quarantine conflicting keys.
Keep the service grain
Aggregate invoice lines first. Preserve unmatched service IDs.
Explain & assign
Produce a rule, owner, business consequence, and next action.
From a flag to a next step.
Select a service to inspect the business implication, raw evidence, and SQL predicate. Dollar columns repeat service-level values across overlapping rules.
| Service | Discrepancy | Priority | Owner | Expected | Billed |
|---|
The checks are the product.
SQL executes in SQLite. No ORM, framework, or hidden transformations. Null expected or billed amounts mean unknown—not zero.
Define the grain before the join.
One intended snapshot per service_id. Controls agreed monthly plan and commercial status; cannot prove service delivery.
One intended snapshot per service_id. Controls observed provisioning state at cutoff. This fixture is a snapshot, not an event history.
One intended record per line_id for the selected period. Multiple distinct line IDs are summed, even when the amounts are identical.
USD integer cents. Zero-cent tolerance: a $0.01 difference is flagged. Positive variance means billed exceeds expected; negative means a potential shortfall.
Both sources active → plan. Both inactive, or inactive CRM without activation evidence → zero. Missing active-service evidence, source disagreement, or conflicts → unknown.
One reconciliation row per service and month; one exception per service, month, and rule. No confirmed adjustments. No production scale, real customer data, or employer assignment is implied.
Planted defects. Explicit expectations.
The fixture inventory and isolated boundary tests verify behavior beyond the example rows. Test results below come from the saved acceptance run.
python3 run.py python3 -m unittest -v test_acceptance.py
Machine-readable test evidence ↗ Inspect acceptance tests ↗ Design references & decisions ↗