Amazon Athena

Querying without building anything

Data arrives in object storage constantly. Application logs, exports from other systems, files from partners, output from processing jobs.

Asking a question of it conventionally means building something first. A pipeline reads the files, transforms them and loads them into a warehouse. Only then can anyone query.

That is reasonable for data queried every day. It is disproportionate for a question somebody wants to answer once.

Athena removes the intermediate step. A table is defined describing where the files are and what they look like, and SQL runs against them where they sit.

There is no cluster to provision, nothing to start, and no charge between queries.

What a table actually is here

The table is a definition rather than storage.

A catalogue entry records the location of the files, the format they are in, and the columns they contain. When a query runs, the service reads the files at that location and interprets them according to that definition.

Two consequences follow.

Creating a table is instantaneous, because nothing is being copied. It is a description of files that already exist.

And the definition can be wrong. If it says a column is a number and the files contain text, queries fail or produce unexpected results. The catalogue describes the data; it does not verify it.

Where cost comes from

Billing based on data scanned is the characteristic that most shapes how Athena is used well.

A query reading a hundred gigabytes costs a hundred times one reading a single gigabyte. Since the query text may be identical, with only the layout of the data differing, this puts table design directly into the cost equation.

Two decisions dominate.

File format. Data stored as text, whether CSV or JSON, must be read in full to extract anything, because the columns are interleaved within each row. A query touching three columns of a fifty column dataset still reads all fifty.

Columnar formats store each column separately. That same query reads three columns. The reduction is frequently an order of magnitude, and it applies to both time and cost.

Partitioning. Files organised under prefixes reflecting a column, most often a date, allow a query filtering on that column to read only the relevant prefixes.

Without partitioning, a query for a single day scans the entire table, including years of data nobody asked about.

These two together account for most of the difference between an Athena deployment that costs very little and one that produces alarming bills. Neither is difficult; both are decided when data is written rather than when it is queried.

What it suits

Log investigation. Something went wrong and the logs are in storage. Querying them with SQL beats downloading and searching by hand, and no infrastructure is needed for an occasional need.

Exploring new data. Files have landed and nobody knows what is in them. A few queries answer that before any decision about loading.

Occasional analysis. A report produced twice a year does not justify maintaining data in a warehouse.

Archived data. History retained in storage stays queryable without being restored anywhere.

What it does not suit is data queried constantly by many users. Repeatedly reading files from general purpose storage is slower than reading from storage organised for analytical querying, and at that frequency the scanning costs accumulate. Data used that heavily generally belongs in a warehouse.

Who uses it

Athena is used by analysts, data engineers and operations teams on AWS. It appears in exploratory work, in incident investigation and as the query layer over lakes where a warehouse would be more than the requirement justifies.

Points to consider

Athena is an AWS service and the official documentation is the reference for supported formats, functions, limits and pricing.

Cost control deserves setting up deliberately. Because charges follow scanning, a badly written query against a large unpartitioned table can be expensive in seconds. AWS provides controls for limiting this, and configuring them before opening access to a team is sensible.

Performance depends on how data is laid out. Very many small files perform poorly because of per file overhead, which is why processing jobs commonly combine them.

The catalogue has to reflect the data. Schema changes in the underlying files without corresponding catalogue updates produce failures or wrong results.

Getting started

The official documentation covers table definitions, supported formats, partitioning and cost controls. Querying the same dataset stored as text and then as a columnar format, and comparing the data scanned in each case, demonstrates the cost model more convincingly than the pricing page.