This five-part series on building reverse ETL has been building toward a question it never had to answer head-on: not whether the modern data platform can generate and deliver a data product, but whether it should own the delivery. HL7v2 was the first time the answer split: generate the message in the warehouse, but buy the interface engine for the last mile. Reverse ETL for EDI is where that split becomes the entire story.
Here’s the case. A provider’s claims sit curated in the gold layer and need to reach CMS as X12 837s, the electronic claim format. The warehouse can generate those 837s, validate them, push them over SFTP, and ingest the acknowledgments that come back. I know because I built a proof of concept that does exactly that, end to end.
Having built it, I’d still point almost anyone to a clearinghouse for most of it. This post is the honest accounting of why.
The Shape: The Loosest Coupling, the Widest Async Gap
EDI over SFTP is Part 2’s mechanism four, the far end of the coupling spectrum. There’s no API to call and no broker to publish to. You write a file, drop it on an SFTP endpoint, and walk away. FHIR gave us the tight end, an API write with a per-resource response. HL7v2 gave us the streaming middle, with a synchronous ACK once past the broker. EDI is looser than any of them. The coupling is a file on a server.
That would make it the simplest post in the series, except for what happens after the drop. The acknowledgment isn’t a return value or a handshake. It’s a separate inbound file that arrives later, on the receiver’s schedule, and there’s more than one. A 999, if the receiver produces it, says whether the interchange was accepted or rejected at the envelope level. Later, a 277CA says whether each individual claim was accepted or rejected for adjudication. Because the 999 is conditional, your pipeline has to handle both “999 arrived, act on it” and “no 999, move on” before it reaches the 277CA and any remediation. The async boundary HL7v2 introduced is at its widest here. The verdict is a whole second ingestion pipeline, branching, and running on someone else’s clock.
Generating the 837 is warehouse work the platform does well. Everything downstream of the file drop is a workflow-heavy operational system that happens to be kicked off by a file. The first half is reverse ETL. The second is a claims-submission workflow spanning multiple pipelines.
The Loop: Generate, Submit, and Wait for Two Answers
Start with the half that works. Generating an 837P from curated claim data is the same move as generating a FHIR resource or an HL7v2 message, pointed at X12. The gold layer has the claims, and the transform emits conformant 837P interchanges, with the identifiers and code sets CMS expects. Model one provider’s raw claims to a common 837P model and the 837D becomes a variation on the same asset, not a separate build. This is genuinely good warehouse work, and I’d encourage anyone to do it.
Validation comes next, and it stays the series’ discipline: validate before it leaves. An 837 is dense and CMS is strict, so the PoC drove validation from configuration, with core rules for every claim and overrides for the wrinkles of a specific submission, all checked against the companion guide before a file is written. Catch a malformed interchange in your own pipeline, not in a rejection hours later. That much is still recognizably reverse ETL, and it’s the last point where things stay simple.
Then you submit, and the shape changes. Submission itself is modest: a Snowpark procedure with an external access integration writes the file and pushes it to CMS over SFTP, with a small control plane of tables tracking what was sent. What follows is not modest.
The acknowledgments come back as inbound files on the same SFTP endpoint on CMS’s schedule. The 999, if it comes, and the 277CA each amount to a full ingest-and-transform pipeline: parse the response, extract the verdict, update claim status, and kick off remediation for anything rejected. Streams and tasks can orchestrate the chain, so a file advances from submission to status update without a human holding its hand.
The series’ correlation theme peaks here. In HL7v2, one field, MSH-10, tied a verdict back to the message that earned it. EDI needs more, because verdicts come back at two levels. The interchange and group control numbers you assign at generation, ISA13 and GS06, match a 999 to the exact envelope it acknowledges. The claim-level identifiers in each claim match a 277CA line to the claim it accepted or rejected. Correlation is no longer a thread, it’s a hierarchy, and you own every level. The looser the coupling, the more correlation machinery falls to you.
Buy the Last Mile
Everything in that loop is buildable. I built it, and it worked. The question isn’t can the platform own EDI submission end to end. It’s whether it should, and for almost everyone the answer is no.
Look at what the second half actually is. Not the generation, which is yours to keep, but everything past the file drop: the SFTP control plane, submission orchestration, conditional-999 handling, 277CA ingestion, the claim-status state machine, remediation, and validation-rule upkeep. That’s not a delivery mechanism. It’s a claims-submission operation that runs on someone else’s clock, breaks in someone else’s format, and demands maintenance every time a payer revises a rule.
A clearinghouse exists to own exactly that. It’s the EDI equivalent of Part 4’s interface engine, built for the last mile so you don’t reimplement submission, acknowledgment parsing, and payer-specific quirks yourself. Hand it the 837 and it handles submission, acks, payer connections, and format churn. Your pipeline stops at a clean handoff, the way the HL7v2 pipeline stopped at the broker.
Put the two side by side and the argument is visible. The left edge is identical: generate the 837P, validate it before it leaves. Everything to the right of validation either collapses into one clearinghouse box or expands into the async loop you own. Same input, same good work up front, then a choice about how much operational surface to carry.
There’s an honest exception, and my PoC sat close to it. If you have exactly one stable trading partner, CMS and nothing else, with no multi-payer fan-out and no format variation, the last mile is about as small as it gets, and building it is defensible. Even in my case the workflow was heavy enough that I’d still reach for a clearinghouse in most real engagements. The moment a second payer appears with its own quirks, the build-it math tips over hard, and the clearinghouse that looked optional becomes the obvious answer.
Build the Left Edge, Buy the Right
That’s the whole series in a phrase. Across four formats, the pattern held: curated data in the gold layer, generated into the receiver’s language, delivered by the coupling that fits. FHIR gave us the tight API write and the loose file on a stage. HL7v2 gave us the streaming middle and the async boundary. EDI gave us the loosest coupling and the widest gap, and drew the line the series was always heading toward.
The line is this: generation is where the modern data platform earns its place in these workflows. Turning curated warehouse data into a conformant FHIR resource, HL7v2 message, or X12 interchange, validated before it leaves, is real, durable value the platform delivers better than the alternatives. The last mile is a different question every time. Sometimes you own it, an API write, a file on a stage. Sometimes you buy it, an interface engine, a clearinghouse. The right move isn’t proving you can build the whole thing. It’s knowing which half is worth building and handing off the rest. Because sometimes, just because you can build it doesn’t mean you should.
If you’ve got curated data that needs to reach the systems your business actually runs on, in whatever dialect they speak, that’s the conversation we have every week. Reach out, and we’d be glad to help you think it through.
