Experience studies are the beating heart of life and annuity actuarial work. They're how you know whether your assumptions — mortality, lapse, morbidity, benefit utilization — actually match reality. The problem is scale. When your annuity book runs to over a billion records and more than a trillion dollars in fund value, the traditional tools buckle. I've built and operated end-to-end data pipelines to handle exactly that, and I want to share what actually works.

The scale problem

A serious experience study means integrating years of policyholder data, claims, and external factors, then slicing it every way an actuary might ask. At a billion-plus records, an Excel-and-SQL-Server workflow either falls over or takes so long that the study is stale before it's finished. Because of the long-duration nature of these products, the assumptions you derive drive pricing and reserving for decades — so getting the study both correct and timely genuinely matters.

The architecture that works

I've designed and operated end-to-end PySpark and Delta Lake pipelines on Databricks for annuity experience datasets exceeding one billion records and one trillion USD in fund value. The pattern is a medallion architecture — Bronze for raw ingested data, Silver for cleaned and validated records, Gold for the curated, analysis-ready datasets that feed the actual study. It's the same reference pattern Databricks now publishes for actuarial modeling: ingest claims, projections, and scenario data into Bronze, enrich in Spark to populate Silver and Gold, and trace lineage end-to-end.

A few design choices carry most of the weight:

  • ✔ Delta Lake for reliability and reproducibility: Delta's transactional guarantees and "time travel" let you pin an experience study to an exact data snapshot. When someone asks six months later why a lapse assumption moved, you can reproduce the precise data behind it. That's not a nice-to-have in actuarial work; it's the difference between a defensible study and an unreproducible one.
  • ✔ Spark SQL optimization: This is where the runtime gains live. On these pipelines I reduced model runtime by 40% through Spark SQL optimization, workload-specific cluster configuration, and Delta Lake version control — which also lowered cloud spend. The optimizations are the unglamorous fundamentals: partitioning and file layout so you're not scanning terabytes to answer a narrow question, and sizing clusters to the workload instead of paying for idle capacity.
  • ✔ Pipeline-level data validation: At this scale, bad data is guaranteed, not hypothetical. Building validation checks directly into the transformation routines — catching source errors and logical bugs before they poison the study — is essential.

MLflow: closing the loop on models

Experience studies increasingly feed predictive models, and those models need governance of their own. I integrated MLflow for model versioning and tracking predictive model performance over time. This matters because a model that was well-calibrated last year may drift as experience emerges — think of how fast annuity lapse behavior shifted when interest rates spiked.

MLflow gives you an experiment record: parameters, metrics, artifacts, and model versions, all tracked. Combined with Delta Lake's data versioning, you get full lineage from raw data through to deployed model — exactly the reproducibility and audit trail that model-governance frameworks now demand. When a regulator or an internal validator asks "which data and which model version produced this assumption," you have a clean answer. Databricks' own MLOps guidance leans on precisely this pairing: Delta for transactional data versioning, MLflow for reproducible experiment tracking and model registry.

Don't over-engineer: the R and SQL Server reality

Not every problem needs the biggest hammer. Before and alongside the large-scale Databricks work, I've built variable annuity experience-study pipelines in SQL Server and R, and I led a Databricks proof-of-concept using sparkR and sparklyr that delivered scalable big-data pipelines with materially improved runtime over a legacy R-only approach.

That PoC is instructive. A lot of actuarial teams have deep R skills and years of R code. sparkR and sparklyr let them keep working in a familiar language while Spark handles the distribution underneath — so you get the scalability without forcing everyone to rewrite everything in a new language overnight. It's a pragmatic bridge, and it lowers the adoption barrier enormously. (Milliman's own MedInsight team saw a 5x–7x increase in the speed of delivering insights after moving healthcare data workloads onto a Databricks lakehouse — a reminder that these gains are real and repeatable when the migration is done well.)

What I'd tell a team starting out

Three things. First, invest in the data layer before the modeling layer — a clean, versioned, well-partitioned Delta foundation makes everything downstream faster and more trustworthy. Second, bake governance in from day one with MLflow and lineage, rather than retrofitting it when a regulator asks. Third, meet your team where they are; the sparkR/sparklyr route or a phased migration often beats a disruptive rewrite.

Done well, a modern experience-study pipeline doesn't just run faster — it makes your assumptions more credible, more reproducible, and more defensible. If you're wrestling with experience studies at scale, or want to modernize a legacy R or SQL Server process onto Databricks, that's exactly what I help firms build. Get in touch.