Federated Database Architecture

One-line definition

An architecture in which multiple independent and often heterogeneous databases are presented as a single logical query layer, with a federation engine translating user queries into per-database sub-queries, executing them against each underlying source, and combining the results — all without physically moving the data into a central store. Each underlying database retains its autonomy, its schema, and its operational ownership; the federation layer adds query unification, predicate push-down optimization, and cross-source joins on top.

The Federated Database Architecture (FDA) — also called data virtualization in some marketing materials, federated query in the open-source community, or polyglot query in some academic literature — is the pattern of querying across multiple databases as if they were one without copying their data into a central warehouse. It contrasts directly with the Hub and Spoke Architecture ETL pattern, where data is physically moved into a central warehouse before being queried; federated databases query in place.

The original concept dates to a 1990 ACM Computing Surveys paper by Amit Sheth and James Larson titled “Federated Database Systems for Managing Distributed, Heterogeneous, and Autonomous Databases,” which laid out the taxonomy of federated systems and remains the canonical academic reference. The modern industrial implementations — Trino, Presto, Apache Drill, Denodo, AWS Athena Federated Query, Microsoft PolyBase — adopt the same conceptual framework with practical engineering compromises.

The pattern has become important because real enterprises have data scattered across dozens of operational systems (each chosen for its own purpose: Postgres for transactional data, MongoDB for documents, Snowflake for analytics, Elasticsearch for search, S3-on-Parquet for raw files, Salesforce for CRM, Workday for HR), and modern analytic questions cut across all of them. Asking “show me the lifetime value of customers who logged a support ticket last month” requires joining data from a CRM, a support system, a billing system, and an analytics warehouse — four different databases. Federated query lets this happen without first ETLing all four sources into one place.

1. Motivation — Why Query Across Sources Without Copying?

The motivation for federation is the alternative — physical centralization via ETL — has real costs that federation avoids.

1.1 No ETL Pipeline Maintenance

Traditional analytics requires an ETL pipeline per source, each of which is a non-trivial engineering investment. Schema changes in the source require ETL pipeline updates. New sources require new pipelines. Pipelines fail and need monitoring. Federation skips this entirely — the federation engine queries the source directly, so a schema change in the source is automatically visible in federated queries (modulo schema evolution complexities discussed in section 8).

1.2 Data Sovereignty and Residency

When data cannot move (regulatory: PHI must stay on-prem; GDPR: EU data must stay in EU; legacy: mainframe data cannot easily be exported), federation lets you query without moving. The source data stays where it is, only query results cross the federation boundary. This is one of the strongest arguments for federation in regulated industries.

1.3 Real-Time Freshness

A nightly ETL pipeline produces yesterday’s data. A federation query against the source produces now’s data. For operational analytics — fraud detection, customer support dashboards, real-time pricing — freshness can matter more than the slight performance cost of querying the live source.

1.4 Heterogeneity Without Unification

A central warehouse requires choosing a schema and conforming all source data to it. Federation lets each source keep its native schema; the federation layer maps queries to whatever each source actually has. This is faster to set up — no upfront schema modeling — but produces less semantically consistent answers (the federation layer must reconcile discrepancies query-by-query).

1.5 Cost Avoidance

Storing a petabyte of data in a central warehouse costs money. Storing it in cheaper systems (S3, on-prem) and querying via federation is often cheaper. Whether this nets out positive depends on query frequency: rare queries favor federation; frequent queries favor a copy.

2. The Architectural Components

A federated database has roughly four components, present in varying form in every implementation.

2.1 The Query Parser

Accepts SQL (or a similar declarative query language), parses it into an abstract syntax tree, validates against the union of all source schemas. The parser must understand which tables come from which sources and reject queries that reference unknown sources or unknown tables.

2.2 The Query Planner / Optimizer

Decides how to execute the parsed query. The optimizer’s hardest job is figuring out what to push down to each source vs what to compute centrally. A well-optimized federated query pushes filtering, projection, and aggregation to each source so that only minimal data crosses the federation boundary.

2.3 The Source Connectors

Per-source plugins that translate generic federation queries into source-specific dialects. Trino has dozens of connectors (Postgres connector, MongoDB connector, Kafka connector, Hive connector, Iceberg connector, etc.); each handles a specific source.

The connector knows the source’s SQL dialect (or non-SQL API), authentication, capabilities (which operations can be pushed down), and result format. Building a connector for a new source is the standard extensibility model.

2.4 The Execution Engine

Receives the planned query, dispatches sub-queries to source connectors, gathers results, performs cross-source joins and aggregations, returns results to the user. The execution engine is typically distributed itself — Trino runs on a cluster of worker nodes, each handling part of the query.

3. The Predicate Pushdown Optimization

The single most important optimization in federated databases is predicate pushdown (also called filter pushdown or push-down): pushing as much of the query’s work to the underlying sources as possible so that less data crosses the federation boundary.

3.1 The Naive Without Pushdown

Consider a query: SELECT name FROM postgres.customers WHERE country = 'US'. Without pushdown, the federation engine would: (1) read the entire customers table from Postgres, (2) filter country = 'US' in the federation engine, (3) project the name column, (4) return.

If customers has 100 million rows and 1 million are US, this transfers 100 million rows across the network just to keep 1 million. The query is slow and wasteful.

3.2 With Pushdown

The federation engine pushes the filter to Postgres: it sends SELECT name FROM customers WHERE country = 'US' to Postgres. Postgres uses its index on country to read only the 1 million US rows, transmits just those, and the federation engine returns them. Network transfer is 100× smaller; query is 100× faster.

3.3 What Can Be Pushed Down

The set of pushdownable operations depends on the source’s capabilities. Most modern federation engines push:

• Filters (WHERE) — universally pushdownable to SQL sources, partially pushdownable to NoSQL sources depending on the source’s filter capabilities.
• Projections (SELECT a, b FROM ...) — universally pushdownable.
• Sorts (ORDER BY) — pushdownable but the source may not respect them across the boundary.
• Aggregations (SUM, COUNT, AVG, GROUP BY) — pushdownable to SQL sources; some federation engines (Trino since 2021) can push down aggregations to Postgres, MySQL, etc.
• Joins — generally NOT pushdownable across heterogeneous sources, since each source only sees its own data. Joins must happen in the federation engine. Pushdownable within a single source.
• Window functions — recent additions; Trino can push down window functions to some sources but not all.

The federation engine’s quality is largely measured by how aggressively it pushes down. A federation engine that doesn’t push down aggregations across millions of rows is going to be much slower than one that does.

4. Real-World Technologies

The federated query space has consolidated around a handful of dominant technologies.

4.1 Trino (formerly PrestoSQL)

Originated at Facebook in 2012 (as Presto), forked to PrestoSQL in 2018 over governance disputes, renamed to Trino in 2020. Trino is the dominant open-source federated query engine. It supports dozens of connectors (Hive, Iceberg, Postgres, MySQL, MongoDB, Kafka, Cassandra, Elasticsearch, BigQuery, Redshift, and many more), runs on a master-worker cluster (the coordinator is the master, workers execute splits), and has been adopted by Netflix, Lyft, Airbnb, Salesforce, LinkedIn, Bloomberg, and many others.

Netflix’s data platform is one of the most-discussed Trino deployments. They use Trino + Apache Iceberg (a table format on top of S3) as the core query layer, with Trino federating across Iceberg, Cassandra, ElasticSearch, and several other sources. The architecture handles tens of thousands of analytical queries per day across petabytes of data.

Starburst is the commercial Trino distribution, with enterprise features (security, governance, more connectors, enterprise support).

4.2 PrestoDB

The other Presto fork. Maintained by Facebook (now Meta) and others. Less widely adopted than Trino in the open-source community but still significant in some deployments.

4.3 Apache Drill

A federated query engine focused on schema-on-read for files (JSON, Parquet, log files) plus traditional databases. Apache Drill is older than Presto and has dwindled in popularity as Trino has gained ground, but is still used in some deployments where its file-handling capabilities are valuable.

4.4 Denodo

The dominant commercial data-virtualization platform. Denodo targets enterprises that want federation as a managed offering with strong security, governance, and observability — features that the open-source engines have but require self-hosting. Denodo has been particularly successful in financial services and healthcare.

4.5 AWS Athena Federated Query

AWS Athena is fundamentally a Trino-derived query service over S3. The Federated Query feature lets Athena query other AWS data sources (RDS, DynamoDB, DocumentDB, Redshift) and external sources via custom Lambda-based connectors. The architecture is interesting because it spreads federation work across Lambda functions, charging per-query rather than for a always-on cluster.

4.6 Microsoft PolyBase

PolyBase is Microsoft’s federation layer in SQL Server. It allows SQL Server to query Hadoop, Azure Blob, Oracle, and several other sources using T-SQL. Less general than Trino but well-integrated for SQL Server shops.

4.7 Oracle Heterogeneous Services / Database Gateways

Oracle Heterogeneous Services is Oracle’s federation feature, dating to the early 2000s. It allows Oracle to query other databases (DB2, SQL Server, Sybase) via gateway products. Older and less general than Trino but still in use in legacy Oracle-centric enterprises.

4.8 BigQuery Omni

BigQuery Omni is Google’s federation feature that allows BigQuery to query data in AWS S3 and Azure Blob without moving it. Less general than Trino but well-integrated for BigQuery customers.

5. Real-World Adopters

5.1 Netflix — Trino + Iceberg

Netflix’s primary analytics architecture uses Trino as the query engine over Apache Iceberg tables sitting on S3, federated with Cassandra (operational data) and Elasticsearch (search index). The architecture handles hundreds of petabytes and tens of thousands of analytical queries per day. The choice of federation over centralized warehousing was driven by data scale (replicating everything would be too expensive) and source heterogeneity (Cassandra and Elasticsearch are operational systems that must remain operational).

5.2 Airbnb — Druid + Presto

Airbnb’s data platform uses Druid for low-latency time-series analytics and Presto/Trino for ad-hoc cross-source queries. The federation layer lets data scientists query both warm (Druid) and cold (S3-on-Parquet) data with the same SQL, with Trino routing the query parts to the appropriate engine.

5.3 LinkedIn — Internal Federated Stack

LinkedIn has built and described a federated query stack for analytics across their Espresso (key-value), Pinot (real-time OLAP), and Kafka data sources. The motivation: many internal teams want unified query access without each team building an integration into each source.

5.4 Salesforce — Trino-Based Internal Lake

Salesforce uses Trino for federated analytics across their internal data platforms. The query layer allows analysts to write SQL that joins data from many internal systems without each system needing to be ETLed first.

5.5 Many Enterprises — Denodo for Data-Mesh-Without-Pre-Loading

Denodo has been adopted by many enterprises (banks, insurers, healthcare) that want a Data Mesh Architecture-like setup but cannot afford to migrate all their data to a unified platform. Denodo provides the unified query layer; the underlying sources stay where they are.

6. The Architectural Diagram

flowchart TB
    User[User / BI tool / ML pipeline]
    Parser[Query Parser]
    Planner[Query Planner / Optimizer]
    Engine[Distributed Execution Engine<br/>workers do joins, aggregations]
    
    subgraph Connectors[Source Connectors]
        C1[Postgres Connector]
        C2[MongoDB Connector]
        C3[Snowflake Connector]
        C4[S3-Iceberg Connector]
    end
    
    subgraph Sources[Underlying Data Sources]
        DB1[(Postgres<br/>orders)]
        DB2[(MongoDB<br/>customers)]
        DB3[(Snowflake<br/>analytics)]
        DB4[(S3 + Iceberg<br/>events)]
    end
    
    User --> Parser
    Parser --> Planner
    Planner --> Engine
    Engine --> C1
    Engine --> C2
    Engine --> C3
    Engine --> C4
    C1 -- pushed-down query --> DB1
    C2 -- pushed-down query --> DB2
    C3 -- pushed-down query --> DB3
    C4 -- pushed-down query --> DB4
    DB1 -- filtered rows --> C1
    DB2 -- filtered docs --> C2
    DB3 -- filtered rows --> C3
    DB4 -- filtered rows --> C4

What the diagram shows. A user submits a query. The parser validates and produces an AST. The planner figures out which work goes to which source. The execution engine dispatches sub-queries through connectors to underlying sources, gathers results, and performs cross-source joins. The arrows from connectors to sources are pushed-down sub-queries; the reverse arrows are the (small, post-filter) result sets.

7. Worked Example — E-Commerce Lifetime Value Query

A concrete query makes the federation pattern clear.

7.1 The Query

A business analyst wants: “Show me the top 10 customers by lifetime value in the last 12 months, with their primary support contact and recent ticket counts.”

The relevant data lives in three sources:

• Postgres (orders database): order history with order amounts.
• MongoDB (customers database): customer profiles including primary support contact.
• Snowflake (analytics warehouse): aggregated event data including ticket counts.

7.2 The Federated SQL

SELECT
  c.name,
  c.primary_contact_email,
  o.lifetime_value,
  s.ticket_count_12m
FROM mongodb.customers c
JOIN (
  SELECT customer_id, SUM(amount) AS lifetime_value
  FROM postgres.orders
  WHERE order_date >= '2025-05-01'
  GROUP BY customer_id
) o ON c.customer_id = o.customer_id
JOIN (
  SELECT customer_id, COUNT(*) AS ticket_count_12m
  FROM snowflake.support_events
  WHERE event_date >= '2025-05-01' AND event_type = 'ticket_created'
  GROUP BY customer_id
) s ON c.customer_id = s.customer_id
ORDER BY o.lifetime_value DESC
LIMIT 10;

7.3 The Plan

A good federation engine will:

1. Push the WHERE order_date >= '2025-05-01' GROUP BY customer_id to Postgres. Postgres efficiently scans the recent partition (assuming partitioning by date), aggregates by customer, and returns one row per customer.

2. Push the WHERE event_date >= '2025-05-01' AND event_type = 'ticket_created' GROUP BY customer_id to Snowflake. Snowflake’s columnar storage and clustering keys make this fast.

3. Read the full customers collection from MongoDB (or push down a customer_id IN list once it’s known from Postgres — a more advanced optimization).

4. Join the three result sets in the federation engine’s memory. Sort and limit.

The federation engine’s join of three result sets (each maybe a few hundred thousand rows after pushdown) happens in distributed memory across the federation cluster. The result is a few rows.

7.4 What Could Go Wrong

If pushdown doesn’t work (the engine doesn’t recognize the filter as pushdownable, or the source doesn’t support the pushed-down operation), the query degrades to “scan everything from the source, filter in the engine.” A 12-month filter that doesn’t push down means transferring all of orders history — potentially terabytes. This is the dominant performance failure mode in federated systems.

If the federation engine doesn’t have enough memory to hold the join intermediate results, it spills to disk and slows down dramatically. This is why federation engines like Trino are sized aggressively for memory.

If the source is slow (Postgres index missing on customer_id, MongoDB query plan suboptimal), the federated query waits for the slowest source. Source-side performance matters as much as federation-engine performance.

8. Schema Evolution and Heterogeneity

A practical challenge of federated databases that doesn’t show up in centralized warehouses: schema differences between sources.

8.1 Type Mapping

Different sources have different type systems. Postgres has TIMESTAMP WITH TIME ZONE; MongoDB has BSON Date; Snowflake has TIMESTAMP_TZ. The federation engine must map each source’s types to a common type system. Most use a Trino-defined or Hive-derived type system as the lingua franca.

The mapping is occasionally lossy. A Postgres JSONB field might be mapped to a federation-engine JSON type, losing some Postgres-specific operators. A MongoDB nested document might be flattened or kept nested depending on how the connector handles it.

8.2 Schema Discovery

The federation engine must know the schemas of underlying sources. Some connectors discover schemas on the fly (Trino queries the source’s information schema); others require explicit schema definition. Schema-on-read sources (like Parquet files in S3) require the connector to infer schema, which can produce different schemas in different queries.

8.3 Schema Drift

Over time, source schemas change. New columns, type changes, deprecated columns. The federation engine must handle these gracefully — typically by re-reading source schemas periodically. Catalog-based federation engines (with a central metadata catalog like AWS Glue, Hive Metastore, or Apache Iceberg Catalog) handle this better than ones that re-introspect each query.

9. Variants

9.1 Read-Only Federation

By far the most common. The federation engine only reads from sources; writes still go to the underlying sources directly through their native APIs. Trino, Presto, Drill, Denodo all default to this model.

9.2 Federated Writes

Less common, much harder. A federated write would be a transaction that updates multiple underlying sources atomically. This requires distributed transactions across heterogeneous sources, which is operationally and technically extreme. Some federation engines support limited write through specific connectors (Trino can write to Iceberg tables, Hive tables, and a few others), but cross-source ACID writes are rare.

The distributed-transaction problem (see Two-Phase Commit) doesn’t compose well with heterogeneous sources because each source’s transaction model differs. Most federation users accept that writes must go to native sources.

9.3 Caching Federation

A federation engine that caches frequent query results to reduce load on underlying sources. Most production deployments add a caching layer (Trino has materialized views since 2022, Denodo has comprehensive caching, AWS Athena uses query result caching). Caching trades freshness for performance — the cached result may be stale by the cache TTL.

9.4 Federation with Data Movement

A hybrid: the federation engine queries sources directly when the data is small or fresh, but batches frequent queries into a periodic ETL into a hot store (like Iceberg). This blurs the line between federation and ETL but is pragmatic for many deployments.

10. Comparison with Adjacent Patterns

10.1 vs Data Lake Architecture

A data lake centralizes all data into a single (usually object) store, typically with a schema-on-read approach (Parquet/Iceberg/Delta on S3). Federation queries multiple sources without centralizing. The choice depends on cost (federation avoids storage replication) and freshness (federation queries live data; a lake is as fresh as its loading pipelines).

Many modern architectures combine both: a lake holds the cold/historical data, federation queries combine lake data with operational sources for fresh signals.

10.2 vs Data Mesh Architecture

Data mesh is an organizational pattern (domain-owned data products) plus a technical pattern (standardized interfaces between domain data products). Federation is one possible implementation of the technical layer of data mesh — it lets domain data products be queried without first being ingested into a central platform. But not every data mesh uses federation; many use centralized lakes with domain-owned subsets.

10.3 vs Hub and Spoke Architecture ETL

Hub-and-spoke ETL physically copies data into a central warehouse. Federation queries in place. The trade-offs:

• ETL: faster queries (data is local in the warehouse), but stale (as fresh as the last load) and expensive (storage replication).
• Federation: slower queries (each source must be hit), but fresh (live data) and cheaper (no storage replication).

Most large enterprises use both: ETL for the heavily-queried analytics workloads, federation for the long-tail or fresh-data-required queries.

10.4 vs Distributed SQL Database System Design

A distributed SQL database (CockroachDB, Spanner, TiDB, YugabyteDB) provides ACID transactions across a single logical database implemented over many nodes. Federation provides queries across multiple independent databases. The two patterns are conceptually different: distributed SQL is one database with many nodes; federation is many databases with one query layer.

10.5 vs Sharded Architecture

Sharding partitions one logical database across many shards, each owned by the same operator. Federation joins across many independently owned and operated databases. Sharding is operationally one system; federation is operationally many.

11. Pitfalls

11.1 Federation Overhead — Joining Across DBs Is Slower

A join inside one database can use indexes, statistics, and data-locality tricks. A federation join across two databases must transfer data across the network and join it in the federation engine. This is fundamentally slower. For very large joins, the difference can be 10× or more.

The mitigation is to avoid cross-source joins where possible (denormalize so the data lives together) or use federation only for low-frequency analytical queries where the slowness is tolerable.

11.2 Security Model Differences Between Sources

Each underlying source has its own authentication, authorization, and audit mechanisms. The federation engine must reconcile them. A user authorized in Postgres may not be authorized for the equivalent data in MongoDB. The federation engine typically uses one of two patterns: (1) a service-account that has full access to all sources, with per-user authorization enforced by the federation engine itself; (2) per-user credentials that authenticate to each source separately.

The first is simpler but creates a lot of trust in the federation engine — a bug there can leak data across security boundaries. The second is more secure but operationally complex (managing credentials for every user against every source).

11.3 Query Planner Gives Up When One Source Is Small Unstructured

A federation engine’s planner relies on source statistics to make pushdown decisions. If a source has no statistics (e.g., a small unstructured JSON-files-on-S3 source), the planner may default to reading all data. With 1 GB of unstructured data, this is fine; with 1 TB, it’s catastrophic. The mitigation is to ensure every source has statistics or to partition large unstructured sources.

11.4 The “One Slow Source Ruins the Query” Problem

A query that joins data from three sources is bottlenecked on the slowest of the three. If one source is a slow legacy system or an overloaded production database, the federated query inherits its slowness. There is no way around this — federation cannot make the source faster.

The mitigation is monitoring: track per-source query latency in federation engine metrics, identify slow sources, and either fix them, migrate them to faster substrates, or limit how often they’re federated.

11.5 ACL / Security Complexity Multi-Source

Even with a unified federation security model, the underlying sources still have their own access controls. A user removed from a federation engine’s allowlist may still have direct access to the underlying source. A federation engine “deny all to user X” doesn’t prevent X from connecting to Postgres directly. This is the natural consequence of federation — sources retain their autonomy, including their security autonomy.

11.6 Metadata Catalog Drift

Federation engines maintain catalog metadata about underlying sources (table list, column types). When the source changes (a column is added or removed), the catalog can be out of sync. Queries against the source succeed but federation queries fail until catalog is refreshed.

The mitigation is automated catalog refresh and good error messages on catalog mismatches.

11.7 Cross-Source Joins as the Foreground Path

A common anti-pattern is using federation for the foreground of a user-facing application: a customer support tool that does cross-source federated joins for every page load. Federation is too slow for this — page latency would be seconds. Federation belongs in analytical or batch workloads, not in user-facing low-latency paths.

12. Operational Considerations

12.1 Federation Cluster Sizing

The federation engine must be sized for peak query load and for the largest single query’s memory footprint. A query that joins 100 million rows from two sources requires the engine to hold both sides in memory simultaneously. Trino-style engines are typically sized at ~32 GB RAM per worker for moderate workloads, much higher for heavy joins.

12.2 Source Connection Pooling

Each query may open many connections to underlying sources (one per worker per source per query). Connection-pool exhaustion at the source side is a frequent failure mode. The federation engine must pool connections aggressively and respect source connection limits.

12.3 Statistics Maintenance

Pushdown effectiveness depends on accurate per-source statistics. Stale statistics produce bad plans. The federation engine’s catalog must be refreshed periodically — either by the engine pulling stats from sources, or by the source pushing stats to the catalog.

12.4 Observability

Federated queries are notoriously hard to debug. A slow query might be slow because of any of: slow source, network, federation engine memory pressure, bad plan, lock contention at source. Mature federation deployments have detailed per-stage timing instrumentation in the federation engine — Trino’s Web UI shows per-stage progress, which is essential for debugging.

13. Interview / Design-Discussion Framing

In system design interviews, federation comes up when the prompt mentions “multiple data sources” or “querying across services.” The interviewer wants the candidate to:

1. Recognize federation as one option. Often the obvious answer is a centralized warehouse + ETL; federation is the alternative worth considering when freshness, cost, or data-sovereignty matters.

2. Explain the pushdown optimization. A candidate who understands what pushes down well demonstrates deeper understanding.

3. Identify the failure modes. Slow source dominance, cross-source-join overhead, security-model reconciliation.

4. Compare with alternatives. When does centralized warehousing win? When does data mesh + federation win?

5. Address security thoughtfully. Federation across sources with different security models is non-trivial. Strong candidates address this.

14. The Cost-Based Optimizer in Federated Settings

A specific technical challenge of federated databases worth elaborating: the cost-based optimizer’s job is fundamentally harder than in a single database.

In a single database, the optimizer has detailed statistics — table sizes, column histograms, index cardinality estimates, and execution-engine cost models all in one place. It can evaluate dozens of candidate query plans and pick the cheapest in milliseconds.

In a federated database, the optimizer has limited information about each source. It may know the source’s table sizes (if the catalog is populated) but rarely knows column histograms or detailed selectivity estimates. The optimizer must make decisions like “should I push down this aggregation to Postgres or do it centrally?” without knowing how fast Postgres will execute the aggregation. The result: federated optimizers tend to be more conservative, less aggressive about reordering operations, and more reliant on hints from users.

Adaptive query execution (a recent Trino feature) helps: the engine starts with an initial plan, monitors source response times during execution, and adjusts the plan dynamically. This is especially valuable for federated queries where source behavior is hard to predict in advance.

15. The Federation-and-Caching Pattern

A pragmatic pattern that bridges federation and centralization: use federation for ad-hoc queries, but cache frequent query results in a fast central store.

The pattern works like this: a query first checks the cache. If the result is fresh enough (per a TTL), return it. If not, run the federated query, store the result in the cache, return it. Subsequent queries hit the cache and avoid the federation overhead.

This dramatically improves performance for the long tail of repeated dashboard queries while preserving federation’s freshness benefit for ad-hoc queries. Most production deployments end up with some flavor of this pattern — Trino’s materialized views feature, Denodo’s caching layer, or external caches built around the federation engine.

The trade-off is that cached results can be stale. The cache TTL is the lever: shorter TTL = fresher data + more federation load; longer TTL = staler data + less load. Most production deployments use 5-60 minute TTLs depending on the freshness requirement of the consuming dashboards.

16. The Federation Anti-Pattern of “Just Add a Connector”

A common organizational anti-pattern: a team has data in a new SaaS system. They install a federation connector for it. They run ad-hoc cross-source queries against it. The new connector becomes a quiet dependency on production — every analytical workflow now hits the SaaS system every time it runs.

The result: the SaaS system’s API quotas get exhausted, the SaaS vendor charges a fortune for high query volume, and the federation engine queue fills up waiting on the slow SaaS API. The “easy” federation has produced an operational mess.

The anti-pattern’s lesson: federation is not free. Each source you federate against accepts query load from the federation engine. High-volume analytical workloads against a SaaS source will exhaust the source’s quotas, slow down the source’s other users, and produce unreliable federation queries. For high-volume analytical use, periodic ETL of the SaaS data into a fast warehouse is cheaper and more reliable than federation. Federation belongs in the long tail of low-volume queries, not in the hot path.

17. See Also

• System Architectures MOC — index of architecture notes
• SWE Interview Preparation MOC — interview prep coverage
• Hub and Spoke Architecture — alternative pattern (centralized via ETL)
• Data Lake Architecture — central storage of raw data
• Data Mesh Architecture — domain-owned data products, often uses federation
• Distributed SQL Database System Design — single logical database, distinct from federation
• Sharded Architecture — single-system partitioning, distinct from federation
• Two-Phase Commit — needed for federated writes (rare)
• Master-Worker Architecture — federation engines like Trino are master-worker internally
• Cell-Based Architecture — orthogonal pattern for failure isolation
• Edge Computing Architecture — different architectural concern (latency placement)
• Hybrid Cloud Architecture — federation often spans on-prem and cloud sources
• Architecture Decision Records — for documenting federation vs ETL choices

15. Glossary

• Federated database — Multiple independent databases queried as one through a federation layer.
• Data virtualization — Marketing term for the same concept; emphasizes the “virtual” unified view over physical sources.
• Predicate pushdown — Pushing filters and other operations to underlying sources to minimize data transfer. The single most important federation optimization.
• Connector — A per-source plugin that translates federation queries to source-specific dialects.
• Catalog — Metadata about underlying sources (tables, columns, statistics) that the federation engine uses for planning.
• Coordinator — In Trino terminology, the master node of the federation cluster; holds the query parser, planner, and dispatches work.
• Worker — In Trino terminology, the executing nodes of the federation cluster.
• Split — A unit of work in Trino — typically a chunk of one source’s data that one worker reads.
• Schema-on-read — Schema is inferred at query time rather than pre-defined; common for file-based sources.
• Schema-on-write — Schema is defined and enforced at data ingestion; traditional databases.
• Iceberg / Delta / Hudi — Table formats for data lakes; provide schema evolution, time travel, and partition pruning over Parquet files.
• Hive Metastore — A widely-used catalog service that many federation engines (Trino, Spark) use to store table metadata.
• AWS Glue Catalog — AWS’s managed catalog service; used by Athena and many other AWS services.
• Lineage — The dependency graph from source data to query results; harder to track in federated systems than in centralized warehouses.
• Sheth-Larson taxonomy — The 1990 academic classification of federated systems by autonomy and heterogeneity. Still influential.
• Data fabric — An umbrella marketing term that includes federation alongside other data-integration techniques.
• Polyglot persistence — Using different databases for different workloads; federation is one way to query across the resulting heterogeneity.
• Drill-through — The pattern of starting at aggregated data and drilling down to source-level detail; federation enables this without ETL.
• Materialized view — A pre-computed query result stored in the federation engine for fast repeat access.
• Cost-based optimization — Query planning that uses statistics about source data to choose execution plans.
• Adaptive query execution — Modifying query plans during execution based on observed source behavior; recent feature in Trino and similar engines.
• Push down — Synonym for predicate pushdown; the federation-engine-to-source direction of operation delegation.
• Pull up — The opposite: bringing operations from source to engine when they cannot be pushed down.
• Cross-source join — A join whose two sides come from different underlying databases. Cannot be pushed down; must happen in the federation engine.
• Connection pool — Pool of pre-opened connections to underlying sources, reused across queries to avoid per-query connection overhead.
• Catalog refresh — Periodic update of the federation engine’s view of source schemas and statistics.
• Federation cluster — The set of nodes (coordinator + workers in Trino terminology) that run the federation engine.
• Materialized view — Pre-computed query result, refreshed periodically, that turns federation queries into fast lookups.
• Stage — A unit of execution in Trino’s plan; each stage runs across all workers and processes a subset of the query.
• Split — A unit of work within a stage; one source row range or file.
• Sheth-Larson five-level architecture — The 1990 academic taxonomy: federated database systems, federation layer, component databases, plus integration and management layers.
• Heterogeneous federation — Federation across sources with different data models (relational, document, key-value).
• Homogeneous federation — Federation across multiple instances of the same database type; simpler than heterogeneous.
• Tightly-coupled federation — Sources share a common schema and the federation layer enforces consistency. Rare in practice.
• Loosely-coupled federation — Sources retain autonomy; the federation layer reconciles schemas at query time. The dominant industrial pattern.
• Data lake house — A pattern combining data lakes (cheap object storage) with warehouse-like query semantics (Trino + Iceberg/Delta). Often the foundation of a federation architecture.
• Lakehouse — Synonym for data lake house.
• Time travel — Querying a historical version of data, supported by Iceberg, Delta, and Hudi table formats.
• Partition pruning — Skipping entire partitions of source data based on query filters. The most powerful pushdown for partitioned sources.
• Bucket pruning — Skipping entire buckets within a partition. Used by Iceberg and Hive-style tables.
• Vectorized execution — Processing many rows at a time within the federation engine, using SIMD instructions. Trino has this for many operators.
• Distributed join — Joining across federation workers when one side of the join is too large to fit in any single worker’s memory. Trino implements broadcast and shuffle joins.
• Broadcast join — A join strategy where the smaller side is broadcast to every worker holding the larger side; fast when the smaller side fits in memory.
• Shuffle join — A join strategy where both sides are repartitioned by join key across workers; necessary when neither side is small.
• Iceberg catalog — A catalog implementation specifically for Apache Iceberg tables. AWS Glue, Hive Metastore, and Iceberg’s REST catalog all support Iceberg.
• Schema registry — A central registry of schemas, often used with streaming sources (Kafka with Confluent Schema Registry).
• Federation versus virtualization — Some literature distinguishes “federation” (loose coupling, autonomy) from “virtualization” (tighter coupling, possibly stateful). In practice, the terms are used interchangeably.
• Push-down decision — The optimizer’s choice of what work to push down vs do centrally. Source statistics, capabilities, and engine memory all factor in.
• Trino federation footprint — The combined set of all sources Trino is connected to in a deployment. Often dozens at large companies.