Athena Federated Queries Explained

Athena was built to query files in S3. A good deal of the data analysts want is somewhere else: orders in a MySQL database, sessions in DynamoDB, a reference table in Redshift. Federated Query extends Athena to those sources. AWS describes it as running SQL across data stored in relational, non-relational, object and custom data sources, either to query the data in place or to build pipelines that extract it into S3 (Athena docs: federated query).

The appeal is obvious: one query, several systems, no pipeline to build. The limits are less obvious, and they decide whether federation is the right tool.

How it works

Each external source is reached through a connector, a piece of code that translates between Athena and the source. When a query touches a federated table, Athena invokes the connector to determine which data to read, manages parallelism, and pushes down filter predicates. The rows come back to Athena, which performs joins and aggregations with any other tables in the query.

There are two kinds of connector:

• Athena data catalog connectors run as an AWS Lambda function in your account. Custom connectors written with the Athena Query Federation SDK are of this kind.
• Glue Data Catalog federated connectors use an AWS Glue connection, appear as catalogs in Glue, and can be governed with Lake Formation's fine-grained permissions. The guide notes that some of these, created from 21 April 2026, do not use a Lambda function in your account at all.

AWS provides prebuilt connectors for many sources, among them DynamoDB, CloudWatch Logs, DocumentDB, OpenSearch, Redshift, MySQL, PostgreSQL, Oracle, SQL Server, Snowflake, Google BigQuery and Kafka. Which connector types each source supports is tabulated in the guide.

Lambda-based connectors have one more moving part. A Lambda response is limited in size, so larger results are written to a spill bucket in S3 and read from there. The DynamoDB connector's documentation recommends a lifecycle rule on that location to delete old spill files (Athena docs: DynamoDB connector).

What a federated query looks like

Once a connector is registered as a data source, its tables are addressed by catalog, database and table name. The following is written from the documentation and has not been run; mysql_orders stands for a registered connector and lake for a Glue database:

-- Join live orders in MySQL with a customer table in the data lake
SELECT c.segment,
       count(*) AS orders,
       sum(o.total) AS revenue
FROM mysql_orders.sales.orders AS o
JOIN awsdatacatalog.lake.customers AS c
  ON c.customer_id = o.customer_id
WHERE o.order_date >= date '2026-09-01'
GROUP BY c.segment;

-- Passthrough: send a query in the source's own SQL
SELECT *
FROM TABLE(
  mysql_orders.system.query(
    query => 'SELECT status, COUNT(*) AS n FROM sales.orders GROUP BY status'
  )
);

In the first query the date filter can be pushed down, so MySQL returns only September's orders; the join and the aggregation happen in Athena. Note the absence of double-quoted names: the guide states that delimited identifiers are not currently supported for federated queries.

The second form is a passthrough query. It pushes the full query down to the source in the source's own language through a table function, and returns a table that Athena can use (Athena docs: passthrough queries). Here the grouping runs inside MySQL and only the summary travels. Passthrough is read-only, does not support Lake Formation fine-grained access control, and is not available on sources registered as a Glue Data Catalog.

Pushdown decides performance

A federated query is only as fast as the amount of data that has to cross from the source to Athena. If the connector can push the filter down, the source does the filtering and returns a few rows. If it cannot, every row is fetched and filtered in Athena.

The guide is direct about the consequence: for connectors that do not support predicate pushdown, queries with a predicate take longer, averaging about 2 minutes on small datasets, and on large datasets many queries can time out (Athena docs: available connectors).

Practical rules follow:

• Filter on columns the source can use, ideally indexed ones, and keep filters simple.
• Select only the columns needed.
• Aggregate in the source with a passthrough query when the summary is all you want.
• Avoid joining two large federated tables. Both sides must be pulled into Athena first.

Documented limits

• Read-only. Write operations such as INSERT INTO are not supported on federated sources.
• Views on federated sources are stored in Glue, not in the source.
• Networking. Some prebuilt connectors need a VPC and security group, and using Secrets Manager for credentials needs a VPC endpoint.
• Third-party connectors built with the SDK are not supported by AWS; support comes from the connector's provider.

What it costs

The pricing page states that federated queries are billed per terabyte scanned by Athena, aggregated across data sources, with the usual 10 MB minimum per query, and that they also invoke Lambda functions in your account, charged at standard Lambda rates (AWS: Athena pricing).

There is a third cost that appears on no AWS bill: load on the source. A federated query runs against a live database. An analyst's wide scan on the production orders table competes with the application for the same resources. Pointing connectors at a read replica, where one exists, keeps analytical queries away from transactions.

Federate or ingest?

Federation and ingestion also combine. A scheduled CREATE TABLE AS SELECT from a federated source into Parquet on S3 is a simple extraction pipeline, which is the second use AWS names for the feature. For sources that change constantly, change data capture is the more complete answer.

Where it fits

Treat federated queries as a convenience for reach, not as a replacement for a data lake. They are at their best for exploration, for small lookups, and for bootstrapping a pipeline. Curated, repeated analytics belongs on data that has been landed in S3, where Athena reads it cheaply. For governing who can see what across those catalogs, see AWS Lake Formation; for the most common NoSQL source, see Amazon DynamoDB explained.

Querying data lakes on AWS, with S3, Glue and Redshift, is covered in the Data Engineering course.

Sources

• Use Amazon Athena Federated Query (Amazon Athena User Guide) — AWS Documentation
• Available data source connectors (Amazon Athena User Guide) — AWS Documentation
• Use federated passthrough queries (Amazon Athena User Guide) — AWS Documentation
• Amazon Athena DynamoDB connector (Amazon Athena User Guide) — AWS Documentation
• Amazon Athena pricing (Amazon Web Services) — Amazon Web Services