Merciven
Work  /  Data engineering

Pharmacy data feed migration

A 165-column patient status and dispense feed moved from SQL Server stored procedures to Databricks, and proved row for row against the legacy output before cut-over.

merciven.co/work/pharmacy-feed-migration/live/LiveOpen ↗
The Pharmacy data feed migration app, running with sample data

The problem

A partner hub receives patient status and dispense updates several times a day. The file came from a chain of SQL Server stored procedures, thousands of lines of status mapping, and it had to move to the Databricks lakehouse without a single row or value changing.

Objectives

  1. Rebuild the feed on the enterprise data warehouse tables
  2. Send exactly the rows legacy sends, with the same values
  3. Prove it without copying patient data out of the secure environment
  4. Package it in the team's function-based notebook pattern

Process

  1. DiscoverTraced the live legacy chain and found that the obvious source table had stopped a year earlier.
  2. DefinePinned the business rule (enrolled and consented patients) and tested it against a year of legacy rows.
  3. BuildA modular v1 on the warehouse: one view per subject, 165 columns in spec order, built-in checks.
  4. ProveKey-level reconciliation in production, then a column-by-column comparison using SHA-256 fingerprints.

Who it's for

Partner hub

Needs accurate, on-time patient status to support patients on therapy.

Program management

Needs proof the new feed matches before the old one is switched off.

Challenges

  • Business logic spread across four stored procedures
  • A test environment missing data for some patients, which looked like a pipeline bug
  • Patient data that can't be exported for side-by-side comparison

What we built

Modular views

Population, dispense, prescription, prescriber, insurance and claims each in their own view, so a reviewer can check one subject at a time.

Rule proven on history

The enrolment and consent rule was checked against tens of thousands of legacy rows before any code was written.

Row reconciliation

Every legacy record ID is matched to the new output: missing, extra and changed rows are counted, not eyeballed.

Column fingerprints

Each column is hashed on both sides, so whole columns are compared without moving any values.

Flow

  1. Status events (warehouse)
  2. Enrolment + consent filter
  3. Dispense, Rx, prescriber and insurance joins
  4. 165-column file
  5. Row reconciliation
  6. Column fingerprints
  7. Partner hub

Output

legacy_ids   legacy_sent   new_before_filter   new_after_filter
198          163           198                 163

result: 163 match  |  35 correctly left out  |  0 missing  |  0 extra
Production reconciliation for one delivery day: every row legacy sent, and only those. IDs and counts only.

Tools & languages

  • Azure Databricks
  • Spark SQL
  • Python
  • SQL Server
  • T-SQL
  • Delta Lake
  • Azure DevOps

The code

The feed's code belongs to the client. The open-source sproc-to-lakehouse tool shows the same migrate-then-prove approach on synthetic data.

The repository is private. We're happy to share it with a hiring team or walk you through it on a call.

Open the live demoRequest a code walkthrough

Samples and demos use invented data. No client code or client data is shown.