Lakehouse with Apache Iceberg skill for Claude Code

An Iceberg table is healthy when every engine reaches it through one catalog, queries prune by partition and file statistics without users knowing the layout, data files sit near the target size, snapshot history is kept exactly as long as someone needs time travel, and maintenance runs on a schedule rather than after an incident.

Does a lakehouse fit?

Situation	Recommendation
Several engines (Spark, Trino, Flink, Snowflake, DuckDB) must read and write the same data	Iceberg lakehouse
Data on object storage too large or costly to keep only in a warehouse	Iceberg lakehouse
Need ACID writes, schema evolution, or time travel over Parquet files	Iceberg (or Delta, see comparison)
One warehouse is the only consumer and data fits its pricing	Stay in the warehouse; Iceberg adds operations work
Tens of GB, one analyst, local files	DuckDB or Polars on Parquet (duckdb-analytics)
Low-latency point reads and frequent single-row updates (OLTP)	A transactional database, not a table format

Default to the warehouse until a concrete need for multi-engine access, open storage, or cost appears; then adopt Iceberg table by table.

First, inspect what exists

-- Spark SQL with the Iceberg runtime and catalog named `lake`
SHOW TBLPROPERTIES lake.db.orders;                 -- format-version, write modes, retention
SELECT count(*) AS files, round(avg(file_size_in_bytes)/1048576) AS avg_mb,
       sum(record_count) AS rows
FROM lake.db.orders.files;
SELECT count(*) AS snapshots, min(committed_at), max(committed_at) FROM lake.db.orders.snapshots;
SELECT partition, file_count, record_count FROM lake.db.orders.partitions
ORDER BY file_count DESC LIMIT 20;

Record: catalog type, engines and their Iceberg versions, format version, partition spec, average file size, snapshot count and age, and who relies on time travel.

Workflow

- [ ] 1. Confirm the lakehouse fits and list every engine that must read/write
- [ ] 2. Pick one catalog all engines support (REST by default)
- [ ] 3. Design schema, partition transforms, sort order, write modes
- [ ] 4. Create tables with explicit properties; load a sample; check pruning
- [ ] 5. Schedule maintenance: compaction, expire snapshots, orphan cleanup
- [ ] 6. Add health checks on metadata tables (file size, snapshot count)
- [ ] 7. Document retention and time-travel guarantees for consumers

Catalogs

The catalog stores the pointer to each table’s current metadata file and performs the atomic commit. Every engine must use the same catalog for a table, or commits will be lost.

Option	Choose when
Iceberg REST catalog (Apache Polaris, Lakekeeper, Gravitino, Unity Catalog, vendor-hosted)	Default for new platforms: every major engine speaks the REST spec; supports credential vending and central access control
AWS Glue Data Catalog (native or via its Iceberg REST endpoint), S3 Tables	AWS-centric stacks using Athena, EMR, Glue, Redshift
Project Nessie	You need git-like commits and branches across many tables at once
Snowflake-managed (Horizon) or Snowflake Open Catalog	Snowflake is the primary writer and other engines read
Hive Metastore	Existing Hadoop estate only; do not start new platforms on it
JDBC / SQL catalog	Small or local setups and tests

Apache Polaris graduated to an Apache top-level project in February 2026. Whatever the catalog, prefer vended, short-lived storage credentials over long-lived keys in every engine config.

Table design

CREATE TABLE lake.db.orders (
  order_id     BIGINT NOT NULL,
  customer_id  BIGINT NOT NULL,
  status       STRING,
  amount       DECIMAL(18,2),
  order_ts     TIMESTAMP NOT NULL          -- timestamptz in Spark; store UTC
)
USING iceberg
PARTITIONED BY (days(order_ts))
TBLPROPERTIES (
  'format-version' = '2',
  'write.delete.mode' = 'merge-on-read',
  'write.update.mode' = 'merge-on-read',
  'write.merge.mode'  = 'merge-on-read',
  'write.distribution-mode' = 'hash',
  'write.metadata.delete-after-commit.enabled' = 'true',
  'write.metadata.previous-versions-max' = '50'
);
ALTER TABLE lake.db.orders WRITE ORDERED BY customer_id, order_ts;

Partitioning rules:

• Partition by a transform of the column users filter on: days(ts), hours(ts), months(ts), bucket(N, id), truncate(W, s). Users filter on order_ts directly and Iceberg prunes; there is no separate dt column to keep in sync.
• Size partitions so each holds at least a few target-size files (default target 512 MB). Too fine (hourly on a small table, identity on a high-cardinality key) produces small files and huge metadata.
• Small tables (a few GB) often need no partitioning; sort order and file statistics do the pruning.
• Use bucket(N, key) for very large tables joined or looked up by a high-cardinality key; keep N modest (16-128) and consistent across tables you join.
• Sort order (WRITE ORDERED BY) clusters values within files so min/max statistics skip files for selective filters. Compaction with strategy => 'sort' maintains it.
• Partition evolution is metadata-only; old data keeps its old spec:

ALTER TABLE lake.db.orders REPLACE PARTITION FIELD days(order_ts) WITH hours(order_ts);
ALTER TABLE lake.db.orders ADD PARTITION FIELD bucket(16, customer_id);

Write mode choice:

Workload	Mode
Append-mostly, rare large deletes, read-heavy	Copy-on-write (default): rewrites files on UPDATE/DELETE/MERGE, fastest reads
Frequent MERGE/UPDATE/DELETE, CDC, streaming upserts	Merge-on-read: writes delete files, cheap writes, reads pay until compaction

Merge-on-read without regular rewrite_data_files and rewrite_position_delete_files gets slower with every commit.

Schema evolution

Iceberg tracks columns by ID, so these are metadata-only and safe for existing files: add a nullable column, drop, rename, reorder, widen int -> bigint, float -> double, increase decimal precision, and make a required column optional.

• Adding a required (NOT NULL) column needs a default: only format v3 supports column default values. On v2, add it nullable and backfill.
• Narrowing or changing a type (string to int) is not supported in place: add a new column, backfill, switch readers, drop the old one.
• Engines may cache schemas; after a change, refresh or restart long-running readers and validate with a query from each engine.

Snapshots, time travel, branches

SELECT * FROM lake.db.orders VERSION AS OF 8954597067493422955;
SELECT * FROM lake.db.orders TIMESTAMP AS OF '2026-10-19 00:00:00';
CALL lake.system.rollback_to_snapshot('db.orders', 8954597067493422955);   -- confirm with user first

ALTER TABLE lake.db.orders CREATE TAG `month-end-2026-09` RETAIN 400 DAYS;  -- kept despite expiry
ALTER TABLE lake.db.orders CREATE BRANCH audit;

Write-audit-publish with branches: set table property write.wap.enabled=true, write the batch with Spark conf spark.wap.branch=audit, run data checks against VERSION AS OF 'audit', then publish with CALL lake.system.fast_forward('db.orders', 'main', 'audit'). Readers of main never see unaudited data.

Rollback changes what every consumer sees and discards later commits from the current state: get explicit user confirmation and the snapshot ID before running it.

Maintenance

Task	Frequency	Command (Spark)
Compact small files	Daily for streaming or MERGE-heavy tables; weekly otherwise	CALL lake.system.rewrite_data_files(table => 'db.orders', strategy => 'sort', where => 'order_ts >= TIMESTAMP ''2026-10-13''', options => map('partial-progress.enabled','true'))
Compact delete files (merge-on-read)	With compaction	CALL lake.system.rewrite_position_delete_files(table => 'db.orders')
Expire snapshots	Daily	CALL lake.system.expire_snapshots(table => 'db.orders', older_than => TIMESTAMP '2026-10-13 00:00:00', retain_last => 20)
Remove orphan files	Weekly	CALL lake.system.remove_orphan_files(table => 'db.orders', older_than => TIMESTAMP '2026-10-16 00:00:00', dry_run => true) first
Rewrite manifests	When planning time grows or after many small commits	CALL lake.system.rewrite_manifests('db.orders')

Rules:

• Order: compact, then expire snapshots, then remove orphans. Compaction creates a new snapshot; expiry is what actually lets old files be deleted.
• Compact recent partitions with a where filter; rewriting cold partitions that are already well sized wastes compute.
• Defaults: snapshots older than 5 days become eligible (history.expire.max-snapshot-age-ms), at least 1 kept. Set retention to the longest time-travel or streaming-replay need, and say it out loud to consumers.
• remove_orphan_files with an older_than shorter than the longest-running write job deletes files of in-flight commits and corrupts the table. Keep it at 3 days or more, run dry_run => true first, and get user confirmation before the real run.
• Expiring snapshots permanently removes time travel to them. Confirm retention with the user before shortening it on production tables.
• Compaction and concurrent writers conflict on the same partitions; schedule compaction away from heavy MERGE windows and use partial progress so a conflict loses little work.
• Managed catalogs (S3 Tables, some REST services, Snowflake) can run compaction and expiry for you; check before scheduling duplicates.

Format versions

• v2 is the default (format-version = 2): row-level deletes, merge-on-read.
• v3 is GA since Iceberg 1.10: binary deletion vectors, row lineage, the variant type, column default values, nanosecond timestamps, geospatial types.
• Upgrading (ALTER TABLE ... SET TBLPROPERTIES ('format-version'='3')) is one-way. Every engine that touches the table must support v3 first; some engines read v3 but cannot write it. Verify each engine’s version, upgrade one table, test reads and writes from all engines, then roll out.

Engines

Engine	Strengths	Notes
Spark	Full DDL, MERGE, all maintenance procedures	Reference implementation; needs the Iceberg runtime jar matching Spark and Scala versions
Flink	Streaming ingestion and CDC upserts	Produces many small files; pair with frequent compaction
Trino	Interactive SQL, MERGE, maintenance via ALTER TABLE ... EXECUTE	Catalog types: rest, glue, nessie, jdbc, hive_metastore, snowflake
DuckDB	Local analysis; attach a REST catalog, read and write	iceberg_scan on a path is read-only
Snowflake	Iceberg tables managed by Snowflake or read from an external REST catalog	Choose which side owns writes per table
Athena, BigQuery, Databricks, Redshift	Vendor engines over Glue, BigLake, or Unity catalogs	Check each one’s supported format version and write support

For engine connection configs and syntax (Spark session settings, Trino catalog files, DuckDB ATTACH, Snowflake DDL, time travel per engine), read references/engine-cheatsheet.md when wiring up or debugging a specific engine.

Iceberg vs Delta Lake

Factor	Iceberg	Delta Lake
Engine breadth	Broadest multi-vendor support through the REST catalog spec	Strongest on Databricks and Spark; others via connectors
Partitioning	Hidden partitioning and in-place partition evolution	Explicit partition columns, or liquid clustering on Databricks
Catalog	Pluggable; REST spec is the standard	Unity Catalog or a metastore
Interop	The common target: Delta UniForm and conversion tools expose other formats as Iceberg	UniForm writes Iceberg metadata so Iceberg clients can read Delta tables

If the platform is Databricks-first, Delta with UniForm is reasonable. If several engines and vendors must write, choose Iceberg.

Gotchas

• Two catalogs pointing at the same table location: both accept commits and one side’s writes vanish. One table, one catalog.
• Filtering on an expression of the partition source (CAST(order_ts AS DATE) = ...) may not prune in every engine; filter with range predicates on the column itself.
• Streaming writers committing every few seconds create thousands of snapshots and metadata files per day; raise the commit interval and enable metadata cleanup.
• DELETE FROM t without a WHERE in an engine session pointed at production is a full logical delete; time travel can restore it only until snapshots expire.
• S3 request throttling on hot prefixes: consider write.object-storage.enabled=true for very high write rates.
• Timestamps: timestamp vs timestamptz semantics differ by engine; store UTC timestamptz and convert at presentation.

Verification

1. Run a typical filtered query in each engine and confirm pruning in its plan (files or splits scanned versus total).
2. After compaction: average file size from the files table is near target, and file count dropped.
3. After expiry: snapshot count matches the retention policy; tagged snapshots remain.
4. Orphan cleanup dry run lists only files older than the threshold and outside current metadata.
5. Alert when average file size drops below roughly a quarter of target or snapshot count grows past the retention policy.

Output

**Table:** lake.db.orders (Iceberg v2, REST catalog: Polaris), partitioned days(order_ts), sorted customer_id
**Writers/readers:** Flink CDC (upserts, merge-on-read), Spark batch, Trino BI, Snowflake read-only
**Maintenance:** daily rewrite_data_files (last 7 days, sort) + rewrite_position_delete_files; expire_snapshots older than 7 days, retain_last 20; weekly remove_orphan_files older_than 3 days
**Health:** files 48,120 -> 3,904; avg 21 MB -> 410 MB; snapshots 5,812 -> 168
**Retention promise:** 7 days time travel; month-end tags kept 400 days