I've got the right answer to my question from here on Reddit

Inserting one row at a time will cause single row based snapshot/transaction being created. So if you are inserting thousands or millions of rows, expect each to create a snapshot and also create a parquet of few KBs with just one record in it. INSERT INTO table VALUES syntax will cause all kind of issues including the one you reported.

I will suggest the following:

Try creating a staging table/file/in-memory location where you can store the intermediate rows before doing a BULK upload into Iceberg table. This way there will be only one snapshot/transaction and also the underline parquet file size will also be optimal like 256MB standard. For example create a csv file to hold few thousands of records in the staging area and then upload that csv file as bulk upload to Iceberg since then it will be considered a single snapshot/transaction, this will keep your metadata folder space small in size.

If you want to continue with your existing approach than Iceberg provide options to expires and delete snapshots. You might want to run the expire command after few thousand records being inserted. This way your metadata folder space will be under check.

My recommendation will be Option#1.