Athena: cost is a function of bytes scanned

Lesson 1 of 5 in Analyzing Data.

Athena's price list has one line on it. You pay $5 per terabyte of data scanned — the rate varies a little by Region — rounded up to the nearest megabyte, with a 10 MB minimum per query. Nothing else in that sentence is a variable you control except the first noun.

Read what that meter does not measure, because every wrong answer in this lesson comes from measuring the wrong thing. It does not measure how long the query ran. It does not measure how many rows came back. It does not measure how many columns you printed, or how many joins you wrote, or how clever the SQL was. A query that scans 4 TB and returns one row costs twenty dollars; a query that scans 40 MB and returns four million rows costs a fifth of a cent.

Four more facts about the meter are asked about directly. DDL is free — CREATE TABLE, ALTER TABLE, partition management, all free. A failed query is free. A cancelled query is charged for whatever it had already scanned when you stopped it. And a federated query additionally bills the Lambda connector it invokes, because that connector runs in your account.

So the whole of Athena cost engineering is one question asked three times: can we make this query open fewer objects, read fewer bytes from each one, and touch fewer columns? Those are partition pruning, columnar storage, and column pruning, and they are in that order on purpose — the first one is worth an order of magnitude and the third one is worth nothing at all without the second.

Those three are layout decisions, made where the data is written rather than where it is read — which is why the lab that takes them apart format by format and codec by codec lives back in the transformation lesson, at file formats, compression, and partitioning.

The lab below is for the question that comes after that work is done: the table is already partitioned, columnar and compressed — now what? There is a second set of levers with nothing to do with layout, every one of them specific to Athena, and one of the five does nothing at all.

Partition projection deserves its own paragraph, because it is routinely described as a cost feature and it is not one.

Pruning is what reduces bytes scanned, and Athena prunes every partitioned table — including the projected ones. What projection removes is the metadata round trip: the GetPartitions call that gets slower every month as dated prefixes accumulate, and the ALTER TABLE ADD PARTITION job or MSCK REPAIR TABLE that has to keep the metastore in step. For a log layout with one prefix per hour, that maintenance is the operational burden, and projection deletes it.

Its three sharp edges are all exam material:

Registered partition metadata is ignored for a projected table. If half your partitions are registered and half are projected, the registered ones are not consulted.
A query outside the declared range returns zero rows and no error. Declare 'projection.dt.range'='2024/01/01,NOW' and a query for 2023 succeeds, silently, emptily. That is a data-quality trap dressed as a performance feature.
It is Athena-only. Redshift Spectrum, Athena for Spark and EMR read the standard partition metadata, so a table that works beautifully in Athena returns nothing through Spectrum. SHOW PARTITIONS likewise does not list projected partitions.

And one sizing rule from the documentation worth remembering: if more than half your projected partitions are empty, traditional partitions are the better choice — Athena will dutifully project and try every one of them.

Three things that look like cost controls and are not, which between them account for most wasted effort in this area.

LIMIT is not a predicate. It bounds the result, not the scan. On a query that filters, aggregates or orders, the engine has to read the data to produce the answer at all, so LIMIT 10 costs byte for byte what the same query cost without it. The honest exception is narrow: a bare SELECT with no filter, no aggregate and no ordering can stop once it has enough rows, and then it reads one object or a few — an amount that depends on how the files are laid out, and is never zero.

An approximate function does not reduce bytes scanned. approx_distinct reads exactly the column count(distinct …) reads. What it buys is memory and runtime — AWS's own measurement on a 74 GB table is about 40% faster, at a documented 2.3% standard error — so it is money under an hourly capacity reservation and nothing at all under the per-byte meter.

A data usage control does not make anything cheaper. It caps a blast radius. The per-query control always cancels the offending query — the action is not configurable — the value may be set anywhere between 10 MB and 7 EB, and the cancelled query is still billed for what it had already scanned. Worth knowing for the clean-up as well: a cancelled or failed query may have left partial results and incomplete multipart uploads in the result prefix, which Athena does not remove, so that bucket wants a lifecycle rule.

Every lever that is sometimes offered as "reduce Athena cost", and what it actually does. Only the first three reduce bytes scanned.
Lever	Effect on bytes scanned	What it really buys	Watch out for
Partition + predicate	orders of magnitude lower	the query opens a fraction of the objects	a filter on a column inside the file prunes nothing; nor does a function wrapped around the partition column
Convert to Parquet	typically several times lower	compression, plus the ability to read one column	a one-off CTAS conversion cost, and a compaction decision about output file size
*Name columns, not ``**	lower on columnar, unchanged on row formats	reads only the chunks you asked for	offered as a fix for raw JSON tables, where it does nothing
Add `LIMIT`	no change on anything filtered, aggregated or ordered	a smaller result set, and nothing else	the commonest wrong intuition about Athena cost. The one exception is a bare `SELECT` with no filter, where the engine can stop early and read a single object
`approx_distinct`	no change — it reads the same column	less memory and a faster run, at a 2.3% standard error	a saving only if you are billed per DPU-hour on a reservation; on the per-byte meter the charge is identical
Partition projection	no change by itself	removes the `GetPartitions` call and all partition maintenance	out-of-range queries return zero rows silently; Spectrum and EMR ignore it
Result reuse	zero on a hit	repeated identical queries become free	the answer is as stale as the max age you set — up to 7 days
Data usage control	no change	caps exposure, or raises an alarm	a cancelled query is lost capability, and is still billed for what it scanned
Capacity reservation	meter replaced, not reduced	predictable DPU-hour cost and guaranteed concurrency	idle reserved capacity still bills — a steady workload wins, a bursty one loses

What to carry out of this lesson

$5 per TB scanned, rounded up to the megabyte, 10 MB minimum per query. DDL free, failed queries free, cancelled queries billed, federated queries also bill their Lambda connector.

Three layout levers in order: partition, columnar, columns. The third does nothing without the second.

LIMIT is not a cost control on anything filtered, aggregated or ordered, and approx_distinct is not one either — it reads the same column and saves runtime, not bytes.

Partition projection removes metadata work, not bytes. It makes the registered partitions irrelevant, returns zero rows outside its declared range without an error, and does not apply through Spectrum or EMR.

Result reuse trades freshness for cost by exactly the max age you set — default 60 minutes, maximum 7 days.

A usage control is not a cost reduction. It caps a blast radius. Reducing cost without losing capability means changing the physical layout.

Key terms: Bytes scanned, Partition pruning, Column pruning, Athena query result reuse, Athena capacity reservation

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.