Use Trino ALTER TABLE EXECUTE optimize to compact small files in an Iceberg table via SQL

Steps

1. Connect to Trino with the Iceberg catalog configured and identify a table with many small files: SELECT file_count, total_size FROM <catalog>.information_schema.tables WHERE table_name = '<table>' or query the Iceberg metadata table iceberg.<schema>.<table>$files and aggregate file sizes
2. Run the optimize command: ALTER TABLE <catalog>.<schema>.<table> EXECUTE optimize(file_size_threshold => '128MB') — Trino rewrites files smaller than the threshold into larger files using the Iceberg rewrite_data_files mechanism under the hood
3. Optionally scope the optimize to a specific partition to reduce resource usage: ALTER TABLE <catalog>.<schema>.<table> EXECUTE optimize WHERE partition_column = 'value' and file_size_threshold => '128MB'
4. Monitor query progress in the Trino Web UI; for large tables with many small files this can be a long-running query — consider running during off-peak hours and setting an appropriate query_max_execution_time session property
5. After completion, query the Iceberg metadata again to confirm file counts have decreased and average file sizes are near the target; then run expire_snapshots (via Spark procedure or Iceberg REST API) to clean up the now-superseded pre-compaction files