Query Federation and External Data
You'll learn how to query data across multiple sources using federated queries in Athena, BigQuery, and cross-database access patterns without moving or duplicating data.
What is Query Federation?
Query federation lets you query data where it lives — across different databases, data lakes, warehouses, or even third-party services — using a single SQL statement. Think of it like having one universal remote that controls your TV, streaming box, and sound system: instead of switching tools, you use one query engine to access everything.
Rather than extracting, transforming, and loading (ETL) data into a central location, federated queries reach out to external sources in real-time or near-real-time, join datasets on-the-fly, and return unified results.
How Cloud Platforms Handle Federation
Amazon Athena uses federated query with data source connectors (Lambda-based) to query RDS databases, DynamoDB tables, Redis, or on-premises databases alongside S3 data lakes. You register a connector, then reference external tables in your queries.
Google BigQuery supports federated queries through external data sources like Cloud SQL, Cloud Spanner, or Google Sheets. You create external tables pointing to these sources, then join them with native BigQuery tables seamlessly.
Azure Synapse Serverless SQL Pool enables querying across Azure SQL databases, Cosmos DB, and data lakes simultaneously using T-SQL syntax.
Common Access Patterns
- Cross-database joins: Combine operational data (RDS) with analytical data (S3/data lake)
- Real-time enrichment: Join streaming data with reference tables in external databases
- Multi-cloud integration: Query datasets across AWS, Azure, or GCP without replication
Federation trades some performance for flexibility — queries may be slower than native operations due to network latency and remote processing.
Key Takeaway:
Query federation lets you analyze data across multiple heterogeneous sources with SQL, eliminating data movement while enabling real-time cross-system insights.