Federation, mounting, zero-copy sharing and reverse ETL
What does each way of using data in place cost, and why does reverse ETL hand the destination a vote?
Federation and query pass-through
Federation means one engine queries data that lives in another system, without copying it first. The premise: the data lives somewhere you do not control, and moving it is expensive or forbidden. So send the question to the data, not the data to the question. That only works if the remote side can answer a meaningful part of the question.
That "if" is the whole mechanism. The value of federation equals the selectivity of what can be pushed down.
When pushdown fails, you have built a very slow full extract with extra steps, and people will call it a performance problem. The best predictor of success is the intersection of the two engines' expressive power, not the speed of either.
Two consequences are found late:
- Blind planning. The local optimizer has no statistics (row counts, value distributions) about the remote side, so its cost-based choices become guesses. The machinery that normally gives good plans loses its inputs at the boundary.
- Borrowed latency, availability and risk. Your query's speed and uptime are now the remote system's. A federated query against an operational database is an uncontrolled load generator pointed at production.
The assumption that cannot be verified: that the remote system can absorb what you are about to send. Who pays: whoever is on call for that system, which is usually not you.
In one sentence: federation's ceiling is whatever the far side can be persuaded to compute; everything else is a transfer you did not intend.
External tables and lake mounting
This is the narrower case where the remote side is dumb storage, not an engine. Nothing can be pushed down except what the file format supports by itself. That is why the columnar footer matters so much here: it is the only query capability the remote side has. A format with statistics lets the engine skip blocks. A format without them forces a full read. The file's self-published interface becomes the pushdown surface.
The deeper mechanism is separating ownership from access. The data is registered rather than ingested, so many engines can read one copy and no engine owns it. The cost: the engine now has responsibility without control.
- It cannot guarantee the files have not changed under it.
- It cannot enforce constraints.
- It often cannot know the layout is poor until a query is slow.
- Table maintenance (compacting small files, removing old ones) must be done by someone. If the data is mounted and not ingested, it is nobody by default.
This is the repeating shape of immutable storage plus mutable intent: the indirection is cheap to create, and the collector is what gets forgotten.
In one sentence: mounting gives you the data without the obligations, which is exactly the problem.
Zero-copy data sharing
The constraint being attacked: sharing data across an organizational boundary normally means copying it, and a copy is a new object with its own life cycle, freshness, cost, permissions and drift. Copies are how one dataset becomes eleven datasets that disagree.
The mechanism is to share a reference, mediated by a catalog, instead of the bytes. The recipient's engine reads the provider's storage with credentials that the catalog hands out per request. This is only possible because of the catalog as arbiter: once the catalog is an arbiter in the authorization path, it can grant scoped access to something it does not move. Sharing is a feature of the catalog as a service, not of storage.
| What is gained | What is paid | |
|---|---|---|
| One copy | One truth, one freshness. No divergence | The consumer reads the provider's live layout, so the provider cannot restructure without breaking consumers they may not know about |
| The consumer's query load lands on the provider's storage and bill | ||
| An availability dependency that a copy did not have |
The name "zero-copy" describes the storage, not the relationship. The relationship is tighter than a copy, not looser. The cost of the copy was real; removing the copy moved the cost into coupling.
And it works only inside whatever boundary the catalog and storage span. Across that boundary (a different cloud, vendor or legal region) you are copying again. This mechanism belongs to a shared base. It is not a general solution.
In one sentence: one copy means one truth, and one owner who can now break everyone at once.
Reverse ETL
The direction reverses, and every property of the boundary changes with it. A warehouse is built to be read by patient consumers in large amounts. An operational system (a CRM, a billing system) is built for small, fast, transactional writes by systems that care about latency. Pushing from the first to the second crosses a mismatch in the hostile direction. Three things follow.
- The destination rate-limits you. The transfer is throughput-bounded by a party that does not want your traffic. This is flow control, arriving from the wrong end.
- The destination is stateful and cares about single records. So you must send diffs (what changed), not whole sets. You need a reliable way to know what changed (change data capture: reading the stream of inserts, updates and deletes). It is now required, not optional.
- The writes at the destination are not transactional and not atomic. A sync that is only partly applied leaves the operational system in a state that is neither old nor new, with no rollback.
The structural point
The warehouse was defined as the place where correctness is established eventually, with delay measured in minutes to hours. Making it a source of operational truth imports that delay into systems whose users expect immediate answers, and turns analytics downtime into operational downtime. It is often the right choice, but it should be seen for what it is: promoting a derived, late, best-effort dataset into the serving path.
In one sentence: reverse ETL turns your analytical delay into someone's product behaviour, and the destination gets a vote you cannot override.