Why Athena Returns No Rows on Partitioned Tables (and How to Fix It)

The files are in S3. The table exists in the Glue Data Catalog. SELECT COUNT(*) returns 0. No error, no warning.

This is almost always a partition metadata problem. Athena doesn’t list S3 to find your data. It asks the catalog (or partition projection) which partitions exist, and only reads those prefixes. If the catalog and S3 disagree, Athena trusts the catalog.

This post covers the causes I’ve hit in practice, in the order I check them.

Step 0: confirm it’s a partition problem

Before touching anything, confirm what Athena thinks exists:

SHOW PARTITIONS my_db.events;

And compare against what’s actually in S3:

$ aws s3 ls s3://my-bucket/events/ --recursive | head

If SHOW PARTITIONS is empty or missing recent values, you have a metadata desync. If partitions exist but queries still return nothing, jump to causes 4–6.

Cause 1: new partitions were never registered

Writing files to s3://my-bucket/events/dt=2026-10-01/ does not register partition dt=2026-10-01. Something has to add it to the catalog.

Fix for Hive-style layouts (key=value paths):

MSCK REPAIR TABLE my_db.events;

Two caveats:

MSCK REPAIR TABLE only adds partitions. It doesn’t remove partitions whose S3 prefixes were deleted.
It scans the whole table location. On tables with thousands of partitions it gets slow and can time out. Running it after every load is a smell.

For targeted additions, prefer explicit DDL:

ALTER TABLE my_db.events ADD IF NOT EXISTS
  PARTITION (dt = '2026-10-01') LOCATION 's3://my-bucket/events/dt=2026-10-01/';

Cause 2: your layout isn’t Hive-style

MSCK REPAIR TABLE only understands key=value prefixes. If your writer produces:

s3://my-bucket/events/2026/10/01/part-0000.parquet

MSCK silently does nothing. Options:

ALTER TABLE ... ADD PARTITION ... LOCATION for each prefix.
Partition projection with a storage.location.template (see below).
Change the writer to emit year=2026/month=10/day=01/.

Cause 3: camelCase partition keys

Athena lowercases column and partition names. A path like eventDate=2026-10-01/ won’t match a partition column the catalog stores as eventdate, and MSCK won’t pick it up. Use lowercase (snake_case) partition keys in your S3 paths.

Cause 4: partition projection is enabled (and misconfigured)

This one is non-obvious. When partition projection is enabled on a table, Athena ignores partition metadata registered in the Glue Data Catalog for that table. Running MSCK REPAIR TABLE or a crawler won’t help, because Athena isn’t reading those partitions anyway.

Check the table properties:

SHOW TBLPROPERTIES my_db.events;

Typical failure modes:

projection.dt.range doesn’t cover the dates you’re querying (e.g. 2024-01-01,2025-12-31 and you’re querying 2026).
projection.dt.format doesn’t match the path format (yyyy-MM-dd vs yyyy/MM/dd).
storage.location.template doesn’t match the real prefix. A typo here returns zero rows, not an error.

A working example for a non-Hive daily layout:

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

Cause 5: the files are “hidden”

Athena ignores objects whose names start with _ or .. Some writers produce _SUCCESS, _temporary/ and similar markers. That’s expected. The problem is when your actual data files get those prefixes, which happens with some custom writers or failed renames.

Check which files Athena is reading:

SELECT "$path", COUNT(*) FROM my_db.events
WHERE dt = '2026-10-01'
GROUP BY 1;

If no $path rows come back for a partition you know has files, look at the object names.

Cause 6: the crawler rewrote the schema

If a Glue crawler runs on the table, it can re-infer partition keys from a slightly different path structure (an extra prefix level, a stray file at the root) and change the partition columns. Your WHERE dt = ... now filters on a column that no longer maps to the path.

Compare SHOW CREATE TABLE before and after crawler runs. For tables you own end-to-end, I’d rather manage partitions with DDL or projection and keep the crawler away from them.

Which fix to pick

Situation	Approach
Hive-style paths, few partitions	`MSCK REPAIR TABLE` after loads
Many partitions, predictable values (dates, IDs)	Partition projection
Non-Hive paths, irregular values	`ALTER TABLE ADD PARTITION` from the writer
Partitions written by a Glue job	`enableUpdateCatalog` on the sink

For high-volume, date-partitioned tables, projection removes the problem entirely: no catalog calls, no repair step.