Athena Over a Million Log Objects: Crawler, Projection, or Compaction
A SaaS company keeps 4.2 TB of ALB and CloudFront logs in s3://logs-prod/ under year=/month=/day=/hour=/, growing about 12 GB/day. Analysts query it with Athena from QuickSight; one 12-month dashboard query scans 310 GB and takes 4 minutes. A Glue crawler registers partitions every 6 hours, takes 52 minutes, and intermittently fails with an internal service exception after creating 30,000+ partitions; the newest hour is frequently missing from query results. The prefix holds about 1.1 million objects averaging 11 MB. The team proposes converting to Parquet with daily partitions and running a nightly MSCK REPAIR TABLE, and asks whether that will fix both the staleness and the query cost.
1. Evaluate the proposal against Athena partition projection. Which do you choose, in what order, and why?
Read the worked answer
MSCK REPAIR TABLE is not incremental: it lists the prefix to discover partitions, and against 1.1 million objects it will run for tens of minutes, throttle, and fail unpredictably. Running it nightly also does not fix staleness, it just moves the missing-hour problem from 6 hours to 24.
Partition projection is the better catalog answer: define the table with projection types for year/month/day (and hour if queries filter on it), with a location template and a bounded date range such as 2022-01-01,NOW+1DAY. New hours become queryable the moment objects land, with no DDL, no crawler, and no partition metadata to maintain. The tradeoff is that the physical layout must match the template exactly, which is acceptable for a fixed layout you control.
But partitioning is not the main cost driver here. The 310 GB scan comes from full scans of text-format log data with filters on non-partition columns such as status and path. Converting to Parquet is where the 10x+ reduction comes from, and files averaging 11 MB are below the efficient range.
Order: (1) convert to Parquet with a compaction job targeting 128-512 MB files; (2) drop the crawler and define the table with projection; (3) verify with EXPLAIN and Athena scanned-bytes metrics; (4) keep hourly partitions if dashboards filter by hour, collapse to daily only if they never do.
2. If the crawler is removed, how do you keep up with schema changes in ALB and CloudFront logs?
Read the worked answer
Neither source rewrites old data, which makes this tractable without a crawler.
- CloudFront standard logs carry a #Fields header per log version; parse the header rather than assuming a fixed order, and map versions to known column sets.
- ALB adds new fields at the end of the line. Define a superset schema and use ALTER TABLE ADD COLUMN when a new trailing field appears; adding a column at the end is metadata-only and does not rewrite data.
Operationally: run a weekly job that samples the newest files, diffs their field count and header against the current table schema, and raises an alert. Keep a small canary query that selects the newest hour and asserts row count above zero, which catches silent schema mismatches before analysts do. OpenCSVSerde and similar text serdes silently return nulls for extra columns, so a null-rate monitor on the last few columns is a cheap early warning.
Reintroducing the crawler is not the answer: it will rediscover and rewrite the schema unpredictably and reintroduce the partition churn you just removed.
3. Estimate whether the cost problem is solved by the plan, and state what you would monitor to prove it.
Read the worked answer
Do the math. At $5 per TB scanned, 310 GB is about $1.55 per run; at roughly 200 dashboard refreshes per day that is over $300/day, before ad-hoc queries.
After Parquet conversion and projection, a query that touches only the needed columns of a date range typically lands in the low tens of GB, so the same workload falls by roughly an order of magnitude. That is the win, and it should be demonstrated on one representative dashboard before rollout, not assumed.
Guardrails to add rather than trusting the estimate:
- Athena workgroups with per-query and per-workgroup data-scanned limits, so a bad query cannot scan the full lake.
- Query result reuse enabled for repeated dashboard refreshes.
- CTAS or INSERT INTO to materialize the expensive monthly rollup instead of recomputing it per dashboard.
Monitor: bytes scanned per workgroup per day, average object size per partition, count of partitions with no new data, and the p95 of each named dashboard query. If scanned bytes stop falling, the remaining cost is almost always uncompacted files or a filter on a non-partition column, not the catalog.