TapCub is live — analytics, insights and live chat, free on one platform
Glossary

What is data federation? How it works vs a data warehouse

Data federation queries several separate systems as one, without copying the data first. How a federated query runs, federation vs warehouse, and a break-even example for choosing between them.

Xu An Reviewed by Lucas Hayes Also called federated query Last updated Nov 19, 2026 Glossary

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.

  1. 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.
  2. 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.
  3. Push down. Work that the source can do is sent there. PostgreSQL's postgres_fdw, for example, sends only WHERE clauses 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.
  4. Transfer. Rows that survive the remote filtering travel over the network. This step usually dominates the cost.
  5. 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 federationData warehouse (ETL / ELT)Application integration (API sync)
Where the data livesStays in each sourceCopied into one storeCopied between operational systems
FreshnessAs fresh as the source at query timeAs fresh as the last loadAs fresh as the last sync
Query speedBounded by the slowest source and the networkFast; data is shaped for analysisNot meant for analytical queries
Load on source systemsEvery query hits themOnly the scheduled load hits themEvery sync hits them
HistoryOnly what each source keepsCan keep full historyUsually current state only
Cleaning and joiningDone again on every queryDone once during loadDone in the sync logic
Cost patternPay per queryPay for storage and loadsPay for building and running syncs
Good fitOccasional cross-system questions, fresh operational data, data that cannot be copiedRepeated heavy analysis, reporting, historyKeeping 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.

PlanRows sent to the federation layerSize
No pushdown: fetch every order, filter locally12,000,00012,000,000 × 40 B = 480 MB
Pushdown: the sales database filters to the last 30 days and amount over 100400,000400,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 dayFederated totalWarehouse total (600 s load + runs × 3 s)Cheaper in time
145 s603 sFederation
10450 s630 sFederation
14630 s642 sFederation (about equal)
15675 s645 sWarehouse
2009,000 s1,200 sWarehouse

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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

Was this explanation clear?

We use your answers to fix and expand this page.

Xu An

Customer Experience Consultant

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

FAQ

Questions people ask

Short answers to the follow-up questions that come up most.

Still have a question?

Chat with the team behind TapCub — we usually reply within a few hours on business days.

What is the difference between data federation and a data warehouse?

A warehouse copies data into one store ahead of time, so queries are fast but only as fresh as the last load. Federation leaves data in place and queries it live, which is fresher but depends on the slowest source and the network.

Is data federation the same as data virtualization?

The terms overlap and many vendors use them interchangeably. Federation emphasises querying several sources as one; virtualization emphasises the abstraction layer that hides where the data lives.

When is data federation a bad idea?

When the same heavy query runs many times a day, when sources cannot take analytical load, or when large tables sit across a slow link. In those cases copying the data once is cheaper than moving it on every query.

Does TapCub support federated queries?

No. TapCub does not query your other databases at run time. You can send a few attributes in as properties, or export TapCub's raw detail and join it in your own warehouse.

See this number on your own site

Add one script tag for web and apps, and the first reports fill in within minutes.

See your data clearly. Find your growth.

Every click, backed by data. Install one line of code and see your first numbers in a minute.

No credit card · Free plans for analytics and chat · Cookieless analytics