Federated Queries

---

Federated Queries on GCP

---

Federated Queries

Federated queries are a powerful database capability that enables an OLAP database to directly query and combine data from multiple external sources without first ingesting that data into the warehouse. This creates a flexible, unified query layer across diverse data systems.

How It Works

Rather than requiring you to move all data into your data warehouse before analysis, federated queries allow the OLAP database to reach out to external sources in real-time. When you execute a query, the database:

1. Connects to the specified external sources (object storage like S3, or databases like MySQL/PostgreSQL)

2. Retrieves the relevant data based on your query

3. Processes and combines results from multiple sources

4. Returns unified results as if querying a single database

Practical Example: Snowflake External Tables

Snowflake demonstrates this well with external tables on S3 buckets. When you create an external table, you define the data location and format, but the actual data remains in S3. When queried, Snowflake dynamically reads from S3 and can even join this external data with internal tables—bridging the gap between traditional data warehouses and data lakes.

Advanced Feature: Materialized Views

Some OLAP systems can convert federated queries into materialized views, which cache the query results. This provides near-native table performance while automatically refreshing when the external source changes, eliminating manual data synchronization.

Let me create a visual diagram to illustrate this architecture:

Federated Query Architecture example

Key Benefits

Flexibility: Query data wherever it lives without complex ETL pipelines

Real-time access: Always work with the most current data from source systems

Cost efficiency: Reduce storage costs by querying data in place rather than duplicating it

Simplified architecture: Fewer data movement processes to maintain and monitor

Federated queries essentially turn your data warehouse into a query engine that can seamlessly work across your entire data ecosystem, whether that data resides in cloud storage, relational databases, or the warehouse itself.

---

Are Federated Queries Only in OLAP Systems?

No, federated queries are not exclusive to OLAP systems. They're available across different types of database systems:

OLTP Databases: Traditional relational databases like PostgreSQL and MySQL support federated queries through features like:

• PostgreSQL's Foreign Data Wrappers (FDW) - can query remote databases, CSV files, and other sources

• MySQL's FEDERATED storage engine - allows querying tables on remote MySQL servers

• Oracle's database links - connect to and query remote Oracle and non-Oracle databases

OLAP/Data Warehouses: Systems like Snowflake, BigQuery, Redshift, and Databricks have robust federated query capabilities, often optimized for large-scale analytics across data lakes and external sources.

Specialized Query Engines: Tools like Presto, Trino, and Apache Drill are specifically designed as federated query engines that can query multiple heterogeneous data sources.

The concept works across the spectrum—it's just that OLAP systems tend to emphasize this capability more because analytics workloads commonly need to combine data from many sources.

It's not a SQL concept

Federated queries are enabled by SQL but not inherent to SQL itself. Here's the distinction:

SQL as the interface: Federated queries typically use standard SQL syntax to query external sources. From the user's perspective, you write normal SQL queries—the federation happens behind the scenes.

Implementation-specific: The actual federation mechanism is implemented by the database system, not defined by the SQL standard. Each system has its own way of:

• Defining external data sources

• Establishing connections

• Optimizing cross-source queries

• Handling authentication and security

Example syntax differences:

-- PostgreSQL (using Foreign Data Wrapper)
CREATE FOREIGN TABLE remote_orders (
    order_id INT,
    customer_id INT
) SERVER mysql_server;

-- Snowflake (using External Table)
CREATE EXTERNAL TABLE s3_logs
LOCATION = 's3://bucket/path/'
FILE_FORMAT = (TYPE = 'JSON');

So while SQL is the query language used, federated queries are really a database architecture feature that different systems implement in their own ways, much like how indexing or partitioning works—SQL provides the interface, but the implementation is system-specific.

---