AWS Athena, in depth: how serverless SQL over S3 executes, what you pay for, and the layout, partitioning and workgroup decisions that control both

What Athena is made of

Athena separates four things that a traditional database keeps together. Storage is S3: Athena does not own your data, and the same files can be read by Spark on EMR, Glue jobs or Redshift Spectrum. Metadata lives in the AWS Glue Data Catalog by default: databases, tables, column types, the S3 location of each table and its partitions. Compute is a managed, distributed SQL engine; the current SQL engine, Athena engine version 3, is based on the open-source Trino project. Results are written back to an S3 location configured on the workgroup or query.

Because the table is just metadata over a prefix, CREATE EXTERNAL TABLE does not move or validate any data. If the files do not match the declared schema, you find out at query time, as nulls or parse errors. That is the price of schema-on-read, and it means the catalog is the contract between the teams writing files and the teams querying them.

The life of a query

Every query is asynchronous. A client calls StartQueryExecution and gets back a query execution id. The query is queued, then planned: the engine reads the table definition, applies partition filters to decide which S3 prefixes are relevant, and decides which columns it needs. Workers then read the files, apply predicates, join and aggregate, and the final result is written to the results location as a CSV file plus a metadata file. The client polls GetQueryExecution until the state is SUCCEEDED, FAILED or CANCELLED, then pages through GetQueryResults or reads the result file directly from S3.

Here is a minimal, production-shaped loop with boto3. It sets a workgroup, uses backoff while polling, and surfaces the failure reason instead of swallowing it:

import time
import boto3

athena = boto3.client("athena")

def run_query(sql: str, database: str, workgroup: str = "analytics") -> str:
    qid = athena.start_query_execution(
        QueryString=sql,
        QueryExecutionContext={"Database": database},
        WorkGroup=workgroup,          # result location and limits come from here
    )["QueryExecutionId"]

    delay = 0.5
    while True:
        q = athena.get_query_execution(QueryExecutionId=qid)["QueryExecution"]
        state = q["Status"]["State"]
        if state in ("SUCCEEDED", "FAILED", "CANCELLED"):
            break
        time.sleep(delay)
        delay = min(delay * 2, 10)   # do not hammer GetQueryExecution

    if state != "SUCCEEDED":
        raise RuntimeError(f"{qid} {state}: {q['Status'].get('StateChangeReason')}")

    scanned = q["Statistics"]["DataScannedInBytes"]
    print(f"{qid} scanned {scanned / 1e9:.2f} GB")
    return q["ResultConfiguration"]["OutputLocation"]

Log DataScannedInBytes for every query your applications run. It is the single number that explains both your bill and most of your latency,.

What you pay for, with a worked example

Athena SQL has two billing models, and you can use both in the same account.

Per query. You pay per terabyte scanned. The list price on the AWS pricing page is 5 USD per TB, rounded up to the nearest megabyte. DDL statements such as CREATE TABLE are not billed as scans. S3 request charges and result-storage charges are separate, and they apply to failed queries too.

Capacity reservations. You reserve Data Processing Units (DPUs) and assign workgroups to the reservation. The price is 0.30 USD per DPU-hour. AWS documents one DPU as typically 4 vCPUs and 16 GB of memory, a minimum of 4 DPUs per reservation, Athena allocating between 4 and 124 DPUs to a DML query according to its complexity, and 4 DPUs per DDL query. Queries on a reservation do not count towards your account's active query quotas and are queued, for up to 10 hours, when the reservation is busy. A reservation can serve up to 20 workgroups, and capacity requests can take up to 30 minutes and are not guaranteed.

Worked example. A clickstream table holds one year of events, 36 TB as gzipped JSON. A dashboard runs 200 queries a day, each looking at the last 7 days for 4 of the 40 columns.

The Parquet line is an estimate: the real gain depends on how well your data compresses and how evenly the columns are sized. The order of magnitude is the point. Layout, not query tuning, decides Athena cost. The same table also shows when a capacity reservation is worth considering: when per-query cost is high and steady, or when you need predictable concurrency for a business-critical workgroup, which per-query billing cannot give you.

Data layout: format and file size

Athena reads whatever is in S3, but how fast and how cheaply depends on the files.

• Use a columnar format. Parquet and ORC store each column separately, with per-chunk statistics such as minimum and maximum values. The engine reads only the columns the query names and can skip row groups whose statistics rule out a match. CSV and JSON force a full read of every byte.
• Compress with a splittable or columnar-friendly codec. Snappy or ZSTD inside Parquet are common choices. A single large gzipped CSV cannot be split, so one worker must read all of it.
• Avoid many tiny files. Every file costs an S3 request plus open and footer-read overhead. Streaming writers that flush every few seconds create millions of kilobyte-sized objects, and queries then spend most of their time listing and opening files. Compact into files of tens to hundreds of megabytes.

The usual pattern is to land raw data as it arrives, then run a scheduled job that rewrites it as partitioned, compacted Parquet. That job can be Athena itself (CTAS or INSERT INTO, below) or a Glue Spark job; AWS Glue ETL covers the Spark route. Storage-side settings such as encryption and lifecycle rules live on the bucket, described in Amazon S3.

Partitioning and partition projection

A partition is a column whose value is encoded in the S3 path, such as s3://lake/events/dt=2026-09-30/. When a query filters on dt, the planner reads only the matching prefixes. Partitions must be registered somewhere, and you have three options.

MSCK REPAIR TABLE scans the table location and adds any Hive-style partitions it finds. It is simple but slow on large tables, because it lists everything. ALTER TABLE ADD PARTITION registers partitions explicitly, usually called by the job that wrote the data. Partition projection skips the catalog entirely: you describe the partition values as a rule in table properties, and Athena computes the relevant locations at planning time.

CREATE EXTERNAL TABLE events (
  user_id    string,
  event_type string,
  amount     double
)
PARTITIONED BY (dt string)
STORED AS PARQUET
LOCATION 's3://example-lake/events/'
TBLPROPERTIES (
  'projection.enabled'        = 'true',
  'projection.dt.type'        = 'date',
  'projection.dt.format'      = 'yyyy-MM-dd',
  'projection.dt.range'       = '2024-01-01,NOW',
  'storage.location.template' = 's3://example-lake/events/dt=${dt}/'
);

SELECT event_type, count(*) AS n, sum(amount) AS revenue
FROM events
WHERE dt BETWEEN '2026-09-24' AND '2026-09-30'
GROUP BY event_type;

With projection, new days become queryable as soon as the files land, with no crawler or repair step. The catch: only Athena understands projection rules. Other engines reading the same Glue table see no partitions unless you also register them.

Choose partition keys from the filters people actually use, usually a date and sometimes one low-cardinality dimension such as region. Do not partition by user id or another high-cardinality key: you get millions of tiny partitions, each with tiny files. AWS documents that Athena can query Glue tables with up to 10 million partitions but cannot read more than 1 million partitions in a single scan.

Writing data with CTAS, INSERT INTO and UNLOAD

Athena can write as well as read. CREATE TABLE AS SELECT (CTAS) creates a new table, and its files, from a query. INSERT INTO appends a query's output to an existing table. UNLOAD writes a query result to S3 in a chosen format without creating a table, which is useful for handing a Parquet extract to another system.

CREATE TABLE curated.events_parquet
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  external_location = 's3://example-lake/curated/events/',
  partitioned_by = ARRAY['dt']
) AS
SELECT user_id, event_type, amount, dt
FROM raw.events_json
WHERE dt BETWEEN '2026-09-01' AND '2026-09-30';

One limit shapes every backfill: a CTAS query can create at most 100 partitions, and an INSERT INTO can add at most 100 partitions to the destination. Exceeding it fails with HIVE_TOO_MANY_OPEN_PARTITIONS. The documented workaround is a CTAS for the first slice followed by a series of INSERT INTO statements, each covering at most 100 partition values, with non-overlapping WHERE ranges so you do not duplicate data. A month per statement for a daily partition key is the usual pattern.

Iceberg tables when data changes

Plain Hive-style tables are append-only in practice: correcting a row means rewriting files yourself. Athena also supports Apache Iceberg tables, created with TBLPROPERTIES ('table_type' = 'ICEBERG'). Iceberg keeps its own metadata about which files make up each snapshot, which gives you UPDATE, DELETE and MERGE INTO, time-travel queries against earlier snapshots, and hidden partitioning that does not require users to filter on a path column.

Iceberg moves the maintenance problem rather than removing it. Frequent small writes create many small data files and delete files, so schedule OPTIMIZE table REWRITE DATA USING BIN_PACK to compact them and VACUUM to expire old snapshots and remove unreferenced files. Without both, latency and storage drift upward.

Workgroups, governance and quotas

A workgroup is the unit of control. It holds the query result location and its encryption, the engine version, an optional per-query data-scanned limit, and CloudWatch metrics. Set Override client-side settings so that users cannot point results at an unencrypted bucket. Use separate workgroups for ad-hoc analysts, scheduled pipelines and BI tools, give each a per-query scan limit that fits its purpose, and grant access with IAM conditions on the workgroup resource, as described in IAM condition keys. Table and column-level permissions belong in Lake Formation when several teams share a catalog.

Quotas are shared by all workgroups in an account. These are the defaults AWS documents:

Exceeding the active-query quota returns TooManyRequestsException, and API calls above the rate quotas return a ThrottlingException. Callers must retry with exponential backoff and jitter. A scheduler that fires 300 reports at midnight should be spread out or moved to a capacity reservation.

Failure modes

When Athena is the wrong tool

Athena is excellent for ad-hoc exploration, scheduled reporting on partitioned data and lightweight ETL. It is a poor fit for sub-second dashboards with high concurrency over the same hot data, where a warehouse such as Redshift or a cache in front of results serves better; for row-level transactional lookups, which belong in a database; and for long, complex transformation pipelines, where Spark gives you more control over memory and retries. For dashboards, QuickSight in front of Athena with an in-memory import is the common compromise.

What to do next

1. Create separate workgroups for analysts, pipelines and BI, each with an enforced, encrypted result location and a per-query scan limit.
2. Log DataScannedInBytes for every query your applications run, and list the ten most expensive queries each week.
3. Convert your three most-queried JSON or CSV tables to partitioned, compacted Parquet with CTAS plus INSERT INTO batches of at most 100 partitions.
4. Switch date-partitioned tables to partition projection if only Athena reads them.
5. Add exponential backoff with jitter to every client that calls StartQueryExecution or GetQueryExecution.
6. If any table receives updates or deletes, evaluate Iceberg and schedule OPTIMIZE and VACUUM from day one.