Federated queries are a specific technical capability that gets talked about loosely in the data world. The term means different things in different contexts, and the differences matter practically. This post is an attempt to be precise about what federated query execution actually involves, what makes it hard, and what the realistic tradeoffs are.
The core definition: a federated query is a query that pulls data from two or more separate, independent data systems and combines the results into a single response. The systems are not required to share infrastructure, a common schema, or even a common data model. The federation layer is responsible for making them appear unified from the query side.
Why you end up with multiple systems in the first place
Most growing companies do not choose to run multiple data systems. They accumulate them. A product starts on PostgreSQL. Analytics moves to Redshift or Bigquery because it scales better for aggregations. A third-party SaaS product that handles billing, support, or marketing creates its own data export. An acquired company brings its own stack. A custom events pipeline gets built alongside the main warehouse because the main warehouse is too expensive for high-volume raw events.
Five years in, many companies have three to five distinct data sources that are not formally integrated. The integration would require engineering time that has never been prioritized over other work. So questions that span more than one system get answered manually: an analyst pulls from each source separately, assembles a spreadsheet, and joins them by hand.
Federated query execution automates that manual assembly step. Instead of pulling from each system separately and joining in a spreadsheet, the query is decomposed across sources and the results are combined in the response.
The decomposition problem
The hard part of federated queries is not connecting to multiple databases. Most analytics tools can do that. The hard part is decomposing a single question into correct sub-queries for each source.
Consider a question like "show me revenue alongside customer support ticket volume for the past 90 days, grouped by month." Revenue data is in Snowflake. Support ticket data is in a separate PostgreSQL instance. Neither system knows about the other. To answer this question, the query layer needs to figure out that these two pieces of information come from different systems, generate a correct Snowflake query for revenue, generate a correct PostgreSQL query for ticket volume, execute both, and then join the results on the month dimension.
That join is the crux of the problem. If revenue is recorded with a timestamp in UTC and support tickets are recorded with a timestamp in KST, grouping by month requires timezone normalization before the join will produce correct results. If revenue is grouped by close_month (when the deal closed) and tickets are grouped by created_at, the month groupings are measuring different things and joining them directly produces a misleading comparison.
The federation layer has to resolve these inconsistencies. It cannot do that without metadata about each source: timezone conventions, date column semantics, granularity alignment. This is why schema indexing and metadata enrichment are prerequisites for federation, not features you can add on top later.
Entity resolution across systems
The second hard problem in federation is entity resolution: the same real-world entity often has different identifiers in different systems. A customer might be identified by an internal numeric ID in the transactional warehouse, by an email address in the marketing system, and by a UUID in the product analytics system. None of these identifiers is the same, but they all refer to the same person.
When a federated query needs to join customer-level data across two systems, the federation layer needs to resolve these identifiers to a common key. Options include: a dedicated identity resolution table that maps IDs across systems, using email as a common key where both systems store it, or accepting that the join can only be done at an aggregate level (by region, by time period) rather than at the entity level.
In practice, federated queries work well at aggregate dimensions and less well at record-level joins between systems that have no common identifier. This is not a failure of the approach; it is a reflection of the real complexity in the data. The alternative, manual cross-system assembly in a spreadsheet, has the same limitation. The difference is that a federated query system can at least surface the ambiguity explicitly rather than letting an analyst assume a join is valid when it is not.
Latency and when it matters
Federated queries run against each source system in real time. This means latency is the sum of the slowest individual query plus the result assembly overhead. For most analytical questions, which involve aggregations over bounded time windows, query execution takes one to five seconds per source. A federated query across two systems takes two to ten seconds, which is acceptable for ad-hoc analysis.
The tradeoff against a consolidated warehouse is real. A consolidated warehouse where all data has been ETL'd into a single system can answer these questions faster, because the join happens on co-located data rather than across network hops. The question is what you are trading for that speed: a consolidation pipeline that requires engineering maintenance, a refresh lag that means the consolidated data is never fully current, and the up-front investment in building the integration.
For ad-hoc analytical questions where a two-to-ten second response time is acceptable, federation avoids all of that overhead. For operational dashboards that need sub-second refresh or for computationally intensive transformations over very large datasets, a consolidated warehouse is the right tool. These are not competing approaches for the same use case; they serve different access patterns.
What federation does not solve
Federation makes cross-system queries technically possible. It does not make data quality issues disappear. If the revenue data in Snowflake includes a category of test transactions that have not been filtered out, the federated query will include them. If the support ticket system double-counts escalated tickets, the federated query inherits that double-counting.
The quality of federated results is bounded by the quality of data in each source system. Federation cannot repair inconsistent definitions, missing data, or known model limitations. Analysts who use federated queries need the same data quality awareness they would apply when querying any system directly.
We are not saying federation makes data quality less important. We are saying federation is a query execution mechanism, not a data quality layer. The two concerns are separate, and conflating them leads to disappointment when the cross-system result surfaces an inconsistency that was already present in the underlying sources.
The practical starting point
The most useful federated queries are the ones that replace recurring manual assembly. If your team has a weekly process where an analyst pulls from two systems and stitches them together in a spreadsheet to produce a combined view, that is the right starting point for federation. The question is well-defined, the systems are known, and the manual process has already validated that the join is meaningful.
Starting with ambiguous cross-system questions that have never been answered before adds the complexity of figuring out whether the join is valid on top of the complexity of executing it. That is a harder problem. Known recurring use cases where the semantics are understood produce better initial results and build more confidence in the approach before expanding to harder questions.