A Pharmacy Benefit Administrator Brought Claims In-House Without Building Delivery Into Snowflake

A pharmacy benefit administrator serves employer groups, unions, public sector organizations, and health systems across the United States, pairing the pricing leverage of a pharmacy benefit manager with the concierge service of a benefits administrator, backed by its own in-house clinical team. It is a regulated business by design, operating under HIPAA, holding URAC accreditation for pharmacy benefit management, and carrying a completed SOC 2 audit.

Strip away the clinical and financial layers, and a pharmacy benefit is a stream of files. Eligibility files arrive from each employer client and the platforms attached to it. Claims, accumulator, invoice, and report files move between the administrator, third-party administrators, carriers, and brokers. Nearly all of it carries PHI. So when the administrator decided, within its first year, to run claims processing itself rather than leave it outside the company, all of that traffic had to converge on the administrator. Files.com became the exchange layer between Snowflake and that ecosystem, turning each new counterparty route into configuration rather than a new delivery pipeline.

Claims Came In-House and the Files Came With Them

The administrator had already made one decision about file exchange. At launch, it wanted one governed path for client claim data, with nothing moved by hand at the daily claim volumes the company expected to take on. It stood up Files.com as its intake point: clients deliver claim files over SFTP, a sync lands them in AWS S3, ETL runs there, and the data loads into Snowflake, all under a Business Associate Agreement.

Bringing claims processing in-house changed the shape of the problem. An intake point receives files. A claims operation exchanges them, in both directions, with everyone. The company now had to return claims, invoice, and report extracts to TPAs, carriers, and brokers, and take in eligibility, accumulator, and response files from vendors and clients. The ecosystem is large and it is not uniform: more than 100 employer clients, each with its own folder structure and routing rules, and each with its own brokers and vendors attached. A client that only sends eligibility might need a single connection. A client whose invoice and claims files flow back out can need many separate users set up for that one relationship.

The data engineering side was ready. Snowflake jobs generate every per-client report and extract. What Snowflake cannot do is deliver them.

Delivery, in this ecosystem, means fanning one generated file out to several destinations, preserving each counterparty's fixed filename conventions because their automated pickup jobs break on anything else, encrypting with a partner's PGP key where the partner mandates it, and connecting out to dozens of external SFTP servers, since many counterparties require the administrator to push files to their systems. Building all of that inside the warehouse was the expensive path the administrator avoided. The alternative was worse: people hand-moving PHI-bearing files across dozens of counterparties at daily claim volume. And the clock was running, because a claims operation cannot start until its connections exist.

The layer between the warehouse and the ecosystem had to take a batch of extracts from S3 and route each file to the right destinations without renaming it, speak SFTP in both directions, encrypt and decrypt with PGP inside the flow, keep every client's structure separate, carry PHI under a BAA, and make the next client a matter of configuration rather than development. The administrator made Files.com that layer, expanding the intake site it had run since launch into a bidirectional exchange hub.

What Snowflake Generates, Files.com Delivers

The boundary between the two systems is deliberate. Snowflake is responsible for producing the file, and it never learns where the file is going.

Snowflake jobs write their extracts to S3. A Files.com Sync carries each batch into a single landing-zone folder. From there, Files.com Automations take over: filename-pattern and regex rules match each file and copy it to its destinations, one file to several per-client and per-vendor folders, with PHI-bearing files routed separately from non-PHI and every filename preserved. The estate has grown to hundreds of automations and sync flows, most of them built in a single year, spanning the folder structures of more than 100 clients.

Inbound traffic runs the same pattern in reverse. Files.com pulls from vendors' SFTP servers or receives their uploads, decrypts with PGP where a partner encrypts, and lands the files in S3 for ETL into Snowflake. Cadences vary by partner, and short retention windows can require Files.com to poll a vendor's server several times a day for the newest response file.

Files.com is deliberately the exchange layer rather than the archive. Retention on the platform is short-term, and S3 holds the durable copy of everything that passes through.

Two Paths to a Counterparty

Most counterparties log in to the administrator's Files.com site to drop off and collect their files, each seeing only the folders that belong to them. Those that require the administrator to come to them receive deliveries over dedicated IP addresses they can whitelist in their own firewalls.

With hundreds of automations running, the real risk is a silent failure. The administrator routes automation failure and skip events through Files.com Event Channels, so a run that fails raises an alert instead of surfacing days later as a file a counterparty never received.

A Claims Operation Absorbed as Configuration

With the hub in production, the administrator runs its entire claims exchange as a repeatable pattern rather than a pile of one-off deliveries.

• Bringing claims processing in-house added dozens of outbound SFTP connections to TPAs and vendors, most of them within months, each one configuration on the existing hub rather than a development project.
• Claims, eligibility, invoice, and report files now move between Snowflake and the partner ecosystem with no one touching them, encrypted where partners mandate it and logged end to end under the BAA.
• Onboarding a new employer client is a folder structure, credentials for its brokers and vendors, and automations that follow the established pattern, which matters because new clients start together on January 1.
• The whole estate is run day to day by a small data team: hundreds of automations, outbound connections that nearly doubled in a year, and the folder structures of more than 100 clients.

The immediate result was the claims pivot itself. The larger one is that growth stopped being an engineering event. A year in which the counterparty footprint nearly doubled changed the automation count, not the architecture.

The Warehouse Never Learns a Destination

Today, Snowflake's job ends when the extract lands in S3. Files.com owns everything after: which counterparties get the file, what name it arrives under, whether it travels encrypted, and how it gets there. That boundary is what lets a company that only recently entered the market exchange PHI with the ecosystem attached to more than 100 employer clients without a delivery team. When a new client signs, a data engineer writes filename patterns and an analyst provisions the client's brokers and vendors. Nobody builds a pipeline, and nobody moves a claims file by hand. The administrator did not teach its data warehouse to manage files. It put Files.com next to the warehouse and let each system do the one job it was built for.