`
---
### Author:  [@Ken](https://community.dremio.com/u/Ken)
#### Post date: [March 26, 2025, 1:34pm UTC](https://community.dremio.com/t/create-table-not-as-iceberg/12882/5 "2025-03-26T13:34:39Z")
@balaji.ramaswamy
Using two iceberg tables as an illustration.
THe one in `l0` is constantly getting new data via `MERGE`.
I perform vacuum (expire snapshot retain 1) and optimise after every MERGE.
However, it’s data size grows to 31GiB.
I ran this SQL to determine the `file_size_in_bytes`
```auto
select sum("record_count"), sum("file_size_in_bytes") from TABLE( table_files( 'Minio.finance.l0.XXXX' ) );
```
It says it is around 3.5GB (which is **VERY DIFFERENT** from the actual size of 31GiB).
========
However, after I recreate the table (and put it in the `backup_2025_03_26`)
```auto
create table Minio.finance.backup_2025_03_26."XXXXXX"
as (SELECT * FROM Minio.finance.l0."XXXXXX")
```
The file sized shrank to `3.3GiB`. This is more inline with what the original statistics is saying.
Note that I have always expired all snapshots and it should not bloat to 31GiB.
I feel that I hit something similar to what is reported here ([Iceberg file size on dremio - #13 by dacopan](https://community.dremio.com/t/iceberg-file-size-on-dremio/11758/13))
=======
In the original iceberg table (with regular MERGE of new data)
—\> The data files folder are multiple. Those **XLDIR** directories were created after `vacuum operation`, where snapshots got deleted.
However, I think there are still dangling snapshots in the data files.
Meanwhile, in the **recreated table** (via CTAS), there’s just ONE data file folder
So I suppose that many data files folder in the original iceberg tables are actually useless (or else they would have been copied to the new iceberg table via CTAS)
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [March 28, 2025, 5:35am UTC](https://community.dremio.com/t/create-table-not-as-iceberg/12882/6 "2025-03-28T05:35:29Z")
Thanks for the clear steps @Ken Let me see if someone can try and repro this
---
### Author:  [@rdkworld](https://community.dremio.com/u/rdkworld)
#### Post date: [March 28, 2025, 11:30pm UTC](https://community.dremio.com/t/create-table-not-as-iceberg/12882/7 "2025-03-28T23:30:26Z")
Thanks Balaji, it worked
---
### Author:  [@Ken](https://community.dremio.com/u/Ken)
#### Post date: [April 3, 2025, 2:10pm UTC](https://community.dremio.com/t/create-table-not-as-iceberg/12882/8 "2025-04-03T14:10:41Z")
@balaji.ramaswamy
In the below screenshot, it shows clearly that
In Minio storage, the file exists.
However, dremio cannot detect that.
Why cannot it delete this orphan data file?
I tried
```auto
VACUUM TABLE Minio.finance.l0.yahoo_stock_price_1m_history_2025_03 EXPIRE SNAPSHOTS RETAIN_LAST 1
```
but it is still there.
============
**I manually deleted this data file using `mcli` and the result number of data counts remain the SAME!!!**
```auto
minio@minio-prod:~$ mcli rm --dangerous --force -r /mnt/data/finance/l0/yahoo_stock_price_1m_history_2025_03/1812f77a-4b1f-2a34-94b0-cfe8048cff00
```
So I confirm this is an orphan data file.
@balaji.ramaswamy Does Dremio support deleting orphan data files?
---
### Author:  [@Ken](https://community.dremio.com/u/Ken)
#### Post date: [April 3, 2025, 2:48pm UTC](https://community.dremio.com/t/create-table-not-as-iceberg/12882/9 "2025-04-03T14:48:34Z")
This screenshot shows this
- Left: It is the result of this query
```auto
select * from table(table_files('Minio.finance.l0.yahoo_stock_price_1m_history_2025_03'))
```
It shows the data files actively tracked by Dremio
- Right. It is the list of data files in Minio using the Minio Client.
```auto
mcli du /mnt/data/finance/l0/yahoo_stock_price_1m_history_2025_03/*
```
The two highlighted rows are the actual data files tracked by Dremio and the rest are orphan files.
For now, I think I will just write a python script to delete these files not tracked by dremio. Hopefully it will work.
---
### Author:  [@Ken](https://community.dremio.com/u/Ken)
#### Post date: [April 5, 2025, 3:57pm UTC](https://community.dremio.com/t/create-table-not-as-iceberg/12882/10 "2025-04-05T15:57:05Z")
> [@Ken](#):
>
> For now, I think I will just write a python script to delete these files not tracked by dremio. Hopefully it will work.
I confirm that this approach works!
In short, the approach is to delete data files not longer tracked by Dremio, but still remains in Minio.
Step 1. Query the data\_files in Dremio
```auto
select "file_path" from table( table_files('XXXX'))
```
This list represents the list of data files tracked by Dremio.
Step 2. Use the list\_objects function in Minio Client to list all the objects under the iceberg table folder
```auto
objects = self.client.list_objects(
bucket_name,
prefix=bucket_subpath,
recursive=recursive
)
```
Step 3: Perform reconciliation which matches each data file tracked by Dremio in Step 1 with the listed object in Step 2
Step 4: Except the Metadata files, delete all data files in Minio which are NOT tracked by Dremio. Use the remove\_object method.
```auto
self.client.remove_object(
bucket_name=bucket_name,
object_name= file_path,
)
```
**Validation**
- File Size dropped
I have a superset dashboard which tracks the actual table size in Minio via `du -sh` command once every hour. The below graph shows that all table folders drop in size significantly after the above logic is applied.
Below is another graph which shows the **available percentage** of disk space in the whole Minio VM. It is obtained via `df -h` and by looking at the `/` mount.
- Row Count increases
Most importantly, row count for all tables did not drop.
Below is the growth in row count of 2 of the tables.
