Quick answer
Data federation is a way to query data held in several separate systems as if it were one database, without copying it into a central store first. A federation layer splits the query, runs each part where the data lives, and combines the results at query time.
Formula: Break-even runs per day n = load time ÷ (federated time − warehouse time per run); for example 600 ÷ (45 − 3) ≈ 14.3.
What is data federation?
Data federation is a way to query data that lives in several separate systems as if it were one database, without copying that data into a central store first. A federation layer accepts one query, splits it into pieces for each source, sends the pieces to where the data is, and combines the answers on the way back. The data stays where it was created; only the query results move.
The idea is old. It comes from "federated database systems" in the research literature, where independent databases cooperate while each keeps its own owner and schema. Today it shows up as foreign-data wrappers in PostgreSQL, distributed SQL engines such as Trino, and the "virtual" layers sold by data platforms. Federation is one answer to the problem described on data integration; the other main answers copy the data first, as in ETL and warehousing.
How a federated query actually runs
A federated query is not magic: it is a plan with a few predictable steps.
- Connect. The federation layer holds a connection and a mapping for each source, such as a foreign table that points at a remote table. Trino, for example, is built to operate over different kinds of data sources, such as relational databases and Cassandra.
- Plan. The engine splits the query into the part each source can run and the part it must run itself. Whether a filter or a join can be handled remotely is decided by the engine's rules.
- Push down. Work that the source can do is sent there. PostgreSQL's
postgres_fdw, for example, sends onlyWHEREclauses that use built-in operators and functions to the remote server, and sends an entire join to the remote server when both tables live on it. Anything it cannot push down is evaluated locally after rows are fetched. - Transfer. Rows that survive the remote filtering travel over the network. This step usually dominates the cost.
- Combine. The layer joins, aggregates and returns the result. Nothing is stored, so the next run starts from step 2 again.
The practical rule that follows from steps 3 and 4: a federated query is fast when the remote systems do the reducing, and slow when the federation layer has to pull raw rows and reduce them itself. You can see what was pushed down by reading the plan; in PostgreSQL that is EXPLAIN VERBOSE, which shows the query text actually sent to the remote server.
Federation vs warehouse vs integration
| Data federation | Data warehouse (ETL / ELT) | Application integration (API sync) | |
|---|---|---|---|
| Where the data lives | Stays in each source | Copied into one store | Copied between operational systems |
| Freshness | As fresh as the source at query time | As fresh as the last load | As fresh as the last sync |
| Query speed | Bounded by the slowest source and the network | Fast; data is shaped for analysis | Not meant for analytical queries |
| Load on source systems | Every query hits them | Only the scheduled load hits them | Every sync hits them |
| History | Only what each source keeps | Can keep full history | Usually current state only |
| Cleaning and joining | Done again on every query | Done once during load | Done in the sync logic |
| Cost pattern | Pay per query | Pay for storage and loads | Pay for building and running syncs |
| Good fit | Occasional cross-system questions, fresh operational data, data that cannot be copied | Repeated heavy analysis, reporting, history | Keeping two operational systems consistent |
The lakehouse vs warehouse comparison covers the copy-first side in depth. Federation is often called data virtualization; the two words overlap, and many vendors use them interchangeably.
Worked example: where the time and bytes go
Sample data: a company keeps 50,000 customers in a CRM database and 12,000,000 order rows in a separate sales database. An analyst asks: which German customers placed an order over 100 in the last 30 days?
Bytes transferred. Assume an average order row is 40 bytes.
| Plan | Rows sent to the federation layer | Size |
|---|---|---|
| No pushdown: fetch every order, filter locally | 12,000,000 | 12,000,000 × 40 B = 480 MB |
| Pushdown: the sales database filters to the last 30 days and amount over 100 | 400,000 | 400,000 × 40 B = 16 MB |
Pushdown cuts the transfer by a factor of 30. The same query, written the same way, can be fast or painfully slow depending only on whether the engine can push the filter.
Federate or copy? Suppose the federated query takes 45 seconds. A nightly load into a warehouse takes 600 seconds and afterward the same question takes 3 seconds.
| Runs per day | Federated total | Warehouse total (600 s load + runs × 3 s) | Cheaper in time |
|---|---|---|---|
| 1 | 45 s | 603 s | Federation |
| 10 | 450 s | 630 s | Federation |
| 14 | 630 s | 642 s | Federation (about equal) |
| 15 | 675 s | 645 s | Warehouse |
| 200 | 9,000 s | 1,200 s | Warehouse |
The break-even is where 45 × n = 600 + 3n, which gives n = 14.3 runs a day. Below that, federation is cheaper in time and needs no pipeline to maintain; above it, copying wins. Elapsed time is not the only cost, because every federated run also loads the source systems, but the shape of the comparison holds: federation is for occasional questions, copying is for repeated ones.
When to use data federation
Use these rules as a starting point, then check them against your own numbers:
- Use federation if the question is asked rarely or is exploratory, the answer must reflect the source right now, the result after pushdown is small, or policy forbids copying the data out of its system.
- Copy the data if the same query runs many times a day, you need history the source does not keep, the join key needs cleaning that is expensive to repeat, or the source systems cannot take the analytical load.
- Combine them when a mostly stable core is loaded into a warehouse and one fast-moving table is federated on top.
- Do not federate across a slow link between regions for large tables; the transfer step will dominate.
Common mistakes
- Treating federation as a free warehouse. It removes the copy, not the work: cleaning, joining and deduplication happen on every query unless you add them to the source.
- Hitting a production database with analyst queries. A federated scan runs on the source's own CPU. Point it at a read replica or limit it to small, filtered queries.
- Ignoring pushdown limits. A filter using a custom function may be evaluated locally after all rows are fetched. Read the plan before trusting a query that "worked on a small table".
- Expecting consistent snapshots. Sources are read at slightly different moments, so totals across systems can disagree for a few seconds during busy writes.
- Assuming the sources agree on identity. Federation joins on a key; if the CRM and the sales system do not share one, you still need entity resolution or a unified data model first.
Data federation and TapCub
Imagine the same analyst wants to relate product usage to billing: usage events in TapCub, invoices in a finance database. Here is where TapCub fits.
- TapCub does not run federated queries. It will not reach into your finance database at query time, and it does not act as a federation layer. Analysis inside TapCub runs on the events and user properties collected in the TapCub project.
- Copy the small thing you need in. If you need one finance attribute, such as plan or contract value, send it as a user property or an event property so it can be used to filter and split in All-platform analytics › Behavior › Behavior analysis.
- Copy TapCub data out when you need to join heavily. All-platform analytics › Data › Data export creates asynchronous exports of raw detail, so you can load it next to your invoices in your own warehouse.
- Keep the contract clear. Write down which events and properties you intend to send in All-platform analytics › Data › Tracking plan, so the people who join this data later know what each field means.
TapCub's role in a federation story is therefore one of the sources, not the layer on top. If the question is asked once a quarter, a federated query across your own systems may be enough; if it is asked daily, export and load.
Sources
- PostgreSQL Documentation, postgres_fdw: access data stored in external PostgreSQL servers.
- Trino Documentation, Use cases.
Was this explanation clear?
We use your answers to fix and expand this page.
Couldn’t send. Please try again in a minute.
Thanks for the feedback. We’ll use it to improve this page.You’ve already sent feedback on this page. Thanks.

Customer Experience Consultant
Writes about live chat, visitor conversations, and self-service, sharing practical ways to make customer communication clearer and more effective.