Reduce Amazon Athena Costs on Apache Iceberg Tables

Amazon Athena bills on a single axis: $5.00 per terabyte of data scanned. No cluster to provision, no idle compute to pay for — just a direct tax on how much data your queries touch. On well-optimized Iceberg tables, that model is elegant: partition pruning and column statistics skip 90%+ of files, and a complex analytical query costs pennies. On poorly maintained tables — which is to say, most production Iceberg deployments — the same query reads every file in every partition, and the invoice compounds at $5 per TB with every execution.

The gap between those two scenarios is not about Athena configuration. It is about table structure. Iceberg tables degrade structurally over time: streaming ingestion fragments data into hundreds of thousands of small files, sort order drifts away from query patterns, manifests grow too large for efficient planning, and snapshots retain dead data that inflates scan volume. Each of these problems independently increases the bytes Athena reads per query. Together, they can push scan volumes 10–20× above what the logical data requires.

This guide breaks the problem into two paths. The first is continuous, intelligent maintenance — an autonomous control plane like LakeOps that monitors table health, executes maintenance when degradation is detected, and keeps Athena scan volumes minimal without human intervention. The second is disciplined manual maintenance using Athena SQL and Spark procedures. Both paths lead to 80%+ cost reduction; the difference is operational overhead and reliability at scale. For a broader perspective on lakehouse cost management, see State of Iceberg FinOps.

LakeOps dashboard showing lake-wide health KPIs, storage trends, and optimization activity — LakeOps dashboard: lake-wide health KPIs, storage trends, and optimization activity across all connected catalogs.

Why Athena costs explode on Iceberg tables

Athena's pricing model is transparent — $5.00 per TB of data scanned, rounded to the nearest megabyte with a 10 MB minimum per query. DDL statements are free. Failed queries are not charged. The entire cost surface reduces to a single variable: how many bytes does this query read from S3? Understanding why that number is inflated requires understanding how Iceberg table structure interacts with Athena's query execution.

Small files multiply GET requests and inflate scan volume

Streaming ingestion from Kafka, Flink, or Spark Structured Streaming commits data in small batches — typically every 1–10 minutes. Each commit produces one or more Parquet files per partition, most ranging from 1–50 MB. A table with 50 partitions and 5-minute checkpoints produces 14,400 new files per day. At 10 MB average, that is 140 GB of data stored across 14,400 objects.

When Athena queries this table, it must open, read metadata from, and scan each of those files. Parquet is columnar, so Athena reads only the requested columns — but it still must issue a GET request per file, read the footer to evaluate row-group statistics, and decide whether to read the data pages. With thousands of tiny files, the per-file overhead dominates: each GET adds latency, and the planner cannot skip files whose statistics overlap broadly because small unsorted files contain scattered values across every column.

The result: a query that filters WHERE event_date = '2026-07-15' on a well-compacted table might read 2 GB from 8 files. On a fragmented table with identical data, the same query reads 20 GB across 4,000 files — 10× the scan volume, 10× the Athena cost.

Operations monitoring showing coverage, table readiness, and maintenance execution timeline — Operations monitoring: coverage percentage, table readiness, and maintenance timeline — track how many tables are drifting toward cost inflation.

Bad sort order prevents file skipping

Iceberg tracks per-file column statistics — the minimum and maximum value of each column within each data file. Athena's planner uses these min/max bounds to skip files that cannot contain matching rows. If a file's customer_id column has min=1000 and max=2000, a query filtering WHERE customer_id = 5000 skips it entirely.

But this only works when data is sorted on the filter columns. Unsorted files contain values spanning the entire domain of a column — every file's min/max range overlaps every other file. Nothing can be skipped. A query filtering on customer_id = 5000 reads every file in the table because every file potentially contains that value.

Consider a 1 TB table queried 100 times per day with a selective filter. Sorted on the filter column, Athena reads ~50 GB per query (95% file skip rate): $0.25 per query, $25/day, $750/month. Unsorted, Athena reads the full terabyte: $5.00 per query, $500/day, $15,000/month. That is a 20× cost difference from sort order alone.

Stale manifests slow query planning

Iceberg's metadata layer consists of manifest files — lists of data files with their partition values and column statistics. When a table has thousands of commits without manifest compaction, it accumulates hundreds or thousands of small manifest files. Athena must read all of them during query planning to determine which data files to scan.

Each manifest read is a GET request against S3. With 500 manifest files, every query issues 500 GETs before scanning any data. While the direct API cost is small ($0.0002 per query), the planning latency delays query start by seconds and holds DPU resources. More critically, fragmented manifests make Athena's planner less efficient at filtering — increasing scan volume because the planner cannot efficiently evaluate statistics across thousands of manifest entries under time constraints.

Lake-wide table health view showing tables classified by status, size, and record count — Lake-wide table health: tables classified by status, size, and record count — unhealthy tables surface before costs compound.

Scan amplification from uncompacted data

Scan amplification is the ratio between data physically read and data logically needed. On a healthy table, scan amplification approaches 1×. On degraded tables, it routinely reaches 10–50×. The causes compound: small files prevent effective statistics pruning, lack of sort order eliminates file skipping, delete files force reading original data plus deletes, and retained snapshots keep old file versions in the planning path.

A real-world example: a 500 GB Iceberg table with 200,000 small files, no sort order, and 6 months of uncompacted data. Every analytical query scanning this table reads the full 500 GB — $2.50 per query on Athena. With 50 daily queries, that is $125/day or $3,750/month for a single table. After compaction and sort order optimization, the same queries read 30–50 GB: $7.50–$12.50/day. A 90% reduction.

Retained snapshots and delete files

Iceberg creates a new snapshot with every commit. Without expiration, snapshots accumulate indefinitely. While expired data files referenced only by old snapshots do not directly increase Athena scan volume (Athena reads the current snapshot), the indirect effects are significant: more snapshots mean more manifest files to maintain, more metadata to traverse during planning, and delete files that force Athena to apply row-level deletes at read time — reading both the base data file and the delete file to produce correct results.

Tables receiving frequent updates via MERGE INTO or DELETE accumulate delete files rapidly. Each query must read and apply these deletes, increasing scan volume beyond the logical data size. A table with 10,000 delete files forces Athena to scan the delete metadata on every query — adding gigabytes of scan volume that appear directly on the invoice.

Proactive insights showing file fragmentation, scan amplification, and manifest bloat at four severity levels — Proactive insights: file fragmentation, scan amplification, and manifest bloat surfaced by severity — catch Athena cost inflation before it hits the invoice.

Path 1: Intelligent continuous optimization with LakeOps

LakeOps is an autonomous control plane for Apache Iceberg lakehouses. It connects to your existing catalogs (AWS Glue, Hive Metastore, Nessie, Unity Catalog), continuously monitors every table's structural health, and executes maintenance operations when degradation is detected — without requiring you to write or schedule any maintenance code.

For Athena cost reduction specifically, LakeOps addresses every root cause covered above: it compacts fragmented files with a purpose-built engine at a fraction of Spark's cost, derives query-aware sort orders that maximize file skipping, routes heavy workloads to cheaper engines, and coordinates all lifecycle operations automatically. The result is tables that stay structurally optimized — and Athena scan volumes that stay minimal — continuously.

Compaction engine: 95% faster at $5/TB vs $50/TB

LakeOps runs compaction through a purpose-built Rust/DataFusion engine rather than Spark. The performance difference is architectural: no JVM startup, no executor provisioning, no garbage collection pauses. Binpack compaction that takes 45 minutes on an EMR cluster completes in under 3 minutes. The cost follows: $5 per TB compacted versus $50+ per TB on Spark/EMR — making it economically viable to compact continuously rather than on weekly schedules where degradation accumulates between runs. For a deep dive on compaction approaches, see Iceberg compaction strategies.

For streaming tables producing 14,400 files per day, continuous compaction means Athena always queries against well-sized files (256–512 MB targets) rather than thousands of fragments. The scan volume reduction is immediate: fewer files with tighter statistics means Athena's planner skips more data, directly reducing the per-query bill.

Compaction benchmarks showing speed, cost, and query improvement across real production tables — Compaction benchmarks: speed, cost, and query improvement across real production tables — Rust/DataFusion vs Spark.

Query-aware sort order: 12× fewer files scanned

Compaction alone merges small files — but unless data is sorted on columns that queries actually filter on, min/max statistics remain broad and file skipping stays ineffective. LakeOps analyzes production query patterns — the actual WHERE clauses, JOIN keys, and GROUP BY columns hitting each table — and derives optimal sort orders that maximize file skipping for the real workload.

The impact on Athena is direct and measurable. A table sorted on its most-queried filter columns enables Athena to skip 90–95% of files for selective queries. In production deployments, query-aware sorting reduces the number of files scanned by 12× on average — translating directly to a 12× reduction in Athena scan charges for those queries.

Layout simulations let you preview the impact before committing to a sort order rewrite. Replay historical query patterns against candidate sort strategies to see projected file-skip rates and scan reductions — validate the ROI before paying the I/O cost.

Layout simulations showing sort-order strategies and file-skipping improvements — Layout simulations: preview sort-order strategies and projected file-skipping improvements before committing to a rewrite.

Multi-engine routing: move workloads off Athena entirely

Some workloads should not run on Athena at all. Athena's $5/TB pricing makes it cost-effective for ad-hoc queries on well-optimized tables, but prohibitively expensive for heavy batch workloads scanning terabytes repeatedly. LakeOps routes these workloads to cheaper engines — Trino, EMR Spark, or StarRocks — based on cost/latency tradeoffs. For a detailed walkthrough, see Routing multiple query engines with Iceberg.

Consider a nightly reporting pipeline scanning 5 TB across 20 tables. On Athena: $25 per run, $750/month. On a right-sized Trino cluster: $3–5 per run, $90–150/month. On DuckDB for smaller tables: effectively free beyond compute instance cost. Multi-engine routing does not replace Athena — it reserves Athena for the interactive, ad-hoc queries where its serverless model shines, and moves predictable heavy workloads to cost-optimal alternatives.

Routing performance metrics showing latency, query volume, and engine load — Routing performance: latency, query volume, and engine load — identify which Athena workloads benefit from routing to cheaper engines.

LakeOps maintains a cost model across all connected engines, factoring in compute pricing, data locality, and query complexity. The engine comparison view makes the tradeoff explicit — where Athena remains the right choice and where switching saves 5–10× per query.

Engine comparison showing cost vs latency across query engines — Engine comparison: cost vs latency — identify which workloads should move from Athena to cheaper alternatives.

Adaptive maintenance: coordinated lifecycle operations

Reducing Athena cost is not a single operation — it requires multiple maintenance actions executed in the correct order. LakeOps bundles these as Adaptive Maintenance: compaction, snapshot expiration, manifest rewrite, and orphan cleanup executed as a coordinated unit. The system determines which operations each table needs based on health signals, sequences them correctly (expire before orphan cleanup, compact before manifest rewrite), and adapts frequency to ingestion rate.

This matters for Athena costs specifically because each operation contributes to scan reduction:

Compaction → fewer files, tighter statistics, less data scanned
Snapshot expiration → fewer delete files to apply, less overhead per query
Manifest rewrite → faster planning, better pruning decisions
Orphan cleanup → no direct Athena impact, but prevents metadata pollution

Table events showing compaction, expiration, and manifest rewrite operations with timestamps — Table events: compaction, expiration, and manifest rewrite operations logged with timestamps and impact metrics.

The Adaptive Maintenance view shows the coordinated state of each table — which operations are active, which are scheduled, and what completed on the last run. Rather than managing four independent cron jobs per table, you get a single pane that sequences everything based on the table's current condition.

Adaptive Maintenance showing compaction, expiry, manifest rewrite, and orphan cleanup coordinated per table — Adaptive Maintenance: compaction, expiry, manifest rewrite, and orphan cleanup coordinated per table based on health signals.

Organization-wide policies

At lake scale — hundreds or thousands of tables across multiple catalogs — individual table maintenance does not work. LakeOps applies policies at catalog, namespace, or table level: define compaction targets, expiration windows, sort strategies, and routing rules once, and they apply automatically to every table matching the scope. New tables inherit policies from their namespace. For a broader view of cost management across the storage layer, see Reducing AWS S3 cost with Iceberg.

LakeOps walkthrough — catalog connection, table health, and autonomous optimization.

Path 2: Manual Athena cost reduction

The second path uses Athena SQL and Spark procedures to achieve the same structural improvements manually. This works well for small lakes (under 50 tables) with predictable ingestion patterns and dedicated engineering time. It requires scheduling, monitoring, and iterating on maintenance scripts — but every operation is transparent and under your control.

Step 1: Diagnose scan amplification

Before optimizing, measure. Query Iceberg's metadata tables to understand current file counts, sizes, and partition distribution:

sql

1-- Count files and average size per partition2SELECT3  partition,4  COUNT(*) AS file_count,5  SUM(file_size_in_bytes) / 1024 / 1024 AS total_size_mb,6  AVG(file_size_in_bytes) / 1024 / 1024 AS avg_file_size_mb7FROM "my_database"."my_table$files"8GROUP BY partition9ORDER BY file_count DESC10LIMIT 20;

sql

1-- Check snapshot accumulation2SELECT3  snapshot_id,4  committed_at,5  operation,6  summary7FROM "my_database"."my_table$snapshots"8ORDER BY committed_at DESC9LIMIT 50;

sql

1-- Identify tables with excessive manifests2SELECT3  path,4  length AS manifest_size_bytes,5  added_data_files_count,6  existing_data_files_count7FROM "my_database"."my_table$manifests"8ORDER BY added_data_files_count DESC;

Key indicators of Athena cost inflation:

Average file size below 64 MB → small file problem
File count per partition above 100 → fragmentation
More than 50 snapshots → expiration needed
Manifest count above 200 → manifest rewrite needed

Step 2: Compact small files with OPTIMIZE

Athena's OPTIMIZE statement performs bin-pack compaction — merging small files into larger ones without changing sort order. This is the highest-impact single operation for reducing scan costs.

sql

1-- Basic bin-pack compaction2OPTIMIZE my_database.my_table REWRITE DATA USING BIN_PACK;

For large partitioned tables, Athena limits OPTIMIZE to 100 partitions per execution. Use a WHERE clause to compact in batches:

sql

1-- Compact specific date partitions2OPTIMIZE my_database.my_table3REWRITE DATA USING BIN_PACK4WHERE event_date >= DATE '2026-07-01'5  AND event_date < DATE '2026-08-01';

Configure target file sizes via table properties before running OPTIMIZE:

sql

1-- Set compaction thresholds2ALTER TABLE my_database.my_table SET TBLPROPERTIES (3  'optimize_rewrite_data_file_threshold' = '10',4  'optimize_rewrite_min_data_file_size_bytes' = '67108864',5  'optimize_rewrite_max_data_file_size_bytes' = '536870912',6  'write_target_data_file_size_bytes' = '268435456'7);

These properties tell Athena to:

Only rewrite partitions with 10+ files (optimize_rewrite_data_file_threshold)
Consider files under 64 MB as candidates for compaction
Cap output files at 512 MB
Target 256 MB per output file

After compaction, verify the improvement:

sql

1-- Verify file consolidation2SELECT3  partition,4  COUNT(*) AS file_count,5  AVG(file_size_in_bytes) / 1024 / 1024 AS avg_file_size_mb6FROM "my_database"."my_table$files"7GROUP BY partition8ORDER BY file_count DESC9LIMIT 10;

Step 3: Clean up with VACUUM

Athena's VACUUM command performs snapshot expiration and orphan file removal in a single operation — free of Athena scan charges:

sql

1-- Expire snapshots and remove orphan files2VACUUM my_database.my_table;

Configure retention before running VACUUM:

sql

1-- Set retention: keep 7 days of snapshots, minimum 5 snapshots2ALTER TABLE my_database.my_table SET TBLPROPERTIES (3  'vacuum_max_snapshot_age_seconds' = '604800',4  'vacuum_min_snapshots_to_keep' = '5',5  'vacuum_max_metadata_files_to_keep' = '100'6);7 8VACUUM my_database.my_table;

Important constraints:

VACUUM can only delete up to 20,000 objects per execution — run repeatedly on tables with heavy accumulation
Always run VACUUM after OPTIMIZE: compaction creates new files and orphans old ones; VACUUM cleans up the orphans
VACUUM is free (no scan charges) — run it aggressively

Step 4: Enable partition pruning and column statistics

Proper table design enables Athena to skip data before scanning. Use hidden partitioning with Iceberg's transform functions:

sql

1-- Create table with hidden partitioning2CREATE TABLE my_database.events (3  event_id STRING,4  user_id BIGINT,5  event_type STRING,6  event_timestamp TIMESTAMP,7  payload STRING8)9PARTITIONED BY (day(event_timestamp), bucket(16, user_id))10LOCATION 's3://my-bucket/warehouse/events'11TBLPROPERTIES (12  'table_type' = 'ICEBERG',13  'format' = 'parquet',14  'write_compression' = 'zstd'15);

Always include partition columns in WHERE clauses — Athena uses these to eliminate entire partitions before scanning:

sql

1-- Partition-pruned query: only scans one day2SELECT event_type, COUNT(*) AS cnt3FROM my_database.events4WHERE event_timestamp >= TIMESTAMP '2026-07-15 00:00:00'5  AND event_timestamp < TIMESTAMP '2026-07-16 00:00:00'6  AND user_id = 123457GROUP BY event_type;

Enable Parquet column indexes for page-level pruning:

sql

1-- Enable column indexing for finer-grained pruning2ALTER TABLE my_database.my_table SET TBLPROPERTIES (3  'use_iceberg_parquet_column_index' = 'true',4  'use_iceberg_statistics' = 'true'5);

Column indexes enable Athena to skip individual pages within row groups — beyond file-level pruning. This is most effective on sorted columns where values within each page form tight ranges.

Step 5: Apply sort order with Spark

Athena's OPTIMIZE only supports bin-packing — it cannot re-sort data. For sort-based compaction that enables effective file skipping, use Spark on EMR or Glue:

sql

1-- Sort compaction via Spark (EMR/Glue)2CALL system.rewrite_data_files(3  table => 'my_database.my_table',4  strategy => 'sort',5  sort_order => 'event_date ASC NULLS LAST, user_id ASC NULLS LAST',6  options => map(7    'target-file-size-bytes', '268435456',8    'max-concurrent-file-group-rewrites', '10'9  )10);

sql

1-- Z-order for multi-dimensional query patterns2CALL system.rewrite_data_files(3  table => 'my_database.my_table',4  strategy => 'sort',5  sort_order => 'zorder(event_date, user_id, event_type)',6  options => map(7    'target-file-size-bytes', '268435456',8    'rewrite-all', 'true'9  )10);

After sorting, verify that file statistics have tightened:

sql

1-- Check column statistics bounds per file (via Spark)2SELECT3  file_path,4  lower_bounds,5  upper_bounds,6  file_size_in_bytes / 1024 / 1024 AS size_mb7FROM my_database.my_table.files8ORDER BY lower_bounds9LIMIT 20;

Step 6: Rewrite manifests

Manifest fragmentation slows planning and reduces pruning efficiency. Consolidate via Spark:

sql

1-- Rewrite manifests (Spark on EMR/Glue)2CALL system.rewrite_manifests('my_database.my_table');

This consolidates many small manifest files into fewer large ones, reducing the number of S3 GETs during planning and improving Athena's ability to evaluate file-level statistics efficiently.

Step 7: Schedule maintenance with Athena workgroups

Use AWS Step Functions or EventBridge Scheduler to automate the OPTIMIZE + VACUUM cycle:

sql

1-- Example: daily maintenance sequence (executed via Step Functions)2-- Step 1: Compact recent partitions3OPTIMIZE my_database.my_table4REWRITE DATA USING BIN_PACK5WHERE event_date >= CURRENT_DATE - INTERVAL '7' DAY;6 7-- Step 2: Clean up8VACUUM my_database.my_table;

Set workgroup-level data scan limits to prevent runaway queries from exploding costs:

sql

1-- In Athena console or via AWS CLI:2-- Per-query scan limit: 100 GB3-- Workgroup-level daily limit: 10 TB4aws athena update-work-group \5  --work-group analytics \6  --configuration-updates '{7    "BytesScannedCutoffPerQuery": 107374182400,8    "RequesterPaysEnabled": false9  }'

Manual path: limitations

The manual approach works but has structural constraints that compound at scale:

Athena OPTIMIZE is bin-pack only — no sort-based compaction, which means Spark/EMR is required for the highest-impact optimization (sort order)
100-partition limit per OPTIMIZE — large tables require scripted batch execution
20,000-object limit per VACUUM — tables with months of accumulation need repeated runs
No cross-table coordination — each table needs its own scheduling logic
No health-driven triggering — fixed schedules compact healthy tables unnecessarily and miss degraded tables between runs
Spark cost for sort compaction — EMR clusters at $50+/TB for sort rewrites may negate the Athena savings on smaller tables

For lakehouses with 50+ tables, the operational burden of maintaining individual schedules, monitoring health signals, and coordinating Spark jobs across catalogs quickly exceeds the savings. This is where Path 1 delivers its value — not replacing the operations, but automating the sequencing, monitoring, and execution at lake scale. For a comprehensive view of optimizing Iceberg performance across all engines, see Optimizing Iceberg lakehouse performance.

Measuring the impact

Whichever path you choose, track these metrics to quantify Athena cost reduction:

Metric	Before optimization	After optimization	Impact
Avg files scanned per query	4,000–10,000	50–200	20–50× reduction
Avg data scanned per query	500 GB – 1 TB	30–80 GB	10–20× reduction
Cost per query (selective)	$2.50 – $5.00	$0.15 – $0.40	85–95% savings
Daily Athena spend (50 queries)	$125 – $250	$7.50 – $20	90% reduction
Monthly Athena bill	$3,750 – $7,500	$225 – $600	80–92% savings

Production results showing 95% faster compaction, 12x faster queries, and 80% cost reduction — Production results: 95% faster compaction, 12× faster queries, 80% cost reduction.

The numbers are consistent across production deployments: 80% or greater Athena cost reduction from the combination of compaction (fewer files), sort order (effective file skipping), and routing (moving heavy scans off Athena entirely). The difference between Path 1 and Path 2 is not the magnitude of savings — it is the operational cost and reliability of maintaining them over time.

Conclusion

Athena's $5/TB pricing model is simple, but the underlying mechanics are not. Table structure — file count, file size, sort order, manifest health, and snapshot hygiene — determines whether Athena reads 50 GB or 5 TB for the same logical query. The path from expensive to optimized is clear: compact small files, sort on query-relevant columns, expire dead snapshots, rewrite fragmented manifests, and route heavy workloads to cheaper engines.

The manual path gives you full control with standard Athena SQL and Spark procedures. The autonomous path with LakeOps eliminates the operational overhead — continuous health monitoring, intelligent maintenance sequencing, and a compaction engine that runs at 5% of Spark's cost. Both paths lead to the same result: Athena bills that reflect your actual data needs rather than your table's structural debt.

Whichever path you choose, start with measurement. Query $files and $snapshots metadata. Calculate your current scan amplification ratio. Run OPTIMIZE on your worst table and measure the before/after scan volume. The savings are immediate, substantial, and directly visible on the next Athena invoice.