The previous issue showed you how to find the tables driving your Athena scan volume.
This is the system for fixing them in the order that creates the highest return per hour of engineering time.
The order matters.
Partitioning, partition projection, file format, and query predicates all interact. Make the wrong change first, and you may have to redo the work.
The optimization sequence is:
Find the dominant query predicates.
Verify the partition key matches them.
Confirm partition pruning is actually occurring.
Move to partition projection where the layout supports it.
Convert qualifying tables to Parquet.
Measure the result against the original baseline.
Do not start by converting every table to Parquet.
Start with the tables that cost you the most.
Why Scan Reduction Is the Primary Lever
Athena cost is closely tied to the amount of data scanned.
That makes scan reduction unusually measurable. If a table moves from full scans to scans that read one-twentieth of the data, the scan volume for those queries can fall dramatically.
The performance benefit arrives at the same time.
Analysts feel the difference between a query that takes ninety seconds and one that takes four. Faster queries also encourage more productive exploration, which makes efficient table design a business capability rather than merely a finance exercise.
The goal is not to make every query cheap.
The goal is to eliminate repeated, avoidable scans from the tables people use most.
Why Partitioned Tables Still Scan Too Much
A table can be partitioned and still receive almost no benefit from partitioning.
Three mistakes cause most of the damage.
The partition key does not match real query behavior
A table partitioned by ingest_date may receive queries filtered by event_date.
The table is technically partitioned. The common queries still scan across the relevant data because they do not constrain the partition key.
Partition based on how the data is actually queried, not only on how it arrives.
A function wraps the partition column
Queries that cast, transform, or apply date functions to the partition column may prevent effective pruning.
For example, this pattern can be problematic:
WHERE CAST(event_date AS DATE) = DATE '2026-09-01'
Prefer predicates that preserve the column’s native type and apply the comparison directly:
WHERE event_date >= TIMESTAMP '2026-09-01 00:00:00'
AND event_date < TIMESTAMP '2026-09-02 00:00:00'
The exact syntax depends on the column type. The principle is consistent: make the partition predicate explicit and comparable without wrapping the partition key in a function.
Partition metadata is incomplete or expensive
Data can exist in [internal] while Athena lacks the corresponding partition metadata.
The result is missing data, unexpected scans, or repeated crawler and repair work.
Tables with very large numbers of registered partitions can also develop meaningful metadata-management overhead. At that point, partition projection may be a better fit if the prefix layout is predictable.
Three Decisions Per Table
1. What should the partition key be?
You usually choose between an ingestion-time column and a business-time column.
Ingestion-time partitioning is simpler operationally. It matches how files arrive and how pipelines typically write data.
Business-time partitioning is often better for query pruning when analysts filter by the date an event actually occurred.
Look at query history before deciding.
The predicate that appears in most queries is stronger evidence than the partition convention your ingestion pipeline happens to prefer.
2. Should you use catalog partitions or partition projection?
Catalog partitions rely on registered metadata, usually maintained through crawlers, repair operations, or explicit partition updates.
Partition projection defines the partition scheme through table properties. Athena calculates partition locations from the defined pattern instead of requiring every partition to be registered individually.
Use partition projection when:
The prefix layout is predictable
Partition values follow a defined date or enum range
The table has many partitions
New partitions arrive regularly
You want to reduce crawler or repair dependence
Keep catalog partitions when:
The layout is irregular
Partition values cannot be derived reliably
The table has modest partition counts
The data location changes unpredictably
Projection is not automatically better. It requires accurate ranges and predictable paths. A bad projection definition can cause Athena to consider locations that do not exist.
3. Should the table be converted to Parquet?
Parquet is most valuable for wide tables where queries select a small subset of columns.
Columnar storage lets Athena read only the required columns, while compression reduces the data transferred and processed.
Conversion is a stronger candidate when:
The table is queried frequently
The table contains many columns
Queries select relatively few columns
The current format is CSV, JSON, or another row-oriented format
The table has meaningful scan volume
Conversion may return less when:
The table is narrow
Queries routinely use almost every column
The table is rarely queried
The conversion process costs more to operate than the scans justify
Do not assume Parquet is valuable because it is modern.
Measure how much of the table queries actually use.
The Optimization Sequence
1. Pull your top five tables
Start with the ranked table list from your scan analysis.
Work in order of aggregate scan volume. Do not spread engineering effort across twenty tables at once.
2. Extract the dominant predicates
Review the most common WHERE clauses for each table.
Record:
The columns filtered
The date ranges used
Whether the partition key appears
Whether functions wrap the partition column
Whether scheduled queries use bounded predicates
This is the input to every later decision.
3. Compare the partition key with real query behavior
If the partition key does not match the dominant query predicate, address that before converting formats.
A perfectly compressed table with the wrong partition key can still scan far more data than necessary.
4. Check whether pruning is occurring
Compare bytes scanned with the total table size for representative queries.
A table where queries scan nearly the full table is a candidate for:
A better partition key
Corrected predicates
Better partition metadata
A different table layout
A purpose-built aggregate
Do not assume pruning works because the table definition says it is partitioned.
Verify it in query execution data.
5. Move regular layouts to partition projection
For tables with predictable date or enum-based prefixes, define the projection properties and test a representative query.
Compare:
Planning time
Total runtime
Bytes scanned
Query success
Results against the previous table
Do not remove the existing metadata process until you've validated the projected table.
6. Audit functions on partition columns
Search query history for casts and date functions applied directly to partition keys.
Fix the highest-volume queries first. A one-line rewrite in a dashboard definition can remove repeated full or broad scans.
7. Convert qualifying tables to Parquet
Write the converted data to a new prefix.
Use compression such as Snappy for a balanced combination of performance and compatibility.
Keep the original data until you validate:
Row counts
Representative query results
Null behavior
Timestamp behavior
Partition coverage
Cost and runtime
8. Avoid small files
Many small files create request and metadata overhead that can reduce Parquet's benefits.
Target a practical file size in the broad range of 128 MB to 512 MB, then validate against your workload. The right value depends on table size, query patterns, writer behavior, and partition cardinality.
Small-file problems tend to return unless the writer is configured to prevent them.
9. Re-run the measurements
After the table has been in use for at least a week, compare it against the baseline:
Total bytes scanned
Average bytes scanned per query
p95 bytes scanned
Query planning time
Runtime
Monthly scan volume
If scan volume did not fall, stop claiming success.
Find out whether the partition key, predicates, file layout, or workload was misdiagnosed.
The Athena Table Optimization Checklist
Use one worksheet per table.
Record:
Table size
Partition count
Monthly query count
Current average and p95 bytes scanned
Current scanned-to-total ratio
Top query predicates
Current partition key
Projection eligibility
Average columns selected per query
Current file format
Average file size
Owner
Recommended change
Before-and-after measurements
Complete the dominant-predicate section before changing the table.
Every other decision depends on it.
What to Measure
Track these per optimized table:
Scanned-to-total ratio
This is your primary pruning signal.
A low ratio generally indicates that queries are reading a limited portion of the table. A ratio near the full table size indicates that pruning may not be occurring.
Interpret the ratio alongside query intent. Some analytical queries legitimately require broad scans.
Query planning time
Rising planning time on a highly partitioned table may indicate that partition metadata is becoming expensive to manage.
Average file size
A declining average file size indicates that the writer is producing small files again.
Monthly bytes scanned
This is the number that ultimately matters to the bill.
Review the top-five ranking quarterly. Usage changes. A table that was insignificant last quarter can become the highest scanner after one new dashboard or reporting workflow goes live.
The Return Compounds
A correctly partitioned, efficiently formatted table reduces scan volume for future queries.
That is what makes this work different from negotiating a temporary discount or trimming an instance.
You are changing the unit economics of the table.
The return grows as usage grows.
Athena optimization is not a mystery, and it is not a platform-wide cleanup project.
It is a measurement problem followed by focused engineering.
Take your five highest-scan tables.
Bring the analysts who query them into the room.
Complete the predicate analysis before changing the table.
Then fix the highest-impact mismatch first.
The boring work is the profitable work.
That’s it for today!
Did you enjoy this newsletter issue?
Share with your friends, colleagues, and your favorite social media platform.
Until next week — Amrut
Get in touch
You can find me on LinkedIn or X.
If you would like to request a topic to read, please feel free to contact me directly via LinkedIn or X.