Multi-Database Federation Studio

Last update on October 14 2026 11:23:23 (UTC/GMT +8 hours)

An Introduction to Multi-Database Federation Studio

Enterprise data has a fragmentation problem. A single organization might run PostgreSQL for its transactional core, MySQL for a legacy application, SQLite for embedded edge devices, and a constellation of REST APIs that expose yet more data. Each source holds a piece of the truth, but no single source holds all of it. Answering even a simple question — "What is the total revenue by customer across all our systems?" — requires pulling data from multiple sources, reconciling formats and identifiers, and combining the results. Traditionally, this means ETL: extract from each source, transform into a common format, load into a warehouse, and only then analyze.

ETL works, but it carries significant costs. Pipelines are complex to build and maintain. Data freshness suffers — by the time data lands in the warehouse, it may already be stale. And the warehouse duplicates data that already exists elsewhere, multiplying storage costs and creating consistency challenges.

The Multi-Database Federation Studio takes a fundamentally different approach. Rather than moving data to the query, it moves the query to the data. Users write a single SQL query against a virtual catalog that spans PostgreSQL, MySQL, SQLite, and even REST APIs. The federation engine — built on Trino or Presto — parses that query, pushes predicates down to each source, retrieves only the relevant data, joins the results, and returns a unified table. No ETL, no duplication, no staleness. Just one SQL query spanning the entire data landscape.

What It Does

The studio presents a single SQL interface that abstracts away the heterogeneity of underlying sources. From the user's perspective, there is one database with one set of tables. Behind the scenes, each table is backed by a different source — a PostgreSQL table, a MySQL view, a SQLite file, or a REST API endpoint — but the user never has to think about that distinction.

The federation engine handles the complexity:

Query parsing and planning: The engine parses the SQL, identifies which tables are involved, and determines which parts of the query can be pushed to each source.

Predicate pushdown: Filters, projections, and aggregations are pushed down to the source databases wherever possible. If a query asks for orders WHERE created_at > '2025-01-01', the filter is sent to PostgreSQL rather than applied after data retrieval. This dramatically reduces the amount of data transferred across the network.

Distributed joins: When a query joins tables from different sources, the engine executes the join itself — pulling the necessary data from each source and performing the join in its own memory or on disk.

Unified results: The final result set is returned as a single table, regardless of how many sources contributed.

Beyond basic querying, the studio supports:

Cross-source joins: Combine data from PostgreSQL and MySQL in a single query
REST API integration: Treat API endpoints as queryable tables, with automatic pagination and JSON flattening
Caching: Store frequently accessed results in Redis to avoid repeated federation overhead
Schema discovery: Automatically discover and expose the schemas of connected sources

The result is a system where the data's physical location becomes an implementation detail rather than a constraint on analysis.

Architecture and Data Flow

Trino/Presto: The Federation Engine

Trino (formerly PrestoSQL) is the workhorse of the federation layer. It is a distributed SQL query engine designed specifically for querying data where it lives — across heterogeneous sources, at scale, without movement or duplication.

Trino's architecture is built around connectors. Each connector understands how to communicate with a specific data source, exposing its tables, columns, and statistics to Trino's query planner. Trino ships with connectors for PostgreSQL, MySQL, and dozens of other databases. For sources without a native connector — like REST APIs — custom connectors or the generic JDBC connector can be used.

When a query arrives, Trino's coordinator parses it, plans it, and distributes work across worker nodes. The planner is cost-based, using statistics from connectors to decide how to execute the query. For federated queries, the planner decides what to push down and what to execute centrally — a critical decision that determines performance.

Predicate pushdown is the most important optimization. When a connector supports it, Trino sends filter conditions to the source database, which applies them before returning data. A PostgreSQL table with a billion rows filtered to a thousand becomes a thousand rows transferred over the network, not a billion. Connectors advertise their pushdown capabilities, and Trino's planner exploits them.

SQLAlchemy for Dialect Abstraction

While Trino handles federation at the query engine level, SQLAlchemy provides dialect abstraction at the application level. The studio uses SQLAlchemy to generate SQL that is portable across the underlying sources and to manage connections to each.

SQLAlchemy's dialect system understands the differences between PostgreSQL, MySQL, and SQLite — type mappings, function names, quoting conventions, and SQL feature support. When the studio needs to introspect a source's schema or execute a metadata query, SQLAlchemy generates the appropriate dialect-specific SQL.

This abstraction is particularly valuable for the studio's metadata layer. Discovering tables, columns, and relationships across heterogeneous sources requires queries that differ by database. SQLAlchemy's reflection API normalizes these differences, presenting a uniform view of each source's schema.

FastAPI for Orchestration

FastAPI orchestrates the studio's operations: managing connections, submitting queries to Trino, caching results, and serving the frontend. Its async architecture handles the concurrent I/O that federation involves — a single query may touch four sources simultaneously, and FastAPI manages those interactions without blocking.

The API layer exposes endpoints for:

Connection management: Registering, testing, and removing data sources
Schema discovery: Listing available catalogs, schemas, and tables
Query execution: Submitting queries and streaming results
Caching: Checking cache before execution and populating it after
Metadata: Describing tables, columns, and statistics

Redis for Caching

Federation queries are expensive. They involve network round-trips to multiple sources, data transfer, and central processing. Caching is essential for making the studio responsive.

Redis stores query results keyed by a hash of the normalized query text and relevant parameters. When a query arrives, the studio checks the cache first. A hit returns results instantly; a miss executes the query and populates the cache. TTLs control staleness — short for volatile data, longer for stable reference data.

Redis is also used for:

Query result pagination: Large result sets are chunked and cached, allowing the frontend to page through results without re-executing the query
Session state: Tracking user sessions and their active connections
Rate limiting: Protecting sources from excessive query load

Docker Compose for Multi-DB Setup

Development and demonstration require a realistic multi-database environment. Docker Compose provides this: a single docker-compose.yml file that spins up PostgreSQL, MySQL, SQLite (via a volume), Trino, Redis, and the FastAPI application, all networked together.

This reproducibility is valuable for onboarding — a new developer runs docker-compose up and has a working federation environment in minutes. It is also valuable for testing, where the same environment can be spun up in CI to verify that federation queries work correctly across all supported sources.

Use Cases and Applications

Enterprise Data Teams with Legacy Systems

Large organizations rarely have the luxury of a single, modern database. Acquisitions bring disparate systems. Legacy applications persist because replacing them is too risky or expensive. Departmental databases proliferate as teams solve their own problems.

Federation allows these organizations to query across their entire landscape without migrating anything. A financial analyst can join customer data from a modern PostgreSQL system with transaction history from a legacy MySQL database, all in one query. The migration can happen later, on its own timeline, without blocking analysis today.

Real-Time Analytics Across Sources

When data must be fresh, ETL's latency is unacceptable. Federation queries the source directly, returning current data. A logistics company can query shipment status from an operational database, combine it with inventory data from a warehouse system, and correlate it with weather data from an API — all in real time.

API-Enriched Data

REST APIs are a common source of external data — exchange rates, weather, geolocation, market data. Treating APIs as queryable tables lets analysts join API data with internal data using SQL, rather than writing custom integration code for each API.

A retail analyst might join product sales from PostgreSQL with competitor pricing from a REST API, all in a single query. The federation engine handles API pagination, rate limiting, and JSON flattening transparently.

Data Lake and Warehouse Integration

Trino's connector ecosystem extends beyond relational databases to data lakes (HDFS, S3) and warehouses (Snowflake, BigQuery). A federation studio built on Trino can query across all of these, making it a unified interface to the entire data estate.

Prototyping and Exploration

Before committing to an ETL pipeline, analysts can use federation to explore whether a data combination is valuable. If it is, the pipeline can be built. If not, no pipeline investment was wasted. Federation lowers the cost of exploration, encouraging more experimentation.

Modern Technology Stack

Trino/Presto for Federation

Trino is the industry-standard open-source federation engine. Its connector architecture, cost-based optimizer, and predicate pushdown capabilities make it uniquely suited to querying heterogeneous sources efficiently. Presto, its predecessor, shares the same architecture and remains in use in many organizations.

SQLAlchemy for Dialect Abstraction

SQLAlchemy normalizes the differences between database dialects, providing a uniform interface for connection management, schema reflection, and query generation. Its maturity and ubiquity in the Python ecosystem make it a natural choice.

FastAPI for Orchestration

FastAPI handles the application layer: connection management, query submission, caching, and API serving. Its async architecture, Pydantic validation, and automatic documentation make it well-suited to the orchestration role.

Redis for Caching

Redis provides fast, flexible caching for query results and session state. Its TTL support handles staleness, and its data structures (strings, hashes, lists) map naturally to the studio's caching needs.

Docker Compose for Multi-DB Setup

Docker Compose defines the development environment as code, ensuring that every developer and every CI run has the same multi-database setup. This reproducibility eliminates "works on my machine" problems and accelerates onboarding.

User Benefits

Query Across Heterogeneous Sources Without ETL

The defining benefit is the elimination of ETL for analytical queries. Data stays where it lives; queries travel to it. This removes the latency, cost, and complexity of pipelines, and it ensures that analysis reflects current data rather than the last pipeline run.

Ideal for Enterprise Data Teams

Enterprise data teams deal with the accumulated complexity of years of system evolution. Federation meets them where they are, providing a unified query interface over a fragmented landscape. It does not require migration, does not require standardization, and does not require organizational consensus on a single database. It simply makes the existing landscape queryable as a whole.

Reduced Data Duplication

ETL pipelines duplicate data — the warehouse copy and the source copy. Federation eliminates this duplication, reducing storage costs and removing a source of inconsistency. There is one copy of the truth, in the source system, and it is queried directly.

Faster Time to Insight

Without ETL, there is no pipeline to build, no schedule to wait for, no transformation logic to maintain. Analysts can query new data combinations immediately, exploring and iterating without infrastructure work. Time to insight shrinks from weeks to minutes.

Preserved Source Autonomy

Each source system remains under the control of its owning team. Federation reads from sources; it does not write to them, does not impose schema requirements, and does not require changes to source applications. This respects organizational boundaries and avoids the political friction that centralized data initiatives often encounter.

Conclusion

The Multi-Database Federation Studio addresses a problem that is endemic to enterprise data: fragmentation. By providing a single SQL interface over PostgreSQL, MySQL, SQLite, and REST APIs — powered by Trino's federation engine, SQLAlchemy's dialect abstraction, FastAPI's orchestration, and Redis's caching — it enables querying across heterogeneous sources without ETL.

The technology stack is mature and proven. Trino powers federation at some of the largest organizations in the world. SQLAlchemy, FastAPI, and Redis are staples of the Python ecosystem. Docker Compose makes the environment reproducible. There is no experimental dependency, no bleeding-edge framework.

For enterprise data teams managing legacy and modern systems side by side, federation offers a path forward that does not require migration or consensus. It meets the data landscape as it is, makes it queryable as a whole, and leaves each source system under its own team's control. In a world of ever-increasing data fragmentation, tools that unify without centralizing become increasingly valuable — and the Multi-Database Federation Studio is exactly such a tool.