How MEV Designed a Snowflake-to-API Architecture for Real-Time Pharmacy Coverage Lookup

Client:

Healthcare technology company

Industry:

Healthcare / Pharmacy claims and coverage workflows

Focus:

Designing a production-ready architecture that separates claims data processing from low-latency API serving

Technologies:

Snowflake / AWS S3 / SingleStore / REST API / AWS Step Functions

[ about the client/ ]

The client is a healthcare technology company building a platform for pharmacy claims and drug coverage lookup workflows.

Their product needed to process large volumes of tokenized U.S. pharmacy claims data and make coverage insights available through APIs. The platform was designed to help identify a patient’s drug coverage at the pharmacy counter, even when the patient presents the wrong card or does not know their plan. The system handled more than 5B healthcare transaction rows at terabyte scale.

What Needed to Be Done

MEV was building the product from scratch. The job was to design the right architecture before the platform moved into real-world pharmacy-counter usage.

Snowflake was the right foundation for the early product phase. It gave the team a central place to ingest vendor claims data, transform inconsistent healthcare datasets, validate outputs, and shape the data model while business rules were still evolving. But the product also needed to support live API lookup workflows. That required a different access pattern than Snowflake was meant to serve.

So MEV needed to design a clean split:

Snowflake for ingestion, transformation, enrichment, validation, and warehousing.
AWS S3 as the handoff layer for curated lookup-ready outputs.
A transactional serving database behind the REST API for fast reads.

Why It Mattered

Pharmacy-counter lookup is time-sensitive. The platform could not depend on a warehouse-style query path once it moved toward real-world API usage.

The architecture needed to support two very different jobs:

Data processing: Large-scale claims ingestion, cleanup, transformation, enrichment, validation, and refresh logic.
Data serving: Fast lookup responses through an API when a coverage check is requested.

Keeping those responsibilities separate made the platform more practical to scale. Snowflake could remain the source of structured, validated claims outputs, while the serving layer could be optimized for low-latency reads.

This also reduced the risk of forcing one system to do two jobs badly: heavy data processing and real-time API serving.

What MEV Built

MEV designed a Snowflake-centered data processing layer and a separate AWS-based serving path for pharmacy coverage lookup. Vendor pharmacy claims data landed in Snowflake through Snowflake Shares. Inside Snowflake, MEV built ordered SQL transformations for standardization, enrichment, projection, aggregation, and output preparation. The pipeline also included data-quality gates checking schema expectations, required fields, formats, row counts, and output integrity before export.

Once the curated data was ready, it was exported from Snowflake to AWS S3 and loaded into a serving database behind the API. Earlier architecture phases used Postgres as the serving layer. Later, the serving layer moved to SingleStore to improve reload speed and cost at scale. Reload time dropped from 10–12 hours to 2–3 hours, and reload cost went from over $1,000 to about $50.

Technical Flow

01:

Vendor claims data lands in Snowflake

Third-party pharmacy claims data arrives through Snowflake Shares and lands in vendor-native schemas.

02:

Snowflake handles data processing

MEV uses Snowflake to store raw and transformed data and run ordered SQL transformations for standardization, enrichment, projection, and aggregation.

03:

Data-quality gates validate outputs

Before export, the pipeline checks schema expectations, required fields, data formats, row counts, and output integrity.

04:

Curated lookup data is exported to AWS S3

Once the data is transformed and validated, curated outputs move from Snowflake to AWS S3.

05:

Serving database is loaded

The lookup-ready data is loaded into a transactional database built for API reads. Earlier phases used Postgres; later, the architecture moved to SingleStore.

06:

REST API serves coverage lookup requests

The API reads from the serving database instead of querying Snowflake directly, allowing the platform to support low-latency lookup workflows.

07:

Snowflake remains the processing layer

Snowflake continues to handle large-scale ingestion, transformation, warehousing, and validation, while the API-serving layer handles fast operational access.

Outcome

The client gained a production-ready architecture for pharmacy coverage lookup.

The platform did not treat Snowflake as the live serving layer. Instead, MEV designed a cleaner separation of responsibilities:

Snowflake for claims ingestion, transformation, enrichment, validation, and warehousing.
AWS S3 for curated data handoff.
SingleStore for low-latency serving behind the REST API.

This made the system better suited for pharmacy-counter workflows, where coverage lookup needs to happen quickly and reliably.

This case is part of the full case study

Building a Snowflake-Based Pharmacy Claims Platform for Real-Time Drug Coverage

[ portfolio/ ]