# Amazon Athena

Cloud service · AWS. Updated 2026-11-12. Page: https://blowstack.com/tools/cloud-services-hub/amazon-athena

> Amazon Athena is a query service for analyzing data in Amazon S3 using SQL, allowing direct data analysis in S3 without loading it into databases or warehouses.

Practice exams covering Amazon Athena: [ANS-C01 AWS Certified Advanced Networking - Specialty](https://blowstack.com/practice-exams/aws-certified-advanced-networking-specialty), [CLF-C02 AWS Certified Cloud Practitioner](https://blowstack.com/practice-exams/aws-certified-cloud-practitioner), [DEA-C01 AWS Certified Data Engineer - Associate](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate), [DVA-C02 AWS Certified Developer - Associate](https://blowstack.com/practice-exams/aws-certified-developer-associate), [DOP-C02 AWS Certified DevOps Engineer - Professional](https://blowstack.com/practice-exams/aws-certified-devops-engineer-professional), [AIP-C01 AWS Certified Generative AI Developer - Professional](https://blowstack.com/practice-exams/aws-certified-generative-ai-developer-professional), [MLA-C02 (beta) AWS Certified Machine Learning Engineer - Associate](https://blowstack.com/practice-exams/aws-certified-machine-learning-engineer-associate), [SCS-C03 AWS Certified Security - Specialty](https://blowstack.com/practice-exams/aws-certified-security-specialty), [SAA-C03 AWS Certified Solutions Architect - Associate](https://blowstack.com/practice-exams/aws-certified-solutions-architect-associate), [SAP-C03 AWS Certified Solutions Architect - Professional](https://blowstack.com/practice-exams/aws-certified-solutions-architect-professional).

## Free Amazon Athena tools

- [SQL Date Format Converter](https://blowstack.com/tools/aws-sql-date-format-converter): Get the expression that reads a date or timestamp in Athena, Redshift and Glue, and fix date errors.

## 13 key features of Amazon Athena

1. **Serverless**: Automatically scales, requiring no infrastructure management, allowing users to focus on querying data
2. **Direct SQL on S3**: Enables direct querying of data stored in Amazon S3 using SQL, eliminating the need for data loading into separate databases.
3. **Parallel Query Execution**:  Enhances performance by executing queries in parallel, ensuring fast results for large datasets and complex queries.
4. **High Availability**: Distributes queries across multiple facilities to ensure continuous service, even if one facility is down.
5. **Interactive Querying**: Built-in SQL editor for interactive analysis and immediate querying, with schema definition flexibility.
6. **Support for Various Data Formats**: Queries multiple data formats like CSV, JSON, ORC, Parquet, and Avro, accommodating diverse data analysis needs.
7. **SQL with Trino and Presto**: Supports complex SQL queries, including joins, window functions, and arrays.
8. **Apache Spark Integration**: Supports running Apache Spark applications, allowing for complex data processing without Spark cluster management.
9. **Federated SQL Queries**: Data source connectors enable federated SQL queries against data sources outside Amazon S3.
10. **Partition Projection**: Partition projection speeds up query processing for highly partitioned tables and automates partition management.
11. **User-Defined Functions**: SQL queries can use your own user-defined functions.
12. **Machine Learning Inference in SQL**: SQL statements can use machine learning inference from Amazon SageMaker AI.
13. **Workgroup Data Usage Controls**: Workgroups support a per-query scanned-data limit that cancels queries exceeding it, and workgroup-wide scanned-data thresholds over hourly or daily periods that can trigger Amazon SNS notifications.

## Where Amazon Athena is used

1. **Log Analysis**: Useful for querying and analyzing logs from web servers, applications, and devices to troubleshoot and gain operational insights.
2. **Data Exploration**: Enables querying and exploration of large datasets in S3 to identify patterns and relationships.
3. **Report Generation**: Facilitates the creation of reports on metrics, trends, and aggregations, with integration into BI tools like AWS Quick for enhanced visualization.
4. **Serverless ETL**: Performs extract, transform, and load operations directly in S3 using SQL, eliminating the need for infrastructure management.
5. **Data Lake Analysis**: Supports comprehensive analysis of data lakes, merging data from various sources for unified insights.
6. **Ad-hoc Querying:**: Ideal for executing spontaneous queries to test hypotheses, detect anomalies, or troubleshoot.
7. **Cost Optimization**: Analyzes AWS billing and usage reports to identify cost-saving opportunities and optimize resource allocation.
8. **Research Support**: Assists in querying and analyzing scientific or experimental data for pattern recognition and hypothesis testing.
9. **Compliance Auditing**: Facilitates historical data analysis for regulatory compliance and auditing purposes.
10. **Machine Learning Preparation**: Prepares and refines data for machine learning, ensuring quality and structure compatibility.

## How Amazon Athena is priced

- **Pay per query**: Charges are incurred based on the volume of data each query scans, measured in terabytes. Costs decrease when data is compressed, partitioned, or stored in a columnar format, as these methods reduce the scanned data volume.
- **Capacity Reservations**: You pay an hourly rate for reserved dedicated SQL computing capacity, with a minimum reservation duration of 1 minute and a minimum capacity of 4 Data Processing Units (DPUs). This offers more predictable pricing for steady query loads.
- **Apache Spark compute**: Athena for Apache Spark charges for compute usage measured in DPU-hours rather than the volume of data scanned.

## Services Amazon Athena works with

- **[Amazon S3](https://blowstack.com/tools/cloud-services-hub/amazon-s3)**: Amazon Athena directly queries data stored in Amazon S3 using standard SQL, enabling you to analyze large-scale datasets without the need for ETL processes.
- **[AWS Glue](https://blowstack.com/tools/cloud-services-hub/aws-glue)**: AWS Glue provides a data catalog that helps Amazon Athena understand the schema and location of your data, making it easier to run queries and maintain metadata.
- **[Amazon QuickSight](https://blowstack.com/tools/cloud-services-hub/amazon-quicksight)**: Amazon QuickSight integrates with Amazon Athena to visualize data and generate business intelligence insights using the queried data.
- **[Amazon CloudWatch](https://blowstack.com/tools/cloud-services-hub/amazon-cloudwatch)**: Amazon CloudWatch gathers logs, metrics, and events from Amazon Athena, enabling you to monitor query performance and set alarms for operational issues.
- **[AWS Lambda](https://blowstack.com/tools/cloud-services-hub/aws-lambda)**: AWS Lambda can be triggered by the completion of Amazon Athena queries, allowing for automation of functions such as data transformation and notification.
- **[Amazon Data Firehose](https://blowstack.com/tools/cloud-services-hub/amazon-kinesis-data-firehose)**: Amazon Data Firehose can deliver streaming data directly into Amazon S3, allowing Amazon Athena to query real-time data alongside historical data stored in S3.
- **[AWS Identity and Access Management (IAM)](https://blowstack.com/tools/cloud-services-hub/aws-identity-and-access-management-(iam))**: AWS IAM provides fine-grained access control policies for Amazon Athena, enabling secure access to your data and protecting sensitive information.
- **[Amazon Redshift](https://blowstack.com/tools/cloud-services-hub/amazon-redshift)**: Amazon Athena provides a built-in data source connector for querying data in Amazon Redshift.
- **[Amazon AppFlow](https://blowstack.com/tools/cloud-services-hub/amazon-appflow)**: Athena can query Amazon AppFlow output in Amazon S3 using tables that AppFlow automatically registers in the AWS Glue Data Catalog. This lets you analyze transferred SaaS records with SQL without manually defining their table schemas.
- **[Amazon Aurora](https://blowstack.com/tools/cloud-services-hub/amazon-aurora)**: Athena can query Aurora MySQL and Aurora PostgreSQL databases through federated data source connectors. Queries can join operational records in Aurora with datasets stored in Amazon S3.
- **[Amazon Aurora Serverless](https://blowstack.com/tools/cloud-services-hub/amazon-aurora-serverless)**: Athena can query compatible Aurora Serverless databases through its MySQL and PostgreSQL federated connectors. The connector must have network access and credentials for the database endpoint.
- **[Amazon CloudFront](https://blowstack.com/tools/cloud-services-hub/amazon-cloudfront)**: Athena can query CloudFront access logs delivered to Amazon S3. For example, SQL queries can identify frequently requested objects, geographic traffic patterns, and elevated error rates.
- **[Amazon DataZone](https://blowstack.com/tools/cloud-services-hub/amazon-datazone)**: Athena provides SQL querying for data accessed through Amazon DataZone data lake environments. Project members can query subscribed, Glue-cataloged datasets after the required access permissions are granted.
- **[Amazon DocumentDB (with MongoDB compatibility)](https://blowstack.com/tools/cloud-services-hub/amazon-documentdb-(with-mongodb-compatibility))**: Athena's DocumentDB connector supports federated SQL queries against documents in Amazon DocumentDB. You can join document data with tables in Amazon S3 without first copying the entire database.
- **[Amazon DynamoDB](https://blowstack.com/tools/cloud-services-hub/amazon-dynamodb)**: Athena's DynamoDB connector exposes DynamoDB tables to federated SQL queries. This supports analysis across DynamoDB tables and joins with other Athena data sources.
- **[Amazon ElastiCache](https://blowstack.com/tools/cloud-services-hub/amazon-elasticache)**: Athena's Redis connector can query supported Redis data in Amazon ElastiCache. It maps supported Redis structures into tables that can be queried and joined with other Athena datasets.
- **[Amazon EMR](https://blowstack.com/tools/cloud-services-hub/amazon-emr)**: Athena integrates with the SQL editor in Amazon EMR Studio, allowing users to author and execute Athena queries from the Studio interface. Athena can also use an external Hive metastore hosted on an EMR cluster through its Hive metastore connector.
- **[Amazon EventBridge](https://blowstack.com/tools/cloud-services-hub/amazon-eventbridge)**: Athena sends query execution state-change events to EventBridge. Rules can react when a query succeeds, fails, or is cancelled, such as by starting a downstream workflow.
- **[Amazon GuardDuty](https://blowstack.com/tools/cloud-services-hub/amazon-guardduty)**: Athena can analyze GuardDuty findings exported to Amazon S3. SQL queries can aggregate findings across accounts or identify recurring activity involving particular resources.
- **[Amazon HealthLake](https://blowstack.com/tools/cloud-services-hub/amazon-healthlake)**: Athena can query HealthLake data exposed through its AWS Glue and Lake Formation integration. This enables SQL analysis of supported FHIR datastore data using the applicable access controls.
- **[Amazon Macie](https://blowstack.com/tools/cloud-services-hub/amazon-macie)**: Athena can query Macie's sensitive data discovery results stored in Amazon S3. These detailed results support analyses such as identifying objects containing particular sensitive data types.
- **[Amazon Managed Grafana](https://blowstack.com/tools/cloud-services-hub/amazon-managed-grafana)**: Athena is available as a data source for Amazon Managed Grafana. Dashboards can run Athena SQL queries to visualize datasets in Amazon S3 and other supported Athena sources.
- **[Amazon Managed Streaming for Apache Kafka](https://blowstack.com/tools/cloud-services-hub/amazon-managed-streaming-for-apache-kafka)**: Athena's Kafka connector can query topics in Amazon MSK clusters. It exposes supported topic data for SQL analysis and joins with other Athena data sources.
- **[Amazon Managed Workflows for Apache Airflow (Amazon MWAA)](https://blowstack.com/tools/cloud-services-hub/amazon-managed-workflows-for-apache-airflow-(amazon-mwaa))**: Athena queries can be orchestrated from Amazon MWAA using the Apache Airflow Amazon provider's Athena operators and sensors. A workflow can submit a query, wait for completion, and then process its results.
- **[Amazon Neptune](https://blowstack.com/tools/cloud-services-hub/amazon-neptune)**: Athena's Neptune connector supports federated SQL access to supported graph data in Neptune. This allows graph-derived information to be combined with relational and Amazon S3 datasets.
- **[Amazon OpenSearch Service](https://blowstack.com/tools/cloud-services-hub/amazon-opensearch-service)**: Athena's OpenSearch connector lets SQL queries read data from Amazon OpenSearch Service indexes. Queries can combine indexed documents with tables from other Athena data sources.
- **[Amazon Q](https://blowstack.com/tools/cloud-services-hub/amazon-q)**: Amazon Q Developer can assist with SQL authoring in Athena, including generating queries from natural-language requests. Users can review and edit the generated SQL before executing it.
- **[Amazon RDS](https://blowstack.com/tools/cloud-services-hub/amazon-rds)**: Athena can query supported Amazon RDS database engines through federated connectors, including MySQL, PostgreSQL, Oracle, and SQL Server connectors. This enables SQL joins between database records and data in Amazon S3 without a full database export.
- **[Amazon Route 53](https://blowstack.com/tools/cloud-services-hub/amazon-route-53)**: Athena can analyze Route 53 Resolver query logs delivered to Amazon S3. Queries can identify frequently requested domains, DNS response codes, and the resources making particular DNS requests.
- **[Amazon S3 Glacier](https://blowstack.com/tools/cloud-services-hub/amazon-s3-glacier)**: Athena can query restored objects from S3 Glacier Flexible Retrieval and S3 Glacier Deep Archive when the table is configured to read restored Glacier objects. The objects must remain restored while the queries run.
- **[Amazon SageMaker](https://blowstack.com/tools/cloud-services-hub/amazon-sagemaker)**: Athena SQL queries can invoke models deployed to SageMaker endpoints for machine learning inference. Athena also queries SageMaker Feature Store offline data, enabling SQL-based feature selection and training dataset preparation.
- **[Amazon SageMaker Data Wrangler](https://blowstack.com/tools/cloud-services-hub/amazon-sagemaker-data-wrangler)**: Athena is a supported query source for SageMaker Data Wrangler. You can import the results of an Athena SQL query into a Data Wrangler flow for preparation and feature engineering.
- **[Amazon Security Lake](https://blowstack.com/tools/cloud-services-hub/amazon-security-lake)**: Athena can query Security Lake's security datasets through their Glue catalog tables and Lake Formation permissions. Analysts can correlate normalized security records across accounts, Regions, and source types.
- **[Amazon Timestream](https://blowstack.com/tools/cloud-services-hub/amazon-timestream)**: Athena's Timestream connector supports federated queries against Timestream for LiveAnalytics tables. Time-series measurements can be joined with business or reference data available through Athena.
- **[Amazon VPC](https://blowstack.com/tools/cloud-services-hub/amazon-vpc)**: Athena can query VPC Flow Logs stored in Amazon S3 to analyze network traffic and rejected connections. Athena also supports VPC interface endpoints for private access to its API.
- **[AWS Application Discovery Service](https://blowstack.com/tools/cloud-services-hub/aws-application-discovery-service)**: Athena can analyze discovery data made available in Amazon S3 through Application Discovery Service's data exploration integration. SQL queries can examine server characteristics, utilization, and network connections for migration planning.
- **[AWS Billing and Cost Management](https://blowstack.com/tools/cloud-services-hub/aws-billing-and-cost-management)**: Athena can query billing datasets delivered to Amazon S3 through AWS Billing and Cost Management exports. This supports custom cost allocation, usage analysis, and reporting across accounts and resource tags.
- **[AWS CloudFormation](https://blowstack.com/tools/cloud-services-hub/aws-cloudformation)**: Athena resources can be provisioned with CloudFormation, including workgroups, data catalogs, named queries, prepared statements, and capacity reservations. Templates can standardize query settings and deploy saved SQL alongside other infrastructure.
- **[AWS CloudTrail](https://blowstack.com/tools/cloud-services-hub/aws-cloudtrail)**: Athena can query CloudTrail logs stored in Amazon S3, and CloudTrail provides a workflow for creating an Athena table for a trail's logs. CloudTrail also records Athena API activity for auditing.
- **[AWS Config](https://blowstack.com/tools/cloud-services-hub/aws-config)**: AWS Config can record supported Athena resource configurations and evaluate workgroups with Athena-specific managed rules. For example, it can check whether a workgroup requires encryption for query results.
- **[AWS Cost and Usage Report](https://blowstack.com/tools/cloud-services-hub/aws-cost-and-usage-report)**: Athena can query Cost and Usage Reports delivered to Amazon S3 using the report's Athena integration. SQL queries can break down spending by service, account, usage type, and cost allocation tag.
- **[AWS Data Exchange](https://blowstack.com/tools/cloud-services-hub/aws-data-exchange)**: Athena can query datasets accessed through AWS Data Exchange for Amazon S3 when the required table metadata and permissions are configured. Subscribers can analyze shared data in place rather than maintaining a separate downloaded copy.
- **[AWS IAM Identity Center (AWS Single Sign-On)](https://blowstack.com/tools/cloud-services-hub/aws-iam-identity-center-(aws-single-sign-on))**: Athena supports IAM Identity Center authentication and trusted identity propagation for supported query workflows. Identity Center-enabled workgroups can preserve a user's identity when accessing data governed by Lake Formation.
- **[AWS IoT SiteWise](https://blowstack.com/tools/cloud-services-hub/aws-iot-sitewise)**: Athena's AWS IoT SiteWise connector enables SQL queries over supported SiteWise industrial asset data. This allows asset information and measurements to be analyzed alongside datasets from other Athena sources.
- **[AWS Key Management Service (AWS KMS)](https://blowstack.com/tools/cloud-services-hub/aws-key-management-service-(aws-kms))**: Athena uses AWS KMS keys to protect supported encrypted source data, query results, and other Athena-managed artifacts. Workgroups can enforce KMS encryption for query results, with key permissions controlling access.
- **[AWS Lake Formation](https://blowstack.com/tools/cloud-services-hub/aws-lake-formation)**: Athena enforces Lake Formation permissions when querying registered data lake tables. Supported permissions can restrict access to databases, tables, columns, and filtered rows.
- **[AWS Network Firewall](https://blowstack.com/tools/cloud-services-hub/aws-network-firewall)**: Athena can query Network Firewall flow and alert logs delivered to Amazon S3. SQL analysis can identify traffic matching firewall rules and investigate network security alerts.
- **[AWS PrivateLink](https://blowstack.com/tools/cloud-services-hub/aws-privatelink)**: Athena supports AWS PrivateLink through interface VPC endpoints. Applications can reach supported Athena API operations privately without requiring an internet gateway or NAT device.
- **[AWS Secrets Manager](https://blowstack.com/tools/cloud-services-hub/aws-secrets-manager)**: Athena federated connectors can retrieve database credentials and other connection secrets from Secrets Manager. This keeps credentials out of query text and connector configuration values.
- **[AWS Security Token Service (AWS STS)](https://blowstack.com/tools/cloud-services-hub/aws-security-token-service-(aws-sts))**: Athena JDBC and ODBC authentication workflows can use STS temporary credentials, including credentials obtained by assuming an IAM role. This enables federated access without distributing long-lived AWS access keys.
- **[AWS Serverless Application Repository](https://blowstack.com/tools/cloud-services-hub/aws-serverless-application-repository)**: Athena's Lambda-based federated connectors are available as applications in the Serverless Application Repository. Deploying a connector application creates the resources Athena uses to query its supported external data source.
- **[AWS Step Functions](https://blowstack.com/tools/cloud-services-hub/aws-step-functions)**: Athena has an optimized Step Functions integration for starting queries, retrieving results, and stopping query execution. A workflow can wait for a query to finish before running dependent processing steps.
- **[AWS Systems Manager](https://blowstack.com/tools/cloud-services-hub/aws-systems-manager)**: Athena can query Systems Manager Inventory data collected into Amazon S3 through resource data sync. This supports fleet-wide SQL analysis of installed software, operating systems, and other collected inventory attributes.
- **[AWS WAF](https://blowstack.com/tools/cloud-services-hub/aws-waf)**: Athena can query AWS WAF traffic logs stored in Amazon S3. Queries can identify blocked requests, frequently matched rules, and request patterns associated with particular clients.
- **[Elastic Load Balancing (ELB)](https://blowstack.com/tools/cloud-services-hub/elastic-load-balancing-(elb))**: Athena can query supported Elastic Load Balancing access logs delivered to Amazon S3. For example, queries over Application Load Balancer logs can identify slow requests, target errors, and traffic distribution.

## Amazon Athena exam questions

Real practice exam questions on Amazon Athena, with the correct answer and its explanation.

### Question 1 (CLF-C02)

**Question:**

A company is selecting an AWS analytics service for occasional investigations of archived website activity. The finance team wants a serverless SQL query service whose query charges are based on the amount of source data scanned. Reducing the data scanned by each query should directly reduce query costs. Which service and pricing model meet these requirements?

**Correct answer:** Amazon Athena, with standard SQL query pricing based on the amount of data scanned

**Explanation:** Amazon Athena provides serverless SQL queries. Under its standard per-query pricing model, charges depend on the amount of data scanned, so scanning less data can directly lower query costs.

### Question 2 (MLA-C02 (beta))

**Question:**

A machine learning team stores several terabytes of daily observations in Amazon S3 as CSV files partitioned by observation date. An Amazon Athena external table exposes all 180 columns. Feature engineering queries already filter on the date partition, but each query uses only 5–10 columns and still scans large amounts of data. Different experiments require different columns, so the curated dataset must retain all 180 columns. The team wants to use Athena SQL to create a reusable curated dataset that minimizes reading of unrequested columns during subsequent queries. Which solution best meets these requirements?

**Correct answer:** Use an Athena CREATE TABLE AS SELECT statement to write all columns as compressed Parquet data to a new S3 location, preserving the observation-date partitions.

**Explanation:** Parquet stores values in column chunks, allowing Athena to read the columns needed by each query without reading all 180 columns. The curated table retains every field for future experiments, while date partition pruning and compression provide complementary scan reductions. CTAS performs the conversion using Athena SQL.

### Question 3 (SAP-C03)

**Question:**

A company uses AWS Organizations with consolidated billing. Finance is building an Amazon Athena reporting pipeline from an organization-wide AWS Cost and Usage Report (CUR) delivered to Amazon S3.

Auditors require the company to retain every delivered report version so they can reproduce previously published financial results. Operational reports must instead use only the latest complete data for each billing month, including updates that AWS publishes after a month is finalized. During testing, engineers discover that successive deliveries contain overlapping month-to-date data. Corrections can change or remove previously reported line items.

Which solution meets these requirements without double-counting charges or retaining obsolete line items in operational reports?

**Correct answer:** Configure CUR to create a new report version for each update and enable refreshes of closed reports. Retain the delivered versions in S3. For each completed delivery, use its manifest to load the complete set of report files into a separate snapshot location, and update the Athena partition for that billing month to reference only that snapshot.

**Explanation:** CUR updates are report snapshots, not append-only usage increments. Retaining new versions preserves the audit history, and refreshing closed reports allows subsequent billing adjustments to appear. Processing all files identified by a delivery's manifest and replacing the operational partition's snapshot prevents both duplicate charges and obsolete records from earlier deliveries.

### Question 4 (DEA-C01)

**Question:**

A payment processor stores an immutable daily delivery of Parquet files in Amazon S3. The delivery is available through a catalog table named payment_events. Each row contains a non-null payment_id and a non-null amount stored as DECIMAL(18,2).

Retries can produce multiple rows with the same payment_id. Repeated rows are acceptable if their amounts are identical. A payment_id associated with different amounts anywhere in the delivery indicates a consistency defect, even when the conflicting rows are in different files.

A data engineer must investigate the complete delivery without modifying it. The investigation must return every original row associated with a conflicting payment_id, including the S3 source file for each occurrence.

Which solution meets these requirements?

**Correct answer:** Run an Amazon Athena query with a CTE that groups payment_events by payment_id and uses HAVING COUNT(DISTINCT amount) > 1. Join the CTE back to payment_events on payment_id, and select the original rows together with the "$path" column.

**Explanation:** The grouped check identifies payment IDs with genuinely different amounts across the entire delivery. Joining those IDs back to the original table preserves every occurrence, including identical retries associated with a conflicting ID. Athena's "$path" metadata column identifies each row's S3 source file without modifying the source data.

### Question 5 (AIP-C01)

**Question:**

A municipal utility operates a generative AI assistant that reports water-quality violations from measurement data in Amazon S3. The application maps supported questions to approved, deterministic SQL aggregations and executes them through Amazon Athena. It returns numerical results through a fixed response template only after query execution succeeds. Unsupported questions are rejected.

New measurements can arrive seconds apart. Each answer must reflect all measurements committed before its request begins. The ingestion system ensures that the relevant data remains unchanged while that request executes. SQL text does not change when new measurements arrive, and the application uses a new ClientRequestToken for every StartQueryExecution call.

The application enables Athena query result reuse with a maximum age of 60 minutes. During testing, some answers incorrectly report no violations even though newly committed measurements exceed safety limits.

Which change BEST meets the freshness requirement while preserving the existing controlled calculation and response pipeline?

**Correct answer:** Set ResultReuseByAgeConfiguration.Enabled to false in every StartQueryExecution request for these answers. Continue releasing results only after successful execution through the existing fixed response template.

**Explanation:** Disabling query result reuse forces Athena to execute the query rather than return eligible results from an earlier execution. Given the stated ingestion guarantees and approved deterministic SQL, this addresses the stale-data failure while retaining controlled calculation and fail-closed response handling.

### Question 6 (DVA-C02)

**Question:**

A developer is investigating HTTP 502 responses from a checkout service. Newline-delimited JSON logs are stored only in Amazon S3 and are exposed through two external tables: frontend_logs(dt, request_id, deployment_id, status_code) and dependency_logs(dt, request_id, deployment_id, dependency_name, outcome). Both tables are partitioned by dt, a UTC date string. The application propagates request_id to dependency calls. Each frontend request produces one frontend record, but retries can produce several dependency records for the same request and dependency. A deployment_id is shared by many requests.

The developer must report, for each dependency, the number of distinct frontend requests on 2026-06-15 that returned HTTP 502 and had at least one TIMEOUT outcome for that dependency on the same date. Which query meets these requirements?

**Correct answer:** Run an Amazon Athena query:
SELECT d.dependency_name, COUNT(DISTINCT f.request_id) AS failed_requests
FROM frontend_logs f
JOIN dependency_logs d ON f.request_id = d.request_id
WHERE f.dt = '2026-06-15' AND d.dt = '2026-06-15'
  AND f.status_code = 502 AND d.outcome = 'TIMEOUT'
GROUP BY d.dependency_name;

**Explanation:** Athena queries the logs directly in S3. Joining on the propagated request_id correlates frontend failures with their dependency calls. Filtering both date partitions and both error conditions selects the required records, while COUNT(DISTINCT f.request_id) prevents retries from counting a request more than once per dependency.

### Question 7 (SCS-C03)

**Question:**

A security engineer is investigating suspicious application requests that triggered AWS API calls between 22:00 UTC and 23:00 UTC on June 14. Application logs and AWS CloudTrail logs are stored in separate Amazon S3 prefixes. Existing external tables use partition projection with an arrival_date partition key derived from the UTC date when each object was delivered to S3, not from the events inside the object. Both tables contain several years of data.

All relevant records have now arrived. Delivery occurs between zero and four hours after each event. Both sources record accurate UTC event timestamps and matching AWS request identifiers. The engineer's SQL query correctly joins records by account ID and request identifier and filters both sources to the incident's event-time interval. However, the query restricts arrival_date to June 14 and misses some known matching records. Query result reuse is disabled, and the projected date ranges include the entire investigation period.

Which change will produce complete correlation results while scanning the fewest necessary date partitions?

**Correct answer:** Restrict arrival_date to June 14 and June 15 in both tables, and retain the existing event-time filters and join conditions.

**Explanation:** Events occurring from 22:00 to 23:00 UTC on June 14 can arrive on June 14 or, with a four-hour delay, on June 15. Both arrival-date partitions are necessary. Retaining the event-time filters excludes events outside the incident interval while the partition predicates avoid scanning unrelated dates.

### Question 8 (SAA-C03)

**Question:**

A company uses a custom business intelligence portal to visualize data queried by Amazon Athena from Amazon S3. Hundreds of employees frequently request the same charts, causing the portal to submit identical, deterministic aggregation queries with the same catalog, database, and workgroup settings. All viewers are authorized to access the same data. The source data changes periodically, and the business accepts query results that are up to 10 minutes old. The company wants to reduce dashboard latency and repeated data scans without changing the source tables or introducing another analytics store. Which solution meets these requirements?

**Correct answer:** Configure the portal's Athena query requests to enable query result reuse with a maximum result age of 10 minutes.

**Explanation:** Athena query result reuse can return a previous matching query result instead of executing the query and scanning the source data again. A 10-minute maximum age meets the freshness requirement while reducing latency and repeated scans for these identical, authorized queries.

### Question 9 (DOP-C02)

**Question:**

A company queries archived AWS CloudTrail events, EC2 operating system logs, and VPC Flow Logs in Amazon S3 by using Amazon Athena. During an incident investigation, an engineer finds the following evidence for one EC2 instance:

• A CloudTrail event records an unauthorized security group ingress change at 10:01:30 UTC.
• An operating system audit record shows execution of a malicious program at a host timestamp of 10:03:00.
• Independent clock-monitoring records establish that the instance clock was 120 seconds ahead of UTC throughout the incident, with an uncertainty of ±10 seconds.
• An ACCEPT flow record for the instance covers 10:00:40–10:02:20 UTC. The record contains aggregate connection metadata, not individual packet timestamps.

All relevant original records are retained. A draft incident report states that the security group change enabled the malicious execution because the execution occurred afterward.

Which investigation approach produces an evidence-supported timeline?

**Correct answer:** Use Athena to subtract 120 seconds from the host timestamp and retain its uncertainty range. Place the execution at approximately 10:00:50–10:01:10 UTC, preserve the flow record as an interval, and revise the report because the observed execution preceded the security group change. Search earlier evidence to investigate how access was obtained.

**Explanation:** A clock that is ahead requires subtracting the measured offset. The resulting execution interval ends before the security group change, so that change cannot have enabled this already-observed execution. The aggregate flow record does not establish an exact connection or execution time. Searching backward can identify earlier enabling conditions without claiming that the earliest observed event proves the initial compromise time.

### Question 10 (ANS-C01)

**Question:**

A company is investigating intermittent connection resets between application instances in a VPC. The security team wants to deliver new VPC flow logs to Amazon S3 and analyze them with Amazon Athena. For each destination address and port, the team must count accepted TCP flow-log records that contain at least one TCP RST flag during the aggregation interval. Records that also contain other TCP control flags must be included. The team does not require individual reset packet counts or the order of packet events. Which solution meets these requirements?

**Correct answer:** Create a custom-format flow log that includes dstaddr, dstport, protocol, action, log-status, and tcp-flags. In Athena, filter for protocol 6, action ACCEPT, log-status OK, and bitwise_and(CAST(tcp_flags AS BIGINT), 4) = 4. Count the matching records grouped by dstaddr and dstport.

**Explanation:** The RST flag has a value of 4. The extended tcp-flags field represents the bitwise OR of recorded flags during the aggregation interval, so testing whether the RST bit is set includes both RST-only records and records containing additional flags. The remaining filters select successfully logged, accepted TCP traffic. Counting records satisfies the requirement without claiming to count individual reset packets.

## Practice Amazon Athena in a full exam

Questions on Amazon Athena in each practice exam, with every answer option and an explanation for each:

- [DEA-C01](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate): 56 questions
- [SAP-C03](https://blowstack.com/practice-exams/aws-certified-solutions-architect-professional): 14 questions
- [AIP-C01](https://blowstack.com/practice-exams/aws-certified-generative-ai-developer-professional): 9 questions
- [DOP-C02](https://blowstack.com/practice-exams/aws-certified-devops-engineer-professional): 9 questions
- [SCS-C03](https://blowstack.com/practice-exams/aws-certified-security-specialty): 7 questions
- [SAA-C03](https://blowstack.com/practice-exams/aws-certified-solutions-architect-associate): 7 questions
- [ANS-C01](https://blowstack.com/practice-exams/aws-certified-advanced-networking-specialty): 7 questions
- [CLF-C02](https://blowstack.com/practice-exams/aws-certified-cloud-practitioner): 4 questions
- [MLA-C02 (beta)](https://blowstack.com/practice-exams/aws-certified-machine-learning-engineer-associate): 3 questions
- [DVA-C02](https://blowstack.com/practice-exams/aws-certified-developer-associate): 1 question

## What is Amazon Athena

Amazon Athena, provided by AWS, is a serverless, interactive query service that enables the analysis of large-scale data directly in Amazon S3 using standard SQL. Users can execute queries on structured, semi-structured, or unstructured data in formats like CSV, JSON, ORC, Parquet, and Avro, without needing to load it into separate databases or warehouses.

A query string can contain at most 262144 bytes when encoded in UTF-8. This quota cannot be increased.

Athena automatically scales, managing the query load efficiently across multiple servers, with pricing based on data processed or compute used. It supports SQL through Trino and Presto query engines, facilitating complex queries and integrates with tools like AWS Quick for visualization.

For SQL workloads, Capacity Reservations let you request dedicated compute in Data Processing Units (DPUs) and assign it to your workloads. Capacity Reservations and per-query billing can be used in the same account for different performance, workload-isolation, and cost goals.

Active DML and DDL query quotas count both running and queued queries. Exceeding an active-query quota results in a TooManyRequestsException.

DML queries have a timeout after which they are canceled; you can request an increase up to a maximum of 240 minutes.

Additionally, Athena offers capabilities for running Apache Spark applications through Python or notebook APIs, enhancing its analytical power without requiring Spark cluster management. This service simplifies data analysis, allowing immediate querying with the option to define data schema manually or automatically, streamlining the process for quick, insightful decision-making.

## Learn Amazon Athena in lessons

24 short lessons on Amazon Athena in 8 certification courses.

- CLF-C02: Amazon Athena: querying data in Amazon S3 (Premium), https://app.blowstack.com/lessons/amazon-athena-querying-data-in-amazon-s3-clf-c02
- MLA-C02 (beta): Amazon Athena: S3 data formats for SQL analytics (Premium), https://app.blowstack.com/lessons/amazon-athena-s3-data-formats-for-sql-analytics-mla-c02
- SAP-C03: Amazon Athena: serverless SQL transformation pipelines (Premium), https://app.blowstack.com/lessons/amazon-athena-serverless-sql-transformation-pipelines-sap-c03
- DEA-C01: Amazon OpenSearch Service vs Amazon Athena: log analytics (Premium), https://app.blowstack.com/lessons/amazon-opensearch-service-vs-amazon-athena-log-analytics-dea-c01
- AIP-C01: Amazon Athena: parameterized SQL (Premium), https://app.blowstack.com/lessons/amazon-athena-parameterized-sql-aip-c01
- SCS-C03: Amazon Athena: SQL over heterogeneous security logs (Premium), https://app.blowstack.com/lessons/amazon-athena-sql-over-heterogeneous-security-logs-scs-c03

## Concepts related to Amazon Athena

- [Schema on Read](https://blowstack.com/tools/cloud-concepts-hub/schema-on-read): Schema on read is an approach that applies a schema when data is read or queried rather than requiring the storage layer to enforce that schema when data is written.
- [Temporal Correlation](https://blowstack.com/tools/cloud-concepts-hub/temporal-correlation): Temporal correlation is the process of relating events by their timestamps, time windows, and sequence to identify potentially connected activity.
- [Text-to-SQL](https://blowstack.com/tools/cloud-concepts-hub/text-to-sql): Text-to-SQL translates natural-language questions into SQL queries that retrieve or calculate answers from structured data.
- [Analytics](https://blowstack.com/tools/cloud-concepts-hub/analytics): Analytics is the systematic examination of data to answer questions, identify patterns, and support decisions.
- [Data Partitioning](https://blowstack.com/tools/cloud-concepts-hub/data-partitioning): Data partitioning divides a dataset into smaller subsets using defined rules so that storage, ingestion, processing, and queries can operate on manageable portions of the data.
- [Log Normalization](https://blowstack.com/tools/cloud-concepts-hub/log-normalization): Log normalization converts differently structured log records into a consistent schema and representation so they can be searched, compared, and correlated.
- [Schema Evolution](https://blowstack.com/tools/cloud-concepts-hub/schema-evolution): Schema evolution is the controlled modification of data structures over time while managing compatibility between existing data, producers, and consumers.
- [ACID Transactions](https://blowstack.com/tools/cloud-concepts-hub/acid-transactions): ACID transactions group data operations into a reliable unit of work with atomicity, consistency, isolation, and durability guarantees.
- [ORC File Format](https://blowstack.com/tools/cloud-concepts-hub/orc-file-format): ORC File Format is an open, columnar binary format that stores typed data with compression and statistics for efficient analytical processing.
- [Referential Integrity](https://blowstack.com/tools/cloud-concepts-hub/referential-integrity): Referential integrity ensures that references between related records point to existing, valid target records.
- [Window Functions](https://blowstack.com/tools/cloud-concepts-hub/window-functions): Window functions calculate values across related rows while preserving each row in the query result.
- [Apache Parquet](https://blowstack.com/tools/cloud-concepts-hub/apache-parquet): Apache Parquet is an open, column-oriented binary file format designed for efficient storage and analytical processing of structured and nested data.
- [Backward Compatibility](https://blowstack.com/tools/cloud-concepts-hub/backward-compatibility): Backward compatibility is the ability of an updated system, interface, or data reader to continue supporting the contracts or data of earlier versions.
- [Columnar Storage](https://blowstack.com/tools/cloud-concepts-hub/columnar-storage): Columnar storage organizes data by column rather than by complete row, allowing analytical workloads to read and compress selected fields efficiently.
- [Data Compression](https://blowstack.com/tools/cloud-concepts-hub/data-compression): Data compression represents information using fewer bytes to reduce storage consumption and data movement.
- [Data Deduplication](https://blowstack.com/tools/cloud-concepts-hub/data-deduplication): Data deduplication identifies repeated representations of the same information and removes or consolidates them according to defined matching and retention rules.
- [Data Validation](https://blowstack.com/tools/cloud-concepts-hub/data-validation): Data validation is the process of checking data against defined structural, quality, and business rules before accepting or using it.
- [JDBC](https://blowstack.com/tools/cloud-concepts-hub/jdbc): JDBC (Java Database Connectivity) is a standard Java API for connecting to databases, executing SQL, and processing results through database-specific drivers.
- [Joins](https://blowstack.com/tools/cloud-concepts-hub/joins): Joins combine records from two or more datasets using a condition that defines how the records relate.
- [Key Performance Indicators](https://blowstack.com/tools/cloud-concepts-hub/key-performance-indicators): Key Performance Indicators are measurable values used to determine whether an application, process, or organization is achieving its most important objectives.
- [Log Retention](https://blowstack.com/tools/cloud-concepts-hub/log-retention): Log retention is the policy and practice of keeping log records available for a defined period before securely deleting them.
- [Open Database Connectivity (ODBC)](https://blowstack.com/tools/cloud-concepts-hub/open-database-connectivity-(odbc)): Open Database Connectivity (ODBC) is a standard programming interface that lets applications access data sources through compatible drivers.
- [Optimistic Concurrency Control](https://blowstack.com/tools/cloud-concepts-hub/optimistic-concurrency-control): Optimistic Concurrency Control lets concurrent operations proceed independently and validates their assumptions before committing changes.
- [Random Sampling](https://blowstack.com/tools/cloud-concepts-hub/random-sampling): Random sampling selects observations from a defined population using chance rather than their position, convenience, or an engineer’s judgment.
- [SQL Joins](https://blowstack.com/tools/cloud-concepts-hub/sql-joins): SQL joins combine rows from tables or other relational inputs according to a matching condition and rules for retaining unmatched rows.
- [Stratified Sampling](https://blowstack.com/tools/cloud-concepts-hub/stratified-sampling): Stratified sampling divides a population into defined, nonoverlapping groups and randomly selects records from each group to create a sample.
- [Timeline Reconstruction](https://blowstack.com/tools/cloud-concepts-hub/timeline-reconstruction): Timeline reconstruction is the process of correlating evidence into a time-ordered account of an incident to establish what happened, which entities were involved, and how events relate.
- [Audit Logging](https://blowstack.com/tools/cloud-concepts-hub/audit-logging): Audit logging records attributable events about actions on systems and data so that activity can be investigated, verified, and reviewed.
- [Benefits Realization](https://blowstack.com/tools/cloud-concepts-hub/benefits-realization): Benefits Realization is the process of turning an investment’s intended outcomes into measurable, sustained business value.
- [Binning](https://blowstack.com/tools/cloud-concepts-hub/binning): Binning converts numerical values into discrete intervals so machine learning models can use ranges rather than exact measurements.
- [Broadcast Joins](https://blowstack.com/tools/cloud-concepts-hub/broadcast-joins): Broadcast joins replicate a small join input to participating workers so each worker can join it locally with its portion of a larger input.
- [Chargeback](https://blowstack.com/tools/cloud-concepts-hub/chargeback): Chargeback is the practice of assigning cloud costs to the teams, products, or business units responsible for them and posting those costs to their internal budgets or financial accounts.
- [Class Imbalance](https://blowstack.com/tools/cloud-concepts-hub/class-imbalance): Class imbalance occurs when some target classes in a dataset have substantially more examples than others.
- [Comma-Separated Values](https://blowstack.com/tools/cloud-concepts-hub/comma-separated-values): Comma-Separated Values (CSV) is a plain-text format that represents tabular records as fields separated by commas.
- [Control Groups](https://blowstack.com/tools/cloud-concepts-hub/control-groups): Control groups are comparable groups that do not receive a specific intervention and provide a reference for measuring its incremental effects.
- [Data Lakes](https://blowstack.com/tools/cloud-concepts-hub/data-lakes): A data lake is a shared repository that stores large volumes of structured, semi-structured, and unstructured data for analytics and other processing.
- [Data Representativeness](https://blowstack.com/tools/cloud-concepts-hub/data-representativeness): Data representativeness is the degree to which a dataset reflects the people, conditions, and patterns relevant to an AI system’s intended use.
- [Data Silos](https://blowstack.com/tools/cloud-concepts-hub/data-silos): Data silos are isolated collections of data that are difficult for other teams or systems to discover, access, interpret, or combine.
- [Denormalization](https://blowstack.com/tools/cloud-concepts-hub/denormalization): Denormalization deliberately combines related data or stores redundant values to make common reads require less work.
- [Hypothesis Testing](https://blowstack.com/tools/cloud-concepts-hub/hypothesis-testing): Hypothesis testing uses sample data to evaluate whether evidence supports rejecting a defined claim about a population or system.
- [JSON](https://blowstack.com/tools/cloud-concepts-hub/json): JSON is a text-based data interchange format that represents structured values using objects, arrays, strings, numbers, booleans, and null.
- [Leading Indicators](https://blowstack.com/tools/cloud-concepts-hub/leading-indicators): Leading indicators are measurable signals that precede an outcome and help predict whether an AI initiative will achieve its intended business value.
- [Missing Value Imputation](https://blowstack.com/tools/cloud-concepts-hub/missing-value-imputation): Missing value imputation replaces absent data values with justified estimates or explicit placeholders so datasets can be analyzed or used by machine learning systems.
- [Null Handling](https://blowstack.com/tools/cloud-concepts-hub/null-handling): Null handling is the explicit detection, interpretation, and treatment of missing or unknown values during data processing.
- [Object Storage](https://blowstack.com/tools/cloud-concepts-hub/object-storage): Object storage stores data as individually addressable objects containing a payload, metadata, and a unique key within a bucket or container.
- [OLTP Versus OLAP](https://blowstack.com/tools/cloud-concepts-hub/oltp-versus-olap): OLTP versus OLAP distinguishes databases optimized for frequent operational transactions from systems optimized for large-scale analytical queries.
- [Open Cybersecurity Schema Framework (OCSF)](https://blowstack.com/tools/cloud-concepts-hub/open-cybersecurity-schema-framework-(ocsf)): Open Cybersecurity Schema Framework (OCSF) is an open, vendor-neutral framework for representing cybersecurity events in a common schema.
- [Partition Pruning](https://blowstack.com/tools/cloud-concepts-hub/partition-pruning): Partition pruning is the exclusion of data partitions that cannot satisfy a query’s filters, reducing the amount of data scanned.
- [Partitioning](https://blowstack.com/tools/cloud-concepts-hub/partitioning): Partitioning divides data or a stream of records into smaller, addressable groups so storage, ingestion, and processing can scale efficiently.
- [Point-in-Time Joins](https://blowstack.com/tools/cloud-concepts-hub/point-in-time-joins): Point-in-Time Joins combine records with the feature values that were valid and available at each record’s historical prediction time.
- [Porters Five Forces](https://blowstack.com/tools/cloud-concepts-hub/porters-five-forces-porters-five-forces-is-a-framework-for-assessing-how-industry-competition-new-entrants-substitutes-suppliers-and-buyers-influence-profitability-and-competitive-positioning_): Porters Five Forces is a framework for assessing how industry competition, new entrants, substitutes, suppliers, and buyers influence profitability and competitive positioning.
- [Predicate Pushdown](https://blowstack.com/tools/cloud-concepts-hub/predicate-pushdown): Predicate pushdown is a query optimization that moves filtering closer to the data source so fewer records reach later processing stages.
- [Query Federation](https://blowstack.com/tools/cloud-concepts-hub/query-federation): Query federation lets a query engine access and combine data from separate data sources without first loading all of that data into one store.
- [Regular Expressions](https://blowstack.com/tools/cloud-concepts-hub/regular-expressions): Regular expressions are text patterns that describe character sequences for searching, matching, extracting, or replacing data.
- [Relational Joins](https://blowstack.com/tools/cloud-concepts-hub/relational-joins): Relational joins combine rows from two datasets according to a matching condition, usually involving shared keys.
- [Semi-Structured Data](https://blowstack.com/tools/cloud-concepts-hub/semi-structured-data): Semi-structured data uses keys, tags, or other markers to organize information without requiring every record to follow the same fixed tabular schema.
- [Sensitivity Analysis](https://blowstack.com/tools/cloud-concepts-hub/sensitivity-analysis): Sensitivity analysis evaluates how changes in uncertain assumptions affect an outcome, such as the return on investment of a cloud-based AI initiative.
- [Skills Matrix](https://blowstack.com/tools/cloud-concepts-hub/skills-matrix): A skills matrix maps people or roles to required competencies and proficiency levels to reveal capability gaps and guide workforce development.
- [Slowly Changing Dimensions](https://blowstack.com/tools/cloud-concepts-hub/slowly-changing-dimensions): Slowly Changing Dimensions is a data-modeling approach for deciding how changes to descriptive attributes are overwritten, retained, or versioned over time.
- [Star Schema](https://blowstack.com/tools/cloud-concepts-hub/star-schema): A star schema is an analytical data model that connects a central fact table to descriptive dimension tables through keys.
- [Structured Data](https://blowstack.com/tools/cloud-concepts-hub/structured-data): Structured data is information organized according to a defined schema, with consistent fields, data types, and relationships that support systematic processing.
- [Subgroup Analysis](https://blowstack.com/tools/cloud-concepts-hub/subgroup-analysis): Subgroup analysis evaluates AI data, predictions, and outcomes separately for defined groups to reveal differences hidden by overall metrics.
- [SWOT Analysis](https://blowstack.com/tools/cloud-concepts-hub/swot-analysis): SWOT analysis is a strategic assessment of internal strengths and weaknesses and external opportunities and threats that influence an organization's choices.
- [Task Completion Rate](https://blowstack.com/tools/cloud-concepts-hub/task-completion-rate): Task Completion Rate is the percentage of attempted tasks that achieve a predefined successful outcome.
- [Time-Series Data](https://blowstack.com/tools/cloud-concepts-hub/time-series-data): Time-series data consists of observations associated with timestamps and analyzed to understand changes over time.
- [User Satisfaction](https://blowstack.com/tools/cloud-concepts-hub/user-satisfaction): User satisfaction is the degree to which an application meets users’ expectations and perceived needs during an interaction or task.
- [Weighted Scoring](https://blowstack.com/tools/cloud-concepts-hub/weighted-scoring): Weighted scoring is a decision-making method that ranks alternatives by combining criterion scores according to their relative importance.
