Why flat-file rosters break: CSV over SFTP in healthcare data pipelines

Part 1 of 7 — Roster API Series: migrating member-file ingestion from CSV over SFTP to a FHIR API.

If you work in value-based care, you have shipped a roster file. A CSV with one row per patient, dropped on an SFTP server every month, picked up by a vendor job at 2 a.m. It defines who the vendor processes: which patients get risk insights, quality gap reports, care management outreach. It also, very often, defines what you get billed for.

It works. Until it doesn’t. This series takes that pattern apart and rebuilds it as an API, using a public standard (HL7 Da Vinci ATR) as the target. This first post is about why the flat-file pattern breaks — not in theory, but in the specific ways I’ve seen it break.

The archetype

Strip away vendor branding and almost every roster integration looks like this:

┌──────────────┐   export    ┌─────────┐  SFTP put   ┌──────────────┐  daily cron  ┌────────────┐
│ Source (EHR, │ ──────────► │ roster  │ ──────────► │ vendor SFTP  │ ───────────► │ vendor app │
│ payer feed,  │             │ .csv    │  key auth   │ /inbound/    │   pickup     │  (cohort)  │
│ spreadsheet) │             └─────────┘             └──────────────┘              └────────────┘

The file usually carries:

Column	Purpose
`mrn`	Preferred identifier — the source system’s medical record number
`first_name`, `last_name`, `dob`	Fallback identity when no MRN is present
`plan_type`	Which program/risk model applies (Medicare Advantage, ACO, Medicaid, commercial)
per-product flags	Booleans that enable or hide a product for that patient

And the contract around the file is almost always the same three rules:

One file, every patient. Aggregate everything into a single CSV.
Last file wins. Each upload replaces the previous cohort entirely.
Pickup is scheduled. The vendor polls the folder on a fixed cadence.

Each rule is reasonable on its own. Together they produce a system with no error channel.

Failure mode 1: full replace is a loaded gun

“Last file wins” means the cohort is a pure function of the most recent upload. That has consequences:

A partial file is a mass disenrollment. An export that times out halfway produces a valid CSV with 40% of your patients. The vendor ingests it, and 60% of your patients silently drop out of the cohort. No error — the file was well-formed.
An empty file is a valid file. Header row, zero data rows. Same result, worse.
Two sources can’t share a roster. If clinic A and clinic B both upload, whichever lands last defines the whole cohort. So someone ends up building a merge job upstream, which is now a second point of failure.

The root problem: the protocol has no way to express intent. “Remove this patient” and “I forgot this patient” are byte-identical.

Failure mode 2: identity by name + DOB

When the MRN is missing, the vendor falls back to matching on first name, last name and date of birth. That’s a deterministic match on three fields that are:

Not unique. Common names plus a shared birthday collide more often than you think in a panel of 100k.
Not stable. Name changes, hyphenation, JOSE vs JOSÉ, O'BRIEN vs OBRIEN.
Not formatted consistently. 1958-03-07, 03/07/1958, 7/3/58 — and someone opened the file in Excel before upload.

Even the MRN has a trap: an MRN is only unique within the system that issued it. A CSV column called mrn with no namespace is ambiguous the moment you have two EHRs. FHIR solves this with Identifier.system + Identifier.value; a CSV has no place to say which system issued the number.

Failure mode 3: no acknowledgement

You put the file. SFTP returns success. That’s the last thing you know.

Was the file picked up? You find out when someone complains.
Did it parse? Did 12 rows fail validation? There’s no response channel, so there’s no row-level feedback.
Which version is the vendor currently using? You can’t ask.

In practice teams compensate with email threads (“did you get Tuesday’s file?”) and a support ticket per incident. That’s a protocol, just a bad one.

Failure mode 4: no deltas, no audit

Because each upload is a full snapshot, the change — who joined, who left, when — is not data anyone sends. If you need it (and you do: attribution changes drive billing, outreach and quality denominators), you have to diff snapshots yourself:

import csv

def load(path: str) -> dict[str, dict]:
    with open(path, newline="") as f:
        return {row["mrn"]: row for row in csv.DictReader(f)}

prev = load("roster_2026-09.csv")
curr = load("roster_2026-10.csv")

added   = curr.keys() - prev.keys()
removed = prev.keys() - curr.keys()
changed = {k for k in curr.keys() & prev.keys() if curr[k] != prev[k]}

print(f"+{len(added)}  -{len(removed)}  ~{len(changed)}")

That works only if you kept both files, only if the MRN is populated and stable, and it tells you nothing about why someone was removed. The vendor runs the same diff on their side with their own logic, and the two diffs disagree at month-end reconciliation.

Failure mode 5: the security model is a key file on a laptop

SSH key authentication is fine cryptography. The operational reality is less fine:

One long-lived key pair per integration, often generated on someone’s machine.
No scopes. The key can write anything into that folder.
Rotation is a manual, cross-company ticket, so it doesn’t happen.
The inbound folder holds PHI at rest on a server you don’t control, with retention you don’t know.

What “better” needs to look like

Flip each failure mode and you get the requirements for the rest of this series:

Failure	Requirement	Covered in
Full replace	Explicit add/remove operations; inactive instead of deleted	Part 3
Fuzzy identity	Namespaced identifiers; structured patient resources	Part 2
No acknowledgement	Synchronous responses with row-level outcomes; async jobs with status	Parts 3–4
No deltas/audit	Changes as first-class data; `_since` queries	Parts 3–4
Static keys	Short-lived, scoped tokens from signed assertions	Part 5
Polling	Push notifications when the list changes	Part 6
Big-bang cutover	Dual-run and reconcile	Part 7

None of this needs inventing. HL7’s Da Vinci Member Attribution (ATR) List implementation guide already defines a FHIR-based model and API for exactly this exchange — and CMS uses the same pattern in production for ACO beneficiary lists in the Beneficiary Claims Data API (BCDA). That’s the target architecture.

The code in this series is Python + FastAPI, with synthetic patients only (Synthea). No real rosters, no vendor-specific schemas.

Next: Part 2 — Modeling a roster as a FHIR Group

References

25 Oct 2026 · #healthcare #fhir #dataengineering #rosterapiseries

GLB_