The analytics request sounds innocent: "revenue by customer segment, joined with billing status." Orders live in Postgres. Customers in MySQL. Clickstream events in parquet on S3. Billing in a SaaS vendor's API. The traditional answer is a warehouse plus four pipelines and a permanent on-call rotation for the pipelines. The federated answer looks like this:
A real techdiagrams document — download the JSON and import it into the editor.
The architecture: a planner with passports
A federated engine — Trino/Presto is the archetype; DuckDB, DataFusion and Spark SQL play the game too — splits into two ideas:
A coordinator that owns the plan. It parses your SQL, asks each source's catalog what exists, and builds one distributed plan: which fragments run where, what joins with what, in which order. To you it's one database; the fact that orders and customers live on different engines is a physical detail.
Connectors that translate. Each source gets an adapter speaking its dialect: SQL to Postgres and MySQL, object listings and parquet readers for the lake, paginated REST for the SaaS API. Crucially, a connector doesn't just fetch — it negotiates.
Pushdown: the entire performance story
The naive version of federation is a disaster: pull all rows from everywhere, join in the engine, melt the network. The real version pushes work down into the sources:
- Filter pushdown —
WHERE created_at > now() - interval '30' dayexecutes inside Postgres; only surviving rows travel. - Projection pushdown — need 3 columns of 60? Only 3 cross the wire (and columnar formats like parquet don't even read the other 57 off disk).
- Partition pruning — the lake scan touches only the date-partitions the query can possibly need.
- Aggregate pushdown, where connectors support it —
SUM(...) GROUP BYcomputed by the source, shipping totals instead of rows.
What can't be pushed down — the cross-source join itself — runs in the engine's workers. The diagram's purple note is the mental model: sources ship survivors, the engine hosts the reunion.
What federation buys — and what it charges
Buys: always-current answers (no pipeline lag — the query hits the live source), no storage copies, no ETL fleet to babysit, and one SQL dialect and one access-control point over everything. For exploration and cross-system questions, it's transformative: the schema conversation replaces the data-engineering ticket.
Charges:
- Latency is the source's latency. A federated query is as slow as its slowest participant — that SaaS API's 2-second pagination is now inside your query plan (note the
rate-limitandretrybadges on that edge in the diagram; they're load-bearing). - You're taxing production stores. That elegant filter pushdown is real load on the Postgres that also serves checkout. Analysts can DoS your OLTP database politely, with valid SQL. Read replicas exist for this reason.
- Cross-source consistency doesn't exist. The Postgres rows are from now, the API page from two seconds ago — there is no snapshot spanning systems. Fine for analytics, disqualifying for money math.
The mature pattern is a hybrid: federate the exploratory and cross-source questions, then promote the queries that become dashboards into materialized, scheduled copies. Federation first, pipelines only where proven necessary — the reverse of the traditional order, and much cheaper in practice.
The diagram is the design review
Every trade-off above is visible in the picture: the fan-out that means slowest-source latency, the edges into production stores that need replicas, the rate-limited API edge that needs backpressure. Import it into techdiagrams.net, swap in your actual sources, and the "should this be federated or pipelined?" debate gets a canvas — with real numbers on the edges — instead of a meeting.