building-iceberg-tableslisted

# Building Iceberg Tables ## When to use - Creating or maintaining Apache Iceberg tables on a lakehouse. - Choosing partitioning, or evolving partitioning/schema without rewrites. - Managing snapshots, time travel, compaction, and small files. - Do NOT use for Delta-specific work (use `engineering-databricks-pipelines`). ## Workflow ``` - [ ] Partition by query filter columns; use hidden partition transforms - [ ] Use MERGE for idempotent upserts - [ ] Schedule compaction (rewrite_data_files) to fix small files - [ ] Expire old snapshots + remove orphan files to control metadata/storage - [ ] Evolve partitioning/schema by field ID (no data rewrite) ``` 1. **Partition on filter columns** using hidden partition transforms (`days(ts)`, `bucket(N, id)`), so queries prune without users adding derived partition columns. 2. **Idempotent writes** via `MERGE INTO` keyed on the business key. 3. **Compact regularly** — streaming/small-batch writes create many small files; `rewrite_data_files` restores read performance. 4. **Maintain metadata** — expire old snapshots and remove orphan files, or snapshot history and storage grow without bound. 5. **Evolve freely** — Iceberg tracks columns/partitions by ID, so add/drop/rename and even partition-spec changes need no data rewrite. ## Patterns **Create with hidden partitioning + MERGE upsert:** ```sql CREATE TABLE lake.db.orders (order_id BIGINT, customer_id BIGINT, amount DECIMAL, ordered_at TIMESTAMP) USING iceberg PA