← All workEntry 1 of 6
  • Azure Functions
  • CosmosDB
  • SQL Server
  • Terraform

Healthcare Claims Ingestion Pipeline

Claims and remittance files from healthcare clients used to flow through three separate legacy pipelines. Now they share one, and the switch happened without customers seeing a thing.

§1

The problem

Three legacy pipelines processed healthcare claims and remittance files, each in its own way. The goal was a single performant pipeline with a robust interface, proper data validation and reliable persistence, while meeting healthcare privacy and hosting requirements. Existing customers also had to move to a new Azure subscription, credentials included. And because they depended on these pipelines every day, there could be no big-bang cutover.

§2

How it works

  1. SFTP intakeblob event per file
  2. Split + dedupeduplicates flagged
  3. Parse + processasync, SQL Server state
  4. CosmosDBpooled, partitioned
Fig. 1  Each file landing on SFTP raises a blob event. Functions upload and split it, flag duplicates and queue the parts; processing runs asynchronously with SQL Server holding its state, and documents land in CosmosDB, served by APIs.
  1. Customersnothing changes
  2. Routing appdecides per source
  3. Pipelines
    • Legacy, until migrated
    • New unified pipeline
    moved over one by one
Fig. 2  Strangler fig migration: a routing application sat in front of everything and kept sending each source to its legacy pipeline until the new one was ready for it. The pipelines merged one at a time while customers saw the same interface throughout.
  1. Azure DevOpsmigration scripts
  2. Key Vaultcredentials moved
  3. Load balancer
    • New subscription
    • Old: instant rollback
    switches backends
  4. Terraformnew infrastructure
Fig. 3  Moving customers to a new subscription: Azure DevOps scripts migrated each customer, including their Key Vault credentials, onto Terraform-built infrastructure. A load balancer switched backends, so rolling back was a single switch.

I led and designed every component. The migration came first, using the strangler fig pattern: an intermediate data-routing application took over the front door while still pointing at the existing legacy systems, and sources moved to the new pipeline one by one, so for customers it all looked the same.

Existing customers also moved to a new Azure subscription. Scripts in Azure DevOps ran the migration, the most critical part being the credentials held in Key Vault, and a load balancer switched backends so rollback was always one step away.

The new infrastructure was built with Terraform and designed for scale: many Azure Functions triggered by blob events, Service Bus and Storage Queues between stages, asynchronous processing with SQL Server holding the state, and Application Insights for telemetry. Documents persist in CosmosDB, where careful partitioning, caching, autoscaled and throttled RU/s, and data spread across pooled containers cut monthly CosmosDB costs by 50%. Separate APIs serve claims data, remittance data and import reporting.

§3

Results

30,000+files ingested every hour, from three legacy pipelines merged into one

MeasureResult
Files ingested30,000+ / hour
Streaming load1 GB / min
Records1M+ / day
Legacy pipelines3 → 1
Operational cost−40%
Monthly CosmosDB cost−50%

← and every customer moved to a new subscription without noticing

§4

What I learned

  • Replace a live system gradually: put a router in front (the strangler fig), then move traffic one piece at a time
  • Plan the rollback before the migration: with a load balancer in front, it was one switch
  • Credentials are the riskiest thing to migrate, so script it, verify it, and never do it by hand
  • With CosmosDB, partitioning and RU management decide the bill: getting them right halved it
  • Duplicate detection belongs at the front of a pipeline, not the end