mgarden

Athena Query Patterns

3 min read

data aws sql claude-curated

Amazon Athena is a serverless query engine that runs SQL directly against files on S3. Under the hood it’s Trino (formerly Presto). No cluster to manage, pay per query.

How Athena reads S3

Athena uses the Glue Data Catalog to know what tables exist and where their files live. A query like SELECT * FROM events WHERE day = '2026-04-29':

  1. Resolves events to an S3 prefix in the catalog.
  2. Lists files matching the partition predicate.
  3. Reads only those files (and only the columns selected, if Parquet).
  4. Returns results.

Athena never copies the data — query happens against S3 in place. That’s the whole pitch: no ingestion step, point at the bucket and query.

Pricing model

Pricing is bytes scanned, billed per query (or via Capacity Reservations for steady workload). At the time of writing, ~$5 per TB scanned in most regions.

Cost optimisation = reduce bytes scanned. Three levers:

  1. Partitioning — query reads only matching prefixes
  2. Columnar formats (Parquet, ORC) — query reads only selected columns
  3. Compression (Snappy, Zstd) — fewer bytes per row to scan

A naive CSV scan of a 1TB table costs 0.025.

Partition pruning

Partitions in S3 are encoded in the path: s3://bucket/events/year=2026/month=04/day=29/. The Glue catalog tracks them. A query like WHERE year=2026 AND month=04 scans only that prefix.

Gotchas:

  • After loading new partitions, run MSCK REPAIR TABLE events; or use partition projection to make Athena aware of them.
  • Partition projection (defined in table properties) skips the catalog lookup and computes partitions from a template — faster for high-partition tables.
  • Predicates on partition columns must be in the WHERE clause to prune. Joining with a partition column doesn’t help.

File format impact

FormatBytes scanned for SELECT col_a FROM 1TB_table
CSV~1 TB (full scan)
JSON~1 TB (full scan)
Parquet~50 GB (one column, compressed)

Convert raw landings to Parquet as soon as possible — typically the first Glue job in the pipeline.

CTAS — Create Table As Select

CTAS materialises a query result as a new table on S3:

CREATE TABLE events_2026_q1
WITH (
  format = 'PARQUET',
  partitioned_by = ARRAY['day'],
  external_location = 's3://my-lake/silver/events_2026_q1/'
)
AS SELECT * FROM events_raw WHERE year = 2026 AND quarter = 1;

Use cases: convert format (CSV → Parquet), repartition, pre-aggregate. Cheaper than running a full Glue job for one-off transforms.

Iceberg tables

Athena supports Apache Iceberg — a table format that adds ACID transactions, schema evolution, time travel, and DELETE/UPDATE to data lake tables. For mutable data (GDPR deletes, slowly-changing dimensions), Iceberg removes the “data lakes are append-only” pain.

CREATE TABLE events_iceberg (...)
LOCATION 's3://my-lake/iceberg/events/'
TBLPROPERTIES ('table_type' = 'ICEBERG');
 
DELETE FROM events_iceberg WHERE user_id = 12345; -- works

Performance tips

  • Use LIMIT — Athena still scans all matching files, but stops returning early. Helps responsiveness, not cost.
  • Avoid SELECT * on wide Parquet tables — defeats column pruning.
  • Push filters into the deepest subquery — Trino’s optimiser is decent but not magic.
  • Watch for skewed joins; broadcast small tables explicitly with /*+ broadcast(small_table) */.

Limits to know

  • 30-minute query timeout (raise on request).
  • Workgroup-level data scan limits — set them, save yourself from a runaway query.

See also

References

Graph View

Backlinks