A reconciled claims warehouse beside a legacy mainframe

The problem

A medical professional liability insurer runs its claims business on an IBM i (AS/400) system that has been in production for decades. It still works, but nobody can say with confidence what its several thousand tables mean. Reports disagree with each other, analysts can't self-serve, and actuarial and finance teams spend their time arguing about whose number is right.

The first build covered the claims domain end to end: claims, payments, reserves, recoveries, reinsurance and written premium, modelled for actuarial and finance users.

Replacing the system wasn't an option yet: you can't re-implement business rules nobody can explain. So the brief was to build a warehouse beside it. The warehouse reads the legacy system strictly read-only and proves, every day, that it produces the same numbers.

Architecture

Orchestration is event-driven: one scheduled trigger starts extraction, and every later stage fires when the previous one completes cleanly. There are no timed offsets that assume how long the last step took.

Engineering decisions worth noting

The legacy system is the answer key

Every business measure has a two-sided reconciliation test. One side computes the figure from gold, the other from the live source, and both run at comparison time rather than against a stored baseline. Results go to a PASS / DRIFT / FAIL board. Money is matched to the penny, structures are matched on shape and keys, and a narrow freshness band covers a source that keeps moving. An accepted difference has to be a dated, attributed business decision, and it stays visible on the board. Nothing gets promoted on an unexplained red.

Move only what changed, but never trust an empty result

The daily run reads the source's own transaction journal and re-selects only the rows it names. An expired journal looks exactly like "nothing changed", so retention is checked before an empty result is believed, and anything untrustworthy falls back to a full reload. Tables that the source clears and rebuilds nightly skip the journal entirely and are kept as dated snapshots. That way the warehouse keeps the history the legacy system overwrites.

Specs, not hand-written code

YAML specifications generate the DDL for every layer, the semantic model, the orchestration and the parity tests. CI fails if a generated file has been edited by hand. Adding a table means adding a spec entry, not writing four files.

Make silent failures loud

The team kept a catalogue of the ways the pipeline could report success while producing wrong data, with a specific guard for each. Examples: join-rate floors that catch fact rows losing their dimension, a guard against reading a table during its rebuild window, and checks that security roles actually bind to someone.

My role

I joined as a contract data engineer to audit and recover a build that had been largely produced by AI agents. It looked finished and reported high migration accuracy. My job was to find out whether it was actually trustworthy, and to make it so.

• Found misleading accuracy metrics. The automated parity tests mixed up measure-level and table-level reconciliation, so the migration looked more accurate than it was. I redesigned the validation approach and widened the goal from "pass the parity tests" to a governed enterprise warehouse.
• Cut platform cost by ~80%. The silver and gold layers were always on and fully reloaded every run. I moved them to incremental loads on scheduled, rather than always-on, compute, which cut capacity cost by about 80%.
• Restored recoverability. About 95% of bronze tables had no time-travel retention. I enabled it so any load can be rolled back to a point in time.
• Built the governance framework. Central load-audit and metadata tables across bronze, silver and gold record row counts, key definitions, schema changes, upstream lineage and failures with full tracebacks. I added sensitivity and endorsement labels, and dynamic role-based access driven by Entra ID groups.
• Set the delivery standard. Every change goes from ticket to GitLab CI to Fabric. Each workspace records the commit deployed to it, and higher environments deploy only from main or a tag. Modular pipelines and scoped test runs mean a failed stage reruns on its own, so recovery no longer means rerunning the whole day.
• Infrastructure as code. I maintained Terraform across three roots (azurerm and Microsoft Fabric providers): workspaces, medallion lakehouses, Container App Jobs, Key Vault and managed identities for dev and UAT.
• Self-service and training. I set up a separate analytics workspace on the SQL endpoint, gold tables and semantic models, isolated from the locked-down CI/CD workspaces. I also ran a Fabric and Power BI training series for the client's analysts.

Stack

Microsoft Fabric (OneLake, Spark notebooks, Direct Lake), PySpark and Spark SQL on Delta, Python 3.12, DuckDB for local runs, Terraform (Fabric and Azure providers), Docker on Azure Container Apps Jobs, Key Vault with managed identity, GitLab CI with gitleaks secret scanning, and Power BI with DAX and TMDL.

I wrote up the approach in more detail in Your legacy system is the answer key.