AWS Athena for the data engineering interview

What Athena actually is

If you're a data engineer interviewing at a company that lives on AWS, Athena will come up, and usually not as a friendly warm-up. It's the one service where the interviewer can pivot from "explain the architecture" to "now optimize this query" to "now defend the bill" in under five minutes. The right framing: Athena is serverless SQL over object storage, with a Trino engine under the hood (Presto until late 2022, then forked to Trino) and a Glue Data Catalog as the metastore. You bring the files in S3; AWS brings the compute.

The thing that throws candidates is that Athena is not a database. There is no storage engine you can tune, no indexes you can add, no buffer pool that warms up. Every query is a fresh scan over files, with the only persistent state being the table definition in Glue and the result manifest written back to S3. Once you internalize that, most of the optimization questions answer themselves — you don't speed up Athena, you give Athena less work.

The interviewer's mental model usually splits into three buckets: architecture (what is it, how does it talk to Glue and S3), cost (how do you keep the bill from exploding), and performance (why is this 4 TB scan taking 22 minutes). The strongest candidates answer all three from the same first principle — column-oriented, partitioned, compressed Parquet on S3 — and only branch into specifics when pushed.

How a query runs end-to-end

A useful mental walkthrough, because interviewers love to ask it as "what happens when I press 'Run query'":

SQL submitted
   → Athena parses + plans (Trino)
   → Glue Catalog resolves table -> S3 path + schema
   → Trino workers fan out, list S3 prefixes
   → Predicate pushdown trims partitions
   → Workers read columnar chunks (Parquet/ORC) or row scans (CSV/JSON)
   → Result set materialized to S3 result bucket
   → Console/JDBC streams result manifest back to you

Each step has at least one interview question hiding in it. Why does the first query against a new table sometimes take longer? Glue partition discovery. Why does the bill spike on JSON ingestion? No columnar pruning. Why does SELECT * cost 40x more than SELECT col1, col2? Trino still reads every column group from disk if you ask for them.

The other detail worth knowing: Athena workgroups are how AWS isolates billing, query limits, and result locations across teams. In a real interview at a place like Stripe or Airbnb, you'd be expected to know that workgroup-level data scan limits are your first line of defense against an analyst accidentally scanning 40 TB of raw event logs at 3 a.m.

Pricing model and the scan-less mindset

There are two pricing modes, and you should know both cold.

The on-demand price has not budged in years and is the number every candidate should have memorized. The provisioned mode is the right answer when an interviewer asks "what would you do if Athena bills hit $80k/month?" — at that point, dedicated DPU-hours often beat per-TB pricing, and the conversation shifts from optimization to capacity planning.

The deeper point: most teams overspend on Athena not because they need more capacity but because they never converted their lake to Parquet. Going from CSV to Snappy-compressed Parquet routinely cuts scans by 5-10x with zero query changes. That's a one-day project that pays back in one billing cycle.

Optimization tactics that show up in interviews

This is the section where weak candidates list features and strong candidates explain trade-offs. Here are the levers, in roughly the order an interviewer expects to hear them:

Partitioning. Tables are partitioned by columns (typically date, region, event type) that translate to S3 prefixes. A WHERE date = '2026-05-20' on a partitioned table prunes everything outside that prefix before a single byte is read.

SELECT user_id, event_name
FROM events
WHERE event_date = DATE '2026-05-20'
  AND country = 'US';

If event_date and country are both partitions, Athena reads exactly one prefix. If they aren't, Athena reads the entire table. This single mistake is the most common reason a query that should cost cents costs hundreds of dollars.

Columnar formats. Parquet and ORC are the only formats you should be storing analytical data in. Athena reads only the columns referenced in the query, so a 200-column wide table with a SELECT col1, col2 query touches roughly 1% of the bytes a CSV version would.

Compression. Snappy is the standard — fast to decompress, decent ratio. Gzip compresses harder but is slower; ZSTD is a newer middle ground. The interview answer is "Snappy unless I have a reason."

File sizing. Many small files murder Athena (and Trino in general). Each file requires a metadata round-trip to S3, and the workers can't parallelize cleanly. The rule of thumb: target 128 MB to 512 MB per file, compact small files with a scheduled job, and if you're streaming into S3 with Firehose, set buffer hints accordingly.

Predicate pushdown is built into Trino and works automatically for Parquet/ORC — your WHERE clauses are applied at the file level using min/max statistics in row groups. You don't enable this; you just write filterable predicates.

Partition projection is the underrated Athena-specific trick. If your partition values are predictable (every day for the last three years), you can tell Glue the partition shape via projection config and skip the MSCK REPAIR / partition-listing dance entirely. Cuts planning time on large tables from 30 seconds to under 1.

Athena vs Trino vs BigQuery vs Redshift Spectrum

This comparison shows up in system design rounds. Have a clean answer.

The honest summary: Athena wins on operational simplicity, loses on cost at extreme scale, and never wins on performance against a tuned Trino cluster. If your data already lives in S3 and your team is two engineers, Athena is correct. If you have 50 analysts running thousands of queries a day, do the math on provisioned mode or self-hosted Trino.

If you want to drill questions like this every day against a structured roadmap, the NAILDD app ships with hundreds of data engineering interview problems covering exactly this kind of architecture vs cost trade-off.

Common pitfalls

The first pitfall is treating Athena like a warehouse and storing data in CSV or JSON Lines because that's how the upstream system wrote it. Every query then scans every byte of every column referenced or not, which is how teams accidentally rack up four-figure daily bills on data that would fit in a single Parquet file. The fix is unglamorous — schedule a daily compaction job that rewrites raw landing-zone files into partitioned Parquet, even if the upstream pipeline still writes CSV.

A second trap is forgetting to update Glue partitions after writing new data to S3. Athena only sees partitions registered in the catalog, so if you write s3://bucket/events/dt=2026-05-20/ but never run MSCK REPAIR TABLE or call ALTER TABLE ADD PARTITION, your most recent data is invisible to queries. Partition projection eliminates this entirely by computing partitions from a configured pattern instead of relying on registered partitions, and is almost always the right choice for time-based tables.

A third, subtler failure is assuming LIMIT reduces cost. It doesn't, or rather, it doesn't in the way candidates think. Athena still has to scan whatever the planner needs to satisfy the query — if there's an ORDER BY before the LIMIT, that's a full scan and sort. The only way LIMIT saves money is when combined with predicate pushdown that already restricts the scan. If you want a cheap preview, use TABLESAMPLE BERNOULLI(1) on the underlying table.

The fourth pitfall is wildly varying file sizes inside the same partition. A partition with one 2 GB file and a thousand 50 KB files runs slower than one with eight 256 MB files, even if total bytes are identical. Worker scheduling stalls on the small-file tail. Compaction jobs that target a consistent file size are how mature data platforms keep Athena predictable.

Finally, candidates routinely forget that the result bucket itself costs money. Athena writes query results to S3 by default, and on busy workgroups those results accumulate fast. A lifecycle policy that expires results after seven days is a five-minute fix that no one does until the storage bill spikes.

Related reading

• SQL for the data engineer interview
• SQL antipatterns for data engineering interviews
• BigQuery for the data engineering interview
• Apache Iceberg deep dive
• EXPLAIN and query plans
• Databricks for the data engineering interview

FAQ

Is Athena actually Trino under the hood?

Yes — Athena engine version 3 runs on a fork of Trino. Earlier engine versions (v1, v2) ran on Presto, which is the project Trino itself was forked from in 2019. For interview purposes, treat Athena as managed Trino with AWS handling cluster provisioning, scaling, and version upgrades. The SQL dialect, function library, and query semantics track Trino, with occasional lag when upstream releases new features.

When should I use provisioned capacity instead of per-query pricing?

Run the math: if your monthly per-query bill is consistently in the tens of thousands of dollars and your workload is predictable (not bursty), provisioned DPU-hours usually beat on-demand by 30-50%. The break-even point depends on workload shape, but a common rule is 20+ active query hours per day across a workgroup. Companies with bursty analytics traffic that's flat by day stay on per-query; companies with always-on dashboards and pipelines should benchmark both modes.

How does partition projection differ from regular partitioning?

Regular partitioning requires every partition value to be registered in the Glue Catalog — you add them via ALTER TABLE ADD PARTITION or by running MSCK REPAIR TABLE, and Athena lists them during planning. Partition projection skips the catalog entirely by computing valid partition values from a configured pattern (date range, integer range, enum list) at query time. This eliminates the partition-listing bottleneck on tables with thousands of partitions and is the right default for time-based tables.

What's the maximum query timeout?

Athena queries time out at 30 minutes by default, and the hard limit is 30 minutes for DDL and longer for DML depending on engine version. If your query routinely brushes that limit, you're scanning too much data or hitting a planning pathology — refactor to push more filtering into partitions, or break the query into stages that materialize intermediate results with CTAS (CREATE TABLE AS SELECT).

Can Athena query data outside S3?

Yes, via Athena Federated Query. Connectors exist for DynamoDB, RDS, Redshift, CloudWatch Logs, and custom JDBC sources. Each federation hop costs Lambda invocations, so federated queries are generally slower and more expensive than the same data in S3. Treat federation as a way to join a small lookup table from a transactional store into a large S3 scan, not as a primary access pattern.

How do I avoid accidental five-figure bills?

Set workgroup-level data scan limits — both per-query and per-workgroup-per-day. The per-query limit will fail any query that would scan more than the threshold, which forces analysts to fix the predicate before resubmitting. The per-workgroup limit caps total spend. Pair this with billing alerts in CloudWatch on Athena's BytesScanned metric and you'll catch runaway analysts before the finance team does.