Amazon Athena

Overview

Amazon Athena is an interactive query service that runs standard SQL directly on files stored in Amazon S3. You do not load the data into a database first. You describe the files as a table in a catalog, write a query, and get results in seconds or minutes. There are no servers or clusters to run.

Athena is billed mainly by the amount of data a query scans, so good file formats and partitioning make queries both faster and cheaper. It is a natural tool for ad hoc analysis, data checks and light transformation on a data lake. For teams that feed tabular foundation models, it is the fastest way to look at raw tables, profile columns, find missing values and cut samples before heavier jobs run. See /model/architecture for how those models read rows and columns.

What it is

Athena is a serverless query engine. It reads data in place from Amazon S3 and uses table definitions stored in the AWS Glue Data Catalog. The SQL engine is based on the Presto and Trino family for most queries, with other engine options for Spark notebooks and Apache Iceberg operations. See Trino for the open source engine whose ideas Athena shares.

What you can query

• CSV, TSV and JSON files.
• Columnar formats such as Parquet and ORC.
• Avro and text logs, including web and load balancer logs.
• Tables in open table formats such as Apache Iceberg, and, with support, Delta and Hudi.
• Data in other sources through federated query connectors.

What you get back

Query results are written to an S3 location you choose, and the console and APIs show them. The result file is a plain CSV, which makes it easy to pass to another tool.

An everyday analogy

Imagine a huge library where every book is a data file. A traditional database would require you to copy every book into its own building first. Athena is a quick reader who walks into the library, looks at the card catalog to find which shelves matter, reads only those pages and tells you the answer. You pay for the pages read. If the books are arranged by topic and printed in a compact way, the reader needs to read fewer pages.

Where it came from

Athena was announced by Amazon in 2016 and built on Presto, an open source distributed SQL engine created at a large social media company to query big data fast. The goal was to give AWS users a way to ask questions of data in S3 with no setup. Later releases added the Glue catalog integration, federated queries, support for table formats with row-level changes, notebooks with Apache Spark, and workgroups for cost and access control.

The wider history is the move from Hadoop clusters to separating storage and compute. Data stays in cheap object storage, and engines come and go over it. Athena is one of several such engines, along with Trino, Apache Spark and Amazon EMR. Specific version histories are best checked in the official documentation.

How it works

Step 1: Describe the data as a table

You create an external table that says: here is a set of files in this S3 prefix, with these columns and this format. The definition goes into the Glue Data Catalog. No data is copied. Analogy: you write a catalog card for a shelf of books.

Step 2: Write SQL

You write ANSI-style SQL with joins, aggregates, window functions and common table expressions. Functions for dates, strings, arrays, maps and JSON are available.

Step 3: The engine plans the query

Athena reads the table definition, works out which partitions match your filters and which files it must read, and splits the work into tasks that run in parallel across a managed fleet.

Step 4: Data is scanned

Workers read the needed files from S3. With columnar formats they read only the columns you ask for and can skip blocks using file statistics. With CSV they must read whole rows.

Step 5: Results are gathered

Partial results are combined, sorted or aggregated and written to the result location. You see the data scanned and the run time.

Step 6: Cost is counted

The charge depends on bytes scanned, with a small minimum per query. Failed queries are not charged for the data they did not complete, but other rules exist, so check the documentation. Some features, such as provisioned capacity, change the pricing model. This article gives no prices.

Why partitioning and formats matter

Suppose a table holds ten years of events as CSV, 5 TB in total. A query for one day must scan all of it unless the data is partitioned by date. If the data is stored as Parquet and partitioned by day, the same query may scan a tiny fraction. The result is the same, but the cost and time can differ by a large factor.

Partition projection

For tables with predictable partitions such as dates, partition projection lets Athena compute partition locations from rules in the table properties. This avoids slow partition listing in the catalog.

Key concepts and terms

A worked example

Suppose a team has raw order files in CSV. They want to know the quality of a table and build a smaller Parquet table for later use. All names are fake.

Step 1: Define the raw table

CREATE EXTERNAL TABLE IF NOT EXISTS raw_db.orders_csv (
  order_id     string,
  customer_id  string,
  product_id   string,
  amount       double,
  order_ts     string,
  channel      string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://example-raw-bucket/orders/'
TBLPROPERTIES ('skip.header.line.count'='1');

Step 2: Profile the columns

SELECT
  count(*)                                    AS row_count,
  count(DISTINCT customer_id)                 AS customers,
  count_if(amount IS NULL)                    AS null_amount,
  count_if(channel IS NULL OR channel = '')   AS blank_channel,
  min(from_iso8601_timestamp(order_ts))       AS first_order,
  max(from_iso8601_timestamp(order_ts))       AS last_order,
  approx_percentile(amount, 0.5)              AS median_amount,
  approx_percentile(amount, 0.99)             AS p99_amount
FROM raw_db.orders_csv;

A single scan answers many questions about missing values, ranges and outliers.

Step 3: Write a clean partitioned Parquet table

CREATE TABLE curated_db.orders_clean
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  external_location = 's3://example-curated-bucket/orders_clean/',
  partitioned_by = ARRAY['order_date']
) AS
SELECT
  order_id,
  customer_id,
  product_id,
  amount,
  channel,
  from_iso8601_timestamp(order_ts)               AS order_ts,
  CAST(date(from_iso8601_timestamp(order_ts)) AS varchar) AS order_date
FROM raw_db.orders_csv
WHERE order_id IS NOT NULL;

Partition columns must come last in the select list. This is a frequent source of errors.

Step 4: Use it

SELECT customer_id, count(*) AS n, sum(amount) AS total
FROM curated_db.orders_clean
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

Because the table is partitioned by date and stored in Parquet, this query reads only January and only three columns.

Step 5: Run it from Python

import boto3, time

athena = boto3.client("athena", region_name="us-east-1")
resp = athena.start_query_execution(
    QueryString="SELECT count(*) FROM curated_db.orders_clean",
    QueryExecutionContext={"Database": "curated_db"},
    ResultConfiguration={"OutputLocation": "s3://example-results-bucket/athena/"},
    WorkGroup="analytics",
)
qid = resp["QueryExecutionId"]
while True:
    state = athena.get_query_execution(QueryExecutionId=qid)["QueryExecution"]["Status"]["State"]
    if state in ("SUCCEEDED", "FAILED", "CANCELLED"):
        break
    time.sleep(1)
print(state)

A numeric walk-through of cost

Say the raw CSV table is 2,000 GB and a query scans it all. The curated Parquet table for the same data is about 400 GB because of compression and column storage. A query on one month of data that touches 3 of 6 columns might scan about 400 GB divided by 120 months, then half of that for columns, or roughly 1.7 GB. So the scan falls from 2,000 GB to under 2 GB, a reduction of more than a thousand times. These numbers are illustrative, but the pattern holds in practice.

More SQL patterns worth knowing

Window functions let you rank and compare within groups without joins.

SELECT
  customer_id,
  order_ts,
  amount,
  row_number() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) AS recency_rank,
  avg(amount)  OVER (PARTITION BY customer_id ORDER BY order_ts
                     ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)         AS rolling_avg_5
FROM curated_db.orders_clean
WHERE order_date >= '2026-01-01';

Common table expressions keep long queries readable.

WITH monthly AS (
  SELECT customer_id, date_trunc('month', order_ts) AS m, sum(amount) AS spend
  FROM curated_db.orders_clean
  GROUP BY 1, 2
)
SELECT customer_id, avg(spend) AS avg_monthly_spend
FROM monthly
GROUP BY customer_id;

Arrays and JSON help with semi-structured fields.

SELECT json_extract_scalar(payload, '$.device.os') AS os, count(*) AS n
FROM raw_db.events_json
GROUP BY 1
ORDER BY n DESC;

Working with Iceberg tables

Athena can create and query Iceberg tables, which add row-level changes and snapshots to files in S3.

CREATE TABLE curated_db.customers_iceberg (
  customer_id string,
  segment     string,
  updated_at  timestamp
)
PARTITIONED BY (bucket(16, customer_id))
LOCATION 's3://example-curated-bucket/customers_iceberg/'
TBLPROPERTIES ('table_type'='ICEBERG');

MERGE INTO curated_db.customers_iceberg t
USING staging_db.customer_updates s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET segment = s.segment, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, segment, updated_at)
  VALUES (s.customer_id, s.segment, s.updated_at);

Time travel lets you read an older version, which helps reproduce a training table exactly as it was.

Strengths and limitations

Strengths

• No infrastructure. Start querying in minutes.
• Standard SQL that analysts already know.
• Works on data in place, in many formats.
• Pay for scanned data, so rare queries cost little.
• Integrates with the Glue catalog, IAM and Lake Formation controls.
• Can write results back as new tables with CTAS and UNLOAD.
• Good for log analysis, spot checks and prototype transformations.

Limitations

• Not a low latency serving database. Queries take seconds, not milliseconds.
• Performance depends on file layout. Many small files hurt.
• Heavy joins on very large tables can be slow or hit limits.
• Query concurrency and quotas apply.
• Limited control over the engine's resources compared with a self-run cluster, unless provisioned capacity is used.
• Not suited to iterative machine learning code. Use Spark or Python for that.
• Cost can surprise you if a query accidentally scans a huge unpartitioned table.

How it compares with alternatives

Rule of thumb: Athena for questions, Spark for pipelines, a warehouse or columnar database for dashboards that need steady low latency.

Choosing between options in plain steps

1. If the question is SQL and the data is already in S3, start with Athena.
2. If the same query runs all day for many dashboard users, consider a warehouse or a columnar database.
3. If you need Python code, machine learning libraries or multi-step pipelines, use Spark through EMR Serverless or Amazon EMR.
4. If you need full control of the engine and its connectors, run Trino yourself.

How the catalog and Lake Formation fit in

The catalog stores what a table looks like. AWS Lake Formation adds who may see which tables, columns and rows. Athena checks both before reading. This separation means one set of rules can protect the same table for Athena, Spark and other engines that respect the catalog.

Role in tabular foundation models

Tabular foundation models learn from raw tables, with rows and columns of mixed types, missing values, sometimes no clear column names, and predictions made from examples supplied in context. They are pre-trained on large sets of synthetic or real tables and can support imputation, anomaly detection and semantic or sector classification. See /model/research and /model/benchmarks.

Athena sits on the data side of that story. It does not host the model, but it is where many teams explore and shape the tables the model will see.

Profiling before modelling

Before giving a table to a model, you want to know its shape. One SQL pass can report row counts, distinct counts, null counts per column, min and max values and approximate percentiles. This tells you which columns are mostly missing, which look like identifiers and which have extreme outliers. A tabular model can handle blanks, but you still need to know how many there are.

Cutting context and evaluation sets

In-context learning needs a set of labelled example rows and a set of rows to predict. SQL makes sampling simple:

-- reproducible sample of about 1 percent using a hash of the key
SELECT *
FROM curated_db.orders_clean
WHERE (from_big_endian_64(xxhash64(to_utf8(customer_id))) % 100) = 0;

Hash-based sampling keeps the same rows across runs, which helps when comparing models. For a split, hash on the entity key so that one customer never appears in both context and test sets, which avoids leakage.

Finding anomalies and checking imputation

Simple SQL can flag rows beyond a percentile, duplicate keys or impossible values such as negative ages. These flags serve as a baseline against which a model's anomaly scores can be compared. For imputation, SQL can compute simple fills such as column medians or last observed values, so that you can measure whether a model's fills are better than the baseline. See Schema drift detection for watching when inputs change.

Semantic and sector classification inputs

To classify a column's meaning or a company's sector, models often use samples of values. A query can extract the top values and counts per column, giving a compact summary to pass downstream. Combined with the Data catalog, the result can be stored as column metadata.

Reading model outputs

Predictions written to S3 as Parquet can be exposed as a table, so analysts can join scores to business data with SQL. That closes the loop between a model and the people who use its results.

Governance

Access can be controlled through AWS Lake Formation permissions, workgroups and IAM. Column masking and row filters keep sensitive fields away from users who do not need them. Lineage tools such as Data lineage help track which tables fed a model.

Features that make rows easier for a model

SQL is good at turning event tables into one row per entity. Here is a pattern for building simple entity features.

SELECT
  customer_id,
  count(*)                                         AS orders_90d,
  sum(amount)                                      AS spend_90d,
  max(order_ts)                                    AS last_order_ts,
  date_diff('day', max(order_ts), current_timestamp) AS days_since_last,
  count_if(channel = 'web') * 1.0 / count(*)       AS web_share
FROM curated_db.orders_clean
WHERE order_ts >= date_add('day', -90, current_timestamp)
GROUP BY customer_id;

A tabular model can take columns like these directly. It does not need them scaled or one-hot encoded, though clean types matter. Keep missing values as nulls rather than replacing them with magic numbers such as -1, because the model may read a magic number as a real value.

Checking for leakage

A leak happens when a feature contains information from the future of the label. SQL helps you guard against it. Always compute features using only rows with timestamps before the prediction date, and write that cutoff into the query text so it is auditable.

Versioned training tables

With Iceberg snapshots or dated partitions, you can name the exact table version used by a model run. Store the snapshot id with the model's metadata. Later, if a result is questioned, you can rebuild the same input.

Practical guidance and best practices

Store data well

• Use Parquet or ORC with compression for anything queried often.
• Keep files at a healthy size, often tens to hundreds of megabytes. Compact small files.
• Partition by columns used in filters, such as date, but avoid thousands of tiny partitions.
• Use partition projection for date-like partitions.
• Consider Apache Iceberg tables for row-level updates, time travel and schema changes. See Iceberg compaction for maintenance.

Write efficient SQL

• Select only the columns you need. Avoid SELECT * on wide tables.
• Always filter on partition columns when you can.
• Put the larger table on the left side of a join, as a general guideline, and filter before joining.
• Use approximate functions such as approx_distinct when exact counts are not required.
• Use LIMIT while exploring, remembering that LIMIT does not always reduce the data scanned.
• Use CTAS to materialise a heavy intermediate result once.

Control cost and access

• Put teams in separate workgroups with data scanned limits per query.
• Set result location retention so old results are removed.
• Turn on result reuse for repeated dashboards.
• Tag workgroups for cost allocation.

Keep it reliable

• Handle query failures by retrying idempotent work.
• Watch for schema changes in raw files. See Schema evolution.
• Test queries on a small partition before running on the full table.
• Document table owners in the catalog.

Move data in

Raw files arrive in S3 by many routes, including AWS DataSync, streaming from Amazon MSK, and transformation jobs. Athena then reads them once they are in place.

Reading the query plan

Use EXPLAIN to see how Athena will run a query. Look for the scan steps, which show the partitions and columns read, and for join steps, which show how data moves. If a filter does not appear at the scan stage, the engine may be reading more than you expected, often because a function was applied to the partition column.

EXPLAIN
SELECT count(*) FROM curated_db.orders_clean
WHERE order_date = '2026-01-15';

Troubleshooting checklist

Common mistakes

• Querying huge CSV with no partitions. Every query scans everything.
• Too many small files. Query planning slows and cost rises. Compact them.
• SELECT * everywhere. Columnar formats only help if you ask for fewer columns.
• Wrong partition column order in CTAS. Partition columns must be last.
• Forgetting to repair or add partitions. New folders are invisible until registered or projected.
• Assuming types are enforced. External tables parse files at read time. Bad values may become nulls or errors.
• Mixing schemas in one prefix. Files with different columns in one folder cause confusing results.
• Treating Athena as a transactional store. Use a database for that.
• Letting results pile up. The result bucket grows without a lifecycle rule.

• Sampling with random functions and expecting repeatability. Use hash-based sampling instead.

• Wrapping a partition column in a function in the filter. It can stop partition pruning.

• Ignoring encoding. Files in a different character set show odd symbols in text columns.
• Storing money as double without care. Rounding can drift. Consider decimal types for exact sums.

Frequently asked questions

What is Amazon Athena used for?

Athena is used to run SQL queries on files in S3 without managing servers. Common uses are log analysis, data exploration, checking data quality, building reports and creating smaller curated tables from raw files. It is best for questions and light transformation rather than for serving applications that need millisecond responses.

How does Athena pricing work in general terms?

Athena is mostly charged by the amount of data a query scans, with other models such as reserved capacity available for steady use. That is why columnar formats, compression and partitioning matter so much. Check the official pricing page for current details, since this article does not quote amounts.

Do I need to load data into Athena?

No. Athena queries files where they sit in S3. You create a table definition that points to the files and describes the columns. The definition is stored in the Glue Data Catalog, and the data itself is not copied or moved.

Which file format is best for Athena?

Columnar formats such as Parquet or ORC are usually best because Athena reads only the needed columns and can skip blocks using statistics. Compression further reduces the data scanned. CSV and JSON work but are slower and costlier at scale.

What is the difference between Athena and Trino?

Athena is a managed service that uses engines from the Presto and Trino family and hides the cluster. Trino is open source software you run yourself. Athena is simpler to start with. Trino offers more control and a wider range of connectors if you are willing to operate it.

Can Athena update or delete rows?

Standard external tables over plain files are append-style. Tables in formats such as Apache Iceberg support inserts, updates, deletes and merges with ACID guarantees, and Athena can work with them. If you need row-level changes, use a table format that supports them.

How can Athena help with machine learning on tables?

It helps with data preparation. You can profile columns, count missing values, build features with SQL, create reproducible samples and join model predictions back to business data. The model itself runs elsewhere, but Athena makes the data work faster and easier to inspect.

Why is my Athena query slow or expensive?

The usual causes are scanning an unpartitioned table, reading many small files, using CSV instead of Parquet, selecting all columns, or running a heavy join on skewed data. Look at the data scanned figure for the query, then improve layout and filters.

How do I secure data queried through Athena?

Use IAM policies for access to the service and to S3, set workgroup controls, and use Lake Formation for table, column and row level permissions. Encrypt data at rest and results as well. Keep sensitive fields masked or tokenized in curated tables.

Can I call Athena from my own code?

Yes. The service has an API and SDKs. You start a query, poll its state and then read the result from S3 or through the results API. Many orchestration tools, including Apache Airflow, have operators for this.

Glossary

• Ad hoc query: a one-off question asked of data.
• ANSI SQL: the standard form of SQL.
• CTAS: create a table from the result of a query.
• Columnar format: a file layout that stores values by column.
• Data scanned: bytes read, used for billing and a measure of efficiency.
• External table: a table definition over files stored outside a database.
• Federated query: a query across data sources through connectors.
• Glue Data Catalog: a shared store of table definitions.
• Partition: a physical slice of a table, usually by date or category.
• Partition pruning: skipping partitions that do not match a filter.
• Presto: an open source distributed SQL engine that influenced Athena.
• SerDe: code that parses records of a given file type.
• Workgroup: a control boundary for users, limits and cost.

Summary

Amazon Athena lets you run SQL directly on files in S3 with no servers to manage. You define tables in the Glue Data Catalog, write queries, and pay mainly for the data scanned. Storing data as partitioned, compressed Parquet makes queries fast and cheap, while unpartitioned CSV makes them slow and costly.

For tabular foundation models, Athena is the quick inspection and shaping layer. It profiles columns and missing values, builds reproducible samples and splits, produces simple baselines for imputation and anomaly detection, and exposes model outputs back to analysts as tables. Pair it with Spark for heavy pipelines, an open table format for updates, and strong governance for sensitive data.

Related

It belongs to AWS data analytics in my stack, under Data engineering and lakehouse. Others in the same group: