skip to content

Athena returns zero rows for a partition whose files are in S3 — what went wrong in the Glue Data Catalog?

level: middleimportance: must knowfreq 74%

answer

  1. the engine never lists the bucket
  2. a table is its partition list
  3. the files are there, the metadata is not
  4. zero rows and zero bytes scanned is a clue
  5. SHOW PARTITIONS before blaming the query

basics

~20 s

Almost always the partition is not registered. Athena reads only partition entries in the Glue Data Catalog, so a new prefix nobody added is invisible. Re-run the crawler, run MSCK REPAIR TABLE, add the partition explicitly, or use partition projection.

solid answer

~50 s

For a partitioned table, Amazon Athena does not list S3 to find data — it asks the AWS Glue Data Catalog which partitions exist and reads only those locations. Files sitting under `dt=2026-08-20/` with no matching partition record are simply not part of the table, so the query returns zero rows rather than an error. The fixes, in order of how much you own the pipeline: re-run the Glue crawler; run `MSCK REPAIR TABLE t` in Athena, which scans the prefix and adds Hive-style (`key=value`) folders; run `ALTER TABLE t ADD PARTITION (dt='2026-08-20') LOCATION '...'` for non-Hive layouts; have the writing job call `BatchCreatePartition` itself; or enable **partition projection**, where Athena computes partition values from table properties and never consults partition metadata at all. If the partition *is* registered, check that its own `Location` really points at the files and that the table's `Location` was not changed underneath it.

code

text · 8 lines
text
s3://lake/events/dt=2026-08-19/part-0000.parquet   <- partition registered
s3://lake/events/dt=2026-08-20/part-0000.parquet   <- written last night, no partition record

athena> SHOW PARTITIONS events;
dt=2026-08-19

athena> SELECT count(*) FROM events WHERE dt = '2026-08-20';
0        -- (Run time: 1.2s, Data scanned: 0 KB)

go deeper

for a junior

Remember that a partitioned table is only as complete as its partition list, and that SHOW PARTITIONS is the first thing to run when a query comes back empty.

for a middle

Explain the mechanism: the engine resolves partitions from the catalog and reads only those locations, so unregistered prefixes are invisible. Know the repair options and what each one costs.

for a senior

Diagnose the whole family — wrong partition location, case mismatch, depth mismatch, bad SerDe — and design the write path so the partition is registered by the job that produced it rather than by a schedule.

for a principal

Decide the lake-wide policy: which zones tolerate crawler lag, where partition projection replaces metadata entirely, and what freshness contract downstream consumers are actually promised.

## Why zero rows and not an error A partitioned table in the AWS Glue Data Catalog is a table record plus a **set of partition records**. Each partition record carries its own storage location. When Amazon Athena plans a query it calls `GetPartitions` against the catalog, applies the query's partition predicates to that list, and reads only the S3 locations that survive. It does **not** list the table's prefix looking for folders. The consequence is unforgiving and silent: if yesterday's crawler run registered `dt=2026-08-19` and a job has since written `dt=2026-08-20`, the new files belong to no partition, so they belong to no table. A query filtered to the new date matches zero partitions, scans zero bytes, and returns an empty result set with a green checkmark. Nothing failed — the catalog simply says the data does not exist. This is the single most common reason a lake query "returns nothing", and interviewers ask it because the failure mode is a success message. ## The repair options **Re-run the crawler.** Correct but coarse: it costs a run, it may re-infer types, and it introduces a lag between the write and the data becoming visible. Bound the cost with the recrawl setting "Crawl new sub-folders only", which restricts the run to folders added since the last crawl and therefore cannot notice schema changes in old ones. **`MSCK REPAIR TABLE t`** in Athena scans the table's prefix and adds every Hive-style `key=value` folder it finds that is missing from the catalog. It only understands Hive-style naming, it never removes stale partitions, and on a table with a very large number of folders it gets slow and can hit the query timeout — so it is a fine repair tool and a poor scheduled job. **`ALTER TABLE t ADD PARTITION (dt='2026-08-20') LOCATION 's3://…'`** is explicit and works for any layout, including non-Hive prefixes such as `2026/08/20/` and partitions whose files live somewhere else entirely. It is the right tool when a pipeline knows exactly which partition it just wrote. **Register from the writer.** A job that produces the data can call the Glue `BatchCreatePartition` API, or a Glue ETL job can be configured with `enableUpdateCatalog` and `partitionKeys` so the catalog is updated as part of the write. This removes the lag entirely: the partition exists the moment the data does. **Partition projection.** In Athena you can set table properties (`projection.enabled`, `projection.dt.type`, `projection.dt.range`, `projection.dt.format`, and `storage.location.template`) that describe the partition space arithmetically. Athena then *computes* which prefixes to read instead of asking the catalog, so there are no partition records to fall behind — a very good fit for date-shaped layouts with predictable prefixes. The tradeoff is that the projection description must match reality; prefixes outside the declared range are unreachable, and other engines reading the same catalog table do not honour the projection properties. ## When the partition exists and it still returns nothing Work down this list: - **Wrong partition Location.** Each partition holds its own location. A hand-written `ADD PARTITION` with a typo, or a table whose `Location` was later changed, leaves a partition pointing at an empty prefix. - **Case and value mismatch.** `dt=2026-08-20` and `DT=2026-08-20` are different folders, and a value with a trailing slash or URL-encoded character will not match the predicate you typed. - **Depth mismatch.** If files are one level deeper than the registered partition location, and the reader is not recursive, the partition looks empty. - **Wrong SerDe or classification.** If the crawler classified Parquet files as CSV — or a custom classifier won when it should not have — rows can come back empty or garbled rather than absent. - **Excluded by the crawler.** Exclude patterns meant for `_temporary/` sometimes match real prefixes. - **Permissions.** If the S3 location is registered with AWS Lake Formation and the querying principal has no grant, you get access denied rather than zero rows — a useful way to tell the two failures apart. ## The habit to build Before blaming the query, ask the catalog directly: `SHOW PARTITIONS t` in Athena, or `aws glue get-partitions --database-name … --table-name …`. If the partition is not in that list, no amount of SQL will find the data. That one check separates a five-minute fix from an afternoon.

  • Why is MSCK REPAIR TABLE a bad choice as a scheduled hourly job?
    It scans the whole table prefix every time, so its cost and runtime grow with the total number of folders, not with the number of new ones — on a large table it eventually exceeds the Athena query timeout. It also only understands Hive-style naming and never removes partitions whose data has been deleted. Registering the specific partition from the writing job is O(1) and exact.
  • What does partition projection change about the Glue Data Catalog for that table?
    Nothing is written to it. Athena reads the projection properties on the table and derives the prefixes to scan arithmetically, so partition records are never created or consulted. That removes the whole class of missing-partition bugs, at the cost of the projection definition having to match the real layout, and of other engines reading the same catalog table not honouring it.
  • How would you tell a missing-partition problem apart from a permissions problem?
    A missing partition returns success with zero rows and roughly zero bytes scanned. A permissions problem — for example a Lake Formation-registered location with no grant — returns an explicit access denied. If `SHOW PARTITIONS` lists the partition but the query is still empty, suspect the partition's own Location or a SerDe mismatch instead.
  • A table has hundreds of thousands of partitions and query planning has become slow. What helps?
    Planning time is dominated by the catalog partition lookup. Creating a partition index on the leading partition keys lets filtered `GetPartitions` calls use the index instead of a full enumeration. Coarsening the partition scheme so there are fewer, larger partitions helps more, and partition projection removes the lookup altogether.

The files are books that arrived at the library and were shelved, but nobody added a card for them. The catalogue search returns nothing, and the search is working perfectly.

saying these in an interview costs you the question

  • Claiming Athena lists S3 to discover new files
  • Assuming a successful crawler run means every partition is current
  • Scheduling MSCK REPAIR TABLE hourly on a huge table
  • Thinking a missing partition raises an error rather than returning nothing
  • Adding a partition without checking the location it points to

context

resolving links…