Making Athena Fast and Cheap: Partition Projection, Parquet Layout, CTAS, and Workgroup Byte Limits

Athena has one price: roughly five dollars per terabyte scanned, with a ten-megabyte minimum per query. That is the entire cost model. There is no cluster to size, no warehouse to suspend, no concurrency slider. Which sounds simple until you realize it also means every performance problem and every cost problem are the same problem, and the fix is always “read fewer bytes.”

I inherited an analytics account once where Athena was the third largest line item, behind S3 and EC2, and ahead of every managed service the team had actually chosen on purpose. Nobody had done anything stupid. They had a well-organized lake, Parquet everywhere, sensible table names. What they had was 4,000 daily queries against tables with no projection, files averaging 3 MB, and a BI tool that helpfully issued SELECT * to build a preview pane.

Three weeks of work took that bill down by about 85% and cut p95 query latency from 40 seconds to under 6. None of it was clever. This post is that work, in the order I do it.

If you want the surrounding context of where Athena sits among the other AWS query engines, the AWS data engineering stack guide covers the landscape. This post assumes Athena is already the thing you are running and it is either slow or expensive.

Bytes scanned is the only number

Open the Athena console, run a query, and look at the two numbers underneath: run time and data scanned. The second one is your invoice. Cache it, log it, alert on it.

The four ways to reduce it, in order of how much they usually buy you:

1. Prune partitions. Do not read the directories at all.
2. Prune columns. Parquet lets Athena read only the columns in your projection, so SELECT * is a self-inflicted wound.
3. Prune row groups. Parquet min/max statistics let the reader skip row groups whose ranges cannot match your predicate — but only if the data is sorted on that column.
4. Compress. Snappy is the default and correct answer. ZSTD scans fewer bytes at slightly higher CPU cost and is worth testing on cold archival tables.

Notice that three of those four are layout decisions made at write time, by whatever job produced the files. Athena tuning is mostly upstream work. You are not optimizing the query engine, you are optimizing what you handed it.

flowchart TD
  q[Athena query] --> part{"Partition pruned?"}
  part -->|no| scanall["Scan every file: full bill"]
  part -->|yes| cols{"Columns projected?"}
  cols -->|"SELECT *"| scanwide[Read all columns]
  cols -->|explicit| rg{"Sorted on predicate?"}
  rg -->|no| readall[Read all row groups]
  rg -->|yes| skip["Skip row groups via min/max stats"]
  skip --> cheap[Small scan, fast query]

Four gates between your query and your invoice. Most teams only close the first one.

Partition projection: stop asking Glue where the files are

Standard partitioned tables store every partition as a row in the Glue Data Catalog. Athena calls GetPartitions before planning, and on a table with 200,000 partitions that call is paginated, slow, and occasionally throttled. I have seen 12 seconds of pure metadata time on a query that scanned 400 MB.

Partition projection replaces the lookup with arithmetic. You tell Athena the shape of the partition space in table properties, and it computes the S3 prefixes directly. No catalog round trip, no MSCK REPAIR, no crawler, no missing-partition incidents at midnight UTC when a new day starts.

-- Projection on a date + region layout. No Glue partitions exist
-- for this table at all; Athena derives the prefixes.
CREATE EXTERNAL TABLE events.web_events (
  event_id     string,
  session_id   string,
  user_id      string,
  event_type   string,
  event_time   timestamp,
  properties   map<string,string>
)
PARTITIONED BY (dt string, region string)
STORED AS PARQUET
LOCATION 's3://acme-lake-curated/events/web_events/'
TBLPROPERTIES (
  'projection.enabled'            = 'true',
  'projection.dt.type'            = 'date',
  'projection.dt.range'           = '2023-01-01,NOW',
  'projection.dt.format'          = 'yyyy-MM-dd',
  'projection.dt.interval'        = '1',
  'projection.dt.interval.unit'   = 'DAYS',
  'projection.region.type'        = 'enum',
  'projection.region.values'      = 'us,eu,apac',
  'storage.location.template'     =
    's3://acme-lake-curated/events/web_events/dt=${dt}/region=${region}/',
  'parquet.compression'           = 'SNAPPY'
);

Two rules I enforce. First, projection.dt.range must end in NOW, not a hardcoded future date, or the table quietly stops seeing new data on the day you forgot about. Second, keep the projected space small: date times region here is a few thousand prefixes. If you project a high-cardinality integer, Athena will enumerate the whole space and you will have invented a slower metastore.

The failure mode nobody warns you about: with projection on, a query for a partition that has no files returns zero rows instantly and silently. That is usually what you want. It also means a broken upstream job looks identical to a quiet day. Put a row-count check in your pipeline — the data quality patterns here apply directly — because Athena will no longer error out on a missing partition.

Parquet layout: file size first, sort order second

The single most common lake pathology is small files. Athena opens every file, reads its footer, and plans around it. At 3 MB per file, footer reads and S3 request overhead dominate; the scan bytes are almost irrelevant compared to the wall clock you burn on list and open operations.

My targets, which have not changed in years:

• File size: 128 MB to 512 MB. Below 64 MB you are wasting planning time; above 1 GB you lose parallelism.
• Row group size: 128 MB. The default in most writers is fine.
• Partition size: aim for at least 1 GB of data per partition. If daily partitions give you 20 MB each, partition monthly.
• Partition count: keep it under a few hundred thousand even with projection.

Sort order is the second lever and the one people skip. Parquet stores min/max per column per row group. If your table is sorted by event_time within each partition, a predicate on event_time skips row groups. If the rows are in arrival order, every row group’s min/max spans the whole day and nothing is skipped. Same bytes on disk, ten times the scan.

This is the same idea as Snowflake micro-partition pruning and BigQuery clustering. Different engines, one physics: the reader can only skip what the writer made skippable.

CTAS is your compaction job

You do not need Spark to fix file layout. Athena’s CREATE TABLE AS SELECT writes Parquet, respects bucketing, and costs one scan of the source. For anything under a few hundred gigabytes it is faster to write and cheaper to run than spinning up Glue or EMR.

-- Rewrite one day of small files into ~256 MB sorted Parquet.
-- Bucketing keeps user_id lookups on a small file subset.
CREATE TABLE events.web_events_compacted
WITH (
  external_location  = 's3://acme-lake-curated/events/web_events_v2/dt=2026-05-04/',
  format             = 'PARQUET',
  parquet_compression = 'SNAPPY',
  bucketed_by        = ARRAY['user_id'],
  bucket_count       = 16          -- 4 GB day / 16 = 256 MB per file
) AS
SELECT
  event_id, session_id, user_id, event_type, event_time, properties
FROM events.web_events_raw
WHERE dt = '2026-05-04'
ORDER BY event_time;   -- sort so row-group min/max are narrow

Two things to know before you build a pipeline on this. CTAS cannot write into a location that already has data, so daily compaction means one target prefix per day and an atomic swap of the partition location — or INSERT INTO against an existing table, which appends but does not let you set bucketing. And a single CTAS has a 100-partition write limit per statement, which is why the pattern above compacts one day at a time in a loop rather than rewriting the table in one shot.

The ORDER BY deserves a note. A global sort forces a single final stage and slows the CTAS down. For most tables I use ORDER BY on the timestamp anyway, because the write happens once and the reads happen thousands of times. If the CTAS becomes the bottleneck, sort within buckets instead and accept slightly wider min/max ranges.

Here is the loop I actually schedule, which is boring on purpose:

import time
import boto3

athena = boto3.client("athena")
WORKGROUP = "etl-compaction"

def run(sql: str) -> str:
    """Submit a query and block until it terminates. Raises on failure."""
    qid = athena.start_query_execution(
        QueryString=sql, WorkGroup=WORKGROUP
    )["QueryExecutionId"]
    while True:
        ex = athena.get_query_execution(QueryExecutionId=qid)["QueryExecution"]
        state = ex["Status"]["State"]
        if state in ("SUCCEEDED", "FAILED", "CANCELLED"):
            break
        time.sleep(2)
    if state != "SUCCEEDED":
        raise RuntimeError(ex["Status"].get("StateChangeReason", state))
    # Log this. Bytes scanned per compaction run is your ROI metric.
    scanned = ex["Statistics"]["DataScannedInBytes"]
    print(f"{qid} scanned {scanned / 1e9:.2f} GB")
    return qid

def compact(day: str) -> None:
    tmp = f"web_events_tmp_{day.replace('-', '')}"
    run(f"DROP TABLE IF EXISTS events.{tmp}")
    run(CTAS_TEMPLATE.format(table=tmp, day=day))
    # Point the real table's partition at the compacted prefix.
    run(
        f"ALTER TABLE events.web_events PARTITION (dt='{day}') "
        f"SET LOCATION 's3://acme-lake-curated/events/web_events_v2/dt={day}/'"
    )

If you find yourself writing much more machinery than this, the honest answer is that you want table format transactions rather than prefix swapping. Athena supports Iceberg tables with OPTIMIZE and VACUUM, which is a genuinely better compaction story — see Delta Lake vs Iceberg for how to pick a format before you commit to one.

Workgroups are the guardrail, not a folder

Workgroups look like an organizational feature. They are actually the only enforcement point Athena gives you. Three settings matter and I set all three on every workgroup I create.

Per-query data scanned limit. A hard ceiling. A query that would exceed it is cancelled before it finishes billing. I set BI workgroups to 100 GB and ad-hoc analyst workgroups to 500 GB. This one setting has saved more money than every query rewrite I have ever done, because it turns a $400 mistake into a Slack message.

Per-workgroup limits and alerts. Aggregate thresholds that publish CloudWatch alarms. Use them to find the team, not to stop the query.

Enforced result location and engine version. Pin the output bucket so results do not scatter, and pin the engine version so an upgrade is a decision rather than a surprise regression.

{
  "Name": "bi-tableau",
  "Configuration": {
    "ResultConfiguration": {
      "OutputLocation": "s3://acme-athena-results/bi/",
      "EncryptionConfiguration": { "EncryptionOption": "SSE_S3" }
    },
    "EnforceWorkGroupConfiguration": true,
    "PublishCloudWatchMetricsEnabled": true,
    "BytesScannedCutoffPerQuery": 107374182400,
    "EngineVersion": { "SelectedEngineVersion": "Athena engine version 3" },
    "ResultConfigurationUpdates": { "RemoveOutputLocation": false }
  },
  "Tags": [{ "Key": "cost-center", "Value": "analytics" }]
}

One more thing about results: Athena writes every result set to S3 and never cleans them up. On an account running thousands of queries a day, the results bucket becomes a real storage line item. Put a 7-day lifecycle rule on it. Nobody has ever missed a query result from three weeks ago.

Reading query stats like a profiler

When a query is slow, do not guess. Athena’s execution details give you queue time, planning time, and execution time separately, and the distinction tells you what to fix.

• High planning time, low scan: partition metadata. Turn on projection.
• High queue time: concurrency limits. You are hitting the account quota for concurrent DML queries. Separate workgroups do not create separate quotas by default; provisioned capacity does.
• High execution, huge scan: layout. Partition, sort, or compact.
• High execution, small scan: usually a join blowing up. Athena is Trino under the hood, so the standard distributed join advice applies: put the big table on the probe side, and watch for the accidental cross join that SQL anti-patterns warns about.

Provisioned capacity deserves a mention because it changes the cost model entirely. You reserve DPUs — 24 minimum — and pay per DPU-hour instead of per byte. It stops being a bad deal only when you are scanning enormous volumes on a predictable schedule, or when queue time on the on-demand pool is breaking an SLA. For most teams the honest answer is that fixing layout is cheaper than buying capacity.

Pitfalls

SELECT * in a BI tool preview. Every preview pane, every “explore this table” button, scans every column. Give BI tools views with an explicit column list and a partition predicate baked in, not raw tables.

Partition projection with a hardcoded end date. The table works perfectly until the range expires, then returns zero rows with no error. Always end date ranges at NOW.

Compressed CSV or JSON in the query path. Gzipped JSON is not splittable, so one file is one reader regardless of size, and there is no column pruning at all. Convert to Parquet at ingest — Firehose can do it for you at delivery time.

Treating CREATE TABLE and the S3 prefix as independent. Dropping an Athena external table does not delete the data; dropping a CTAS table does not either, and then the next CTAS to that location fails because the prefix is not empty. Clean the prefix explicitly.

No byte limit on the ad-hoc workgroup. One analyst, one missing WHERE dt =, one afternoon, four figures. The limit costs nothing to set.

Using Athena as a transformation engine on a schedule. Athena is excellent for queries and acceptable for compaction. If you are chaining fifteen CTAS statements to build a warehouse, you want dbt with incremental models or an actual Spark job.

FAQ

Does partition projection replace the Glue Data Catalog?

No, only the partition entries. The table definition, columns, and types still live in the catalog. Projection means Athena stops calling GetPartitions and computes prefixes itself. Other engines reading the same table still need real partitions unless they support projection too, which is worth checking before you flip it on for a table Spark also reads.

What file size should I actually target?

128 MB to 512 MB per file for Parquet. Below that the per-file overhead dominates; above 1 GB you start losing parallelism because a single file is read by fewer workers. If your daily partition is under 128 MB total, the answer is not a smaller file target, it is a coarser partition.

Is Athena cheaper than Redshift Serverless for the same query?

For infrequent queries on well-partitioned data, almost always yes, because you pay nothing between queries. For dashboards hitting the same tables hundreds of times an hour, the byte charges add up fast and a warehouse with caching wins. I go through the crossover in detail in Redshift Serverless vs Snowflake.

Should I use Iceberg tables in Athena?

If you need row-level updates, deletes, or time travel, yes. Iceberg in Athena gives you MERGE, OPTIMIZE for compaction, and snapshot isolation without the prefix-swapping I described above. The tradeoff is metadata files that need their own maintenance, and slightly slower planning on very large tables.

Why is my query queued for 30 seconds?

Account-level concurrent query quotas. On-demand Athena shares a pool, and workgroups do not partition it. Request a quota increase first; it is often granted. If queueing is structural and tied to a latency SLA, provisioned capacity is the only real fix.

Do I get charged for failed queries?

Cancelled and failed queries are not charged for bytes scanned in most cases, but a query cancelled by a workgroup byte limit has still done the scanning up to that point. Treat the limit as a circuit breaker, not a free rollback.

What this means for your pipelines

Athena rewards work done upstream. Every hour you spend on file sizes, sort order, and partition granularity in the job that writes the data pays back across every query anyone runs against it, forever. Every hour you spend tuning the query itself pays back once. So when Athena is slow, my first question is never about the SQL — it is about what wrote the files.

Do these four things in order and you will capture most of the available win. Turn on partition projection for any table with more than a few thousand partitions. Set a per-query bytes-scanned limit on every workgroup today, before you optimize anything. Compact to 128 to 512 MB Parquet with a sort on your most common time predicate. Then, and only then, look at individual slow queries.

The deeper point is that Athena’s pricing model is honest in a way that cluster pricing is not. There is no idle waste to hide behind and no reserved capacity to amortize your mistakes. Every wasted byte shows up on the invoice with your name on it. That is uncomfortable for a month and then it makes you a better engineer, because it is the only cost model I know of where the fast thing and the cheap thing are always the same thing.