The problem

Millions in revenue were being lost every month because completed work was not consistently making its way from operational systems into billing.

The problem was not simply a missing report. The underlying data had become difficult to reconcile. Technicians recorded work using non-standard descriptions and codes. CRM and billing systems used different identifiers and code sets. The billing and ERP processes were not sufficiently streamlined, and the organisation was simultaneously migrating from Dynamics CRM to Salesforce.

There was no dependable shared key that could be used to join everything together. A conventional dashboard over one system would therefore produce an incomplete picture of the revenue at risk.

Architecture decision

The decision was to build a canonical reconciliation and master-data layer rather than make either the CRM or ERP the assumed source of truth.

Source records were normalised into a common model, with deterministic matching used where identifiers were sufficiently reliable and fuzzy matching used for records that could not be resolved directly.

The important distinction was that uncertain matches were not simply forced into a result. Low-confidence cases were surfaced for human review, with approved analyst decisions persisted as governed master-data overrides.

Why a simple join was not enough

In a clean transactional system, revenue assurance might be implemented as a series of joins between work orders, customers, assets and invoices. That assumption did not hold here.

The same underlying entity could appear differently across systems. Codes had changed, technicians used inconsistent terminology, and identifiers were not guaranteed to survive the CRM transition cleanly.

Treating one source as authoritative would therefore have created false confidence. The architecture instead separated source data from reconciled business identity.

The matching strategy

Matching was deliberately layered rather than relying on a single algorithm.

1. Normalise. Standardise identifiers, addresses, asset information and source-specific coding before attempting reconciliation.
2. Match deterministically. Use trusted identifiers and business rules where the evidence was strong.
3. Resolve the residual. Apply fuzzy matching to records that could not be reconciled deterministically.
4. Expose uncertainty. Give unresolved and low-confidence records enough diagnostic context for an analyst to understand why they had not matched.
5. Capture the decision. Persist approved human overrides in the MDM layer so the same ambiguity did not have to be resolved repeatedly.

This created a useful boundary between automation and judgement. The system automated what could be automated without pretending that poor source data was inherently precise.

MDM and human-in-the-loop review

The master-data component was important because analyst intervention needed to be more than a temporary dashboard action.

When an analyst established that two records represented the same underlying entity, that decision became governed data. Subsequent reconciliation could use the approved relationship rather than rediscovering it.

This effectively created a feedback loop:

That distinction matters in revenue assurance. A human override that exists only in a report is operationally fragile. A governed override becomes part of the data architecture.

Processing architecture

The implementation used SQL Server and T-SQL for relational transformations and reconciliation logic, Microsoft Fabric ETL for data movement and orchestration, and Spark for the more complex processing workloads.

The pipeline was designed around incremental processing rather than repeatedly rebuilding the entire data set. Caching and appropriate load boundaries were important because the reconciliation logic could be expensive, particularly where fuzzy comparison was required.

The key trade-off: accuracy over speed

The architecture intentionally favoured accuracy over the fastest possible processing time.

In a conventional analytics workload, an approximate match may be acceptable if it produces a useful result quickly. Revenue reconciliation has a different failure mode. A false positive can create a fictitious leakage case, while a false negative can leave genuine earned revenue unrecovered.

That made explainability and controlled exception handling more valuable than maximising throughput at every stage.

Diagnostic dashboards

Power BI was used as an operational diagnostic layer rather than simply a presentation layer.

Analysts needed to understand not only which work appeared unbillable, but why it had failed reconciliation. The dashboards therefore exposed matching status, diagnostic attributes and exception information so users could investigate the path from operational work to billing.

This was particularly important while source systems and coding conventions were changing. The dashboard became a way to identify recurring data-quality problems rather than treating every exception as an isolated transaction.

Migration resilience

The Dynamics-to-Salesforce migration could have made the revenue-assurance problem substantially worse if reconciliation had been tightly coupled to the existing CRM schema.

The canonical layer provided an abstraction between source-system implementation and revenue-assurance logic. Source-specific identifiers and code mappings could change while the downstream reconciliation model remained comparatively stable.

This also made the architecture useful beyond the immediate leakage problem: source-system migrations became a data-integration concern rather than requiring the entire revenue-assurance model to be redesigned.

Consequences

Outcome

The result was a repeatable revenue-assurance capability for identifying work that had been performed but had not successfully reached billing.

More importantly, the architecture changed the nature of the problem. What had previously been a difficult argument over whether revenue was being lost became a data-backed process with reconciliation evidence, diagnostic visibility and governed human decisions.

The central lesson was not that Spark, Fabric or Power BI solved revenue leakage. They were components of a larger design: separate source-system data from canonical business identity, automate high-confidence reconciliation, preserve uncertainty where the data is weak, and turn human judgement into governed data that improves the system over time.