Data Analytics
Consultants: Kiran Lokhande
A multi-billion-dollar insurance organization needed a reliable way to calculate outstanding financial reserves across the full claims lifecycle. With data spread across dozens of internal systems and third-party administrators, reserve calculations had become manual, inconsistent, and difficult to audit. Green Leaf partnered with the client to design and implement a modern Reserve Engine that transformed reserves into a real-time, trusted financial metric.
Reserve calculations relied on manual Excel processes across nearly 70 disparate data sources, leading to inconsistence, delays, and limited auditability.
Green Leaf built a centralized Reserve Engine using a Lakehouse and Data Vault 2.0 architecture to dynamically calculate reserves at any point in time.
The client lacked a centralized, reliable way to calculate outstanding reserves across the claims lifecycle. Reserve and payment data was spread across approximately 70 disparate sources, including TPAs and internal OLTP systems, each with its own schema and business logic.
Point-in-time reserve calculations were performed manually in Excel, requiring significant effort and frequent intervention. Data inconsistencies between finance, actuarial, and claims teams created reconciliation challenges and eroded confidence in reported numbers. True-ups were common, reporting capabilities were limited, and supporting regulatory and actuarial requirements required extensive manual validation.
The business needed an automated , scalable solution that ensured accuracy, transparency, and auditability while supporting regulatory reporting, actuarial modeling, and future growth.
Green Leaf designed and implemented a Reserve Engine built on a Lakehouse architecture using Data Vault 2.0 principles. The solution conformed and normalized data from all reserve and payment sources into a single, governed dataset that supported the full lifecycle of a claim.
Using PySpark and Spark SQL within Azure Synapse and Delta Lake, the platform processed reserve and payment transactions through Bronze, Silver, and Gold layers with ACID-compliant tables and slowly changing dimensions. Reusable transformation logic and parameterized business rules ensured flexibility as reserve methodologies evolved.
For each claim and coverage combination, the engine automatically ingested transactions, applied business logic, enforced data quality checks, and calculated key measures including paid-to-date, outstanding reserves, incurred losses, and point-in-time balances. All measures were time-stamped and version-controlled, providing a complete transaction trail and full audit traceability.
Built-in data quality controls—including duplicate checks, negative balance prevention, and reconciliation against control totals—were monitored through dashboards to ensure ongoing reliability.
The Reserve Engine reduced manual reserve calculation effort by 90% and enabled millions of transactions to be processed in seconds. Financial close cycles became faster and more reliable, with consistent results available across finance, actuarial, and operations teams.
The solution enabled true point-in-time reserve reporting, improved data quality, and strengthened SOX compliance through transparent, repeatable, and auditable processes. By unifying and conforming data from dozens of sources, the client established a scalable foundation for analytics, forecasting, and regulatory reporting.
What was once a manual, monthly exercise is now a real-time, trusted financial metric that supports confident decision-making across the enterprise.
Discover how Green Leaf Consulting Group’s strategic IT solutions can bring you growth through technology. Contact Neil McCole at neilm@greenleafgrp.com or (484) 535-4405.