Athena Iceberg does not delete orphan files

Question

I have Athena Iceberg table.
The table has 2 partitions.

Each hour I update it with `MERGE` and `DELETE` commands. 
```
SELECT count(*) FROM "my_table$files"
```
now **gives 16. Meanwhile data folder contains 158 files**.

None of 
```
VACUUM  my_table
```
or 
```
OPTIMIZE my_table REWRITE DATA USING BIN_PACK
```
Is not helping to remove unnecessary files.

Table has following `TBLPROPERTIES`
```
TBLPROPERTIES (
  'table_type'='iceberg',
  'vacuum_max_snapshot_age_seconds'='60',
  'write_compression'='ZSTD',
  'format'='parquet',
  'vacuum_max_metadata_files_to_keep'='2',
  'optimize_rewrite_delete_file_threshold'='2',
  'optimize_rewrite_data_file_threshold'='2'
)
```

It is that aggressive because I do not need any history of changes. I'm interested in the latest state only.

Number of files is keep growing and never decrease, the spite the fact that the number of rows in the table is almost constant.

What I'm doing wrong, and how to stop files inflation.

Uladzislau · Answer

First of all seems `SELECT count(*) FROM "my_table$files"` is showing only DATA files but not DELETE files. 
Do you have Table Optimization Compaction on? 
If yes, this optimization is not equal to calling OPTIMIZE. This automatic compaction skips delete files and seems VACUUM never deletes them later.

The Cyber Express · Answer

Thanks for sharing

Athena Iceberg does not delete orphan files

Relevant content