Amazon Athena

• Amazon Athena is a serverless, interactive analytics service built on open-source frameworks, supporting open-table and file formats.
• provides a simplified, flexible way to analyze petabytes of data in an S3 data lake and 30+ data sources, including on-premises data sources or other cloud systems using SQL or Python without loading the data.
• is built on open-source Trino and Presto engines and Apache Spark frameworks, with no provisioning or configuration effort required.
• supports Athena for Apache Spark, enabling serverless Spark applications for advanced analytics and machine learning workloads.
• features Athena SQL v3 engine with improved performance, enhanced SQL capabilities, and better cost optimization.
• offers Capacity Reservations with flexible options including 1-minute minimum reservations (down from 60 minutes), 4 DPU minimum (down from 24 DPU), auto-scaling, and per-query DPU controls for predictable performance and cost optimization.
• is highly available and runs queries using compute resources across multiple facilities, automatically routing queries appropriately if a particular facility is unreachable
• can process unstructured, semi-structured, and structured datasets.
• integrates with QuickSight for visualizing the data or creating dashboards.
• supports various standard data formats, including CSV, TSV, JSON, ORC, Avro, and Parquet.
• supports modern table formats including Apache Iceberg, Delta Lake, and Apache Hudi for ACID transactions and time travel queries.
• supports compressed data in Snappy, Zlib, LZO, ZSTD, and GZIP formats. You can improve performance and reduce costs by compressing, partitioning, and using columnar formats.
• enables cross-region querying to analyze data stored across multiple AWS regions from a single query interface.
• can handle complex analysis, including large joins, window functions, and arrays
• uses a managed Glue Data Catalog to store information and schemas about the databases and tables that you create for the data stored in S3
• uses schema-on-read technology, which means that the table definitions are applied to the data in S3 when queries are being applied. There’s no data loading or transformation required. Table definitions and schema can be deleted without impacting the underlying data stored in S3.
• supports fine-grained access control with AWS Lake Formation which allows for centrally managing permissions and access control for data catalog resources in the S3 data lake.
• integrates with Amazon DataZone for comprehensive data governance, cataloging, and discovery across the organization.
• supports AWS Clean Rooms integration for privacy-preserving collaborative analytics without sharing raw data.
• integrates with Amazon SageMaker Lakehouse for unified data access across data lakes and data warehouses with federated catalogs and Lake Formation governance controls.
• supports Amazon S3 Tables integration to query Apache Iceberg tables stored in S3 Table Buckets with automated table management (compaction, snapshot management, garbage collection).
• offers Managed Query Results that automatically stores, secures, and manages query result data at no cost, eliminating the need to provision S3 buckets for query results.

Athena Capacity Reservations

• Capacity Reservations provide dedicated serverless capacity in the form of Data Processing Units (DPU) for mission-critical workloads, isolated from other queries in the account.
• Capacity is fully managed by Athena and held for as long as needed, with no impact on account-level concurrency quotas.
• 1-Minute Reservations (Feb 2026): Reserve capacity for as little as 1 minute (down from 60 minutes) with a minimum of 4 DPU (down from 24 DPU), enabling savings up to 95% for workloads completing in under an hour.
• Auto-Scaling (Nov 2025): Built-in auto-scaling solution using AWS Step Functions that monitors CloudWatch utilization metrics at 1-minute granularity and automatically adjusts DPU capacity based on configurable thresholds.
• DPU Cost and Performance Controls (Nov 2025): Set minimum and maximum DPU limits (4 to 124 DPU in increments of 4) at the workgroup level or per-query level to balance cost efficiency, concurrency, and performance.
• Per-query DPU usage is now reported in the Athena console and via GetQueryExecution/BatchGetQueryExecution APIs for complete visibility into capacity utilization.
• Workgroups are assigned to Capacity Reservations, and Athena automatically routes queries from assigned workgroups to reserved capacity.
• Supports both on-demand (per-query scan-based) pricing and Capacity Reservations (DPU-hour based) pricing models.

Athena Workgroups

• Athena workgroups can be used to separate users, teams, applications, or workloads, to set limits on amount of data each query or the entire workgroup can process, and to track costs.
• Resource-level identity-based policies can be used to control access to a specific workgroup.
• Workgroups help view query-related metrics in CloudWatch, control costs by configuring limits on the amount of data scanned, create thresholds, and trigger actions, such as SNS, when these thresholds are breached.
• Workgroups now support query result reuse and caching to reduce costs and improve performance for repeated queries.
• Enhanced cost controls with per-query data scanning limits and automatic query termination for runaway queries.
• Workgroup-level encryption settings and fine-grained access controls for improved security governance.
• Workgroups can be configured with Capacity Reservations for consistent performance and predictable costs.
• IAM Identity Center Workgroups: Athena supports workgroups with IAM Identity Center authentication mode, enabling federated identity across AWS services like Athena and Amazon EMR Studio with trusted identity propagation.
• Managed Query Results: Workgroups can use managed query results, removing the need to create or manage S3 buckets for storing query results. Results are available for 24 hours at no cost.
• Workgroups integrate with IAM, CloudWatch, Simple Notification Service, and AWS Cost and Usage Reports as follows:
  • IAM identity-based policies with resource-level permissions control who can run queries in a workgroup.
  • Athena publishes the workgroup query metrics to CloudWatch if you enable query metrics.
  • SNS topics can be created that issue alarms to specified workgroup users when data usage controls for queries in a workgroup exceed the established thresholds.
  • Workgroup tag can be configured as a cost allocation tag in the Billing and Cost Management console and the costs associated with running queries in that workgroup appear in the Cost and Usage Reports with that cost allocation tag.
  • AWS CUR 2.0 now supports direct Athena and Redshift integration for analyzing cost data using standard SQL.

Athena Federated Queries

• Athena Federated Query enables querying data across multiple sources including Amazon DynamoDB, Amazon RDS, Amazon Redshift, on-premises databases, and third-party sources like Snowflake without moving data.
• Managed Connectors (Apr 2026): Athena now offers managed connectors for 12 data sources including DynamoDB, PostgreSQL, MySQL, and Snowflake. Athena automatically sets up and manages connector resources, registering the data source as a federated catalog in AWS Glue Data Catalog—no Lambda function deployment required.
• Two types of connectors:
  • AWS Glue Data Catalog federated connectors – Use Glue connections with fine-grained governance through Lake Formation.
  • Athena data catalog federated connectors – Legacy connectors that require a Lambda function in your account.
• Federated Passthrough Queries: Run queries on federated data sources using the native query language of the data source itself, pushing the full query down for execution.
• Predicate Pushdown: Optimized connectors use dynamic filtering and expanded predicate pushdown to perform more operations at the source, reducing data transfer and improving performance.
• SageMaker Lakehouse Integration: Federated catalogs through SageMaker Lakehouse enable unified data access with Lake Formation column-level, row-level, and cell-level security controls.
• Supports cross-account federated queries to access data in other AWS accounts.
• Federated views allow creating and querying views on federated data sources.

Athena Best Practices

• Partition the data
  • which helps keep the related data together based on column values such as date, country, and region.
  • Athena supports Hive partitioning and advanced partition projection with custom expressions.
  • Use dynamic partition pruning for improved query performance with complex partition schemes.
  • Consider partition evolution strategies when using modern table formats like Iceberg.
  • Pick partition keys that will support the queries
  • Partition projection is an Athena feature that stores partition information not in the Glue Data Catalog but as rules in the properties of the table in AWS Glue.
• Compression
  • Compressing the data can speed up queries significantly, as long as the files are either of an optimal size or the files are splittable.
  • Smaller data sizes reduce the data scanned from S3, resulting in lower costs of running queries and reduced network traffic.
  • Athena supports ZSTD compression with configurable compression levels for fine-tuned performance-size tradeoffs.
• Optimize file sizes
  • Queries run more efficiently when data scanning can be parallelized and when blocks of data can be read sequentially.
• Modern file formats and optimization
  • Columnar storage formats like ORC and Parquet remain optimal for analytical workloads.
  • Apache Iceberg tables provide ACID transactions, schema evolution, and time travel capabilities.
  • Delta Lake integration enables reliable data lakes with ACID guarantees.
  • Use Z-ordering and data clustering techniques for improved query performance.
  • S3 Table Buckets provide purpose-built storage for Iceberg tables with automated compaction, snapshot management, and garbage collection.
  • A splittable file can be read in parallel by the execution engine in Athena, whereas an unsplittable file can’t be read in parallel.
• Query optimization and performance
  • Leverage query result caching and reuse for frequently executed queries.
  • Use EXPLAIN and ANALYZE statements to understand query execution plans.
  • Use the Cost-Based Optimizer (CBO) with AWS Glue column statistics for improved join reordering and aggregation pushdown.
  • Implement query performance monitoring with CloudWatch Insights.
  • Consider Capacity Reservations with DPU controls for consistent performance requirements.
  • Optimize queries by using appropriate WHERE clauses and avoiding SELECT * statements.
  • Use managed query results to eliminate S3 bucket management overhead for ad-hoc queries.

Security and Governance

• Enhanced Lake Formation Integration: Row-level and cell-level security controls for fine-grained data access, now extended to federated catalogs through SageMaker Lakehouse.
• Tag-Based Access Control (TBAC): Lake Formation TBAC now supports federated catalogs including S3 Tables, Redshift data warehouses, and federated data sources like DynamoDB, MySQL, and Snowflake.
• Data Masking and Anonymization: Built-in functions for protecting sensitive data during queries.
• Cross-Account Access: Secure data sharing across AWS accounts with resource-based policies and federated catalog governance.
• Audit and Compliance: Comprehensive query logging and data lineage tracking through AWS CloudTrail and DataZone.
• Encryption Enhancements: AWS recommends SSE-KMS encryption over SSE-S3 or CSE-KMS. Support for customer-managed KMS keys and field-level encryption.
• Identity-Based Access Control: Integration with AWS IAM for fine-grained permissions, role-based access, and IAM Identity Center for trusted identity propagation.
• VPC Endpoints: Private connectivity to Athena SQL and Athena Spark (PrivateLink support added Apr 2026) without internet gateway requirements.

Advanced Use Cases and Patterns

• Machine Learning Integration: Query results can be directly used with Amazon SageMaker for ML model training and inference.
• Real-time Analytics: Near real-time querying of streaming data from Kinesis Data Firehose with minimal latency.
• Federated Queries: Query data across multiple sources including RDS, Redshift, DynamoDB, Snowflake, and on-premises databases using managed connectors or Lambda-based connectors.
• Data Mesh Architecture: Athena serves as a query engine for decentralized data architectures with domain-specific data products, supported by SageMaker Lakehouse federated catalogs.
• Serverless ETL Pipelines: Combine Athena with AWS Step Functions and Lambda for fully serverless data processing workflows.
• Cost Optimization Patterns: Use Capacity Reservations with auto-scaling for predictable workloads, on-demand for sporadic queries, and DPU controls for fine-grained cost management.
• Multi-Account Analytics: Centralized analytics across multiple AWS accounts using cross-account access patterns and federated catalogs.
• Hybrid Cloud Analytics: Query on-premises data alongside cloud data using federated query capabilities and managed connectors.
• S3 Table Buckets: Use Athena to query purpose-built Apache Iceberg tables in S3 Table Buckets for optimized analytics with automated maintenance.
• Cost and Usage Analysis: AWS CUR 2.0 integrates directly with Athena and Redshift for analyzing AWS billing data without custom data warehouses.

References

• Amazon Athena
• Amazon Athena User Guide
• Athena Features and Capabilities
• Athena for Apache Spark
• Manage Query Processing Capacity
• Athena 1-Minute Reservations and Capacity Controls
• Athena Federated Query
• Managed Query Results
• S3 Table Buckets with Athena
• Using Iceberg Tables in Athena
• Cost-Based Optimizer
• Amazon Athena Pricing
• AWS Big Data Blog – Athena