Pharmacy Claims Platform

Building A Snowflake-Based Pharmacy Claims Platform For Real-Time Drug Coverage Lookup

~5B+ rows

Processed healthcare transaction data at terabyte scale

Subsecond API target

Built for pharmacy-counter lookups with low-latency reads

No PII stored

Match patients to tokenized claims using transient identity inputs

Building A Snowflake-Based Pharmacy Claims Platform For Real-Time Drug Coverage Lookup

Industry

Healthcare

Pharmacy claims and coverage workflows

Provided Services

Healthcare Data Management Solutions Snowflake Data Pipeline Automation

Data Platform Engineering

Healthcare Software Development

Tech Stack

Snowflake / AWS S3 / SingleStore / AWS Glue / REST API / Datavant / SingleStore / AWS Step Functions / Streamlit

Project duration

Enterprise Architect/SME in Healthcare Data, 2 SE, 1 DO, DM

[ client & product overview/ ]

Our client is a healthcare technology company whose platform processes US pharmacy claims data and delivers real-time coverage insights via API.

The goal is to identify a person’s drug coverage (PBM/plan) at the pharmacy counter, even when they present the wrong card or don’t know their plan, by matching transient PII (name, DOB, ZIP code, and gender) to tokenized claim histories.

~5B+ rows processed

The platform handled healthcare transaction data at terabyte scale.

2 product layers

Coverage lookup APIs and an MDM layer for insurance company hierarchy and entity resolution.

Looking to Build a Healthcare Data Product?

Start the Сonversation

Tap the button

Executive Summary

MEV partnered with a healthcare tech company to build a coverage intelligence platform. The platform processes tokenized pharmacy claims, cleans and enriches the data in Snowflake, and makes it available through low-latency APIs on AWS. It helps pharmacy and coverage workflows get fast, reliable lookup results from large volumes of claims data.

The first major challenge was architectural. Snowflake was a good fit for data ingestion, transformation, and warehousing, but it was not the right place to serve fast API lookups at the point of service. MEV designed a pipeline that prepares the data in Snowflake, moves the final curated data to AWS, and loads it into a database built for fast API reads. In earlier phases, the serving layer was Postgres. Later, the team migrated to SingleStore to improve reload speed and cost at scale.

The second challenge was healthcare data complexity. U.S. healthcare data is fragmented, business rules are often unclear or undocumented, and vendor schemas can drift with refreshes. MEV addressed this by codifying transformations, adding data-quality gates, explicitly handling schema volatility, and laying the foundation for an MDM layer to reconcile duplicate entities across sources.

As a result, the client gained:

A production-ready pipeline for tokenized U.S. pharmacy claims, with Snowflake for ETL and SingleStore powering low-latency API reads on AWS.
Privacy-safe identity matching, so temporary patient inputs could be matched against tokenized claims without storing raw PII.
A more scalable serving setup. Reload time dropped from 10–12 hours to 2–3 hours, and reload cost went from over $1,000 to about $50.
A foundation for healthcare-specific MDM and entity resolution across insurance companies, plans, and related entities.

[ challenges/ ]

The Core Challenges Solved

US healthcare topology is very complex. This makes it extremely difficult to work with healthcare datasets, and many business rules are neither clearly understood nor written down. It requires healthcare SMEs, usually from the business side rather than technical, to interpret the data and guide transformations. Even SMEs may disagree on how to handle certain data. Data volumes are also large, often in the terabyte range, so processing time and costs are high. All of this could lead to slow feedback cycles and, as a result, slow progress.

C1:

Coverage speed identification

The system must identify coverage quickly and reliably enough for live pharmacy workflows.

C2:

Privacy constraints without losing utility

The data arrived de-identified. The platform had to match patient inputs to tokenized claims without storing raw PII, using identity only during the API call.

C3:

Vendor data volatility

Vendor schemas and formats could change between refreshes, which created a risk of reactive fixes. Requirements also evolved as real data arrived and new edge cases appeared.

C3:

Scaling beyond initial sources

As more data sources were added, duplicate entities across datasets became harder to manage. The platform needed entity resolution and survivorship rules to support cleaner, more reliable data.

[ how we did it/ ]

Solution & Implementation

S1:

Built the Snowflake-centered ingestion and transformation layer

MEV designed the first layer of the platform around Snowflake. Snowflake handled raw and transformed data storage, plus the ordered transformation logic needed to prepare downstream outputs.

Key steps included:

Vendor ingestion through Snowflake Shares: Data from third-party vendors landed in vendor-native schemas inside Snowflake.
SQL-first transformation flow: Ordered SQL transformations for standardization, enrichment, projection, and aggregation.
Data-quality gates: The pipeline checked schema expectations, required fields, formats, row counts, and output integrity before export.
Golden record consolidation: In one stream, two or three client tables with different schemas were transformed into a unified golden-record structure.

S2:

Privacy-Preserving Identity Matching

The privacy model works like this: claims data stays tokenized at all times. When an API request comes in, the system briefly accepts only the minimum patient details needed for matching: name, date of birth, ZIP code, and gender. It sends that data through the same Datavant tokenization process to generate matching tokens. Those tokens are then compared against tokenized claims history to identify likely drug coverage. Raw PII is discarded immediately and never written to disk or stored in the system.

Key steps included:

Export from Snowflake to AWS: After transformation, curated outputs were exported from Snowflake to S3.
Transactional serving layer: The data was loaded into a database behind the API because Snowflake was too slow for live lookup use cases. Earlier versions used Postgres. Later, the serving layer moved to SingleStore.
Transient tokenization at request time: The API accepted minimal PII, converted it to comparable tokens using the same tokenization mechanism, matched it against tokenized claims, and discarded the raw input.
Security baseline: The flow used encryption in transit and at rest, least-privilege access, and environment isolation. The incoming data was de-identified before receipt, which put this specific flow outside HIPAA scope while still requiring strong security practice.

S3:

Healthcare MDM & Entity Resolution foundation

To support scaling beyond the initial vendor sources, we designed the foundational approach for a healthcare Master Data Management (MDM) layer. The goal is a single, authoritative directory of payers, PBMs, sponsors, and plans, reconciled across all sources into golden records.

The matching approach is staged:

Deterministic rules using exact and canonicalized keys and standard code sets.
Probabilistic / fuzzy matching on string similarity, addresses, and identifiers with tunable thresholds.
ML-assisted clustering for gray-zone candidates, with mandatory human review and adjudication feedback loops to train a domain-tuned model over time.

They needed a scalable, strategic solution that would both ingest unstructured data and surface relevant insights for business planning—without overwhelming account teams.

[ results/ ]

As a result, the client launched a healthcare data platform that processes tokenized claims at terabyte scale and supports live coverage lookup.

R1:

Built a healthcare coverage intelligence platform capable of processing healthcare transaction data at terabyte scale.

R2:

Enabled real-time coverage lookup through low-latency APIs for pharmacy-counter use cases.

R3:

Created a privacy-preserving matching flow that allowed patient lookup against tokenized claims without persisting raw PII.

R4:

Reduced reload cost from well over $1,000 to around $50 for the same load.

R5:

Laid the groundwork for healthcare-specific MDM and entity resolution across payers, PBMs, sponsors, and plans.

[ portfolio/ ]

Related Case Studies

Aristotle Circle

edtech

A Full-Service Internal and Client-Facing Platform Built from the Ground Up

PillowPH

healthcare

MEV freed up time and locked in secure, HIPAA compliant data management

Pursuant

media

MEV solution facilitates reporting for Pursuant, a fundraising and marketing agency, and enables future growth

View All

[ contact us/ ]

No Sales Pitch — We’re Here to Dig Into Your Challenges

Yeah, it’s a boring form. The call though, will make a lot of sense.

The Ball’s In Our Court Now

We’ll get back to you right after reading your message. Thank You!

Oops! Something went wrong while submitting the form.

Or just Schedule a Call with our CEO

Tap the button and schedule a call

Preferences

Privacy is important to us, so you have the option of disabling certain types of storage that may not be necessary for the basic functioning of the website. Blocking categories may impact your experience on the website. More information

Accept all cookies