Analytics

Amazon Athena

Focused AWS Solutions Architect Associate notes from the Analytics domain.

Amazon Athena#

Amazon Athena Overview#

  • Definition: Amazon Athena is a serverless, interactive query service that allows users to analyze data in Amazon S3 and other data sources using standard SQL, without managing infrastructure.
  • Key Features:
    • Queries data directly in S3 using ANSI SQL (Presto engine with extensions).
    • Integrates with AWS Glue Data Catalog for schema metadata.
    • Supports various data formats (e.g., CSV, JSON, Parquet, ORC, Avro).
    • Provides serverless scaling, pay-per-query pricing, and no infrastructure management.
    • Integrates with QuickSight, CloudWatch, and Lake Formation for visualization, monitoring, and governance.
  • Use Cases: Analyze log data (e.g., CloudTrail, VPC Flow Logs), query data lakes, perform ad-hoc analytics, generate business intelligence reports.
  • Key Updates (2024–2025):
    • Enhanced Query Performance: Optimized for complex joins and aggregations (October 2024).
    • AWS Lake Formation Integration: Fine-grained access control and cross-account sharing (March 2024).
    • FIPS 140-2 Compliance: Enhanced for GovCloud (October 2024).
    • Security Hub Integration: Compliance monitoring for query configurations (January 2025).

1. Athena Core Concepts#

Components#

  • Query Engine:
    • Based on Presto with extensions for AWS-specific integrations.
    • Executes SQL queries against data in S3 or federated sources.
    • Explanation: E.g., query CloudTrail logs in S3 using SELECT * FROM cloudtrail_logs.
  • AWS Glue Data Catalog:
    • Metadata repository for databases, tables, and schemas.
    • Defines structure of data in S3 (e.g., columns, partitions).
    • Explanation: E.g., Glue table defines cloudtrail_logs schema.
  • Workgroups:
    • Logical groupings for queries, users, and settings (e.g., query limits, encryption).
    • Controls access, cost tracking, and query execution settings.
    • Explanation: E.g., workgroup for “Analysts” with S3 output encryption.
  • Federated Queries:
    • Queries data from non-S3 sources (e.g., RDS, Redshift, DynamoDB) via connectors.
    • Uses Lambda-based connectors for external data sources.
    • Explanation: E.g., query RDS MySQL table alongside S3 data.
  • Query Results:
    • Stored in S3 (user-specified bucket) as CSV or other formats.
    • Cached for 24 hours to reduce repeat query costs.
    • Explanation: E.g., query results saved to s3://athena-results/.
  • Prepared Statements:
    • Parameterized SQL queries to prevent injection and improve reusability.
    • Explanation: E.g., PREPARE stmt FROM SELECT * FROM logs WHERE date = ?.
  • User-Defined Functions (UDFs):
    • Custom logic in SQL queries using Lambda or Java-based functions.
    • Explanation: E.g., UDF to parse complex JSON logs.

Key Concepts#

  • Data Formats:
    • Optimized formats (Parquet, ORC) reduce query costs and improve performance.
    • Supports compressed formats (e.g., GZIP, Snappy).
    • Explanation: E.g., Parquet reduces data scanned for queries.
  • Partitioning:
    • Organizes S3 data into partitions (e.g., year=2025/month=04) to reduce scanned data.
    • Defined in Glue Data Catalog or manually.
    • Explanation: E.g., partition by date to scan only 2025/04 data.
  • Encryption:
    • Queries and results encrypted in transit (HTTPS) and at rest (S3 SSE-S3, SSE-KMS, or CSE-KMS).
    • Workgroup-level encryption settings.
    • Explanation: E.g., KMS-encrypted query results in S3.
  • Access Control:
    • IAM policies control access to Athena, Glue, and S3.
    • Lake Formation provides fine-grained row/column-level access.
    • Explanation: E.g., IAM policy allows athena:StartQueryExecution.
  • Query Cost:
    • Billed per TB of data scanned ($5/TB, rounded to 10 MB).
    • Caching and compression reduce costs.
    • Explanation: E.g., query scanning 1 GB costs $0.005.

Key Notes:#

  • Exam Relevance: Understand querying S3, Glue integration, partitioning, federation, and cost optimization.
  • Mastery Tip: Compare Athena vs. Redshift Spectrum vs. CloudTrail Lake for querying.

2. Athena Performance Features#

Athena optimizes query execution.

Low Latency#

  • Purpose: Fast query results.
  • Features:
    • Serverless architecture spins up resources instantly.
    • Enhanced query engine for joins/aggregations (2024).
    • Caching of recent results (24 hours).
  • Explanation: E.g., simple query on 1 GB Parquet data completes in seconds.
  • Exam Tip: Highlight serverless speed for ad-hoc queries.

High Throughput#

  • Purpose: Handle large datasets.
  • Features:
    • Scales to petabytes of S3 data.
    • Parallel query execution across distributed nodes.
  • Explanation: E.g., query 1 PB of CloudTrail logs concurrently.
  • Exam Tip: Use for big data analytics.

Scalability#

  • Purpose: Support growing data lakes.
  • Features:
    • Auto-scales compute based on query complexity.
    • Federated queries scale to multiple data sources.
    • Lake Formation supports cross-account data sharing (2024).
  • Explanation: E.g., query 10 TB across 5 accounts via Lake Formation.
  • Exam Tip: Emphasize scalability for enterprise data lakes.

Key Notes:#

  • Performance: Low latency + high throughput + scalability = efficient querying.
  • Exam Tip: Optimize with Parquet, partitioning, and caching.

3. Athena Resilience Features#

Resilience ensures reliable query execution.

Multi-AZ/Region Redundancy#

  • Purpose: Survive failures.
  • Features:
    • Athena is a Regional service with multi-AZ compute.
    • Data stored in durable S3 (11 9s durability).
  • Explanation: E.g., queries continue if us-east-1a fails.
  • Exam Tip: Highlight S3 durability for resilience.

Continuous Availability:#

  • Purpose: Uninterrupted querying.
  • Features:
    • Serverless architecture eliminates downtime.
    • Automatic retries for transient failures.
  • Explanation: E.g., query runs during S3 maintenance.
  • Exam Tip: Use for 24/7 analytics.

Monitoring and Recovery:#

  • Purpose: Track and resolve issues.
  • Features:
    • CloudWatch metrics for query execution (e.g., DataScannedInBytes).
    • CloudTrail logs Athena API calls (e.g., StartQueryExecution).
    • Security Hub detects misconfigured workgroups (2025).
    • Query history tracks failed queries for debugging.
  • Explanation: E.g., alarm on high QueryExecutionTime.
  • Exam Tip: Use CloudWatch and CloudTrail for monitoring.

Data Durability:#

  • Purpose: Protect queried data.
  • Features:
    • S3 provides 99.999999999% durability.
    • Query results stored in S3 with versioning/lifecycle policies.
  • Explanation: E.g., recover deleted query results via S3 versioning.
  • Exam Tip: Highlight S3 resilience for data lakes.

Key Notes:#

  • Resilience: Multi-AZ + serverless + monitoring + S3 durability = reliable querying.
  • Exam Tip: Design resilient data lakes with S3 and Athena.

4. Athena Security Features#

Security is a core focus for Athena in SAA-C03.

Access Control#

  • IAM Policies:
    • Restrict actions (athena:StartQueryExecution, glue:GetTable).
    • Scope to workgroups, databases, or S3 buckets.
    • Example: {“Effect”: “Allow”, “Action”: “athena:StartQueryExecution”, “Resource”: “arn:aws:athena:::workgroup/analysts”}.
  • Lake Formation:
    • Fine-grained access (row/column-level) for data lakes.
    • Cross-account data sharing (2024).
    • Explanation: E.g., restrict analysts to specific columns in sales_data.
  • Workgroup Policies:
    • Control query execution and output locations per workgroup.
    • Explanation: E.g., restrict workgroup to s3://secure-results/.
  • Exam Tip: Practice IAM and Lake Formation policies.

Encryption#

  • In Transit:
    • HTTPS for API calls and query execution.
    • Explanation: E.g., secure StartQueryExecution call.
  • At Rest:
    • Query results encrypted with SSE-S3, SSE-KMS, or CSE-KMS.
    • Metadata in Glue Data Catalog encrypted with KMS.
    • Explanation: E.g., KMS-encrypted query results in S3.
  • Exam Tip: Highlight KMS for compliance.

Compliance:#

  • Purpose: Meet regulatory standards.
  • Features:
    • Supports HIPAA, PCI, SOC, ISO, GDPR, FIPS 140-2 (GovCloud).
    • Security Hub detects non-compliant workgroups (2025).
    • Lake Formation ensures compliant data access.
  • Explanation: E.g., query HIPAA-compliant patient data in S3.
  • Exam Tip: Use Lake Formation for compliance.

Auditing:#

  • Purpose: Track query activity.
  • Features:
    • CloudTrail logs Athena API calls.
    • Query history logs SQL statements and metadata.
    • Security Hub monitors compliance (2025).
    • Explanation: E.g., audit StartQueryExecution for unauthorized access.
  • Exam Tip: Use CloudTrail and query history for auditing.

Key Notes:#

  • Security: IAM + Lake Formation + encryption + auditing = secure querying.
  • Exam Tip: Configure Lake Formation, KMS, and CloudTrail for secure Athena.

5. Athena Cost Optimization#

Cost efficiency is a key exam domain.

Pricing#

  • Query Cost: $5/TB scanned, rounded to 10 MB minimum.
  • Glue Data Catalog:
    • $1/100,000 requests for metadata operations.
    • $1/GB/month for storage.
  • S3 Storage:
    • Query results: $0.023/GB/month.
    • Data lake: $0.023/GB/month.
  • Federated Queries:
    • Lambda charges apply (e.g., $0.20/1M requests).
  • Example:
    • Query scanning 10 GB, 10K Glue requests, 1 GB S3 results, 1K Lambda requests:
      • Query: 10 GB × $5/1,000 GB = $0.05.
      • Glue: 10K × $1/100K = $0.10.
      • S3: 1 GB × $0.023 = $0.023.
      • Lambda: 1K × $0.20/1M = $0.0002.
      • Total: $0.05 + $0.10 + $0.023 + $0.0002 = ~$0.173.
  • Free Tier: None for Athena; S3/Glue free tiers may apply.

Cost Strategies#

  • Use Columnar Formats:
    • Parquet/ORC reduces scanned data vs. CSV/JSON.
    • Explanation: E.g., Parquet cuts 10 GB scan to 1 GB, saving $0.045.
  • Partition Data:
    • Partition S3 data (e.g., year/month) to scan less data.
    • Explanation: E.g., partition by date reduces scan from 100 GB to 1 GB, saving $0.495.
  • Compress Data:
    • Use GZIP/Snappy to reduce storage and scan costs.
    • Explanation: E.g., compress 10 GB to 2 GB, saving $0.04.
  • Cache Results:
    • Reuse cached results (24 hours) to avoid re-scanning.
    • Explanation: E.g., cache saves $0.05 for repeat query.
  • Limit Columns:
    • Select only needed columns in SELECT statements.
    • Explanation: E.g., select 2 columns vs. 20, saving 50% scan cost.
  • Workgroup Limits:
    • Set query cost thresholds to prevent runaway queries.
    • Explanation: E.g., cap workgroup at $10/day.
  • Tagging:
    • Tag workgroups and S3 buckets for cost tracking.
    • Explanation: E.g., tag workgroup with “Project:Analytics”.
  • Monitor Usage:
    • Use CloudWatch metrics and Cost Explorer to track query costs.
    • Explanation: E.g., reduce scans to save $50/month.

Key Notes:#

  • Cost Savings: Parquet + partitioning + caching + limits = lower costs.
  • Exam Tip: Calculate query costs and optimize with partitioning.

6. Athena Advanced Features#

Enhanced Query Performance:#

  • Purpose: Faster complex queries.
  • Features:
    • Optimized for joins, aggregations, and large datasets (2024).
    • Explanation: E.g., 10x faster for GROUP BY on 1 TB data.
  • Exam Tip: Know for high-performance analytics.

AWS Lake Formation Integration:#

  • Purpose: Secure data lakes.
  • Features:
    • Row/column-level access, cross-account sharing (2024).
    • Explanation: E.g., restrict sales_data to region column.
  • Exam Tip: Use for compliance and governance.

Federated Queries:#

  • Purpose: Query multiple sources.
  • Features:
    • Connects to RDS, Redshift, DynamoDB via Lambda.
    • Explanation: E.g., join S3 logs with RDS customer data.
  • Exam Tip: Know for hybrid analytics.

Security Hub Integration:#

  • Purpose: Compliance monitoring.
  • Features:
    • Detects misconfigured workgroups (e.g., unencrypted outputs) (2025).
    • Explanation: E.g., flag missing KMS encryption.
  • Exam Tip: Use for compliance.

Prepared Statements and UDFs:#

  • Purpose: Flexible querying.
  • Features:
    • Prepared statements prevent SQL injection.
    • UDFs add custom logic.
    • Explanation: E.g., UDF parses nested JSON.
  • Exam Tip: Know for advanced SQL use cases.

Key Notes:#

  • Flexibility: Lake Formation + federation + UDFs = advanced analytics.
  • Exam Tip: Master Lake Formation and federated queries.

7. Athena Use Cases#

Understand practical applications.

Log Analysis#

  • Setup: Query CloudTrail, VPC Flow Logs in S3.
  • Features: SQL-based auditing, partitioning.
  • Explanation: E.g., find unauthorized DeleteBucket calls.

Data Lake Analytics#

  • Setup: S3 data lake, Glue Catalog, Lake Formation.
  • Features: Query petabytes with fine-grained access.
  • Explanation: E.g., analyze sales data for trends.

Business Intelligence#

  • Setup: Integrate with QuickSight for dashboards.
  • Features: Ad-hoc queries for reports.
  • Explanation: E.g., create sales report from S3 data.

Federated Analytics#

  • Setup: Query S3 + RDS/Redshift via connectors.
  • Features: Combine cloud and on-premises data.
  • Explanation: E.g., join S3 logs with RDS customer records.

8. Athena vs. Other Query Services#

FeatureAthenaRedshift SpectrumCloudTrail Lake
TypeServerless SQLData Warehouse QueryEvent Query
FocusS3 data lakesRedshift + S3CloudTrail events
DataAny S3 dataS3 data with RedshiftAPI events
Cost$5/TB scanned$5/TB + Redshift costs$2.75/GB ingested
Use CaseLog analyticsLarge-scale BIAPI auditing

Explanation:#

  • Athena: General-purpose S3 querying.
  • Redshift Spectrum: S3 querying with Redshift integration.
  • CloudTrail Lake: Specialized for CloudTrail event analysis.

9. Detailed Explanations for Mastery#

  • Enhanced Query Performance:
    • Example: Faster JOIN on 1 TB sales data.
    • Why It Matters: Scalable analytics (2024).
  • Lake Formation Integration:
    • Example: Restrict sales_data to authorized users.
    • Why It Matters: Secure data lakes (2024).
  • Security Hub Integration:
    • Example: Flag unencrypted query results.
    • Why It Matters: Compliance monitoring (2025).

10. Quick Reference Table#

FeaturePurposeKey DetailExam Relevance
Query EngineExecute SQLPresto-based, serverlessCore Concept
Glue Data CatalogMetadata managementDefines schemas for S3Core Concept
WorkgroupsQuery organizationAccess, encryption, cost controlsCore Concept
Lake FormationSecure data lakesRow/column access, sharing (2024)Security
Federated QueriesMulti-source queriesRDS, Redshift via LambdaFlexibility
Security HubCompliance monitoringMisconfigured workgroups (2025)Security, Resilience
PartitioningReduce scan costsOrganizes S3 data by keysCost, Performance

Found an issue? Improve this note in the GitHub repo.