Athena Partition Projection

TL;DR: By default Athena discovers partitions by listing S3 (or the Glue Catalog) before a query runs — fine at 50 partitions, painful at 50,000. Partition Projection (2020) flips it: you declare the pattern in TBLPROPERTIES and Athena calculates exactly the partition paths a query needs — zero LIST calls, near-instant startup, and new partitions (like today's date) appear automatically with no MSCK REPAIR TABLE or crawler runs. It's a metadata optimization, not a scan-cost one; the big win is usually latency.

The numbers

• S3 LIST is $0.005 per 1,000 calls. A pattern listing 365 daily partitions hourly ≈ 263,000 LIST calls/mo ≈ $1.30/mo per pattern — small alone, real across dozens of queries and millions of partitions.
• The dominant win is query startup: a table with ~4,380 hourly partitions goes from "noticeable lag" listing all of them to "instant" when Athena projects only the 24 hours a query wants.
• Projection types: date (year/month/day/hour — the home run), enum (known regions/categories), integer (clean ID or year-bucket ranges), or injected values passed at query time.

Do this

1. Add projection properties to the table DDL — declare it once and Athena generates paths from the WHERE clause:

   TBLPROPERTIES (
     "projection.enabled" = "true",
     "projection.dt.type" = "date",
     "projection.dt.range" = "2020-01-01,NOW",
     "projection.dt.format" = "yyyy-MM-dd"
   )
   

2. Match the type to the data: date for time-series, enum for a fixed set of regions/categories, integer for clean numeric ranges.
3. Always filter partition columns in the WHERE clause so the win compounds — Athena projects only the needed partitions and scans no data outside them:

   SELECT * FROM logs WHERE dt BETWEEN DATE '2024-11-01' AND DATE '2024-11-07'
   

4. Manage the DDL in code (Terraform/CDK/dbt) so the projection definition is reviewable in PRs, not clicked once into the console and forgotten.
5. Verify in the query execution details that "Data scanned" and execution time drop.

Gotchas

• Schema drift is on you — a new region or renamed path scheme won't be discovered; you update TBLPROPERTIES manually. Add it to your "when we rev the schema" checklist.
• Patterns must be clean — mixed formats, gaps, or one-off subdirectories make projection messy or impossible; random UUID prefixes have nothing to project against (keep Glue discovery there).
• Debugging is harder — an empty result set could mean missing data or a wrong projection config; you lose the explicit Glue Catalog listing.

Skip this if

• The table is small (~20–30 partitions) — listing them is free and the DDL isn't worth it.
• The partition layout is irregular or the schema is still churning frequently — a projection that drifts out of sync is worse than none; stick with Glue discovery until it settles.
• Your Athena bill is about bytes scanned, not partition discovery — reach for columnar formats (Parquet/ORC) and Athena Query Result Reuse, which compound with projection.