AWS Athena 101: serverless SQL on S3 explained

What Athena is, in three parts

Athena is easier to reason about as three separate pieces that happen to be used together.

• Amazon S3 holds the data. CSV, JSON, Parquet or ORC files under a bucket prefix. Athena never copies them.
• The AWS Glue Data Catalog holds the table definitions. A table is metadata: column names and types, the file format, and the S3 location. Dropping a table removes the definition, not the files.
• Athena is the engine. It takes a SQL statement, looks up the table in the catalog, reads the matching files from S3 and returns the result. Its SQL dialect is Trino’s.

Because the engine and the storage are separate, the same files can be queried by other tools, and the same engine can be pointed at new files by adding a table. What Athena does not have is anything that stays warm: no always-on compute, no indexes, no cache you size.

What a query costs

Athena’s default pricing is by data scanned. On the Athena pricing page, checked in October 2026, SQL queries cost $5 per terabyte scanned, rounded up to the nearest megabyte, with a 10 MB minimum per query.

Three things follow from that.

A query that reads less costs less. Scanning one day of a table instead of three years is the difference between cents and dollars, for the same answer.

Small queries are not free. The 10 MB minimum means a dashboard that fires thousands of tiny queries a day still adds up.

The result does not set the price. LIMIT 10 on an unpartitioned text table can still read the whole table to find ten rows.

You also pay standard S3 rates for storage and requests, and Data Catalog rates for the metadata. For steady, heavy workloads AWS sells provisioned capacity at $0.30 per DPU-hour as an alternative to per-scan pricing.

Create a table over files in S3

Suppose order events land in S3 as Parquet files, one folder per day:

s3://my-bucket/orders/dt=2026-09-29/part-000.parquet
s3://my-bucket/orders/dt=2026-09-30/part-000.parquet

The dt=... folder names are Hive-style partitions. This DDL makes them a table:

CREATE EXTERNAL TABLE orders (
  order_id string,
  customer_id string,
  product_id string,
  quantity int,
  price double
)
PARTITIONED BY (dt string)
STORED AS PARQUET
LOCATION 's3://my-bucket/orders/';

EXTERNAL means Athena does not own the files. PARTITIONED BY declares dt as a column that comes from the folder name, not from inside the files.

Tell Athena which partitions exist

A partitioned table returns nothing until its partitions are registered. For Hive-style folders, one statement finds them:

MSCK REPAIR TABLE orders;

It has to be run again whenever new folders arrive. If your paths are not in key=value form, add each partition yourself with ALTER TABLE orders ADD PARTITION (dt = '2026-09-30') LOCATION '...'.

For tables that gain a partition every day, partition projection removes that chore. You describe the pattern once in the table properties and Athena works out the partitions at query time:

ALTER TABLE orders SET TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.dt.type' = 'date',
  'projection.dt.format' = 'yyyy-MM-dd',
  'projection.dt.range' = '2024-01-01,NOW',
  'storage.location.template' =
    's3://my-bucket/orders/dt=${dt}/'
);

Query it, and watch what gets scanned

With the table in place, the partition column behaves like any other column, with one difference: filtering on it decides which folders are read at all.

SELECT
  product_id,
  SUM(quantity) AS units,
  SUM(price * quantity) AS revenue
FROM orders
WHERE dt >= '2026-09-01'
  AND dt < '2026-10-01'
GROUP BY product_id
ORDER BY revenue DESC;

AWS’s documentation states it plainly: if you specify the partition in the WHERE clause, Athena scans the data only from that partition. Leave the filter out and the same query reads every day you have ever stored.

Athena reports data scanned for every query, in the console and in the query’s statistics. Make it a habit to read that number before the result. It is the bill.

Four patterns worth knowing

The queries below run against a small e-commerce dataset in Athena: sessions, orders, customers and products in a database called ecommerce_demo. Each was validated on Athena before it was published here.

Join and aggregate across tables

SELECT
  s.source_medium,
  COUNT(DISTINCT s.session_id) AS sessions,
  COUNT(DISTINCT o.order_id) AS orders,
  SUM(p.price * o.quantity) AS revenue
FROM ecommerce_demo.sessions s
LEFT JOIN ecommerce_demo.orders o
  ON s.session_id = o.session_id
LEFT JOIN ecommerce_demo.products p
  ON o.product_id = p.product_id
WHERE s.date >=
  date_add('day', -30, current_date)
GROUP BY s.source_medium
ORDER BY revenue DESC;

Standard joins work as you expect. Date arithmetic uses Trino functions: date_add, date_diff and date_trunc, with the unit as a quoted string.

Select only the columns you need

SELECT date, source_medium, device_category
FROM ecommerce_demo.sessions
WHERE date >= DATE '2026-01-01';

On Parquet or ORC this reads three columns, not the whole row. On CSV or JSON it reads everything regardless, which is the strongest argument for columnar files.

Count distinct values cheaply

SELECT
  date_trunc('month', s.date) AS month,
  approx_distinct(s.customer_id) AS customers
FROM ecommerce_demo.sessions s
GROUP BY 1
ORDER BY 1;

approx_distinct returns an estimate using far less memory than COUNT(DISTINCT ...). For a trend line over a large table it is usually the right trade.

Rank within groups

SELECT category, product_name, revenue
FROM (
  SELECT
    p.category,
    p.product_name,
    SUM(p.price * o.quantity) AS revenue,
    ROW_NUMBER() OVER (
      PARTITION BY p.category
      ORDER BY SUM(p.price * o.quantity) DESC
    ) AS rn
  FROM ecommerce_demo.orders o
  JOIN ecommerce_demo.products p
    ON o.product_id = p.product_id
  GROUP BY p.category, p.product_name
)
WHERE rn <= 3;

Window functions are fully supported, so “top three products per category” needs no self-join.

Convert raw files to Parquet with CTAS

If your data arrives as CSV or JSON, the cheapest improvement is to rewrite it once as partitioned Parquet. CREATE TABLE AS SELECT does it in one statement:

CREATE TABLE orders_parquet
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  external_location =
    's3://my-bucket/orders_parquet/',
  partitioned_by = ARRAY['dt']
) AS
SELECT
  order_id,
  customer_id,
  product_id,
  quantity,
  price,
  dt
FROM orders_raw;

Two rules from the CTAS documentation catch people out. Partition columns go last in the SELECT list. And one CTAS statement can write at most 100 partitions, so a long history is converted in batches, with INSERT INTO for the rest.

What makes Athena fast and cheap

Every tuning technique in Athena is a way of reading fewer bytes.

• Partition on what you filter by. Almost always a date. Add a second key only if most queries filter on it too.
• Store columnar, compressed files. Parquet or ORC, so a query reads only the columns it names.
• Avoid many small files. Thousands of kilobyte-sized objects cost more in S3 requests and planning than in scanning. Compact them.
• Filter early and name your columns. SELECT * over a wide table defeats the columnar format.
• Use partition projection for tables with many partitions, so the engine does not have to look each one up.

Where Athena fits, and where it does not

Athena is a strong choice when the data already lives in S3 and the work arrives in bursts: log analysis, exploring exports from other systems, validating a pipeline, occasional reporting. You pay nothing between queries.

It is a weaker choice when many people need fast answers all day. Athena has per-account quotas on concurrent queries, and unless a query can reuse a recent result, each person who runs it pays for the scan again. Updates and deletes need Iceberg tables; plain external tables are append-only.

Compared with the alternatives:

• Amazon Redshift keeps compute available, so it suits steady dashboard traffic and heavy joins. You pay for that availability whether or not queries run.
• BigQuery is also serverless and also has a scan-based price, but stores data in its own managed format by default.
• Snowflake and Databricks bill for compute time, not bytes read. Snowflake pricing and Databricks pricing cover how those bills are built.

The data warehouse comparison puts the five side by side.

From queries to reports people read

A query that an analyst runs once is cheap in Athena. The same query behind a dashboard that ten people open is ten scans, and on a scan-priced engine that difference is the whole bill. The answer is the same as on any warehouse: write the query once, run it on a schedule, and let people read the result.

That is the job OWOX Data Marts does on top of Athena. It does not transform your data or change what Athena charges. You connect Athena as a storage with a region, an output bucket for query results and an IAM access key, and the analyst’s SQL becomes a Data Mart: one named, validated definition.

Each Data Mart can then be delivered as a report to Google Sheets, Data Studio, Slack or email. A Google Sheets report writes the rows into the sheet when it runs, so people opening the sheet do not trigger a scan. One scheduled run a day is one scan a day, however many readers there are.

Most of the Data Marts in that list are connectors: they load ad platform and Shopify data into tables in the Athena storage on a schedule, and those tables are queried with the same SQL shown above. The Athena to Google Sheets connector page shows the destination side, and Data Mart management explains how definitions are owned and shared.

Amazon AthenaHow to Connect AWS Athena to Google Sheets Without CSV ExportsOctober 28, 2026→Amazon AthenaHow to Connect AWS Athena to Data StudioOctober 20, 2026→Amazon AthenaAWS Redshift vs Athena: Key Differences ExplainedOctober 21, 2026→

See all articles →