Data Apache Lakehouse

Apache Iceberg Lakehouse

Domain skill for building and operating Python-first Apache Iceberg lakehouses: PyIceberg + Polars/DuckDB medallion writes, REST/Glue catalog choice, and the architectural discipline that keeps incremental jobs incremental.

> Verified: 2026-05-28 against PyIceberg 0.11.1, pyiceberg-core 0.8.0, Iceberg spec V2/V3. > Claims tagged [v0.11] and the PyIceberg capability matrix (in > [references/pyiceberg-capabilities.md](references/pyiceberg-capabilities.md)) are > version-sensitive — re-check against your installed version (pip show pyiceberg) before trusting > them; the matrix lists how to verify each in seconds. Everything else (Iceberg internals, the > architectural principles) is format/architecture-level and ages slowly.

> Part of the data skill family. The cross-cutting pipeline principles (idempotency, watermarks, schema fencing, resilience, bounded memory) are stated generally in the data hub; this skill is their Apache Iceberg expression. For consuming or serving APIs around the lakehouse, see data-api.

When to invoke this skill

• Designing a new pipeline, layer, or transform in a bronze/silver/gold lakehouse.
• Adding or modifying a gold aggregate, analytics view, or derived table.
• Debugging incremental jobs that get slower as data grows, scheduled jobs that overrun their window, lock contention, memory pressure, or "metadata bigger than data."
• Reviewing any PR that touches a layer boundary (bronze→silver, silver→gold) or the catalog config.
• Choosing or swapping a catalog backend (Glue, REST, Polaris, Lakekeeper, Nessie, JDBC, Hive).
• Picking compaction / expire-snapshots / orphan-file-removal cadence for a table.
• Deciding between Iceberg, Delta, and Hudi for a new lakehouse.
• Investigating a bad publish — finding the right snapshot to roll back to, auditing what a backfill landed.

If you're about to add a global lock, a "rebuild from full history" path, a Parquet mirror next to an Iceberg table, a cron that recomputes a snapshot from scratch, or a write that bypasses the catalog — stop and read this skill first.

Medallion rules

1. Bronze — raw ingest, minimal transforms, append-friendly.
2. Silver — typed, deduped, schema-validated; fail closed on drift when correctness depends on it.
3. Gold — business aggregates; read silver/bronze only; no back-writing to lower layers in the same job.

Iceberg internals worth knowing (the parts that change how you write code)

The skill assumes you know the layered model (datafiles → manifests → manifest list → metadata.json → catalog). These are the facts that actually change PyIceberg-writer behavior:

• Pruning happens twice, at different layers. Manifest entries carry per-column lower/upper bounds, evaluated at planning time (cheap Avro reads, zero datafiles touched). Parquet row groups carry their own min/max, evaluated at scan time after the file is opened. A predicate on a sorted/partitioned column gets both layers; a predicate on an unsorted high-cardinality column gets ~no manifest pruning. This is why sort order matters: it tightens manifest bounds.
• Manifest lists carry partition bounds and per-snapshot sequence numbers. Diffing two snapshots is a manifest-list-only walk — you never need to open datafiles to find what changed. This is the underlying machinery of incremental reads even when PyIceberg only exposes it indirectly via watermark columns.
• last-sequence-number in metadata.json is the OCC primitive. Every commit bumps it. Equality deletes are scoped by it (a delete with sequence N only applies to data files with sequence .. The clean form is one transaction — with target.transaction() as tx: tx.append(batch); tx.setproperties({"wm.sales.silver": str(newwm)}) — so data and watermark land in a single atomic commit and can never disagree. A two-commit form (data, then a separate property bump) is acceptable **only** when every write is an idempotent upsert/partition-overwrite, so a crash between the two commits merely re-processes the last window. It's metadata-only, free to read at planning time, and needs no extra sidecar table. Full recipe: [references/single-host-operations.md`](references/single-host-operations.md).

Test: if the upstream table grew 10× tomorrow, would this transform's runtime grow 10×? If yes, it lacks a watermark.

Every aggregate has a declared shape

Each gold/analytics aggregate is one of three shapes, and the shape is named in the module:

• Append-only ledger — immutable primary key; append on the delta dedupes by key.
• Point-update keyed snapshot — MERGE INTO on the keys touched by the delta. Cost is O(delta), not O(history).
• Windowed scan — bounded source.filter(ts > now - window) with partition pruning, on its own cadence.

"Full rebuild" is none of these. If you can't put one of the three labels on an aggregate, the architecture is wrong — don't slow the cadence to mask the cost, reshape the aggregate.

Test: can you write # shape: append | point_update | windowed_scan at the top of the aggregate's module without lying? If no, redesign.

Maintenance is owned, not hoped for

PyIceberg can now expire snapshots [v0.11] (table.maintenance.expire_snapshots()), but this is metadata-only — it does not physically delete the orphaned data files (see the capability matrix). It cannot compact or remove orphan files at all. Physical file GC and compaction are Spark procedures, Trino ALTER TABLE ... EXECUTE, or a managed service. Untended Iceberg tables silently grow until metadata reads dominate query time and small files defeat manifest pruning. The safe order is fixed: rewrite_data_files → expire_snapshots (≥3-day window) → remove_orphan_files (≥3-day window) → rewrite_manifests. Skipping the safety window is how you delete files a still-running query needs.

There are now three legitimate ways to own maintenance — pick one explicitly, don't leave it implicit:

1. External engine — Spark procedures or Trino EXECUTE optimize on a schedule. Most control; you operate the cluster.
2. Managed / zero-ops storage [2025] — the catalog or storage layer runs policy-driven compaction, snapshot expiration, and orphan-file removal on the backend, decoupled from your Python pipeline. Examples: Amazon S3 Tables (continuous compaction + expiration since 2025), Cloudflare R2 Data Catalog, Polaris-managed maintenance. This is the only option where expiry and physical GC both happen without you wiring a second job.
3. Hybrid — PyIceberg expire_snapshots for metadata hygiene from your pipeline, plus a scheduled external/managed job for the physical remove_orphan_files + compaction it cannot do.

On a single host with no cluster, the "external engine" is usually a systemd timer (or cron) that runs PyIceberg expire_snapshots plus a scheduled DuckDB/Spark compaction + orphan-removal step — a legitimate option 1, just hand-wired (see [references/single-host-operations.md](references/single-host-operations.md)).

Test: name the person/service, the cadence, and the command (or backend policy) that maintains this table — and confirm physical file GC is covered, not just metadata expiry. If any of those is "I'll figure it out," the maintenance does not exist.

Validation belongs on a branch, not in production

PyIceberg supports branches and tags since 0.8. Treat the main branch of an Iceberg table as published state; do risky work (large backfills, schema reshuffles, suspicious silver→gold rewrites) on a named branch, validate against it, then merge or discard. This is the write-audit-publish pattern expressed natively in Iceberg. A staging mirror that lives outside the catalog is a hidden interface; an Iceberg branch is a first-class one. Nessie adds multi-table atomic branch merges if you need them.

Test: if this promotion failed audit, could you discard it by dropping a branch? If the answer involves manually rewinding writes to the live table, it shouldn't have been on the live table.

File size is a quality dimension

Parquet file size sets the floor on every downstream query's planning and IO cost. Target ~256 MB; tolerate 128–512 MB; flag and compact anything systematically under 50 MB or over 1 GB. High-frequency writers earn a more aggressive compaction cadence; rarely-updated gold can tolerate days between compactions. The shape of the file layout is a contract the lake owes to readers, not an emergent property of however the writer happened to flush.

Compaction strategy follows query pattern, not folklore:

| Strategy | When | Cost | |---|---|---| | BinPack | Streaming SLA / unclustered reads acceptable | Fastest compaction | | Sort | One dominant filter column | Slower than BinPack; tight manifest bounds on the sort key | | Z-Order | Two or more equally important filter columns | Slowest; best multi-column file pruning |

Scope compaction to a window (where ts >= now - interval '1 hour') and turn on partial-progress-enabled so readers benefit sooner and large jobs don't OOM. Target file size 256–512 MB; keep the Parquet row group size (default 128 MB) dividing evenly into it.

Test: what is the p50 file size on this table right now? If you don't know, you don't know whether reads are healthy — go look before claiming the table is fine.

Catalogs are commodities; pick for latency and governance

The REST Catalog spec is the standardized interface, and every serious catalog speaks it. The backend is a swappable choice driven by two real properties: commit latency under your concurrency, and the governance/auth model you need.

• Apache Polaris — ASF TLP; donated by Snowflake; full RBAC + credential vending. Best self-hosted open-source option for multi-engine access control.
• Lakekeeper — Rust single-binary; OPA authorization; lowest commit latency. Best for latency-sensitive or resource-constrained deployments.
• Apache Nessie — git-branching catalog; required for multi-table atomic WAP and catalog-level rollback across multiple tables in one move.
• AWS Glue — managed; good for AWS-native shops; weak branch/tag story. Credentials follow the standard boto3 chain.
• JDBC — PostgreSQL/MySQL-backed; reasonable small-to-medium production catalog if you already operate a managed RDB. No multi-table transactions.
• Hive Metastore — only worth it if you already run one and intend to keep it. Not worth standing up new.
• Hadoop / file-system catalog — local dev only. Unsafe on S3 without a DynamoDB sidecar (no atomic rename).
• tabulario/iceberg-rest — REST reference implementation. Prototype only.
• SQLite (SqlCatalog) — local dev, and a legitimate single-host / single-writer production catalog (one VPS, one writer process per table): the commit pointer is a local .db file, so there's no network round-trip per commit. It has no cross-host CAS — never point multiple hosts or writers at it. In-memory: tests only. Config recipe (with an S3-compatible store like SeaweedFS/MinIO) in [references/single-host-operations.md](references/single-host-operations.md).

Catalog migration is a pointer move, not a data copy. Use the iceberg-catalog-migrator CLI (lives in the Project Nessie repo) to move tables between any of the above. Prefer migrate (transfers ownership, removes source) over register (leaves both pointing at the same data) — concurrent writes through two catalogs to the same table is silent corruption.

Python client code does not change when the backend does — that's the point.

Test: could you swap your catalog backend without touching any Python outside the catalog constructor and env vars? If no, find the leak — something is depending on backend-specific behavior.

Architectural red flags

When you see one of these, stop and audit — the table tells you which principle is probably broken.

| Smell | Likely principle violated | |---|---| | "Rebuild the snapshot from full history" | Watermark, declared aggregate shape | | read_parquet(full_curated_file) in a promote step | Watermark, single source of truth | | Parquet file next to an Iceberg table for the same data | Single source of truth | | Cron more frequent than the rebuild cost it triggers | Watermark, declared aggregate shape | | Global lock between independent pipelines to "prevent OOM" | All of the above (lock is the symptom, not the fix) | | Two transforms peak at large RSS and "fight for memory" | Watermark, declared aggregate shape | | Cryptic pyiceberg / arrow error far from the writer | Schema fence on the read side missing | | unique() happens "later" or "on read" instead of pre-upsert | Schema fence; PyIceberg upsert requires unique source rows | | One pipeline's failure cascades into skipped runs for unrelated pipelines | Pipeline isolation (and probably a global recompute upstream) | | "We'll compact later" with no scheduled job | Maintenance is owned | | Table metadata size approaches data size | Maintenance is owned (snapshot expiration missing) | | Catalog tightly bound to backend-specific quirks in client code | Catalogs are commodities | | Large backfill written directly to main | Validation belongs on a branch | | Parquet files systematically under 10 MB | File size is a quality dimension | | Equality deletes attempted from Python | PyIceberg can't write or read them — use Spark/Flink for MOR CDC, or upsert from Python | | High-cardinality identity partitioning (e.g., per-user partitions) | Use bucket(col, N) transform; identity partition explodes manifests | | Many tiny manifests; manifests().length distribution skewed small | Need rewrite_manifests; many small commits without manifest compaction | | HadoopCatalog on S3 with multiple writers | Catalog atomicity gap — switch to REST/Glue/Nessie/JDBC | | COUNT DISTINCT SLA assumed via puffin files | PyIceberg doesn't read/write puffin; design around it | | Schema evolution mid-CDC cycle | Changelog views span schema boundaries and break; pause CDC during evolution | | register used (not migrate) for catalog cutover with writers still active on old catalog | Two writers, no shared CAS — corruption path |

Running on a single host (bounded RAM, no cluster)

Much of this skill assumes you can reach for Spark/Trino. Plenty of real Iceberg lakehouses run on one box — a single VPS, one writer process per table, a few GB of RAM. The format supports this fine; the discipline is keeping peak memory bounded and proving it. Code for everything below: [references/single-host-operations.md](references/single-host-operations.md).

Peak memory is bounded by one batch, not one table

A PyIceberg append/overwrite of a 5 GB Arrow table needs 5 GB resident. The fix is to never hold the whole result set: stream a pyarrow.RecordBatchReader and append one batch per snapshot, so peak RAM is one batch regardless of total size. Use Polars scan_parquet(...).sink_* for transforms and scan_parquet(p).limit(0).collect().to_arrow().schema to get a schema without materializing. The cost is more snapshots — compact them on the maintenance pass.

Test: does this write's peak RSS depend on total row count, or on batch size? If it scales with the table, it isn't streaming.

Batch size adapts to the budget; it isn't a constant

A hardcoded max_files=50 either OOMs on a busy day or wastes RAM on a quiet one. Read the actual budget — cgroup v2 memory.current vs memory.max, or a *_MEMORY_MAX_GB env — and scale the decode/write batch between a floor and a cap by current headroom. Defer the heavy gold rebuild when RSS is already near budget rather than letting the OOM killer arbitrate.

Test: if you halve the host's RAM, does the pipeline shrink its batches and survive, or OOM unchanged?

Heavy stages run in their own process

A single long-lived

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: Evan-Kim2028
• Source: Evan-Kim2028/agent-skills
• License: MIT

Install and usage instructions live in the source repository linked above.