Federated Query
One SQL statement across several systems. Genuinely useful, and it gives up consistency, predictable latency, optimiser competence and control of the load you impose.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
If an engine can join a lake table to a production database and a SaaS export in one query, why would anyone still build ingestion?
An analyst who has been told the answer requires three systems and does not want to wait a quarter for three ingestion pipelines. Also, unavoidably, the teams who own those three systems — federation makes their availability part of your query's availability, whether or not anyone asked them (Data Engineering and Backend Engineering).
The unit is a per-source read, and the fact that there is more than one of them is the whole lesson. Each source is read at its own moment, under its own isolation rules, at its own grain — and the query joins those reads as if they were one consistent picture of the world, which they are not.
Register every system in the catalog and let people write SQL across all of it. No pipelines, no copies, no staleness, no schema drift to manage — the data stays where it lives and the engine does the work. As a way to answer a question nobody has asked before, this is excellent and genuinely faster than any alternative.
The query joins orders from Postgres with payments from MySQL. Each source is read at a different moment, so an order that was paid between the two reads appears as an unpaid order, or a payment with no order. Nothing is wrong with either system (Reconciliation).
- The query joins
ordersfrom Postgres withpaymentsfrom MySQL. Each source is read at a different moment, so an order that was paid between the two reads appears as an unpaid order, or a payment with no order. Nothing is wrong with either system (Reconciliation). - The optimiser has no statistics for the remote tables. It guesses a row count, chooses a broadcast, and the guess is wrong by four orders of magnitude (Cost-Based Optimization).
- A join across two different sources cannot be pushed anywhere, so both sides are pulled into the engine — which means the "small lookup table" you joined against is now crossing the network on every query (Source Pushdown).
- The dashboard built on the federated query refreshes every five minutes, so an analytical scan hits a production database every five minutes forever, and nobody remembers approving that (Workload Isolation).
- One source is slow, or down for maintenance, and the query does not degrade — it fails. Federated availability is the product of every participant's availability (Availability, SLOs and Error Budgets).
- The same query gives different answers at different times of day, and both answers are defensible, because one source lags its own replica and the other does not (Replication Lag: Reads That Are Correct and Stale).
What is actually happening
- The engine presents one catalog namespace over several connectors. A table reference resolves to a source, a connector and a remote object; the planner builds one plan whose leaves are reads against different systems (Query Engines).
- Each leaf is optimised independently by whatever the connector can push, and everything above the leaves runs in the engine. A join between two sources is therefore always an engine-side join, always fed by two independent reads (Distributed Query Execution).
- Those reads are not coordinated. There is no distributed transaction, no shared snapshot, and no clock the two sources agree on. Each read sees its own source's consistent state at its own moment (Distributed Consistency: CAP, Quorums, Consensus).
- Cost estimation degrades badly here. A local table has file statistics; a remote table often has none, so the optimiser falls back on defaults and produces join orders and broadcast decisions from numbers it invented (The Planner: Enumerating Ways to Answer).
- The engine also inherits every source's type system and has to reconcile them: decimal scales, timestamp precision, timezone handling, string collation, and the semantics of null. Reconciliation happens per connector, and two connectors can map the same logical type differently (Semantic Changes).
- Nothing in this mechanism is unsound. It is a correct engine-side join over correct per-source reads. What is missing is any property that spans the sources — and consistency is precisely such a property.
One query, several systems, several moments
The picture is simple and the implication is not. One coordinator, one plan, three connectors, three independent reads — and above them, in the engine, a join that treats those three reads as one coherent view of the world.
Look at what is missing from the diagram. There is no transaction coordinator, no shared snapshot identifier, no clock that the three sources agree on. Each of them answers correctly, about itself, at the instant it was asked (Distributed Consistency: CAP, Quorums, Consensus). The engine's join is arithmetic on three photographs taken at three different times.
That is a fine thing to do, provided everyone understands what they are looking at. The failure is not the architecture; the failure is publishing the result as if it had the properties of a query against a single database, which is exactly what one SQL statement implies to everybody who reads it.
There is no cross-source snapshot
This is the single most important sentence in the lesson, and it is easiest to see on a timeline. The engine reads orders over one interval and payments over another. Anything that happens between those intervals exists in one read and not the other, and the join has no way to know.
Below, the two scans are drawn as windows and four business events are placed against them. Two land in both reads and are joined correctly. One commits after the orders scan has passed and before the payments scan begins, and appears as a payment with no order. One is the mirror image and appears as an unpaid order that has in fact been paid.
The times are clock labels on a teaching timeline, not measurements. The gap between the two scans could be milliseconds or minutes depending on plan shape, source latency and how much the engine had to pull; what does not change is that the gap exists and its width is not something the query author controls.
The consequence for practice is specific. A federated referential check will report violations that are not violations. A federated revenue number will differ from the same number computed after ingestion. Neither is a defect to be fixed, and treating them as defects is how teams spend a week proving that two correct systems disagree (Two Dashboards, Two Numbers).
| Event | Happened | Arrived | Lands in |
|---|---|---|---|
| order-A committed and paid | 09:58:00 | 09:58:00 | Both reads Settled well before either scan. Joined correctly, and the case everybody imagines when they write the query. |
| order-B committed, paid 10:02 | 10:01:30 | 10:02:10 | Both reads Also fine — both facts existed before both scans started. |
| order-C committed 10:03:55 | 10:03:55 | 10:03:55 | Payments read only The order scan had already passed. Its payment lands in the second read, so the join produces a payment with no order — a referential violation that is not one. |
| order-D paid 10:05:10 | 10:02:40 | 10:05:10 | Orders read only The order was there; the payment arrived after the payments scan finished. Reported as unpaid, and it is not. |
Clock labels on a teaching timeline, not measurements. Widening or narrowing the gap changes how many events fall into it and changes nothing about whether the gap exists.
Federate, cache, or ingest
The decision is not federation versus a warehouse in the abstract; it is what to do about *this* source, for *this* query pattern, at *this* frequency. The useful axes are how often the question is asked, how fresh the answer must be, how large the source is, and who carries the pager for it (Data Engineering and Database Engineering).
The pattern that works well is a progression rather than a choice. Explore with federation, because it is the only tool that answers a new question today. When the question recurs, cache or materialise the small side. When it becomes a scheduled dependency, ingest it and get history, reproducibility and a contract along with it (Data Ingestion).
The anti-pattern is the middle stage becoming permanent by inertia: a federated query behind a dashboard, refreshing forever against a production database, with nobody remembering it exists until the source team changes something (Data Platform Anti-Patterns).
A join between two systems cannot be pushed to either, so both sides move. This dominates and it is structural rather than fixable by tuning.
Paid in someone else's capacity and absent from every data platform cost dashboard, which is exactly why it grows unchecked (Workload Isolation).
The purest waste in a federated setup and the easiest to remove: cache it or ingest it.
Bad estimates produce bad plans; the cost is a broadcast that should have been a partitioned join (Broadcast Joins).
Real, recurring, and invisible. It is the tax for publishing a federated number without the caveat attached.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative and unitless, to establish an ordering rather than a magnitude. The teaching is that the two largest drivers — cross-source movement and source load — are both properties of the architecture rather than of the query, so tuning the SQL addresses neither.
How often is it asked, how fresh must it be, and whose system pays for it?
when A new or one-off question; the answer must reflect the current state; the source is small or the predicate is highly selective and pushable.
cost No consistency across sources, no reproducibility, latency you do not control, and load on somebody else's system (Source Pushdown).
when The same as above, but recurring often enough that hitting a primary is not acceptable.
cost Replication lag widens cross-source skew, and you now provision and monitor a replica for analytics (Replication Lag: Reads That Are Correct and Stale).
when One side is a slowly-changing lookup read on every query — a country table, a product dimension.
cost An explicit staleness budget, and a refresh job that can fail silently. Buys most of the performance for a fraction of the work (Dimension Tables).
when The query is scheduled, feeds a dashboard, or has to be explainable later.
cost A pipeline to build and operate, plus staleness between runs. Buys history, reproducibility, contracts and a consistent join (Batch Ingestion).
when Freshness genuinely must be continuous and the source can emit changes.
cost The most operational complexity of the five, and a whole class of ordering and late-data concerns (Change Data Capture, Late-Arriving Data).
How to build it
Most important first.
- Treat federation as an exploration and reconciliation tool rather than a serving path. Answering a new question, checking whether a pipeline's output matches its source, prototyping a join before committing to ingestion — all excellent. A dashboard refreshing every five minutes against production — not (Data Observability).
- Where a federated query becomes recurring, that is the signal to ingest. The second time somebody schedules it, build the pipeline: you now know the join, the grain and the freshness requirement, which is more than you knew before (Data Ingestion).
- Point every connector at a replica or an explicitly provisioned read path, never at a primary. This single decision removes most of the operational risk in this lesson (Read Replicas From the Application).
- Bound the blast radius on the source side: a restricted user, a statement timeout, a row limit and a connection cap. Controls that live in the engine protect the engine; controls that live on the source protect the source (Rate Limiting).
- Be explicit about consistency in anything you publish from a federated query. "As of separate reads, seconds apart" is an honest caveat and it belongs in the dataset's documentation, not in somebody's memory (Dataset Documentation).
- Compute statistics for federated tables where the engine supports it. A plan built on real cardinalities is a different plan from one built on defaults, and this is the cheapest available improvement to a federated join (Cost-Based Optimization).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Each source read is consistent within that source, at whatever isolation level the connector requested. That is the strongest guarantee federation offers.
- There is no cross-source snapshot. Two sources are read at two different moments, and no combination of settings changes that — it would require a distributed transaction across systems that share no transaction manager (Distributed Transactions).
- There is no cross-source atomicity or ordering. An event that appears in one source and not yet in the other is a normal, expected, permanent possibility rather than an error condition.
- Availability is conjunctive: the query needs every participating source. There is no partial answer and no graceful degradation unless you build it above the engine (Reliability Patterns).
- Freshness is per source and not equalised. Joining a real-time source to a nightly one gives an answer whose freshness is the *oldest* of the two, presented as if it were uniform (Freshness Monitoring).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check is a cross-source referential assertion run at a known-quiet moment: every payment has an order, every order has a customer. Run in the middle of the day it produces false positives constantly, which is itself the lesson about what federation does not promise.
- It misses every inconsistency that resolves between runs — which is most of them. A gap that exists for two seconds during your read and not during your check is real, was reported, and is undetectable afterwards (The Pipeline Succeeded. The Data Is Wrong.).
- The complementary check is a per-source freshness assertion: refuse to publish a federated result if any participating source is staler than its stated budget (The Freshness SLO).
- Federation gives the freshest answer available anywhere in this domain: the source's own current state, with no pipeline in between. For a question about "right now" nothing else competes.
- That freshness is uneven across the join, and the unevenness is invisible in the result. A row combining a five-second-old order with a twelve-hour-old customer dimension looks exactly like a row where both were current (Slowly Changing Dimensions).
- Latency is the slowest source plus the engine's own work, and it varies with a load you do not control. A federated query has a much worse tail than a query over your own files, and dashboards feel tails (Tail Latency: Why p50 Being Fine Does Not Help).
- Every source evolves on its own schedule and none of them tells you. Federation multiplies the number of schemas you depend on without giving you a contract with any of them (Schema Evolution).
- A type change on one side of a cross-source join changes the join's behaviour rather than breaking it: an id that becomes a string still joins after an implicit cast, right up to the row where the cast differs (Breaking Schema Changes).
- Adding a source to a federated query adds its availability, its schema drift and its load profile to yours. That is an architectural decision that usually arrives as a one-line SQL edit (Data Contracts).
- A failed federated query is retried, and a retry is not free for the sources — a retry storm against an operational database is a real way to turn a slow morning into an outage (Retry Storms: The Load You Generated Yourself).
- Where a federated read feeds a table, make the write idempotent over an explicit range so a re-read replaces rather than appends. Federated sources make duplicates especially likely because there is no offset or watermark to resume from (Idempotent Data Pipelines).
- There is no replay. A source read at 10:03 cannot be re-read as it was at 10:03, which means a federated result is not reproducible — the strongest single argument for ingesting anything you need to explain later (Keeping Raw History: The Recovery Position and the Liability).
What can go wrong
- Cross-source inconsistency reported as a data quality problem, investigated for days, and correct all along.
- A broadcast join sized from a default estimate, failing every worker at once (Broadcast Joins).
- Analytical load on an operational primary, discovered by that team's alerting rather than yours (CPU Saturation: When Cores Become the Queue).
- A federated query that cannot be reproduced, in support of a number somebody has to defend to an auditor.
- Conjunctive availability: one source's maintenance window failing every dashboard built on federation.
- The mitigation failing: a per-source timeout that protects the sources and makes the nightly reconciliation job flaky, so it is eventually disabled by someone tired of the alert (Alert Fatigue: The Page Nobody Reads).
- "Federation means we do not need a warehouse." It means you do not need one *to answer this question today*. Reproducibility, history, cost control and consistent joins are what the warehouse was for, and none of them arrive with a connector (The Data Warehouse).
- "The join is inconsistent, so something is broken." Nothing is broken. There is no cross-source snapshot and there never was; the inconsistency is the architecture, correctly observed (Distributed Consistency: CAP, Quorums, Consensus).
- "It is one SQL statement, so it is one transaction." It is one statement over several independent reads. SQL's syntax implies an atomicity that nothing here provides (Transactions and ACID).
- "The engine will optimise across sources." It will push what each connector accepts and do the rest itself, using cardinality estimates that for remote tables are frequently defaults (Query Optimizers).
- "Read-only access means we cannot hurt the source." A read can saturate I/O, evict a buffer pool and queue behind transactions. Read-only is not load-free (The Buffer Pool).
- Federation crosses trust boundaries by design. Data governed under one system's policies arrives in an engine with different users, different logs and different result caching (Data Access Control).
- The connector's principal is the effective access boundary, not the analyst's identity. A broadly-privileged connector user grants every engine user that reach, and the source's own row-level policies apply to the principal rather than to the person (Row and Column Security).
- A federated result set can contain PII from a system that was never in scope for the analytical platform, and it will be cached, logged and exported like any other result (PII in Pipelines).
- Deletion requests are harder, not easier: the source deletes the row, and the federated extract someone saved last month does not (Deletion Requests).
Operating it
- Per-source read latency and row counts within each federated query, plotted separately. An average across sources hides the one that is slow (The Average Was Fine and Users Were Not).
- Federated query volume per source, per hour, with the source's own load overlaid. This is the chart that gets a conversation with the owning team started before the incident rather than after (Capacity Planning: Traffic to Machines).
- Plan diffs for recurring federated queries: whether the join was broadcast or partitioned, and whether the remote scan pushed anything (Reading EXPLAIN ANALYZE).
- Freshness per participating source, exposed alongside any published federated result so the consumer sees the oldest one (Freshness Monitoring).
- At 10x query volume the sources become the constraint, not the engine. Federation scales like the least scalable participant (Little's Law as Working Intuition).
- At 10x data in one source, the absence of pushdown for cross-source joins turns from an inefficiency into an impossibility.
- At 10x sources, the governance problem overtakes the technical one: who may query what, whose credentials, whose policies apply, and who is told when a source changes (Data Governance).
- Bytes crossing connectors, which is dominated by whatever could not be pushed — normally the cross-source join, because a join between two systems can never be pushed to either (Source Pushdown).
- Load on the operational sources, which is a cost paid in someone else's capacity and shows up on nobody's data platform bill.
- Repeated reads of slowly-changing lookup tables, which is the most common pure waste in federated setups and the easiest to fix by caching or ingesting (Cost vs Freshness).
- Engineering time spent debugging inconsistencies that are not defects. This is a real cost of the architecture and it is invisible in every cost model.
- Federation buys time-to-answer measured in minutes instead of weeks, and costs consistency, reproducibility, predictable latency and control of the load you impose. For exploration that trade is excellent; for a serving path it is usually wrong (The Central Warehouse).
- Avoiding ingestion buys you no pipeline to build and costs you no pipeline to rely on: nothing to replay, nothing to backfill, no history that survives a source's retention policy (Keeping Raw History: The Recovery Position and the Liability).
- Pointing connectors at replicas buys operational safety and costs freshness and one more thing to provision — and replica lag makes cross-source skew larger, not smaller (Replication Lag: Reads That Are Correct and Stale).
Federation snapshot lab
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| Federated | Copied into one place | |
|---|---|---|
| Freshness | Live at each source — separately. | As fresh as the last pipeline run, and the same for all three. |
| Consistency | No shared instant. A join can count a shipment for an order that the orders read did not yet contain. | One instant, recorded, reproducible. |
| Reproducible | No. The same query at the same moment tomorrow gives a different answer, and so does a re-run right now. | Yes, as long as the snapshot is retained. |
| Load on the source | Every analytical query hits production, including the accidental cross join. | One read per pipeline run, at a time you chose. |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe absence of a cross-source snapshot is not an engine limitation; it follows from the sources sharing no transaction manager and no clock. No product removes it, and any claim to have done so is a claim about one vendor's own systems talking to each other.
- ENGINE-SPECIFICHow much an engine can salvage — statistics for remote tables, dynamic filtering into a remote scan, caching of small dimension reads — varies widely. Two engines federating the same two systems can produce very different plans and very different load on the source.
- ORG-SPECIFICWhether federation is acceptable depends on who owns the sources and how the load lands on them. In one company it is a self-service superpower; in another it is an unreviewed dependency on a team that was never consulted and carries the pager.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why a consistent snapshot across independent systems requires a shared transaction manager or a shared clock, and what the available approximations cost. Federation is the applied consequence of that result.