A FinOps team keeps three years of billing data in Amazon S3 as gzip-compressed CSV, 1.2 TB of it. Their trend query uses 4 of the table’s 50 columns, and Athena cancels it every time at the workgroup’s 50 GB per-query limit. The query is fine. The files are the wrong shape: to find four columns in a text file, Athena reads every byte of it, and it bills you for every byte it reads.
What Athena charges for
The pricing page puts it plainly: “You are charged for the number of bytes scanned per query, rounded up to the nearest megabyte, with a 10 MB minimum per query.” On per-query billing the price is set per TB of data scanned; with Capacity Reservations you pay for DPU-hours instead and there is no charge for data scanned. Two more lines matter for budgets. There is no charge for DDL statements such as CREATE, ALTER or DROP TABLE, or for failed queries. But “Canceled queries are billed for the total amount of data scanned when the query is canceled.”
The Athena User Guide adds the detail that makes compression pay: “you pay for the number of bytes scanned before decompression.”
The pricing page’s own example
AWS’s worked example shows the levers clearly. Take a table of four equally sized columns, stored as one uncompressed text file of 3 TB, and query a single column:
| Layout | Size in S3 | Scanned for one column | Cost at the example’s $5 per TB |
|---|---|---|---|
| Uncompressed text | 3 TB | 3 TB | $15 |
| Text, gzip | 1 TB | 1 TB | $5 |
| Parquet, compressed | 1 TB | 0.25 TB | $1.25 |
In the first two rows Athena scans the entire file: a row-oriented text file gives it no way to skip columns, which the pricing page puts as “because text formats can’t be split”. Compression only makes the whole file three times smaller, so you pay a third. Parquet lets Athena read “only the column that is relevant for the query”, a quarter of the file.
Why columnar formats read less
Parquet and ORC “organize data by column instead of by row”. The guide lists what follows: “Only the columns needed for the query are loaded”, values of one column stored together compress well, and files carry metadata, such as minimum and maximum values per block, that lets the engine skip data it does not need. Both formats also support splitting, so Athena can spread the reading across several readers.
Apply that to the billing table. As gzip CSV it is read whole, 1.2 TB for any query: $6 at the example’s rate, if the limit let it finish. In the stage this article is built on, the same data as Parquet with Snappy is 360 GB, and the trend’s four columns hold about 9% of those bytes. The query scans about 32 GB, inside the limit, for about 16 cents at the same rate.
Raising the limit is not a fix. The per-query control “specifies the total amount of data scanned per query. If any query that runs in the workgroup exceeds the limit, it is canceled.” It protects the budget; it does not make a query read less. Workgroup-wide limits are different again. They watch the total scanned per hour or per day and can notify an administrator through Amazon SNS, but “Queries are not automatically canceled when a threshold is reached.”
File size and partitions
Columnar files can be too small. The guide warns that “Datasets that consist of many small files result in poor overall query performance”, because Athena lists and requests every file, and that this is “particularly true with columnar file formats”. Parquet row groups default to 128 MB and ORC stripes to 64 MB, and the guide does not recommend going below those defaults. A table written in 8 MB files hands Athena tens of thousands of objects to open. The best time to combine small files, the guide says, is before they are written.
Partitioning is the third lever. On a table partitioned by date, “When you filter on partition key columns, only data from matching partitions is read.” It helps only queries that filter on the partition key. A three-year trend with no filter still reads every partition.
CTAS to convert, UNLOAD to export
You can convert a table with Athena itself. A CREATE TABLE AS SELECT (CTAS) query that specifies Parquet or ORC as its storage format converts the data in one step, and AWS Glue jobs can do the same.
A plain SELECT writes its results to S3 as CSV, which the guide calls “the only output format supported by the Athena SELECT command”. So a consumer that reads only CSV can take that output while the lake stays columnar. UNLOAD is for the other direction: it writes the results of a SELECT to S3 in a non-CSV format (PARQUET, ORC, AVRO, JSON or TEXTFILE) without creating a table. Two details matter: Athena uses gzip for JSON and TEXTFILE output, and the default field delimiter for text is the octal character \001, so set field_delimiter = ',' when the reader expects commas.
What the exam guides ask
The DEA-C01 guide asks you to “Transform data between formats (for example, from .csv to Apache Parquet)” (Skill 1.2.6), “Describe best practices for indexing, partitioning strategies, compression, and other data optimization techniques” (Skill 2.4.5), “Optimize costs while processing data” (Skill 1.2.4) and “Query data (for example, Amazon Athena)” (Skill 3.1.7). The SAA-C03 guide covers it under “Determine high-performing data ingestion and transformation solutions” (Task 3.5), and the MLA-C02 guide asks you to “Ingest from and write by using appropriate data formats (for example, Apache Parquet, JSON, CSV, ORC) based on data access patterns” (Skill 1.1.5).
A scenario on this topic usually describes text data in S3, a query that touches a few columns, and a cost or limit problem. The distractors change everything except the bytes read:
- Raise the per-query limit. The query finishes and is billed for every byte.
- Recompress the CSV harder. Smaller, and still a text file read whole.
- Switch to JSON. Another text format, read whole like CSV.
- Keep the whole lake in CSV because one consumer needs CSV. Every other query pays for that; export the rows that consumer needs instead.
The answer that works is columnar and compressed, in files that are not too small, with partitions on the columns queries filter by.
Play it
The free stage below opens Brightwater Freight’s FinOps lake in the Lake Tuner. silver.focus holds 36 months of FOCUS billing data as gzip CSV in 8 MB files, and the analysts’ trend query is canceled at their workgroup’s 50 GB limit. You choose the format, compression and target file size for silver, which must also cost under $10 a month to store, and the format Athena writes the auditors’ monthly extract in, because their import tool reads only CSV. The meters show the bytes each query scans as you change a setting, and a failed check shows the sum that broke its limit.