Why This Matters

Athena Federated Query breaks down data silos by enabling SQL queries across multiple data sources without moving data. It eliminates the need for complex ETL pipelines when you need ad-hoc analytical access to operational data. Understanding federated query architecture, Lambda connectors, and predicate push-down is critical for building cost-effective, secure data access patterns on AWS. This service can reduce time-to-insight from days to minutes.

Federated Query Architecture

Real-World Project Structure

athena-federated-project/
+-- connectors/
�   +-- rds-mysql/
�   �   +-- connector-config.json
�   �   +-- deploy.yaml
�   �   +-- test-queries.sql
�   +-- rds-postgres/
�   �   +-- connector-config.json
�   �   +-- deploy.yaml
�   +-- dynamodb/
�       +-- connector-config.json
�       +-- deploy.yaml
+-- glue/
�   +-- catalog-definitions/
�   �   +-- rds_users_table.json
�   �   +-- rds_orders_table.json
�   �   +-- dynamodb_orders_table.json
�   +-- database-mappings.json
+-- queries/
�   +-- cross_database_join.sql
�   +-- dynamodb_analytics.sql
�   +-- operational_reporting.sql
�   +-- data_validation.sql
+-- deploy/
�   +-- deploy-connectors.sh
�   +-- cfn-template.yaml
�   +-- iam-roles.yaml
+-- monitoring/
    +-- cloudwatch-dashboard.json
    +-- alarms.yaml
    +-- query-performance.sql

Lambda Connectors

Lambda connectors are the backbone of federated query. Each connector is a Lambda function that implements the Athena Federation SDK interface.

Official AWS Connectors

Registering a Connector

{
  "columns": [
    {"name": "id", "type": "integer"},
    {"name": "username", "type": "varchar"},
    {"name": "email", "type": "varchar"},
    {"name": "created_at", "type": "timestamp"}
  ],
  "connectionDetails": {
    "host": "my-rds-instance.abc123.us-east-1.rds.amazonaws.com",
    "port": "3306",
    "database": "myapp"
  },
  "connector": "aws-athena-connector-rds-aurora-mysql",
  "tableName": "users"
}

Querying RDS from Athena

SELECT
    r.region_name,
    COUNT(DISTINCT c.customer_id) AS total_customers,
    SUM(o.order_amount) AS total_revenue,
    AVG(o.order_amount) AS avg_order_value
FROM "rds_catalog"."mydb"."regions" r
JOIN "rds_catalog"."mydb"."customers" c ON r.id = c.region_id
JOIN "rds_catalog"."mydb"."orders" o ON c.id = o.customer_id
WHERE o.order_date >= DATE '2025-01-01'
GROUP BY r.region_name
ORDER BY total_revenue DESC;

Key Configuration Requirements

1. VPC Placement: Lambda must be in the same VPC as the RDS instance
2. Security Groups: Lambda SG must have outbound access to RDS on its port
3. IAM Role: Requires rds-data:ExecuteStatement permissions
4. Secrets Manager: Store DB credentials and reference in connector config
5. Subnet Configuration: Lambda needs access to subnets with NAT route to RDS

DynamoDB Federated Queries

SELECT
    device_type,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    COUNT(*) AS reading_count
FROM "dynamodb_catalog"."iot"."sensor_readings"
WHERE partition_key BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY device_type;

DynamoDB-Specific Considerations

• No joins: DynamoDB connector only supports single-table queries
• Filter push-down: Partition key and sort key filters are pushed down
• Cost model: DynamoDB scans on large tables can be expensive
• TTL awareness: DynamoDB connector automatically filters out TTL-expired items
• Item limit: Queries are limited by DynamoDB scan limits

Predicate Push-Down Formula

Data Transfer = Total Table Size x (1 - Filter Selectivity)
Latency = Query Time + Network Transfer + Lambda Cold Start
Cost = Athena Scan Cost + Lambda Duration x Memory + Source Read Cost
Optimal Filter Selectivity > 90% for cost efficiency

Performance Considerations

Security Considerations

Interview Questions and Answers

Q1: What is Athena Federated Query and when should you use it?

Answer: Athena Federated Query allows you to run SQL queries against external data sources (RDS, DynamoDB, OpenSearch, etc.) directly from Athena without moving the data into S3. Use it when you need ad-hoc analytical access to operational data without building ETL pipelines, when data volume is too large for batch exports, or when you want a single SQL interface across heterogeneous sources. Avoid it for repeated, high-volume analytical queries where ETL to S3 is more cost-effective.

Q2: Explain the architecture of a federated query at a high level.

Answer: When a federated query is submitted: (1) Athena parses the SQL and resolves table names via Glue Data Catalog to find the associated Lambda connector ARN; (2) Athena invokes the Lambda function with metadata including the query constraint; (3) The connector MetadataHandler determines schema and creates splits for parallel reading; (4) Multiple RecordHandler invocations read data in parallel splits; (5) Each handler translates Presto SQL to native query language and pushes predicates down; (6) Results stream back through Lambda response channels to Athena.

Q3: What is predicate push-down and why is it critical for federated queries?

Answer: Predicate push-down means pushing WHERE clause filters, column projections, and aggregations to the external data source for execution, rather than scanning all data and filtering in Athena. This is critical because it minimizes data transfer between the source and Athena, reduces Lambda response payload size, leverages the source native indexes (e.g., DynamoDB partition key, RDS B-tree indexes), and reduces load on the source database. Without it, a federated query would scan the entire table.

Q4: How does Athena handle schema discovery for federated sources?

Answer: The Lambda connector implements a MetadataHandler that responds to GetSchemas and GetPartitions calls from Athena. For relational databases, this queries the database INFORMATION_SCHEMA or system catalog tables. For DynamoDB, it inspects the table item structure. The schema is then registered in Glue Data Catalog so Athena knows the column names and types. You can also manually define the schema in Glue to override auto-discovery.

Q5: What are the key Lambda configuration requirements for a federated query connector?

Answer: VPC Configuration: Lambda must be deployed in the same VPC as the data source with network access. Security Groups: Outbound rules must allow traffic to the source on the correct port. IAM Role: Needs permissions for the source (e.g., rds-data:ExecuteStatement). Memory: Increase for CPU-bound query processing (256MB-3008MB). Timeout: Set appropriate timeout (up to 15 minutes) for large result sets. Secrets Manager: Store database credentials securely and reference in connector configuration.

Q6: How do you handle cross-source joins in federated queries?

Answer: Athena supports cross-source joins natively - you can join tables from different Lambda connectors or mix federated tables with S3-backed tables in a single query. Athena coordinates multiple Lambda invocations and performs the join. However, performance depends on the slowest source, data types must be compatible between sources, and for repeated cross-source joins, consider materializing the joined dataset in S3. DynamoDB connectors do not support joins between DynamoDB tables directly.

Q7: What are the cost implications of federated queries compared to traditional ETL?

Answer: Federated Query Cost: Athena charges per GB scanned + Lambda invocation cost (duration x memory) + source read cost (RDS I/O, DynamoDB RCU/WCU). ETL Cost: Glue/EMR compute cost + S3 storage cost + data transfer cost + ongoing maintenance. Trade-off: Federated queries are cheaper for ad-hoc, infrequent queries; ETL to S3 is cheaper for high-frequency, high-volume analytical workloads. Use federated queries for exploration and prototyping, then build ETL pipelines for production dashboards.

Q8: How do you troubleshoot slow federated queries?

Answer: (1) Check CloudWatch Logs for the Lambda connector - look for slow native queries or connection timeouts; (2) Verify predicate push-down - use EXPLAIN in Athena to see if filters are being pushed to the source; (3) Check Lambda memory - insufficient memory can cause garbage collection pauses; (4) Review source database performance - the source may be under load; (5) Monitor split count - too few splits means less parallelism; (6) Use RDS Proxy for JDBC connections to reduce overhead; (7) Check for large result sets - streaming through Lambda has overhead proportional to result size.

Common Pitfalls

See Also

• AWS Athena
• AWS Glue
• Lake Formation

Additional Deep Dive: Lambda Connector Development

Connector Development Framework

The Athena Federation SDK provides a framework for building custom connectors:

public class CustomConnectorMetadataHandler extends MetadataHandler {
    @Override
    public GetSchemasResponse getSchema(GetSchemasRequest request) {
        // Return schema information for tables
        return new GetSchemasResponse(
            request.getCatalogName(),
            schemas
        );
    }

    @Override
    public GetTableResponse getTable(GetTableRequest request) {
        // Return table metadata
        return new GetTableResponse(
            request.getCatalogName(),
            request.getSchemaName(),
            request.getTableName(),
            columns,
            null
        );
    }
}

Connector Split Strategy

public class CustomConnectorRecordHandler extends RecordHandler {
    @Override
    public ReadRecordsResponse readRecords(ReadRecordsRequest request) {
        // Implement parallel split reading
        List<ReadRecordsRequest.Split> splits = request.getSplits();
        for (ReadRecordsRequest.Split split : splits) {
            // Read data from split
            List<Record> records = readFromSplit(split);
            // Return records
        }
        return new ReadRecordsResponse(request.getRequestId(), records);
    }
}

DynamoDB Connector Configuration

{
  "name": "dynamodb-connector",
  "kafkaConnectVersion": "2.7.1",
  "capacity": {
    "provisionedThroughput": {
      "readCapacityUnits": 50,
      "writeCapacityUnits": 50
    }
  },
  "plugin": {
    "customPluginArn": "arn:aws:kafkaconnect:us-east-1:123456789012:custom-plugin/dynamodb-connector"
  },
  "connectorConfiguration": {
    "connector.class": "com.amazonaws.connectors.dynamodb.DynamoDBSourceConnector",
    "dynamodb.region": "us-east-1",
    "dynamodb.table.name": "my-table",
    "dynamodb.endpoint": "https://dynamodb.us-east-1.amazonaws.com"
  }
}

Query Performance Optimization

Cost Optimization Matrix

Cost = Athena Scan Cost + Lambda Duration x Memory + Source Read Cost

Where:
- Athena Scan Cost = Data Scanned (GB) x $5 per TB
- Lambda Cost = Duration (ms) x Memory (GB) x $0.0000166667
- Source Read Cost = RDS I/O + DynamoDB RCU/WCU

Optimization Targets:
- Reduce Data Scanned: Use SELECT with specific columns
- Reduce Lambda Duration: Optimize connector code
- Reduce Source Read: Push predicates to source

CloudWatch Metrics Dashboard

{
  "widgets": [
    {
      "type": "metric",
      "x": 0,
      "y": 0,
      "width": 12,
      "height": 6,
      "properties": {
        "title": "Federated Query Performance",
        "metrics": [
          ["AWS/Lambda", "Duration", "FunctionName", "athena-connector"],
          ["AWS/Lambda", "Errors", "FunctionName", "athena-connector"],
          ["AWS/Lambda", "Invocations", "FunctionName", "athena-connector"]
        ],
        "period": 300,
        "stat": "Average",
        "region": "us-east-1"
      }
    }
  ]
}

Integration Patterns

Connector Deployment Script

#!/bin/bash
# Deploy Athena Federated Query connector

CONNECTOR_NAME=$1
SOURCE_TYPE=$2
REGION=${3:-us-east-1}

echo "Deploying connector: $CONNECTOR_NAME"

# Create Lambda function
aws lambda create-function \
    --function-name "athena-${CONNECTOR_NAME}" \
    --runtime java11 \
    --role "arn:aws:iam::123456789012:role/athena-connector-role" \
    --handler "com.amazonaws.connectors.${SOURCE_TYPE}.LambdaHandler" \
    --code "S3Bucket=my-connectors,S3Key=${SOURCE_TYPE}-connector.zip" \
    --timeout 900 \
    --memory-size 3008 \
    --vpc-config "SubnetIds=subnet-12345678,SecurityGroupIds=sg-12345678" \
    --region ${REGION}

echo "Connector deployed successfully"

Schema Discovery Process

1. Connector Registration: Register connector in Glue Data Catalog
2. Table Mapping: Map source tables to Lambda connector ARN
3. Schema Inference: Connector reads source schema (INFORMATION_SCHEMA for RDS)
4. Column Discovery: Extract column names and types
5. Partition Detection: Identify partition structure
6. Catalog Update: Register discovered schema in Glue

Additional Deep Dive: Query Optimization Techniques

Query Performance Analysis

-- Analyze query execution plan
EXPLAIN SELECT
    u.customer_id,
    u.email,
    COUNT(o.order_id) as order_count
FROM "rds_catalog"."mydb"."users" u
JOIN "rds_catalog"."mydb"."orders" o ON u.customer_id = o.customer_id
WHERE u.created_at > DATE '2025-01-01'
GROUP BY u.customer_id, u.email
HAVING COUNT(o.order_id) > 5;

Cost Estimation Formula

Total Cost = Athena Cost + Lambda Cost + Source Cost

Athena Cost = Data Scanned (GB) / 1024 x $5.00
Lambda Cost = Duration (seconds) x Memory (GB) x $0.0000166667
Source Cost = RDS I/O Read Cost + Network Transfer Cost

Example:
- 10 GB query result
- Lambda: 30 seconds, 1GB memory
- RDS: 1000 read IOPS

Athena: 10 / 1024 x $5.00 = $0.049
Lambda: 30 x 1 x $0.0000166667 = $0.0005
RDS: 1000 / 1000 x $0.10 = $0.10
Total: ~$0.15 per query

Partition Strategy

-- Create partitioned table in Glue for better performance
CREATE TABLE federated_users (
    customer_id int,
    email string,
    created_at timestamp
)
PARTITIONED BY (region string)
LOCATION 's3://my-bucket/users/';

Query Result Caching

import boto3
import json

def cache_query_result(query_id, result_location):
    """Cache frequent query results to S3."""
    athena = boto3.client('athena')

    # Check if result exists in cache
    cache_key = f"cache/{hash(query_id)}.json"

    try:
        s3 = boto3.client('s3')
        s3.head_object(Bucket='my-query-cache', Key=cache_key)
        # Return cached result
        return f"s3://my-query-cache/{cache_key}"
    except:
        # Execute query and cache result
        return None

Connection Pooling with RDS Proxy

import boto3
import psycopg2

def create_pooled_connection():
    """Create connection through RDS Proxy for better performance."""
    rds_proxy_endpoint = "my-proxy.proxy-abc123.us-east-1.rds.amazonaws.com"

    conn = psycopg2.connect(
        host=rds_proxy_endpoint,
        port=5432,
        dbname="mydb",
        user="admin",
        password=get_secret(),
        sslmode="require",
        connect_timeout=10,
        options="-c statement_timeout=30000"
    )
    return conn

Query Monitoring Dashboard

import boto3
from datetime import datetime, timedelta

def get_query_metrics(hours=24):
    """Get Athena query metrics for monitoring."""
    cloudwatch = boto3.client('cloudwatch')

    end_time = datetime.utcnow()
    start_time = end_time - timedelta(hours=hours)

    response = cloudwatch.get_metric_statistics(
        Namespace='AWS/Athena',
        MetricName='QueryExecutionTime',
        Dimensions=[
            {'Name': 'WorkGroup', 'Value': 'primary'}
        ],
        StartTime=start_time,
        EndTime=end_time,
        Period=300,
        Statistics=['Average', 'Maximum', 'Sum']
    )

    return response['Datapoints']

Security Best Practices