Iceberg Lake for Analytics Data: A Guide
Making Apache Iceberg tables faster and saving cost for BI, ad-hoc queries, and aggregation pipelines — optimizing every layer of the stack.
Data platform teams adopt Apache Iceberg for its open format guarantees — ACID transactions, schema evolution, hidden partitioning, and engine-agnostic access. But adopting the format is not the same as making it perform well for analytics.
Analytics workloads are structurally different from ETL. A streaming pipeline cares about write throughput. A dashboard cares about how fast it can skip irrelevant data and return filtered results. These goals conflict at the physical layer, and Iceberg tables that serve both will degrade for the readers unless you actively maintain them.
Here’s a nice Reddit thread that sums things up:
This guide covers the full optimization surface for data engineers running BI dashboards, ad-hoc SQL, and aggregation jobs on Iceberg. It goes deeper than “run compaction” — covering query planning internals, partition design decisions, metadata lifecycle, delete-file economics, multi-engine cost routing, and continuous maintenance sequencing. It also shows methods for optimizing these procedures by hand or using a smart Iceberg control plane that does it autonomously and provides better results with added capabilities such as query-aware compaction, coordinated ops, lake-wide observability and monitoring, and even multi-engine routing.
Learn more here:
How Iceberg resolves an analytics query: the four-stage planning pipeline
Before your query engine reads a single Parquet byte, Iceberg’s planner executes a metadata-only pipeline that determines which files to open. Understanding this pipeline is essential because every optimization technique in this guide targets one of its stages.
Stage 1: Snapshot resolution. The engine contacts the catalog (Glue, REST, Nessie, etc.) to locate the current metadata file, then resolves the active snapshot. This is a constant-time operation — O(1) remote calls regardless of table size. Time-travel queries simply resolve a different snapshot ID.
Stage 2: Manifest-list pruning. The snapshot points to a manifest list. Each entry in this list carries partition-level summary statistics (minimum and maximum values) that allow the engine to skip entire manifest files before reading any data.
Stage 3: Manifest-file pruning (data skipping). For surviving manifests, the planner reads per-file column statistics — min, max, null count, and optionally NDV — for each data file. Files whose column ranges cannot overlap the predicate are skipped. This is where sort order pays off: sorted files have tight min/max bounds, so predicates eliminate most files. Unsorted files have wide, overlapping ranges and pruning fails.
Stage 4: Row-group filtering. Inside each surviving Parquet file, row-group (column chunk) statistics and optional Bloom filters perform one final pruning pass. Tight sort order within files makes row-group boundaries meaningful.
Production measurements show this pipeline eliminating 99.7% of partitions at stage 2, 60–95% of remaining files at stage 3, and 80–99% of row groups at stage 4 — before any actual data processing begins.
If you use an Iceberg control plane, partitioning strategies and metadata planning are analyzed in the context of your actual queries and tables and optimized — manually based on recommendations, or on autopilot.
Cloudera recently contributed manifest caching to Apache Impala that achieved 12× faster query planning by avoiding repeated metadata reads across concurrent queries.
The takeaway: every technique below targets one or more of these four stages. If your optimization does not improve pruning at some stage, it is not helping analytics.
Diagnosing performance problems before changing anything
Optimization without measurement is guesswork. Before modifying partition specs, running compaction, or changing sort order, collect these signals per table:
File count relative to data volume. A 500 GB table should have roughly 1,000–4,000 files at 128–512 MB each. If it has 47,000 files, planning alone costs seconds of LIST/GET operations against object storage. Every engine pays this tax.
Average file size distribution. Fact tables serving interactive dashboards need files in the 128–512 MB range. Files under 64 MB indicate streaming micro-batches that never got compacted. Files over 1 GB may reduce parallelism for concurrent queries.
Manifest count and depth. Each append creates new manifest files. After thousands of commits, the manifest tree fragments. AWS Prescriptive Guidance recommends keeping manifests under 50–100 per table for interactive workloads. Above that, planning time grows linearly.
Snapshot accumulation. Every commit creates a snapshot. Tables with continuous ingest can accumulate thousands. Each snapshot references manifest lists that the planner must traverse. Expired snapshots also prevent garbage collection of old data files.
Delete file ratio. Merge-on-read tables accumulate position and equality delete files. Each read must reconcile these deletes in memory. Equality deletes are particularly expensive — the current algorithm has O(M × N) complexity (M delete entries × N data records). A table with 23,000 delete files will add seconds to every query regardless of selectivity.
Partition skew. One partition with 8,000 files while others have 200 creates a concurrency bottleneck. Morning dashboard loads hit the hot partition simultaneously.
Filter column alignment. If your BI tool filters on event_date, region, and product_id, but your partition is days(created_at) and sort order is user_id, you are paying for full partition scans on every dashboard refresh.
Tools like LakeOps surface these signals automatically across all tables in a catalog — file count trends, manifest depth, partition skew, delete-file growth — so you can prioritize which tables to optimize first.
Partition design: match how analysts filter, not how writers produce
Partitioning is the highest-leverage optimization for analytics because it operates at stage 2 of the planning pipeline — eliminating entire manifest files before any per-file statistics are evaluated.
The core principle: partition by the column that appears in the WHERE clause of 80%+ of your analytics queries. For event-driven analytics, this is almost always a time column with days() or months() transform. For multi-tenant SaaS analytics, it might be bucket(tenant_id, 64).
Hidden partitioning eliminates user errors. Unlike Hive, Iceberg users filter on the source column (WHERE event_time >= '2026-05-01') and the engine automatically maps to the partition transform. This eliminates the class of accidental full-table scans caused by users forgetting to include the partition column in their query.
Partition evolution changes strategy without rewriting data. In traditional data lakes, changing from monthly to daily partitioning required reading and rewriting every file — a petabyte-scale operation taking hours. Iceberg tracks which partition spec applies to which data file in metadata. New writes use the new spec; old data stays readable with the old spec. The planner handles both transparently.
When to add a secondary partition column. Only bucket on a second column if each resulting bucket will contain multiple target-sized files. Partitioning on bucket(user_id, 1024) on a table with 50 GB per day creates 1,024 partitions with ~50 MB each — too small. You want 3–10 files per partition minimum to give compaction room to consolidate.
Anti-patterns that hurt analytics:
- Partitioning on a column BI tools never filter on
- More than 10,000 active partitions with fewer than 3 files each (metadata explosion)
- Raw string partitions like
country='United States'instead of bucket transforms - Partitioning on high-cardinality columns like
user_idfor billion-row tables
File sizing: the single fastest win for dashboard latency
File sizing targets stage 3 of the planning pipeline and directly controls the object-store GET cost per query.
For autnomlus file organization optimization, see this docs page. LakeOps will run compaction based on actual queries for each table to cut query times and costs, coordinate compaction with other ops, and run it on a Rust based engine that’s 95% faster and more efficient. See below or learn more here.
Every data file costs at least one GET to read its Parquet footer during planning, plus one or more GETs to read actual column chunks. A table with 47,000 files requires 47,000 footer reads before any data processing begins. At 5–15ms per S3 GET (even with connection reuse), that is 235–705 seconds of pure metadata overhead.
Production benchmark: consolidating 47,000 files into 280 files at ~512 MB average reduced query time from 52s to 5.8s on the same SQL, engine, and data volume. That is a 9× improvement from file consolidation alone — no sort order change, no partition redesign.
Recommended file size targets by workload:
- Interactive BI (sub-second targets): 128–256 MB per file, 16–32 MB row groups
- Ad-hoc analyst exploration: 256–512 MB per file, 32–64 MB row groups
- Large aggregation/scan jobs: 512 MB–1 GB per file, 64–128 MB row groups
The Iceberg table property write.target-file-size-bytes controls writer behavior, but writers alone cannot fix the problem. Streaming writers (Flink, Kafka Connect, Spark Structured Streaming) produce small checkpoint files by design — often 1–32 MB. Only compaction consolidates these into analytics-ready sizes.
Row group sizing within files. Parquet row groups are the unit of stage-4 pruning. Too large (>128 MB) and you cannot skip within a file. Too small (<8 MB) and Parquet metadata overhead grows. Match row group size to your typical query selectivity — if dashboards filter to ~10% of a file’s rows, 16–32 MB row groups give the planner enough granularity to skip 90% of byte ranges.
Sort order and Z-order: making statistics meaningful
File-level min/max statistics (stage 3) only help if values within each file occupy a narrow range. An unsorted file containing dates from January through December has min=January, max=December — a WHERE clause on any single month cannot eliminate it. A sorted file containing only March data has min=March, max=March — the planner skips it immediately for queries on other months.
Sort compaction rewrites files so that rows are physically ordered by one or more columns. The effect on analytics is dramatic: sorted fact tables scan 51–95% less data per query than unsorted equivalents, depending on predicate selectivity and cardinality.
Z-order interleaves the binary representations of multiple column values, creating a space-filling curve that enables file pruning across two or more dimensions simultaneously. Use Z-order when:
- Two dimensions are equally important in query predicates (e.g.,
pickup_locationanddropoff_locationfor ride analytics) - Neither column alone dominates the workload
- You cannot afford separate sorted copies for different access patterns
Z-order provides balanced multi-dimensional skipping but is less tight per individual column than pure sort. For single-dimension dashboards, prefer sort on the primary filter column.
How to choose sort columns in practice: Pull 30 days of query telemetry. Rank columns by frequency in WHERE, JOIN ON, and GROUP BY. Sort on the top 1–2. If your workload shifts — a new dashboard starts filtering on product_category that was previously cold — the sort order needs updating.
This is where automated query telemetry collection pays off. Platforms like https://lakeops.dev collect cross-engine query patterns — every WHERE, JOIN, and GROUP BY across Trino, Spark, Snowflake, Athena, and DuckDB — and automatically recommend or apply sort orders that maximize data skipping for the actual workload. Production deployments using this feedback loop report 12× average query acceleration without manual sort-key selection.
Branch-based layout testing. Before committing to a costly full-table sort rewrite, create an Iceberg branch, sort a subset, and replay real queries against both layouts. Compare bytes scanned and planning time. This avoids the expensive mistake of sorting on the wrong columns.
Metadata lifecycle: manifests, snapshots, and Puffin statistics
Metadata is what makes Iceberg’s planning pipeline fast — but metadata itself needs maintenance.
As mentioned, adding a control plane will autonomously fix, optimize, and coordinate these operations for you with guardrails and safety measures.
Learn more:
Manifest consolidation. Each append, compaction, and schema change produces new manifest files. A table with daily streaming ingest can accumulate hundreds of manifests within weeks. The RewriteManifests action consolidates fragmented manifest trees so the planner reads fewer files during stage 2–3. Schedule manifest rewrites after compaction to align metadata with the physical file layout.
Teams report planning latency dropping from multiple seconds to sub-second after consolidating 200+ manifests into 20–30 well-structured ones.
Snapshot expiration. Analytics tables with 5-minute ingest intervals create ~288 snapshots per day, ~8,640 per month. Each snapshot references a manifest list. The planner must traverse the current snapshot’s manifest list, but stale snapshots prevent garbage collection and inflate catalog metadata.
Retain snapshots for your audit/time-travel SLA (typically 7–30 days for analytics tables), then expire aggressively. One production table held 120 TB of data files referenced only by expired-but-not-cleaned snapshots — pure storage cost with zero query value.
Puffin statistics (NDV sketches and Bloom filters). Beyond min/max, Iceberg supports Puffin sidecar files containing:
- NDV (number of distinct values) sketches — enable better join-order selection for star-schema analytics
- Bloom filters — enable definitive “not present” answers for high-cardinality join/filter keys
Compute Puffin statistics after layout stabilizes (post-compaction). They are most valuable for:
- Join-heavy dashboards (star schema: one fact table joining 5–10 dimensions)
- High-cardinality ID lookups (customer_id, transaction_id)
- Engines that support Bloom filter pushdown (Trino, Spark 3.4+)
Continuous compaction: why nightly cron jobs fail analytics SLAs
The most common compaction mistake is treating it as a batch job. A nightly compaction at 2 AM does nothing for the analyst whose dashboard runs at 9 AM on data that arrived at 7 AM. By the time compaction finishes, thousands of small files from the morning ingest have already degraded every query.
Get Jonathan Saring’s stories in your inbox
Join Medium for free to get updates from this writer.
If you’re using a control plane like LakeOps, for example, compaction is:
- Done based on actual queries for each table
- Coordinated with other ops to optimize efficiency and cost
- Runs 95% faster on a Rust engine to cut time and costs further
You can set it on auto-pilot too, so it runs when it really needs to, choosing the optimal strategy for each table and coordinated with other maintenance operations to max efficency.
Learn more here:
Event-driven compaction triggers solve this:
- File count in a partition exceeds threshold → trigger binpack
- Average file size drops below target → trigger consolidation
- Delete-file ratio exceeds 10% → trigger compaction with delete merging
- Sort order stale relative to current query patterns → trigger sort compaction
Choosing between binpack, sort, and Z-order compaction:
Binpack is cheapest — it only consolidates small files into larger ones without changing row order. Use for streaming-ingest tables where file sizing is the primary bottleneck and filter columns are already well-partitioned.
Sort compaction rewrites files in column order. It is more expensive but produces the tightest min/max statistics. Use for read-heavy fact tables (read:write ratio > 50:1) with stable, well-known filter patterns.
Z-order is appropriate for multi-dimensional analytics tables. Higher rewrite cost than sort, balanced skipping across dimensions.
Compaction engine efficiency matters at scale. When you operate hundreds of tables, compaction cost becomes a material budget line. Recent production benchmarks on a 200 GB analytics table:
- Spark sort compaction: ~1,612 seconds, ~$1.54
- LakeOps Rust-based engine (Apache DataFusion): ~221 seconds, ~$0.21
That is 86% lower cost and 7.3× faster — relevant when compaction runs across your entire lake weekly. The LakeOps engine also demonstrates self-optimization: on three consecutive runs of the same 1.2 TB table, runtime decreased from 22 minutes to 11, learning data distribution patterns without configuration changes. For detailed engine architecture and benchmarks, see https://lakeops.dev/blog/efficient-lakehouse-compaction-at-scale
Across production deployments, the LakeOps compaction engine achieved 81.1% file reduction (101,223 → 19,170 files) on 5.5 TB with peak throughput of 2,522 MB/s.
When compaction makes the warehouse redundant. Teams running dashboards on continuously compacted, sorted Iceberg tables report that the performance gap between a lakehouse and a dedicated analytics warehouse disappears for read-heavy workloads. Some deployments serve BI dashboards and embedded analytics directly from the Iceberg lake — no CDC pipeline, no materialized views, no separate serving layer — because continuous sort compaction keeps latency in the sub-second range on Trino and DuckDB.
Delete files: the hidden tax on every analytics query
Merge-on-read (MOR) is Iceberg’s strategy for handling updates and deletes without immediately rewriting data files. It creates lightweight delete files that are reconciled at read time. For analytics, this is a silent performance killer.
Position deletes record (file_path, row_position) pairs. They are compact and relatively cheap to apply — the reader skips specific row positions. But they accumulate: each UPDATE or DELETE adds new delete files, and the reader must load all of them.
Equality deletes record column values (typically primary keys) that identify deleted rows. They are more flexible (writers do not need to know physical positions) but far more expensive to apply. The current reconciliation algorithm is O(M × N) — every data record is evaluated against every delete predicate. A table with 10,000 equality delete files becomes catastrophically slow for any analytics query.
Production example: a CDC-fed fact table accumulated 23,000+ delete files covering hundreds of millions of rows. Narrow dashboard queries that should scan 50 MB of data were taking 8+ seconds because delete reconciliation dominated execution time.
Mitigation strategy for analytics tables:
- Monitor delete-file count per partition. Set alerts at thresholds (e.g., >100 delete files per partition).
- Run RewritePositionDeleteFiles when position delete counts spike — this is lightweight and does not require full data rewrite.
- Schedule sort compaction with delete merging on analytics tables receiving daily CDC updates. This physically applies deletes, producing clean Parquet files with zero read-time reconciliation overhead.
- Sequence compaction after snapshot expiration — do not compact files that are about to be garbage-collected.
The long-term solution being proposed in the Iceberg community involves inverted indexes that map column values to row positions, reducing equality delete complexity from O(M × N) to O(N). Until that ships, aggressive compaction on MOR analytics tables is the only path to consistent dashboard performance.
Multi-engine routing: same table, different economics
One of Iceberg’s strongest advantages for analytics is engine-agnostic access — the same table is queryable from Trino, Spark, Snowflake, DuckDB, Athena, StarRocks, and others. But each engine has different cost structures:
- DuckDB: ~$0.01/query at ~0.5s p50 (single-node, memory-efficient)
- Trino: ~$0.03/query at ~1.8s p50 (distributed, elastic)
- Snowflake: ~$0.08/query at ~2.1s p50 (managed, credit-based)
- Athena: $/TB scanned (favors selective queries on sorted tables)
At 10,000 queries per day, defaulting everything to Snowflake instead of routing selective queries to DuckDB costs an additional 700/day — 700/day — 255K/year in pure waste. This is the same class of problem covered in depth in this article.
Routing policies for analytics workloads:
- Latency-critical (executive dashboards, embedded analytics): route to the fastest engine for selective SQL. On well-compacted, sorted tables, DuckDB and Trino often outperform heavier engines.
- Cost-optimized (scheduled reports, wide aggregations): route to the cheapest engine within a relaxed SLA. Athena for scan-based pricing on infrequent queries; Spark for batch aggregations.
- Throughput (peak concurrent load): distribute sessions across engines to avoid single-engine queueing during morning dashboard refresh spikes.
Routing depends on table optimization. A table with 47,000 small, unsorted files cannot route to DuckDB effectively — it will OOM or time out. Compaction and sort order are prerequisites for routing to work. Once tables are well-maintained, “cheap” engines become viable for 80%+ of analytics queries. For the full multi-engine performance stack, see https://lakeops.dev/solutions/iceberg-lakehouse-performance
The maintenance sequence: order matters
Individual optimizations decay without continuous execution. The correct maintenance sequence for analytics tables — where each step depends on the previous one completing — is:
1. Expire snapshots. Release dereferenced files from the manifest list. This shortens planning paths and unblocks garbage collection. Without expiration, orphan cleanup and compaction waste effort on files that are already logically dead.
2. Remove orphan files. Reclaim storage for data files that no snapshot references. Run after expiration so newly dereferenced files are caught. Skipping this step means compaction may process files that should have been deleted.
3. Compact data files. Binpack or sort based on table role. Apply delete files during compaction so downstream reads hit clean Parquet. Must run after orphan cleanup to avoid compacting dead objects.
4. Rewrite manifests. Consolidate the manifest tree to reflect the new physical layout. After compaction changes file boundaries, old manifests reference obsolete file sets. Rewriting aligns metadata with reality.
5. Compute statistics. Refresh Puffin blobs, NDV sketches, and Bloom filters. Must run after compaction and manifest rewrite because statistics should reflect the current physical layout, not the pre-compaction state.
6. Observe and alert. Monitor file count trends, manifest depth, partition skew, and delete-file growth. Trigger the loop again when signals cross thresholds.
Running these out of order wastes compute. Compacting before expiration processes files that will be garbage-collected. Computing statistics before compaction produces stats that become stale immediately. Rewriting manifests before compaction creates manifests that do not match the files compaction will produce.
For the full runbook with sequencing rationale, see this article:
Manual Airflow DAGs rarely keep pace. Across hundreds of tables with different ingest rates, compaction needs, and retention requirements, manually maintaining correct sequencing and per-table tuning is a full-time job. A managed Iceberg control plane (https://lakeops.dev/solutions/managed-iceberg) that sequences these operations per table — event-driven triggers, per-table tuning, and full audit logs — is how platform teams stop firefighting dashboard latency tickets without adding another nightly script. LakeOps implements this loop as an autonomous system — observing which tables degrade between runs, which sort orders are stale, and which partitions have drifted from target file size, then acting in the correct sequence per table. Production deployments manage 786+ tables across 112+ PB autonomously, with every operation logged, reversible, and auditable.
Measuring success: the metrics that matter for analytics
After implementing optimizations, track these metrics per table over time:
Query-side metrics:
- p50 and p95 query latency (segmented by workload type: dashboard vs ad-hoc vs aggregation)
- Bytes scanned per query (should decrease as sort/partition effectiveness improves)
- Planning time (should decrease with manifest consolidation and snapshot expiration)
- Files opened per query (should track file count reduction from compaction)
Table-side metrics:
- Active data file count and average file size
- Manifest file count
- Snapshot count and age distribution
- Delete file count per partition
- Partition skew ratio (max files in any partition / average)
Cost metrics:
- Storage cost (should decrease with orphan cleanup and snapshot expiration)
- Compute cost per query (should decrease with routing optimization)
- Maintenance cost (compaction, manifest rewrite — should be <5% of query cost savings)
On well-maintained analytics tables, expect: 5–12× query latency improvement, 50–90% reduction in bytes scanned, sub-second planning time, and 60–80% lower per-query cost through routing optimization.
Summary
Iceberg gives you the right primitives for open analytics — but primitives are not performance. The physical state of your tables determines whether dashboards load in 500ms or 50s.
The optimization surface has clear layers: partition for how analysts filter, size files for your latency target, sort by columns that appear in WHERE and JOIN, keep metadata lean, compact continuously (not nightly), collapse delete files before they tax reads, route queries to the cheapest viable engine, and automate the maintenance sequence so performance does not regress between sprints.
Each layer compounds the next. Sorted partitions make cheap engines viable. Cheap engines make scan reduction financially visible. Automation keeps all of it running while you ship features instead of maintenance scripts.
For teams operating at lake scale — hundreds of tables, multiple catalogs, continuous ingest — the path from manual optimization to autonomous maintenance is the difference between a data platform that needs constant firefighting and one that runs itself. LakeOps implements this full stack: catalog-level observability, query-driven sort optimization, Rust-native compaction, cross-engine routing, and sequenced maintenance policies — delivering 12× average query acceleration and up to 80% cost reduction across production deployments.
The performance ceiling of your Iceberg lake is not your query engine. It is the physical state of your tables. I hope you found it useful and thanks for reading 🙏
