Monday, 9 a.m. The dashboard shows zero orders for Sunday. The files were sitting in S3 the whole time. Athena just did not know the folder existed.
The culprit is usually a Glue crawler scheduled once a day.
Think of a library with a card catalog. New books land on the shelf, but until the librarian files a card, nobody can find them. The crawler is that librarian, working one shift a day. Partition projection removes the catalog and pins a rule on the wall: the book for date X is on shelf X. Nothing to file.
What is actually happening
Athena gets partition locations from the Glue Data Catalog. If
s3://lake/orders/dt=2026-10-05/ lands but nobody registers it (crawler, MSCK REPAIR TABLE, or ALTER TABLE ADD PARTITION), Athena skips it. No error. Just missing rows.With partition projection, Athena computes the partitions from table properties at query time and stops asking the catalog for them:
1
2
3
4
5
6
7
8
9
ALTER TABLE orders 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://lake/orders/dt=${dt}/'
);Today's folder is queryable the second the first file lands. Three years of daily partitions is about 1,100 entries the catalog no longer has to serve, and on large tables that cuts query planning time too.
The catch: projection is an Athena feature. Once it is on, Athena ignores partitions registered in the catalog for that table, while Glue ETL, EMR and Redshift Spectrum still read only the catalog.
What I do now
- Use projection when keys are predictable: dates, hours, a fixed list of regions. For unbounded keys like customer IDs, the
injectedtype works, but every query must filter on that column. - Always filter on the projected column. An unfiltered query makes Athena project every value in the range.
- If Glue jobs or Spectrum read the same table, keep registering partitions for them. Projection does not help those engines.
- Before deleting the daily crawler, check what else depends on it. Crawlers often pick up schema changes too, and that job still needs an owner.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article