Data Operations and Support · Topic 2 of 6
Analyzing Data with Athena
Use Amazon Athena for serverless SQL on S3 with partitions, CTAS, views, workgroups and federated queries, and compare it with Redshift and EMR.
- 4 min
- Athena is serverless SQL on S3. Cost follows bytes scanned.
- Parquet, compression and partitions are the cost and speed levers.
- CTAS writes a new table in a chosen format. A view stores only the query.
What it is
Amazon Athena is a serverless, interactive query service. You run standard SQL on data in Amazon S3, with no cluster to manage, and you pay for the data each query scans.
Athena reads table definitions from the Glue Data Catalog. It is the quick answer for ad hoc analysis, log analysis and exploring a data lake.
How it works
Query behavior and cost
Athena charges by bytes scanned, so the best lever is to scan less. Combine columnar formats such as Parquet, compression and partitions, and select only the columns you need. A query that filters on a partition column reads only the matching prefixes.
Results are written to an S3 bucket that you choose. Athena can reuse recent results, and it keeps a query history.
Core SQL features
Athena supports SQL for joins, window functions, common table expressions, and JSON and array handling. The engine is based on Trino. Athena also supports running Apache Spark notebooks for code based analysis.
SELECT region, SUM(amount) AS total
FROM sales
WHERE sale_date BETWEEN DATE '2026-09-01' AND DATE '2026-09-30'
GROUP BY region
ORDER BY total DESC;Building new datasets
These statements turn Athena into a transformation tool.
| Statement | Purpose |
|---|---|
CREATE TABLE AS SELECT (CTAS) | Writes query results as a new table with chosen format, compression and partitioning |
INSERT INTO | Adds query results to an existing table |
CREATE VIEW | Saves a query so others reuse it, with no data stored |
A view hides complexity and can limit which columns people see, although the user still needs access to the underlying tables.
Workgroups
A workgroup separates users, teams or workloads. It can set the result location, enforce settings, cap the data scanned per query and publish metrics to CloudWatch. Workgroups also let you track cost per team and apply IAM permissions to each.
Use a per query data limit to stop a runaway query. Use separate workgroups for production reports and for exploration.
Federated queries
Athena federated query reaches data outside S3, such as RDS, DynamoDB, Redshift or on premises sources, through data source connectors that run as Lambda functions. This lets one SQL statement join S3 data with an operational database, without first copying it.
It suits occasional joins. For heavy repeated joins, loading the data into the lake or the warehouse is faster.
Table formats and partition projection
Athena supports Apache Iceberg tables for updates, deletes and time travel. Partition projection computes partitions from table properties, so queries on tables with many partitions plan faster and need no partition maintenance.
Athena compared with other tools
| Tool | Pick it when |
|---|---|
| Athena | Ad hoc or occasional SQL on S3, minimal operations, pay per query |
| Redshift | Heavy, repeated, concurrent analytics with consistent performance |
| EMR or Glue | Large custom Spark or Hive processing |
| QuickSight | Dashboards on top of Athena or Redshift data |
Athena is not a transactional database, and it does not give predictable latency for many concurrent dashboard users.
Security for Athena queries
A user needs permission on Athena actions, the Glue Data Catalog objects, the S3 data and the result bucket. With Lake Formation, permissions on databases, tables, columns and rows can apply in Athena as well. Encrypt the result location with SSE-S3 or SSE-KMS, set through the workgroup.
Troubleshooting slow or failing queries
Slow queries usually scan too much data, read too many small files or join large tables without filters. Failed queries often come from schema mismatches, missing partitions or missing S3 or KMS permissions. Check the query statistics for bytes scanned and rows read.
What to remember for the exam
- Athena is serverless SQL on S3. Cost follows bytes scanned.
- Parquet, compression and partitions are the cost and speed levers.
- CTAS writes a new table in a chosen format. A view stores only the query.
- Workgroups set result locations, data scan limits and cost tracking per team.
- Federated query uses Lambda based connectors to reach other sources.
- Partition projection avoids catalog partition maintenance.
- Athena supports Iceberg tables and Spark notebooks.
- Redshift suits heavy concurrent analytics, Athena suits ad hoc work.
For "reduce Athena cost", look for the answer that reduces bytes scanned, such as Parquet or partitioning, over answers that add more services.
Common mistakes
Querying raw CSV repeatedly. Convert hot data to Parquet with CTAS to cut scan costs.
Using SELECT *. It reads every column of a columnar table.
Forgetting S3 and KMS permissions. A query can fail even when the Athena permission is correct.
Using federated queries for heavy workloads. The source database does the work, and it can slow down.
A view does not grant data access. Users still need permission on the underlying tables and S3 locations unless Lake Formation governs them.
Check yourself
What does Athena charge for?
The amount of data each query scans.
How do you turn query output into a Parquet table?
Use
CREATE TABLE AS SELECTwith the Parquet format.What limits cost and access per team in Athena?
Workgroups, with data scan limits and permissions.
Topic 2 of 6 in Data Operations and Support
Test yourself on Data Operations and Support
The full AWS DEA-C01 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by AWS. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.