A data engineer needs to join data from multiple sources to perform a one-time analysis job. The data is stored in Amazon DynamoDB, Amazon RDS…

Amazon Athena Federated Query allows you to run SQL queries across data stored in various sources like relational, non-relational, and object stores without moving the data. For a one-time analysis job involving DynamoDB, RDS, Redshift, and S3, this is the most cost-effective solution. It is a serverless service, meaning you pay only for the queries you run (based on data scanned) and the underlying Lambda function invocations for the data source connectors. This approach avoids the costs associated with provisioning and managing compute clusters (like EMR or Redshift) or building and running data movement pipelines (ETL), which are less economical for a single, ad-hoc task.

1. Amazon Athena Documentation

"Using Amazon Athena Federated Query": "With Athena Federated Query

you can run SQL queries across data stored in relational

non-relational

object

and custom data sources. [...] Athena executes federated queries by using AWS Lambda data source connectors that run on your behalf." This confirms the capability to query diverse sources in place.

2. Amazon Athena Documentation

"Amazon Athena pricing": "With Athena

you pay only for the queries that you run. You are charged based on the amount of data scanned by each query." This highlights the pay-per-query model that makes it cost-effective for one-time jobs.

3. AWS Big Data Blog

"Query any data source with Amazon Athena’s new federated query feature": "Because you’re not moving data into a centralized location

you’re not paying for additional storage. You pay for the queries you run in Athena and the Lambda usage." This directly supports the cost-effectiveness of the federated query approach for ad-hoc analysis.

4. Amazon EMR Documentation

"Amazon EMR pricing": EMR pricing is based on the instance type and the number of EC2 instances deployed

which is less suitable for a single

short-lived job compared to a serverless option.

5. Amazon Redshift Documentation

"Querying data in supported Amazon RDS for PostgreSQL and Amazon RDS for MySQL databases": This document describes Redshift's federated query capability but operates within the context of a provisioned Redshift cluster

which has a different cost structure than Athena.