Part 3 of this reverse ETL series took a computed clinical score, LACE readmission risk, and delivered it into the chart as a FHIR resource.
That worked because the receiver spoke FHIR. Unfortunately, many of the systems you need to reach don’t. A care-management platform, an older registration system, or a service that has subscribed to the same ADT feed for fifteen years speaks HL7v2. It expects to be told, live, in a pipe-delimited message, the moment something changes.
So this is the real-time case. A patient crosses into a high readmission-risk tier in the gold layer, and a downstream system needs to know now so it can trigger outreach before the patient is out the door. The data product is the same as Part 3. The receiver is what changed, and with it, everything about how the message leaves.
The Shape: Streaming, Because HL7v2 is Event-driven
HL7v2 moves as a stream of events. An admit fires an A01, a discharge an A03, a patient-information update an A08, each emitted the instant its trigger occurs onto a feed that downstream systems subscribe to. That makes this the concrete version of Part 2’s mechanism two, streaming to a broker. A new risk tier is a change to what’s known about the patient, so our notification is an ADT^A08.
Getting the message onto the topic has a wrinkle. Snowflake can’t produce to a Kafka broker directly. A stored procedure can make an outbound HTTPS call through an external access integration, but it can’t hold the persistent connection a Kafka producer needs. So you front the broker with a lightweight Lambda behind API Gateway. (A container on Snowpark Container Services can hold a real producer connection instead. Same outcome, different trade-off.) Either way, Snowflake’s job ends when the topic accepts the message, even though the workflow isn’t done until the receiver processes it.
Two Acknowledgments (and They are Not the Same)
Two acknowledgments drive most of the complexity here. Confusing them is how you end up believing a message was delivered when it was rejected three hops away.
From Snowflake’s side, publishing is fire-and-forget. When the broker accepts the A08, Snowflake gets back proof the message is durably on the topic, and nothing more. You know the event was published, not that anyone consumed it, let alone that the receiver liked it. By design, Snowflake holds no connection open waiting for a verdict. The whole reason to stream is that the warehouse gets to let go.
Downstream of the broker, the acknowledgment is the opposite kind. The consumer, your own MLLP sender or a commercial interface engine, reads the message off the topic, sends it over MLLP, and waits. The receiver returns an ACK if it accepted the message or a NAK with a code explaining why it didn’t. Only then is the message truly delivered. It’s the same per-message handshake as the FHIR API write in Part 3, living entirely on the consumer side.
The broker decouples the two. Snowflake publishes at warehouse pace and moves on, and the consumer deals with the receiver on its own clock. That decoupling is the feature. It’s also what you have to engineer around, because the failure that matters most, a NAK, happens on the far side of a boundary Snowflake can’t see across.
Constructing the Message, and the ID That Ties It Together
So how does a warehouse produce a valid HL7v2 message, and how do you trace a downstream NAK back to it? Both answers live in the header.
Generating the A08 is a transform, like the FHIR post, pointed at a different target. Instead of nested JSON, you assemble delimited segments in order: MSH header, EVN event, PID with the patient identifiers, PV1 with the visit, and the segment carrying your risk tier. The gold layer has the pieces. The work is emitting them with the delimiters, field positions, and codes the receiver expects. Where a risk tier belongs (a Z-segment, an OBX, or somewhere the receiver defined) is a per-interface decision. In a standard this old, the message goes where the receiver has agreed to look for it. Validate before it leaves, so a malformed segment is caught in your pipeline rather than by a NAK downstream.
The MSH segment does double duty. MSH-9 declares the message type and trigger, ADT^A08. MSH-10, the message control ID, uniquely identifies this message and makes the whole asynchronous flow traceable. You assign it at generation time, and the receiver’s ACK or NAK carries it back in the MSA segment so the consumer can match the verdict to the message. Across a broker Snowflake fired into and forgot, MSH-10 is the thread that stitches the two halves together. A NAK three hops downstream is only actionable because it names the message that failed.
If you’ve built ingestion pipelines, this is parsing in reverse. Inbound, you read MSH to learn what a message is. Outbound, you construct it and assign the control ID, and nothing downstream will correlate for you if you don’t.
Who Owns the Last Mile
Everything after the broker has to be owned by something. It’s the choice every healthcare integration eventually faces: buy or build.
The default I’d reach for is a commercial interface engine. Mirth, Rhapsody, Corepoint, and their peers exist to own the MLLP last mile. They consume from your topic, frame the message, manage the connection, handle ACKs and NAKs, retry on failure, and alert you to deliveries that don’t land. Most organizations already run one, so your pipeline can stop at the broker and hand off to a system built for exactly this.
Building the sender yourself shows what the engine does for you. A container on EKS, Fargate, or the like consumes from the topic, frames each message in MLLP (the start-block and end-block bytes that mark where it begins and ends), opens the TCP connection, sends, and waits for the ACK or NAK. A network load balancer distributes the connections, and a site-to-site VPN carries the traffic to the receiver’s network. Every box in that path is a responsibility the engine absorbed in the previous diagram.
Build when you have no engine and a single, stable interface, or when you specifically want the whole path in-house and Snowflake-native. Buy in nearly every other case. MLLP delivery is a solved problem, and the retry, monitoring, and connection management you’d reimplement are what take the time. Either way, the warehouse and broker are identical. All that changes is who owns the synchronous half.
The Message Got Out. Did It Land?
Streaming an A08 to a broker made Part 2’s mechanism two concrete. The control ID you assign at generation time follows the message to the receiver and comes back on the ACK or NAK. That makes the flow traceable and auditable, the kind of evidence most regulated industries require for any data movement.
HL7v2 looks like the odd one out in this series, a pipe-delimited message from 1987 next to FHIR’s JSON, but the pattern didn’t change. A risk tier computed once in the gold layer was needed in a dialect the warehouse doesn’t natively speak, so the warehouse generated it in that dialect and streamed it out. It’s the shape we’ve followed since Part 1: generate in the receiver’s language, deliver by the coupling that fits.
One more format to go. EDI is where we take this pattern somewhere I’ll argue it maybe shouldn’t go. We built a working reverse ETL flow for X12, and I’ll show you why a clearinghouse is usually the better answer, and what we learned building it anyway.
If you’ve got a data product that needs to reach a system speaking HL7v2, or any other dialect the warehouse doesn’t natively emit, that’s the conversation we have every week. Reach out, and we’d be glad to help you think it through.
