Case Study: Automating a Monthly Healthcare Analytics Workload

At a glance

Client: A large U.S. health insurance provider (under NDA — anonymized)
Industry: Healthcare payer
Challenge: A business-critical monthly analytics workload run entirely by hand — slow, fragile, and dependent on specific people.
What we did: Migrated the logic to PySpark / Spark SQL on Databricks, organized it by line of business, parameterized it, and orchestrated it end-to-end with Azure Data Factory.
Outcome: ~14 hrs → ~4.5 hrs (≈68% faster), zero manual runs, far greater reliability.
Stack: Azure DatabricksPySparkSpark SQLAzure Data FactoryPower BISnowflake

The backgroundA report that ran on someone remembering to run it

Our client relies on a monthly analytics process that segments its member population across several lines of business. The output feeds executive and operational dashboards that analysts and data science teams use to monitor member health and service quality.

The logic was sound — but the way it ran was not built to last. Every month, the workload existed as a long chain of SQL queries that had to be executed manually, in sequence, by hand. Someone kicked off each stage, waited, checked the output, and moved to the next. Every single month.

The challengeSlow, fragile, and one person deep

Slow. A full monthly run took roughly 14 hours of hands-on, babysat execution.
Fragile. The job sometimes failed partway through, delaying dashboards and forcing a manual restart from the point of failure.
Person-dependent. It relied on specific people knowing the exact run order — a single point of failure for a business-critical report.
Hard to change. Table names were hard-coded throughout, so even a small source change meant hunting across many queries.

The solutionMigrate the logic, then automate the run

We delivered the rebuild in two phases — migration, then automation — keeping the client's existing data platform in place as the system of record.

1
Discovery
Mapped the manual SQL and source tables
2
Migration
Rewrote logic as PySpark / Spark SQL
3
Automation
Built the ADF driver → orchestrator pipeline
4
UAT
Validated output against the source
5
Handover
Scheduled, monitored, documented

01Migrated the logic to Databricks

We rewrote the legacy SQL as PySpark and Spark SQL on Azure Databricks. Not a lift-and-shift: source tables didn't always map one-to-one across platforms (different naming, different granularity — data split by year in one place existed as a single table in the other), so each query was re-mapped and validated against the source rather than blindly translated.

02Organized the work around lines of business

The workload spans five lines of business — covering commercial/employer coverage, Medicare, Medicaid, and dual-eligible populations. Each got its own dedicated Databricks notebook with labeled, documented commands, making the pipeline easy to read, troubleshoot, and run selectively.

03Made it metadata-driven with a control table

Instead of hard-coding names across dozens of queries, we made the pipeline configuration-driven. A control table holds every catalog, schema, and table name, every notebook path, the cluster/infra settings, and the connection details — all read at runtime. Two lookup steps pull this config before any processing starts, so a source or environment change is a single row edit, not a hunt-and-replace across code. The same table drives which lines of business run, enabling selective monthly executions.

config_utility.pyillustrative — not client code

import json

# Config is looked up from a control table by the pipeline,
# then passed into the notebook as a single JSON parameter.
class Config:
    def __init__(self):
        raw = dbutils.widgets.get("config_details")
        self._cfg = json.loads(raw) if raw else {}
    def get(self, key):
        return self._cfg.get(key)

cfg = Config()

# Every catalog, schema, table and notebook path resolves
# from config — nothing hard-coded inside the notebooks.
claims_tbl = f"{cfg.get('catalog_claims')}.{cfg.get('schema_claims')}.{cfg.get('tbl_claims')}"
run_date   = cfg.get("run_date")

04Tuned for Spark performance

We optimized the rewritten workload for Spark's distributed execution — a large part of how the monthly runtime came down so sharply.

05Orchestrated everything with Azure Data Factory

An Azure Data Factory pipeline runs the whole thing on a fixed monthly schedule using a driver → executor pattern: a driver reads control/config inputs and decides what to run; executor notebooks process each line of business in parallel, then results are merged, segmented, and written back out. Because it's configuration-driven, the team can run only the lines of business they need and disable the rest — no code changes.

06Validated with UAT

Before cutover, user acceptance testing confirmed the new output matched the trusted source numbers line for line — so the client could adopt automation with full confidence.

Fig 1 — From a hand-run SQL chain to a scheduled, self-healing, parallel pipeline.

Under the hood, the orchestration follows a driver → orchestrator pattern. A lightweight driver pipeline holds the per-environment settings and calls the orchestrator, which first reads its configuration from the control table, then fans out to the line-of-business notebooks in parallel before merging, segmenting, and writing the final tables. Every notebook has built-in timeouts and automatic retries, so a transient failure self-heals instead of derailing the whole run.

Fig 2 — Driver → orchestrator pattern: control-table lookups, five parallel LOB notebooks, then merge, segmentation, final build, and policy segregation.

Under the hoodInside one line-of-business notebook

Each line-of-business notebook is a long chain of steps rather than a single query. It builds the eligible member population — deduplicated to one row per member with window functions and coverage-date logic — then washes it against claims for a rolling ~14-month window. From there it tags each claim across several clinical categories (drawing reference code sets from shared “master” tables) and rolls those tags up from claim level to member level. A precedence hierarchy gives each claim a single primary tag, exclusion rules drop invalid or out-of-scope records, and members are finally bucketed by claim volume into segments. Every intermediate result is written as a Delta table and reused downstream — which is what makes a pipeline this deep debuggable step by step and easy to re-run.

claim_tagging.pyillustrative — not client code

# Roll a claim-level flag up to the member, keep one row,
# then bucket members by claim volume.
tagged = spark.sql(f"""
  WITH flagged AS (
    SELECT *,
      MAX(acute_flag) OVER (PARTITION BY member_id)      AS acute_member_flag,
      ROW_NUMBER()    OVER (PARTITION BY member_id, claim_id
                            ORDER BY service_date)       AS rn
    FROM {cfg.get('tbl_claims_tagged')}
  )
  SELECT *,
    CASE WHEN claim_count = 1              THEN '1 claim'
         WHEN claim_count BETWEEN 2 AND 3  THEN '2-3 claims'
         WHEN claim_count BETWEEN 4 AND 10 THEN '4-10 claims'
         ELSE '10+ claims' END AS claim_bin
  FROM flagged WHERE rn = 1
""")

Security & complianceBuilt for protected health information

Because this workload handles protected health information (PHI), security was designed in from the start rather than bolted on. Data was governed through Unity Catalog, with role-based access control, column-level masking, and row-level filters so each consumer only sees what they're permitted to. PHI was de-identified in line with HIPAA-aligned handling, data was encrypted in transit, access was audit-logged, and all credentials were held in Azure Key Vault rather than in code.

Changes moved through separate Dev, Stage, and Production environments. Migration and optimization were built and tested in Dev; Stage ran the full end-to-end process and validated results against historical figures; and in Production the output was first written to a sample table and reconciled against the trusted historical data before the final segmentation table was promoted, written to Snowflake, and served to Power BI. That staged, validation-first path is how a PHI workload gets automated without ever putting member data or reporting accuracy at risk.

The resultsFaster, hands-off, and dependable

~68%

faster monthly runtime

4.5h

monthly run, down from ~14 hrs

51M

member records merged per run

tables loaded (7 refreshed) per run

~100+ SQL queries migrated to PySpark / Spark SQL — roughly 15 per line of business, ~20 in the merge step, and ~6 in segmentation.
~51 million member records processed per run (≈20M / 15M / 7M / 5M / 4M across the five LOBs), refined to ~22 million after segmentation rules and policy filters.
41 tables loaded, 7 refreshed per monthly run.
Reliability: the old sequential job ran ~14 hrs and sometimes failed partway, forcing a restart that pushed data to the next day. The automated pipeline now completes in ~4.5 hrs within the same day, with retries built in — so analysts, business analysts, and product owners get dependable monthly data straight into Power BI.
Cost & effort: meaningful compute-cost reduction and far fewer support hours — no overnight waits or full re-runs on failure. Ongoing upkeep is essentially just updating policy/filter rules in the control table.

Work with us

How Gen2 Analytics can help

If your team is running business-critical data work by hand — or fighting long, fragile batch jobs — we modernize and automate it on Azure Databricks, orchestrated with Azure Data Factory and served through Power BI, with governance built in from day one.

Start a Conversation

Back to all insights

From a 14-Hour Manual Job to a Fully Automated Pipeline