AWS Athena

Amazon Athena

Athena runs SQL directly against files in S3. There is no cluster to provision and nothing to load. You define a table describing where the data is and what shape it has, then query it.

You pay per terabyte scanned. That single fact should shape every decision you make about it, because the difference between a well-organised table and a careless one is routinely a factor of a hundred in both cost and query time.

A table over S3

CREATE EXTERNAL TABLE events (
  event_time  timestamp,
  user_id     string,
  event_type  string,
  amount      double
)
PARTITIONED BY (year string, month string, day string)
STORED AS PARQUET
LOCATION 's3://my-events/events/'
TBLPROPERTIES ('parquet.compression' = 'SNAPPY');

The table is metadata in the Glue Data Catalog. Nothing is copied, and dropping the table deletes no data.

Partitioning is the whole game

Without partitions, every query reads every file:

-- Scans the entire dataset. Could be terabytes
SELECT count(*) FROM events WHERE year = '2026' AND month = '09';

With partitions and a matching S3 layout, Athena reads only the relevant prefixes:

s3://my-events/events/year=2026/month=09/day=16/part-0000.parquet

That key=value directory naming is the Hive convention Athena understands, and it is why Firehose prefixes should be configured that way from the start.

New partitions are not discovered automatically:

MSCK REPAIR TABLE events;

MSCK REPAIR scans the whole location, which becomes slow once you have thousands of partitions. Partition projection avoids it entirely by telling Athena how to compute partition values rather than look them up:

ALTER TABLE events SET TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.year.type' = 'integer',
  'projection.year.range' = '2024,2030',
  'projection.month.type' = 'integer',
  'projection.month.range' = '1,12',
  'projection.month.digits' = '2',
  'projection.day.type' = 'integer',
  'projection.day.range' = '1,31',
  'projection.day.digits' = '2',
  'storage.location.template' = 's3://my-events/events/year=${year}/month=${month}/day=${day}'
);

No metadata store lookups, no repair command, no partition registration. For any time-partitioned dataset this is the right configuration and it is widely unknown.

File format matters as much as partitioning

Parquet is columnar, so selecting three columns of forty reads only those three. It also carries statistics per row group, letting Athena skip blocks that cannot match a predicate.

Converting once pays for itself immediately:

CREATE TABLE events_parquet
WITH (
  format = 'PARQUET',
  parquet_compression = 'SNAPPY',
  partitioned_by = ARRAY['year','month','day'],
  external_location = 's3://my-events/parquet/'
) AS
SELECT event_time, user_id, event_type, amount, year, month, day
FROM events_json;

A CTAS statement like that is also how you build a summary table from an expensive query, so repeated dashboards read a small aggregate rather than the raw data each time.

Avoid many small files. Thousands of tiny objects cost more in overhead than a few large ones. Aim for 128 MB or larger per file, which is what generous Firehose buffering produces.

Writing queries that scan less

Always filter on partition columns. A WHERE on a non-partition column filters after reading:

-- Reads everything, then filters
SELECT * FROM events WHERE user_id = 'u-4821';

-- Reads one day
SELECT * FROM events WHERE year='2026' AND month='09' AND day='16' AND user_id='u-4821';

Never SELECT * on a columnar table. It defeats the format's main advantage.

LIMIT does not reduce the scan in most cases, which surprises people. An aggregate still reads everything it needs; the limit applies to output rows.

Check what a query actually cost:

SELECT query, data_scanned_in_bytes / 1e9 AS gb_scanned, total_execution_time_in_millis
FROM information_schema.query_history
ORDER BY data_scanned_in_bytes DESC
LIMIT 10;

Or from the CLI:

aws athena get-query-execution --query-execution-id "$QID" \
  --query 'QueryExecution.Statistics.[DataScannedInBytes,EngineExecutionTimeInMillis]'

Put a limit on it before someone runs a bad query

A single careless query against an unpartitioned multi-terabyte table is an expensive accident. Workgroups let you cap it:

aws athena create-work-group --name analysts \
  --configuration '{
    "ResultConfiguration": {"OutputLocation": "s3://my-athena-results/analysts/"},
    "BytesScannedCutoffPerQuery": 107374182400,
    "EnforceWorkGroupConfiguration": true,
    "PublishCloudWatchMetricsEnabled": true
  }'

BytesScannedCutoffPerQuery at 100 GB cancels anything exceeding it. Set this on every workgroup before giving anyone access. It is the one control that turns a potential surprise bill into an error message.

Separate workgroups also give per-team cost attribution and separate result locations.

Querying AWS's own logs

Two tables worth having ready.

CloudTrail:

SELECT eventtime, useridentity.arn, eventname, sourceipaddress, errorcode
FROM cloudtrail_logs
WHERE eventname IN ('DeleteBucket','PutBucketPolicy','StopLogging')
  AND year='2026' AND month='09'
ORDER BY eventtime DESC;

ALB access logs, where the separate timing fields are what settle "is it us or the balancer":

SELECT
  url,
  count(*) AS requests,
  avg(target_processing_time) AS avg_backend,
  approx_percentile(target_processing_time, 0.99) AS p99_backend,
  sum(CASE WHEN elb_status_code >= 500 THEN 1 ELSE 0 END) AS errors
FROM alb_logs
WHERE day = '2026/09/16'
GROUP BY url
ORDER BY p99_backend DESC
LIMIT 20;

Both should be partitioned. An unpartitioned CloudTrail table over a year of organisation-wide logs is exactly the query that produces an unpleasant bill.

Common errors

HIVE_PARTITION_SCHEMA_MISMATCH: There is a mismatch between the table and
partition schemas.

A column type changed between partitions. Either correct the data or define the column as string and cast in the query.

HIVE_BAD_DATA: Error parsing field value

A row does not match the declared schema, commonly a null in a numeric column, or a header row in a CSV. skip.header.line.count handles the latter.

Query exhausted resources at this scale factor

Too much data for the query plan, often a large join or window function. Filter harder, or use CTAS to materialise an intermediate result.

Insufficient permissions to execute the query

Athena needs read on the data bucket, write on the results bucket, and Glue catalog permissions. All three.

Query returns zero rows but the data is there. Partitions were never added. Run MSCK REPAIR TABLE or enable partition projection.

Quick reference

---

Key takeaways

• You pay per byte scanned. Every optimisation is about reading less.
• Partition your data with Hive-style key=value prefixes, and filter on those columns.
• Use partition projection instead of MSCK REPAIR TABLE for time-based data.
• Parquet with Snappy can cut a scan by 100x versus raw JSON.
• Never SELECT * on a columnar table, and note that LIMIT usually does not reduce the scan.
• Avoid many small files. 128 MB or larger is the target.
• Set BytesScannedCutoffPerQuery on every workgroup before granting access.
• Use CTAS to materialise expensive results that are queried repeatedly.
• Athena needs permissions on the data bucket, the results bucket and Glue.

---

Check your understanding

---

Frequently Asked Questions

How is Athena billed and how do I reduce the cost?

Per terabyte of data scanned, with no charge for idle time. Everything that reduces bytes read therefore reduces cost: partition the data and filter on partition columns so only relevant prefixes are read, store it as Parquet with Snappy so only the columns you select are touched, avoid SELECT *, and consolidate small files into ones of 128 MB or more. Converting a JSON dataset to partitioned Parquet commonly cuts a scan by two orders of magnitude.

Why does my Athena query return no rows when the data exists in S3?

Because the partitions are not registered. A partitioned table only knows about partitions recorded in the Glue catalog, so data written to a new prefix is invisible until MSCK REPAIR TABLE runs or the partition is added with ALTER TABLE ADD PARTITION. The better fix is partition projection, which lets Athena compute partition locations from a template so new data is queryable immediately with no registration step. Also check the S3 prefix uses key=value naming.

What is partition projection and why should I use it?

It tells Athena the types, ranges and location template of your partition columns, so it derives the S3 path for a queried partition arithmetically instead of looking it up. That removes MSCK REPAIR TABLE from your workflow entirely, makes new data queryable as soon as it lands, and avoids the catalog growing to thousands of partition entries, which is what makes repair slow over time. For any time-based dataset written continuously it is the right configuration.

Should I convert my data to Parquet?

Almost certainly. Parquet is columnar, so a query selecting three of forty columns reads only those three, and it stores per-row-group statistics that let Athena skip blocks which cannot satisfy a predicate. Against uncompressed JSON the reduction in bytes scanned is frequently around a hundredfold, which shows up directly in both cost and query time. A single CTAS statement converts and partitions an existing table, and it usually pays for itself within days.

How do I stop someone running an expensive query?

Set BytesScannedCutoffPerQuery on the workgroup, which cancels any query exceeding the limit: 100 GB is a reasonable starting point for interactive use. Do this before granting access rather than afterwards, because a single SELECT * against an unpartitioned multi-terabyte table is otherwise a real and unpleasant bill. Separate workgroups per team also give you cost attribution and independent result locations, and EnforceWorkGroupConfiguration stops users overriding the settings.

Can I use Athena to query CloudTrail and load balancer logs?

Yes, and it is one of its best uses. Both produce large volumes of S3 data nobody wants to load into a database. AWS documents the table definitions, and the console can generate them. The essential part is partitioning by date: an unpartitioned CloudTrail table over a year of organisation-wide logs will scan everything on every query. For ALB logs, the separate request_processing_time and target_processing_time fields are what let you establish whether latency is in the balancer or your application.