Amazon Athena

Serverless SQL over data in S3. No servers, no clusters — point Athena at S3 files, run SQL, pay per data scanned. Built on Trino/Presto. For a backend role the interview points are partition pruning, file formats, and the cost model.

The model

data sits in S3 (Parquet / JSON / CSV / ORC)
        │
   Glue Data Catalog holds the schema (table definitions, partitions)
        │
   Athena runs SQL against it — serverless, pay per TB scanned

You don’t load data into Athena. The data stays in S3; Athena queries it in place. The Glue Data Catalog stores the table schemas (it’s the shared Hive metastore — Athena, Redshift Spectrum, EMR, and Glue ETL all read it).

CREATE EXTERNAL TABLE orders (
  order_id string, user_id bigint, amount double, status string
)
PARTITIONED BY (year int, month int)
STORED AS PARQUET
LOCATION 's3://my-bucket/orders/';

SELECT user_id, SUM(amount)
FROM orders
WHERE year = 2026 AND month = 5 AND status = 'completed'
GROUP BY user_id;

The cost model — and how to control it

Athena charges per terabyte of data scanned (~$5/TB). The query above costs you nothing if it’s well-designed and a fortune if it isn’t. The two levers:

1. Partition pruning

The table is PARTITIONED BY (year, month), and the data in S3 is laid out as s3://my-bucket/orders/year=2026/month=5/.... A query with WHERE year = 2026 AND month = 5 reads only those partitions’ files — Athena skips the rest entirely. Without the partition filter, it scans the whole table.

Partition pruning is the single biggest Athena cost lever. Always partition by the columns you filter on most (usually date), and always include the partition filter in the WHERE clause.

New partitions need to be registered — either run MSCK REPAIR TABLE / ALTER TABLE ADD PARTITION, or use partition projection (Athena computes partition locations from a pattern, no catalog entries needed).

2. Columnar format

Converting CSV/JSON to Parquet typically cuts scan cost 10-100×. A SELECT user_id over Parquet reads just the user_id column; over CSV it reads the whole file.

So the cost rules are: partition by your filter columns, store as Parquet, and SELECT only the columns you need (never SELECT * on a wide table).

CTAS — Create Table As Select

CREATE TABLE orders_parquet
WITH (format = 'PARQUET', partitioned_by = ARRAY['year', 'month'],
      external_location = 's3://my-bucket/orders-parquet/')
AS SELECT * FROM orders_raw_csv;

CTAS is how you convert formats and re-partition — read the raw CSV/JSON once, write out partitioned Parquet, then query the Parquet version cheaply forever after. Common ETL pattern: raw landing zone (JSON) → CTAS → optimized analytics zone (partitioned Parquet).

Workgroups + cost controls

A workgroup isolates queries (separate query history, separate result location, separate cost tracking) and — importantly — lets you set per-query and per-workgroup data-scan limits. A workgroup with a “fail any query scanning more than 100 GB” limit stops a careless SELECT * from running up a huge bill. Use workgroups to separate teams/environments and to put guardrails on cost.

Athena + Glue division of labor

• Glue Crawler — scans S3, infers schema, creates/updates table definitions and partitions in the Data Catalog. Run it when new data arrives or schema evolves.
• Glue Data Catalog — the schema store. Shared across Athena, Redshift Spectrum, EMR.
• Glue ETL — Spark-based jobs for heavier transformations (Athena is for querying, not heavy transformation).
• Athena — the SQL query engine over what the Catalog describes.

Athena queries; Glue catalogs and transforms.

When Athena vs alternatives

Athena’s sweet spot: occasional-to-moderate SQL over a data lake, where standing up and paying for a warehouse 24/7 isn’t justified.

Common gotchas

• No partition filter → full-table scan → big bill. Always filter on partition columns.
• CSV/JSON instead of Parquet → scanning every column of every row. Convert with CTAS.
• SELECT * on a wide table → reads all columns. Select only what you need.
• New S3 data not queryable → partitions not registered; run the crawler, MSCK REPAIR, or use partition projection.
• Small files problem → thousands of tiny files have per-file overhead; compact them (CTAS into fewer, larger Parquet files).
• Treating Athena as a database → it’s not for low-latency operational queries or high concurrency; it’s a data-lake query engine.

Interview angle

• “How is Athena priced and how do you control cost?” — per TB scanned (~$5/TB). Control it with partition pruning (filter on partition columns so Athena skips files), columnar format (Parquet reads only the columns you query), and selecting only needed columns. Workgroup scan limits as a guardrail.
• “Why convert CSV to Parquet for Athena?” — CSV is row-oriented, so Athena reads every column of every row regardless of your query; Parquet is columnar and compressed, so a query reads only the columns it needs. Typically 10-100× less data scanned.
• “What’s partition pruning?” — the table is partitioned by columns (e.g., year/month) reflected in the S3 path layout; a query that filters on those columns reads only the matching partitions’ files and skips the rest. The biggest Athena cost lever.
• “Athena vs Redshift?” — Athena is serverless, pay-per-query, no cluster — best for ad-hoc/infrequent SQL over a data lake. Redshift is a provisioned warehouse — better for frequent, latency-sensitive analytical workloads where per-query cost and latency would add up on Athena.
• “What’s CTAS for?” — Create Table As Select: read raw data once and write it out as partitioned, compressed Parquet — the standard pattern for converting a raw JSON/CSV landing zone into an optimized analytics layer you then query cheaply.
• “How does Athena relate to Glue?” — Glue Crawlers infer schema and register tables/partitions in the Glue Data Catalog; Athena reads that catalog to know the schema and queries the underlying S3 data. Glue ETL handles heavy transformations; Athena handles querying.