In the first installment of this series on reverse ETL and its applications, I made the case that reverse ETL is defined by the pattern, not the tool, and that everything falls out of one axis: how tightly the warehouse is coupled to the receiver.
At the tight end, delivery is synchronous and the receiver acknowledges each write in the moment; at the loose end, delivery is an asynchronous handoff, and proof of receipt comes later, out of band, if it comes at all. This post turns that axis into four concrete transport mechanisms and a table you can pick from.
The four mechanisms below aren’t competitors; each is a point on that spectrum, and the right one falls out of your constraints. They’re ordered tight to loose, and for each I cover the same six things, captured in the table at the end: coupling, latency, payload shape, what the receiver has to support, comfortable volume, and how you know it worked.
Each diagram shows one concrete architecture, not the only one. Read them as representative, not prescriptive.
Transport Mechanisms
1. Push to an API (External Access Integration)
The use case: You have a receiver that exposes an API and expects records one at a time, or in small batches—a billing system, an EHR, a partner service—and you want to know, per record, whether the write succeeded. This is the tight end, where most people picture reverse ETL living.
How it works: A Snowpark procedure reads the changed rows, shapes each into the JSON the receiver’s contract expects, authenticates through Cognito with an OAuth 2.0 client-credentials grant, and posts to the receiver’s API Gateway, which authorizes the token and hands the request to a lightweight Lambda validator. The credential is an OAuth client secret bound to an external access integration whose network rule allowlists only the gateway’s endpoint, so nothing about the connection lives in code.
When to reach for it: Coupling is tight, deliberately so: you get a synchronous acknowledgment on every call, a 200 that confirms the write or a 4xx that tells you why it didn’t. You’re bound by the receiver’s rate limits—a poor fit for millions of rows, an excellent fit for the changed handful that actually need to move. Because you’ll retry, this is where the idempotency discussion from Part 1 stops being theoretical: at-least-once delivery only stays safe if the receiver can absorb a duplicate.
2. Stream to a Broker
The use case: The change isn’t a single write to one system; it’s an event several downstream systems care about, or a continuous feed a consumer reacts to as it happens. Instead of calling one receiver, you publish to a topic and let anyone subscribe—the first step down from the tight end: still low latency, but now there’s a broker between you and whoever consumes.
How it works: A Snowflake Stream on the gold table captures changed rows, and a Task shapes each into an event and posts (via OAuth 2.0 and an API Gateway) to a message adapter that publishes it onto a multi-topic MSK Serverless Kafka broker. Consumers subscribe to the topics they care about and read on their own schedule, with no live connection back to Snowflake.
When to reach for it: Coupling is medium: the broker decouples producer from consumer, so the two no longer have to be online at the same time, and it’s built for high sustained throughput and many consumers. The key shift is acknowledgment: you get a broker-accept ack that the event was durably written to the topic, but nothing tells the producer a consumer actually processed it. End-to-end delivery becomes the consumer’s concern, tracked through offsets and lag.
3. Copy to an External Stage
The use case: The receiver can’t take a push, or you’re doing bulk data sharing where per-record delivery makes no sense. You hand over a curated dataset as a file and let the other side consume it when they’re ready—the loose end proper: no live connection at delivery time.
How it works: Snowflake runs a COPY INTO against an external stage—a named reference to cloud object storage (S3, ADLS, or GCS) with the storage integration and credentials already attached. The gold query result is unloaded to one or more files (NDJSON, delimited, or Parquet) where the receiver can read them.
Who that consumer is varies more than the mechanism suggests:
- Your own team, one step in a longer pipeline. The files are an intermediate a downstream process picks up; the COPY is just the first hop.
- An external consumer pulling directly. The bucket is the handoff point, and the other side reads on their own cadence.
- A trigger for something downstream. A Lambda fires when the COPY finishes and generates a signed download URL surfaced in a data portal.
When to reach for it: Coupling is loose; producer and consumer are fully detached, and latency is batch—wrong for anything real-time. Volume is the reason this exists: unloading scales with your warehouse and the object store, not a rate limit, so this is the mechanism for millions of rows. Acknowledgment is entirely out of band: a file landing in a bucket is not proof it was processed, and treating the landed file as a processed file is exactly the trap the cross-cutting concerns in Part 1 warned about.
4. Deliver to SFTP
The use case: A trading partner or external system that accepts one thing: a file dropped on an SFTP endpoint. This is the lowest common denominator of B2B exchange, the pattern that refuses to die because everyone can speak it, and the loosest coupling of the four.
How it works: What makes this its own mechanism is delivery to an endpoint you don’t control: the partner runs the SFTP server and expects you to connect and drop the file. A Snowpark procedure opens an SFTP client and pushes the unloaded file straight to that partner-hosted endpoint, Snowflake acting as the client rather than the host.
When to reach for it. Coupling is the loosest: fully asynchronous, on a cadence neither system coordinates live. The only thing the receiver must support is an SFTP endpoint, precisely why the pattern persists across partners who agree on nothing else. Acknowledgment is the weakest of all four—no synchronous signal whatsoever, with proof of receipt arriving out of band as an acknowledgment file (in EDI, an actual 997 or 999 functional ack) or a partner confirmation. This is where the gap between “the job succeeded” and “the data was accepted” is widest.
Choosing between them
Everything above collapses to one table. Read down the coupling column and you’re reading the spectrum from Part 1; read across any row and you have the profile of one mechanism.
Mechanism | Coupling | Latency | Payload Shape | Receiver Must Support | Comfortable Volume | How You Know it Worked |
|---|---|---|---|---|---|---|
Push to an API | Tight | Near-real-time to micro-batch | Transaction, per record | An API | Low: the changed few, rate-limited | Synchronous per-record ack (2xx / 4xx) |
Stream to a broker | Medium | Continuous, near-real-time | Event per record | Consuming from a stream or broker | High sustained throughput | Broker-accept ack; no consumer ack (offsets and lag) |
Copy to an external stage | Loose | Batch, minutes to daily | Bulk file | Reading from object storage | Very high: millions of rows | No sync ack; out of band (manifest, confirmation) |
Deliver to SFTP | Loosest | Batch, scheduled | File, partner’s spec | An SFTP endpoint | High, file-bound | No sync ack; out of band (ack file, control report) |
The pattern in that last column is the whole argument in miniature: the tighter the coupling, the more the receiver tells you, in the moment, exactly what happened. The looser the coupling, the more you buy resilience and scale by giving up that instant certainty and engineering the proof yourself. Pick the row whose constraints you actually have, not the one that sounds the most modern.
How to Actually Choose
Which mechanism you reach for is the answer to three questions I ask at the start of every one of these conversations.
First, latency, asked in reverse. You tell me you need real-time; what actually happens if the data lands in one-minute intervals instead? Fifteen minutes? Once a day? Most of the time “real-time” means “faster than the overnight batch we have now,” and the honest answer moves you down a tier before we’ve designed anything, because each tier you can relax buys back resilience, scale, and cost. Second, the target: what can the receiver actually receive? A partner that only accepts a nightly file settles the question no matter what latency you wanted; an EHR with a real API opens up the tight end. Third, cost: a streaming broker you run around the clock is a different profile than a nightly unload, and sometimes the cheaper mechanism that meets the real latency need is the right answer.
Coupling is what those answers add up to. It isn’t a preference you bring to the table; it’s the concession the constraints hand you. Real latency need plus what the receiver can take plus what it costs to run equals the tightness of the connection, and the tightness picks the mechanism. None of the mechanisms is more correct than the others. The correct one is the one your constraints chose for you.
Choosing the mechanism is the easy half. The transport is a day’s decision; what you live with afterward is everything that comes due the instant the warehouse can change another system’s state: idempotency, retries, receipt handling, observability, and a governed egress boundary. Those are the cross-cutting concerns from Part 1, and they, not the transport, are where these projects actually succeed or fail. A managed tool handles them for you; when you build the pattern natively, you own them, which means more work but also more control. In a regulated environment, that control is usually the point.
From here the series gets specific, taking this same pattern into the payload formats healthcare actually runs on: FHIR, HL7v2, and EDI, where we pick the acknowledgment thread back up. If you’re staring at a curated dataset trapped in a dashboard and trying to work out how to get it where the work actually happens, that’s the conversation we have every week. Reach out; we’d be glad to help you think it through.
