Billing Reconciliation Pipeline & Dashboard

The Problem

A billing analyst at a title and settlement company manually reconciled eRecording fee data from three external vendors every billing period. Each vendor delivered a billing report in a completely different format — different column names, different date fields, different fee bucketing conventions. The analyst process was this:

┌───────────────────────────────┐
│ Get three billing report      │
│ files from vendors            │
└───────────────┬───────────────┘
                ▼
┌──────────────────────────────────┐
│ Manually Roll up fees in Excel   │
└───────────────┬──────────────────┘
                ▼
┌────────────────────────────────────┐
│ Compare against internal by hand   │
└───────────────┬────────────────────┘
                ▼
┌────────────────────────────────────────────────┐
│ Write detailed explanations for descrepancies  │
└────────────────────────────────────────────────┘

The process had no audit trail. There was no way for accounting to distinguish between systemic differences (vendor-contracted service fee rates that intentionally differ from internal rates) and genuine billing errors. The spreadsheet-and-memory workflow consumed hours each billing cycle and offered no scalable path forward.

The Solution

I delivered a self-service reconciliation system on a cloud data platform that eliminated the manual process entirely. The system ingests all three vendor billing formats, compares them package-by-package against the internal recording system, and classifies every record: matched, expected service-fee rate difference, amount mismatch, or vendor/internal-only. A four-page dashboard surfaces cross-vendor findings at a glance and provides drill-down tables for every actionable discrepancy. A natural-language query interface lets the billing analyst and accounting team ask questions about specific mismatch types or dollar thresholds without writing SQL. The full dataset for a billing period — tens of thousands of packages across three vendors — runs in seconds.

Outcome & Impact

13,430 packages reconciled cleanly across three vendors in the first production run. Of the remaining packages, 169 genuine amount mismatches were identified and surfaced with side-by-side vendor-vs-internal fee breakdowns.

Metric	Result
Matched packages (first run)	13,430 across 3 vendors
Genuine mismatches found	169 (Vendor A: 3, Vendor B: 4, Vendor C: 162)
Vendor billing rows ingested	~30,000
Build time	~5 hours
Prior process	Hours per billing cycle, no audit trail

The analyst’s workflow is now:

┌───────────────────────────────┐
│      Upload three CSVs        │
└───────────────┬───────────────┘
                ▼
┌───────────────────────────────┐
│        Open dashboard         │
└───────────────┬───────────────┘
                ▼
┌───────────────────────────────┐
│     Export mismatch table     │
│        for accounting         │
└───────────────────────────────┘

The “why” column is pre-populated with the match status classification, so accounting receives a structured explanation rather than a number that requires a phone call.

My Role & Contributions

Aspect	Detail
Role	PM acting as analyst-builder
Team size	1
Timeline	~5 hours (single session)
Scope	Schema design through deployed dashboard and query interface
Key decisions	Negative fee value handling, vendor fee bucketing logic, correct join key strategy, billing period date column selection, “expected difference” classification design

I owned the full build — schema to deployed dashboard — with no data engineers or analysts involved. An AI coding assistant accelerated SQL generation, but every domain decision was mine: identifying that the internal system stores fees as negative values (requiring absolute-value normalization throughout), discovering that one vendor combines two internal fee types into a single line item, determining the correct billing date column for each vendor (each uses a different field), and solving a join key mismatch where one vendor’s reference values encoded a different identifier namespace entirely.

Technical Overview

Cloud Data Platform
├── Internal Recording System (source — production lakehouse)
│   └── invoices, orders, fee types, company/office hierarchy
│
├── Reconciliation Schema (built)
│   ├── vendor_billing_[a/b/c]   ← one table per vendor, loaded via CSV upload
│   ├── allocation_report_view   ← ported from legacy stored procedure
│   ├── reconciliation_view_[a]
│   ├── reconciliation_view_[b]
│   └── reconciliation_view_[c]
│
├── Dashboard (4 pages — Lakeview)
└── Natural-Language Query Interface (Genie Space)

The core analytical layer is a port of a legacy stored procedure from the company’s SQL Server environment into Spark SQL. This required adapting several dialect-specific constructs — pivot syntax, lateral joins with XML parsing, locking hints — to their Spark equivalents, and handling a boolean storage difference that would have silently filtered out records.

Each reconciliation view dynamically derives its billing period window from the loaded vendor file rather than using hard-coded date ranges, so the views stay valid across billing periods without modification.

Vendor Fee Mapping

The most domain-intensive part of the build was mapping vendor fee columns to internal fee types. One vendor combines what the internal system tracks as two distinct fee types into a single billed line item. A direct column-to-column comparison produced thousands of false mismatches; the fix required summing the two internal fee components before comparing. This was not derivable from column names — it required understanding how the vendor invoices are structured contractually.

Join Key Resolution

One vendor’s billing reference field uses a vendor-assigned identifier in a formatted string — not the internal package ID. The initial join on the internal package ID returned zero matches. Investigating the actual reference format revealed two sub-patterns: a standard format covering ~92% of rows, and a resubmission suffix pattern covering the remaining ~8%, each requiring its own key extraction logic.

Challenges & Key Decisions

Fees Stored as Negatives

The internal recording system stores all fees as debits — negative values. Without normalizing to absolute values throughout every comparison, each reconciliation produced a doubled variance: if the vendor billed $50 and the internal system recorded −$50, the computed difference was $100 rather than $0. The first reconciliation run showed 100% mismatches on packages that should have been clean. This wasn’t documented anywhere and surfaced only through inspection of the raw output.

The “Expected Difference” Category

Service fee rates are set by vendor contract and differ from the internal system’s rates. A naive reconciliation flags every service fee as a mismatch — producing a list of 13,000+ “discrepancies” that are actually expected and correct. I added an explicit match status category for expected rate differences, which reduced the actionable mismatch count from ~13,000 to 169 across all three vendors. Without this classification, the tool would have been noise rather than signal.

Dynamic Billing Period Detection

Hard-coding date ranges would make the views useless when a new billing file is loaded. I built each view to derive its TRecS filter window dynamically from the loaded vendor file’s date range. This required identifying the correct date column per vendor — each vendor’s file contains multiple date fields, and only one represents the billing date rather than the recording date or submission date. Using the wrong date column inflated the “internal-only” package count by roughly 10x.

Lessons Learned

• Financial data models often have silent sign conventions. A detail that isn’t in any documentation — fees stored as negatives — can invalidate every query in the project. The first sanity check on any financial data should be: are credits stored as negative values?
• Fee bucketing differences are contractual, not technical. The fact that one vendor combines two internal fee types into a single line item is a billing contract choice. Schema inspection can’t surface it; you need someone with domain context on how the vendor invoices are structured.
• The “expected difference” category is what makes a reconciliation tool useful. Without separating systemic-but-correct differences from genuine errors, the output is noise. The entire business value of the tool depends on this classification existing.
• AI tooling compresses the technical work; domain knowledge remains the bottleneck. SQL generation for adapted dialect constructs took seconds. Knowing the correct join key namespace, the fee bucketing convention, and the contractual rate differences — none of that came from the model.