Amazon Athena Complete Guide (DEA-C01 Exam Essentials)
Introduction
Amazon Athena frequently appears in both Domain 1: Data Ingestion and Transformation (34%) and Domain 3: Data Operations and Support (22%) of the DEA-C01 exam.
When you see a scenario about "analyzing S3 data directly with SQL," Athena is the answer. It is serverless, requires no infrastructure management, and charges only for the amount of data scanned.
This guide covers the key concepts you need to know for exam preparation.
---
What is Amazon Athena?
Let's start with the fundamentals of Athena.
| Aspect | Detail | | --- | --- | | Definition | Serverless interactive query service for analyzing S3 data with standard SQL | | Infrastructure management | Not required (serverless) | | Pricing model | $5.00 per TB of data scanned | | Query engine | Based on Apache Presto | | Metadata management | Integrated with AWS Glue Data Catalog | | Visualization | Amazon QuickSight |
Key exam points:
"Analyze S3 data with serverless SQL" → Amazon Athena Cost is proportional to the amount of data scanned, so scanning less means lower costs
---
Supported Data Formats and Cost Efficiency
Understanding the formats Athena supports helps you solve cost optimization questions.
| Format | Type | Characteristics | Cost Efficiency | | --- | --- | --- | --- | | CSV / TSV | Row-based | Simple and universal, but inefficient for large volumes | Low | | JSON | Semi-structured | Supports nested data, suitable for logs | Low | | Parquet | Columnar | Column-level scanning, high compression efficiency | High | | ORC | Columnar | Similar to Parquet, supports split processing | High | | Avro | Row-based (splittable) | Schema evolution support, suitable for streaming data | Medium |
When the exam asks "How to reduce Athena costs?", the answer is Parquet or ORC format. Columnar formats scan only the columns needed for the query, reducing data scan volume and costs.
---
Five Cost Optimization Strategies
Athena cost optimization is a frequently tested scenario topic.
| Strategy | Description | | --- | --- | | Use columnar formats | Convert to Parquet or ORC to scan only needed columns | | Data compression | Applying compression reduces the amount of data scanned | | Partitioning | Separate S3 data by date, region, etc. to scan only relevant partitions | | CTAS | Create new tables from SELECT results to convert CSV to Parquet or create summary tables | | Query result reuse | Cache and reuse identical query results to prevent re-scanning |
For cost reduction scenarios, the combination of partitioning + Parquet format + data compression is often the correct answer.
!Athena's 5 cost optimization strategies
Integration with AWS Glue
The standard integration pattern between Athena and Glue.
Key features:
Glue Crawler scans S3 and automatically generates table schemas Athena uses Glue Data Catalog as its metadata store When new partitions are added directly to S3, use MSCK REPAIR TABLE to sync metadata
The Athena + Glue Data Catalog combination is the standard serverless data lake query pattern on AWS.
---
Cost Control and Access Management with Workgroups
Workgroups allow you to separate query execution into logical groups for management.
| Feature | Detail | | --- | --- | | Query history isolation | Isolate query history by team or department | | Cost control | Set data scan limits per workgroup with automatic query termination on exceedance | | Access management | Control user permissions per workgroup via IAM integration | | Cost allocation | Track query costs by department |
When the exam presents a scenario about "limiting Athena costs per team", the answer is setting data scan limits in Workgroups.
---
Federated Query for Multiple Data Sources
Federated Query lets you query not just S3 but various AWS data sources with a single SQL statement.
Supported data sources include Amazon RDS, Redshift, DynamoDB, and S3.
Key features:
Join and analyze multiple sources without ETL Uses SQL and PartiQL Implemented through Lambda-based data source connectors
When the exam asks about "analyzing multiple databases with SQL without complex ETL", the answer is Athena Federated Query.
---
ACID Transactions and Apache Iceberg
Athena supports ACID transactions.
| Feature | Detail | | --- | --- | | ACID support | Data consistency for INSERT, UPDATE, DELETE, and MERGE operations | | Implementation | AWS Glue Data Catalog + Apache Iceberg table format | | Time travel | Query data at specific points in time | | Schema evolution | Modify schemas without interrupting running queries | | Data optimization | OPTIMIZE command merges small files to maintain performance |
When the exam requires UPDATE or DELETE support in Athena, the key point is that Apache Iceberg table format is needed.
---
User Defined Functions (UDFs)
Athena also supports user-defined functions.
Create custom functions via AWS Lambda and call them from SQL Encapsulate complex logic (e.g., geospatial indexing) Execute operations in Athena that are difficult to implement with standard SQL alone
---
Key Use Cases
| Use Case | Description | | --- | --- | | Log analysis | VPC Flow Logs, ELB logs, CloudTrail log analysis | | Cost and usage analysis | Query AWS Cost & Usage Reports directly from S3 | | BI and reporting | Integration with QuickSight, Tableau, Power BI | | Data lake exploration | Instantly query raw S3 data without predefined schemas |
---
Quick Reference Summary
| Keyword | Key Concept | | --- | --- | | Serverless SQL | Direct S3 data querying, no infrastructure needed | | $5/TB | Pay only for data scanned | | Parquet / ORC | Columnar formats for cost reduction | | Partitioning | Scan only relevant data for performance and cost optimization | | Glue Data Catalog | Athena metadata store | | MSCK REPAIR TABLE | Sync S3 new partition metadata | | Workgroups | Per-team cost control and access management | | Federated Query | Single SQL query across multiple data sources | | Apache Iceberg | ACID transactions, time travel support | | UDF | Lambda-based user-defined functions |
---
Wrap-Up
Amazon Athena is a go-to service in DEA-C01 for data lake query and cost optimization scenario questions.
For the exam, it is particularly important to clearly understand the following:
Cost reduction through Parquet/ORC formats and partitioning Integration patterns with Glue Data Catalog Multi-source querying via Federated Query ACID transaction support through Apache Iceberg
Mastering these concepts will prepare you to answer most DEA-C01 data lake query questions**.