---
name: iceberg-optimizer
description: >-
  Diagnoses an Apache Iceberg table and produces a ranked, cost-aware plan
  covering three domains: table layout (compaction, partition evolution, format
  upgrade), ingestion pipeline (write distribution, file sizing, sort order at
  write time), and maintenance (snapshot expiry, orphan cleanup, manifest
  rewrite). Use when asked to optimize, tune, speed up, shrink the cost of,
  clean up, repartition, compact, or design a maintenance schedule for an
  Iceberg table, or to decide whether a table is even worth optimizing.
  Engines: Spark, Trino, DuckDB, AWS Glue/EMR, Snowflake, Flink / Kafka Connect.
  Adapts to Gateway/Direct mode (user-provided live catalog access), DuckDB
  fallback mode (lightweight local Iceberg access when no gateway exists),
  Ask-User mode (user runs queries), or Exported mode (pre-exported metadata files).
---

# Iceberg Optimizer

A maintenance plan is only as good as the workload it is tuned for. Don't reach
for the standard "compact + sort + expire" runbook before knowing how the table
is written, how it is read, and what the owner pays for — the same table can
warrant aggressive daily sort compaction or *no maintenance at all*.

**Observe before you ask. Ask before you decide. Simulate before you recommend.**

**CRITICAL — gradual loading:** Read nothing beyond this file until the engine
and access mode are identified in Phase 0. Then load only the relevant sections
of reference/engine files at the boundary where they are needed (the
`> Load (...)` callouts mark when). Loading everything up front pollutes context
with information that may never apply to this table.

## The flow

**0 Scope** (table, engine, mode; read-only) → **1 Profile** (physical state from
metadata) → **2 Workload** (2a derive ingestion/access signals · 2b interview for
intent) → **3 Decide** (joint scoring → action groups, incl. "do nothing") →
**4 Simulate** (perf / query-cost / maintenance-cost / storage) → **5 Plan**
(Markdown report with exact engine commands + schedule + monitoring). Each phase
below opens with a `> Load (...)` callout naming exactly what to read.

### Phase 0 — Scope & safety

Establish (ask if not stated): **which table(s)** (`catalog.schema.table`) and
**which engine** (Spark / Trino / DuckDB / Glue/EMR / Snowflake / Flink — syntax differs).

**Read-only until Phase 5.** Never run `expire_snapshots`, `remove_orphan_files`,
`rewrite_data_files`, or any `ALTER TABLE` until a specific plan is approved —
`remove_orphan_files` and `expire_snapshots` *delete files*; treat as destructive.

**Detect access mode** (in order):

1. **Gateway / Direct** — the user already has an Iceberg-capable gateway or CLI
   (`trino`, `spark-sql`, `beeline`, Snowflake, Glue/EMR, REST catalog through
   an existing client, or env `TRINO_URL` / `SPARK_HOME` / `DATABRICKS_HOST` /
   `SNOWFLAKE_ACCOUNT`).
   Use that gateway first; it is the source of truth for permissions, catalog
   behavior, query stats, and engine-specific maintenance capability.
2. **Exported** — the user provided files (`profile.json`, metadata CSVs, query
   logs, writer config, or ingestion logs); read those files and do not ask for
   live access.
3. **DuckDB fallback** — no gateway/export exists, but the user can provide an
   Iceberg metadata path or REST catalog details and local `duckdb` is available
   or acceptable to install. Use DuckDB as lightweight read-only profiling
   infrastructure; do not treat it as a maintenance engine unless a documented
   DuckDB operation exactly matches the chosen action.
4. **Ask-User** — no gateway/export/DuckDB fallback is available; ask *"Can I run
   SQL against your catalog, use DuckDB with a metadata path or REST catalog, or
   should I give you queries to paste back?"* and let the user choose.

> **Load (Phase 0):** Nothing beyond SKILL.md. Detect mode and engine only.

### Phase 1 — Profile (metadata)

> **Load (Phase 1):** `Grep references/metadata-tables.md` for only the signal
> sections you need (e.g. `$files`, `$snapshots`, `$manifests`). Do NOT read the
> full file. If the engine is DuckDB, read `engines/duckdb.md` instead of
> translating Spark/Trino metadata-table syntax.

Read the table's physical state. Run/request the diagnostic queries from
`references/metadata-tables.md` (Direct/Ask-User), or use the provided files
(Exported). Prefer the engine input helper when the engine is known:

```
scripts/spark_input.py --table catalog.db.tbl --out-dir input_bundle
scripts/trino_input.py --table catalog.db.tbl --out-dir input_bundle
scripts/glue_input.py --table db.tbl --out-dir input_bundle
scripts/snowflake_input.py --table db.schema.tbl --out-dir input_bundle
```

These helpers create exact read-only export SQL plus `run_profile.sh` and
`run_workload.sh`. For Flink/Kafka Connect writers, collect the Iceberg catalog
profile through Spark, Glue/EMR, Trino, or Snowflake, then use
`engines/ingestion.md` for writer configuration.

When metadata CSVs are available, feed them to the profiler:

```
scripts/profile_table.py --snapshots S --files F [--partitions P] [--manifests M] --out profile.json
```

Emits file-size health, small-file pressure, delete-file pressure,
snapshot/manifest bloat, mixed partition specs, total size and file count — this
phase alone answers "is this table healthy?" and is safe on any table.

### Phase 2 — Reconstruct the workload

> **Load (Phase 2a):** Grep `references/metadata-tables.md` for workload-signal
> sections. Grep `references/workload-interview.md` for the derive-then-ask bank.

#### 2a — Derive what metadata already knows

Most ingestion and access questions are *answerable from metadata* — never ask
the user something `$snapshots` and `$files` already answered.

**Ingestion signals & writer identification:** Derive the ingestion signals
(`write_cadence`, `avg_added_file_mb`, `thin_spread`, `late_data`, delete
pressures, `operation_mix`), infer the likely writer from the signal pattern,
and confirm with the user. The signal definitions, the signal-pattern→writer
table, and the confirmation prompts all live in `references/workload-interview.md`
Part 1a — also confirm `distribution_mode`, `ingestion_write_mode` (mor/cow), and
`checkpoint_interval_secs` there, since Group 2 fixes are writer-specific. **If the
writer's config or logs are accessible** (provided, or reachable in Direct mode),
read them to derive these signals as fact rather than inferring from metadata — Part
1a's writer-source table lists what to read per writer.

**Access pattern signals:**

```
scripts/parse_query_log.py --table catalog.schema.table --out workload.json \
    [--spark-eventlog LOG] [--trino-queries QUERIES] [--explain-analyze explain.txt] \
    [--scan-report SCAN_REPORTS]
```

Extracts per-table ranked WHERE-clause columns, range vs equality predicates,
partition-pruning effectiveness, selectivity (rows scanned ÷ returned), and
query frequency from Spark event logs, Trino query exports/event-listener logs,
Snowflake query-history CSVs generated by `snowflake_input.py`, or Trino
`EXPLAIN ANALYZE` (run `--help` for the fields each reads). If none are
available, ask the user to run the relevant engine input helper or `EXPLAIN
ANALYZE` on 3–5 representative queries.
If the user can supply Iceberg **ScanReport** JSON (`--scan-report`), it also
derives measured `manifest_prune_rate` — the only direct signal for manifest
clustering quality (see `references/metadata-tables.md`).

#### 2b — Interview for intent

> **Load (Phase 2b):** Read `references/workload-interview.md` Part 2 only if 2a
> didn't already load it.

Walk `references/workload-interview.md` Part 2. For each item, present what you
derived in 2a, then ask the user to confirm/correct only the genuinely unknowable
parts. Use `AskUserQuestion` — these are real decisions only the owner can make:
query **latency** requirement, query **frequency** & consumers, **freshness** SLA,
**cost priority** (the `--priority` flag for `simulate.py`), **mutability outlook**
(append-only vs updates/deletes/GDPR), **time-travel / replay / audit** need,
**lifecycle / worth** (hot / warm / cold / not worth optimizing), and
**retention / compliance** (only if deletes were observed in 2a).

### Phase 3 — Decide

> **Load (Phase 3):** Read `references/decision-framework.md` in full.

Apply `references/decision-framework.md`: it combines profile + workload signals
into a ranked set of actions across **three groups** (a complete plan usually
draws from more than one):

- **Group 1 — Table Layout:** compaction (bin-pack / sort / z-order), partition
  evolution, equality/position-delete compaction, format upgrade. Run *after* the writer.
- **Group 2 — Ingestion:** write-time distribution mode, file-size buffering,
  write-time sort order, CDC write-mode (MOR→COW). Run these *first* — fixing the
  writer before compacting its output stops the problem recurring.
- **Group 3 — Maintenance:** snapshot expiry, manifest rewrite, orphan removal,
  bloom filters, scheduling. These are ongoing.

Key gates that prevent over-engineering:
- **Low query frequency + cold table → do little or nothing**; maintenance compute
  can cost more than it ever saves, so pay at query time instead.
- **Sort / z-order only when** queries are selective *and* read often enough to
  amortize the rewrite.
- **Aggressive snapshot expiry only when** replay/time-travel is not needed.
- **Bloom filters only for** high-cardinality equality lookups.

> **Load (Group 2 only):** If any Group 2 actions are recommended, load
> `engines/ingestion.md` now — it has the writer-specific configuration blocks.

### Phase 4 — Simulate

> **Load (Phase 4):** No reference files — run `scripts/simulate.py` directly.

Build candidate scenarios and run:

```
scripts/simulate.py --profile profile.json --workload workload.json \
    [--assumptions a.json] --priority <total|storage|query_cost|latency|maintenance_cost>
```

It compares **Do-nothing / Light / Targeted-sort / Aggressive / Storage-min**
scenarios. Present results as directional estimates with ranges, never precise
figures, and highlight the scenario that wins on the chosen priority. Baseline
scan bytes come from
`selectivity.median_bytes_scanned` when present (logs / EXPLAIN ANALYZE),
otherwise `total_gb × (1 − prune_rate)`; override per-scenario scan fractions with
a post-compaction measurement via `--assumptions '{"scan_fraction":
{"targeted_sort": 0.18}}'`.

### Phase 5 — Plan

> **Load (Phase 5):** Read ONLY the engine file(s) in scope — Spark/Glue/EMR →
> `engines/spark.md` (+ `engines/glue.md` for Glue); Trino → `engines/trino.md`;
> DuckDB → `engines/duckdb.md`; Snowflake → `engines/snowflake.md`; Group 2 actions → `engines/ingestion.md`
> (may already be loaded). Do NOT load files for out-of-scope engines. Grep
> `references/scheduling.md` only if a schedule is requested. Read
> `references/reporting.md` before writing the final recommendation report.

Emit the concrete plan for the chosen scenario as a Markdown report. Name it
`iceberg_optimization_report.md` unless the user requested another path. The
report is the durable handoff artifact: it must let the user quit, reopen the
thread later, and continue from the recorded profile/workload/simulation state.
An HTML report is optional and only produced if the user asks for it after seeing
the Markdown version.

**Operation order — always:** (1) Group 2 ingestion fixes (writer config; takes
effect next run), then (2) Group 1 table layout (compact → expire snapshots →
remove orphans → rewrite manifests), then (3) Group 3 maintenance (schedule
ongoing tasks). Never remove orphans before expiring snapshots.

The report must include, near the top, `## Summary: Why Each Action, In One
Sentence` with a two-column table: `Action` and `Why? (data-driven)`. Each row
must cite the triggering metric(s) or gate(s) from `profile.json`, `workload.json`,
the interview, or simulator output. Include skipped or rejected actions when a
common action was considered but the data says not to run it.

Then provide: **Commands** (exact, engine-specific, parameters from the profile —
verify capabilities in the loaded engine file; e.g. Trino does NOT support
sort/z-order compaction or manifest clustering, use Spark for those);
**Schedule** (from `references/scheduling.md`, matched to write cadence and
scenario); and **Monitoring** (metadata-table threshold queries that trigger the
next run).

Restate the safety note before any destructive command, and get explicit approval
before running anything that deletes files or rewrites data.

## Files in this skill

Only `SKILL.md` loads up front. `references/` (metadata-tables, workload-interview,
decision-framework, reporting, scheduling) and `engines/` (spark, glue, trino, duckdb, snowflake,
ingestion) load on demand at the `> Load (...)` callouts above. Scripts run
directly, never into context: `spark_input.py`, `trino_input.py`,
`glue_input.py`, `snowflake_input.py`, `profile_table.py`,
`parse_query_log.py`, and `simulate.py` under `scripts/`.