Articles · Amazon
Amazon Athena Interview Questions: Partition Projection, Scan Costs, and Empty Results
Debug Athena partition projection when S3 files exist but queries return zero rows, then reduce scan costs with a path, range, and query-plan checklist.
An Amazon Athena query can return zero rows even when matching files exist in S3. Another query can return the right rows while scanning far more data than expected. In an interview, both symptoms are easier to solve when you separate three layers: the SQL predicate, the partition values Athena projects, and the physical S3 paths. The example here is a synthetic troubleshooting exercise, not an AWS incident or a reported employer interview question. AWS's partition projection documentation supplies the product behavior; the dates and table are invented for practice.
Start with Optimize SQL to minimize scans if you want an adjacent SQL warm-up. This guide then focuses on the Athena-specific surprise: projection can make an existing prefix invisible to a query without throwing a missing-partition error.
What partition projection actually does
Traditional Athena partitions are registered in the AWS Glue Data Catalog. Partition projection instead uses table properties to calculate partition values and locations at query time. AWS says Athena ignores registered partition metadata for a table with projection enabled and uses the projection rules. That can reduce the overhead of managing many partitions, especially when partition values follow a predictable pattern. It does not create data in S3, and it does not guarantee that every projected location contains files.
The difference produces two important empty-result cases. First, a projected value can map to an S3 prefix with no objects; Athena can query it and find no data. Second, a query can ask for a value outside the configured projection range; AWS documents that Athena returns zero rows without an error in that case. If you only inspect S3 and confirm files exist, you may miss the range rule that excluded them. If you only inspect Glue's partition list, you may be misled because projected partitions are calculated, not registered in the same way. SHOW PARTITIONS does not reveal projected-only values as a complete projection inventory.
The mental model is a contract: predicate value → projection configuration → generated location → actual objects → rows after file and query filters. You can debug from left to right. Resist starting with “Athena must be cached” or “the files are corrupt.” Those are possible in some contexts, but they do not explain the projection boundary until you examine it.
Work through an empty-result fixture
Suppose a synthetic events table has an event_date partition stored at paths like s3://example-analytics/events/event_date=2026-09-20/. Files exist for September 20 and 21. The table has projection enabled, but projection.event_date.range ends at 2026-09-19. An analyst runs:
SELECT count(*)
FROM events
WHERE event_date = '2026-09-20';
The query returns zero rows. The first check is whether event_date is actually the partition column and whether its type and format align with the table properties. Then inspect the range: the requested value lies outside it. Athena's documented behavior makes zero rows expected even though the S3 prefix exists. Update the range only after confirming the intended coverage and path template, then rerun a bounded query. Do not claim that changing the range proves the files are valid; after projection includes the value, format, permissions, schema, and file contents can still cause an error or unexpected result.
The opposite mismatch is also possible. The range includes a date, but the table's storage.location.template builds s3://example-analytics/events/2026/09/20/ while the objects live under event_date=2026-09-20/. Athena projects the value correctly and looks in the wrong place. AWS's setup guide requires custom location templates to include placeholders for the partition columns and to use the expected path structure. Compare the generated prefix, character for character, with a known object location. A single path-convention mismatch is enough to return no data.
The order of checks matters because a broad repair can hide the original problem. Do not expand a date range to several years merely to make one query return rows. First establish the intended retention and future-date policy. A rolling range may be appropriate for continuously arriving data; a fixed range may be intentional for a frozen archive. Similarly, do not move S3 objects because a table template is wrong until you know whether other readers rely on the existing layout. In an interview, state the smallest reversible correction and how you would confirm it: one date, one known file, one count, and a plan showing the expected partition was selected.
| Check | Synthetic observation | What it means |
|---|---|---|
| Predicate | event_date = '2026-09-20' | The query asks for one partition value. |
| Projection range | Ends 2026-09-19 | The requested value is excluded before S3 lookup. |
| Projected template | .../event_date=${event_date}/ | This would map correctly only after range includes the date. |
| Actual S3 path | .../event_date=2026-09-20/ | Files exist, but existence alone does not override projection. |
Do not skip the last layer. Once the path matches, inspect whether the files contain the expected rows and whether nonpartition predicates filter them out. A count(*) with only the partition predicate is a useful narrow probe; a complex dashboard query is a poor first diagnostic because joins and filters add more ways to get zero.
Why a correct query may still scan too much
An empty result and an expensive result are different problems. Athena's scan cost and runtime are influenced by how much data the query reads, its file format, compression, partition pruning, and selected columns. AWS's data-optimization guide recommends partitioning around common filters and using efficient formats and compression. Projection mainly changes how partition metadata is obtained; it does not automatically make a SELECT * over many partitions cheap.
Suppose the same table has daily partitions and a report asks for a month. If the SQL omits event_date, Athena may consider many projected values. If a filter wraps the partition key in an expression that the engine cannot use for pruning, the intended narrowness may not reach the partition layer. The correct way to investigate is to inspect the plan and query statistics, not to infer bytes scanned from the presence of a WHERE clause. Athena's EXPLAIN documentation describes how to examine partition pruning and query stages. Pair it with the query execution's actual data-scanned metric.
Then inspect the storage layout. Columnar formats such as Parquet or ORC can avoid reading unneeded columns when a query selects only a few fields. A broad SELECT * may defeat much of that benefit. Many tiny files can also add overhead even when total bytes are moderate. If the data is compressed text, compression may reduce bytes stored and transferred, but a text reader still has different column-access behavior from a columnar format. Avoid a made-up claim such as “projection cuts the bill by 80%.” The gain depends on the workload, object layout, and selected columns; this practice case has no measured bill.
Use a layered diagnostic instead of one magic fix
For zero rows, start with the exact query predicate and table definition. Confirm the projected column name, type, format, interval, and range. Compute the expected S3 location from the template. Check whether objects exist at that precise location. Only then inspect file schema and row-level filters. For high scan cost, start with actual scanned bytes and the plan. Confirm partition pruning, selectivity, format, selected columns, and file sizes. The two tracks share the table definition but have distinct success criteria.
| Symptom | Highest-value first question | Evidence | Candidate fix to test |
|---|---|---|---|
| Zero rows, S3 files exist | Is the requested value inside projection range? | Table properties and exact predicate | Correct range or query value. |
| Zero rows, value in range | Does template generate the real S3 prefix? | Expanded template and object path | Correct the template or storage layout. |
| Zero rows, path matches | Do files parse and survive filters? | Narrow count, schema, file sample | Repair data/schema or predicate. |
| Correct rows, high scan | Was partition pruning applied? | EXPLAIN and scanned bytes | Add an effective partition filter. |
| Correct rows, still high scan | Are too many columns or files read? | Projection, file format, object layout | Select columns, compact, or convert format. |
There is a cost to projection itself when the projected space is mostly empty. AWS warns that if more than half the projected partitions are empty, traditional partitions may be preferable. Treat that as an AWS guideline, not a guarantee of a faster result for every table. A sparse customer-by-day projection, for example, can generate many nonexistent combinations. Ask how often new partitions arrive, how sparse the partition domain is, and how queries filter it before choosing projection over catalog registration.
How to answer under interview pressure
Open with a diagnosis tree: “For zero rows, I will verify that the predicate maps to a projected value, then a generated S3 path, then readable data. For high scan cost, I will inspect pruning and actual bytes before changing the format.” Use the September 20 case to show that files can exist while projection excludes the date. Then name the exact table property to inspect, not a vague “check partitions.”
If the interviewer asks whether SHOW PARTITIONS proves the data is missing, say no for a projection-enabled table: that command is not a complete list of computed values. If asked whether adding Glue partitions will fix it, explain that Athena uses projection rules for that table when projection is enabled, so registering a partition alone may not affect the query. If asked whether projection is always faster, mention sparse domains and the AWS guideline. These follow-ups test whether you know which metadata source the engine actually uses.
A good closing experiment is small and reversible. Query one known date, compare EXPLAIN before and after a corrected range or template, verify the expected rows, and record bytes scanned. Then run a representative monthly query to see whether the change helps the real workload. Validate correctness before celebrating lower scan cost: reading zero bytes because the wrong date was projected is not optimization.
If the monthly query is still costly, change one variable at a time. Compare a bounded query that selects only two columns with the existing broad query, then compare file layouts on the same logical data. Record the query ID, scanned bytes, elapsed time, and result count for each run. This makes the explanation auditable and stops an interview answer from collapsing all optimization into a single untested suggestion. It also shows you understand the difference between a metadata shortcut and a storage-layout improvement.