Amazon Athena: Serverless Interactive SQL for Your Data in Amazon S3

What Amazon Athena is

Amazon Athena is a serverless, interactive query service for analyzing data in Amazon S3 with standard SQL. You do not load data into a database or manage a cluster. You define a table over files in S3, run a query, and Athena reads the files in place and returns the results.

Athena suits ad hoc analysis, log investigation (for example AWS CloudTrail, VPC Flow Logs and load balancer access logs), reporting over a data lake, and building tables for downstream tools. It is not a real-time or streaming system: queries run on demand against whatever data is in S3 when they run, and typical queries take seconds to minutes depending on data volume and layout. Athena also offers Apache Spark for notebook workloads; this post covers the SQL engine.

How it works

The SQL engine in Athena engine version 3 is built on open source Trino and Presto, with AWS integrating community improvements and tuning them for Athena. When you submit a query, Athena plans it, splits the work across serverless compute that AWS manages, reads only the S3 objects the query needs, and writes the results to a query result location in S3.

Table definitions (schemas, file formats, S3 locations and partitions) live in the AWS Glue Data Catalog. You can create tables with CREATE EXTERNAL TABLE statements in Athena, or let an AWS Glue crawler discover the schema. Athena applies the schema when it reads the data (“schema on read”), so the underlying files stay unchanged.

A simple table over JSON logs looks like this:

CREATE EXTERNAL TABLE app_logs (
  request_id string,
  status int,
  latency_ms double,
  path string
)
PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://amzn-s3-demo-bucket/app-logs/';

Pricing

Athena SQL has two pricing options, which you can use together in the same account.

• Per query. You pay for the data each query scans; the Athena pricing page uses $5 per TB scanned in its examples. You are not charged for failed queries. If you cancel a query, you pay for the data scanned up to that point. S3 storage, S3 requests and AWS Glue Data Catalog usage are billed separately at their own rates.
• Capacity reservations. You reserve dedicated serverless capacity, measured in Data Processing Units (DPUs), and pay per DPU-hour instead of per TB scanned. One DPU typically provides 4 vCPUs and 16 GB of memory, and the minimum reservation is 4 DPUs. You assign workgroups to a reservation, which lets you isolate important workloads, control concurrency and get predictable cost for heavy, regular query volumes.

Because per-query pricing is driven by bytes scanned, the techniques below reduce cost and usually improve speed at the same time.

Reduce the data each query scans

Partition your data

Partitioning stores data in S3 prefixes by a column you filter on often, such as date, Region or customer. A query with WHERE dt = '2026-11-01' then reads only that prefix. Choose partition keys that match common filters and avoid creating huge numbers of tiny partitions.

New partitions must be known to the catalog. You can add them with ALTER TABLE ADD PARTITION, run MSCK REPAIR TABLE for Hive-style paths, or use partition projection, where Athena calculates partition locations from table properties instead of looking them up, which removes the need to register partitions as data arrives.

Use columnar formats

Apache Parquet and Apache ORC store data by column and keep statistics for each block. Athena reads only the columns a query references and can skip blocks that cannot match a filter. The Athena pricing page shows an example where converting data to Parquet and compressing it cut the data scanned to a small fraction of the original text format.

You can convert existing data with a CREATE TABLE AS SELECT (CTAS) query:

CREATE TABLE app_logs_parquet
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  partitioned_by = ARRAY['dt'],
  external_location = 's3://amzn-s3-demo-bucket/app-logs-parquet/'
) AS
SELECT request_id, status, latency_ms, path, dt
FROM app_logs;

Compress and size files well

Compressed data means fewer bytes to scan. Athena reads common codecs such as GZIP, Snappy and ZSTD, and engine version 3 added support for reading LZ4 and ZSTD compressed Parquet. Very many small files slow queries down, so aim for fewer, larger files when you write or compact data.

Select only what you need

SELECT * on a columnar table still reads every column. Name the columns you need, filter on partition columns, and use LIMIT while exploring.

Workgroups

Workgroups separate users, teams and applications. Each workgroup can enforce its own query result location and encryption settings, has its own query history and saved queries, and can be controlled with IAM policies. You can set per-query data usage limits that cancel any query scanning more than a threshold, and workgroup-wide limits that trigger an Amazon SNS alarm. Workgroups also publish query metrics to Amazon CloudWatch and can carry cost allocation tags, so you can see Athena spend by team.

A sensible starting point is one workgroup per team or application, with a per-query scan limit, and a separate workgroup for scheduled jobs.

Federated queries

Athena Federated Query lets you run SQL against data that is not in S3, such as Amazon DynamoDB, Amazon Redshift, MySQL, PostgreSQL or Amazon CloudWatch Logs, and join it with data lake tables. Each source uses a data source connector, which tells Athena what to read and pushes filters down to the source. AWS provides prebuilt connectors. Many run as AWS Lambda functions in your account, and some connectors created as AWS Glue Data Catalog federated catalogs no longer need a Lambda function. Custom connectors are written with the Athena Query Federation SDK and run on Lambda. Federated sources are read-only from Athena: INSERT INTO is not supported.

Apache Iceberg tables

Plain Hive-style tables in S3 are append-friendly but awkward for updates and deletes. Athena supports Apache Iceberg tables registered in the AWS Glue Data Catalog. Iceberg is an open table format that adds:

• Row-level INSERT, UPDATE, DELETE and MERGE INTO.
• Time travel queries with FOR TIMESTAMP AS OF and FOR VERSION AS OF.
• Schema and partition evolution without rewriting the table.
• Table maintenance with OPTIMIZE (to compact small files) and VACUUM (to remove expired snapshots).

Athena creates and operates on Iceberg format version 2 tables, with data in Parquet, ORC or Avro. Iceberg is a good default for data lake tables that change over time, such as change data capture from operational databases.

Getting started checklist

• Store data in S3 in Parquet or ORC, compressed and partitioned by common filters.
• Define tables in the AWS Glue Data Catalog.
• Create workgroups with result locations, encryption and per-query scan limits.
• Set an S3 lifecycle rule on the query result location so old results expire.
• Use Iceberg for tables that need updates or deletes.

9Acts is an AWS Advanced Tier Services Partner, holds the AWS DevOps Competency, and has worked with clients on AWS since 2016. If you want help designing a data lake on Amazon S3 and Athena, contact us.