Duplicate File Remediation

[MrDerecho]

Feature Request / Improvement

I use pyiceberg add_files to perform enterprise-grade ETL loading and backfilling of iceberg tables- on occasion, there will be a duplicate file, I load so many files that I can't use the duplicate check due to performance constraints (500-2000 files at a time). Note: AWS Athena doesn't have an issue with duplicate loads, however upstream Snowflake external tables and Databricks Delta uniform tables do. Often time the error messages provided have the S3 URI regarding the impacted file- I just need a process to surgically de-dupe the file in question from the metadata in-situ, v.s. reverting to an older snapshot (which may be 100+ since).

The process should be able to both check for duplicate entries in the manifest lists, and/or take a known duplicate file as an input.

[ForeverAngry]

Thanks for raising this @MrDerecho , this is something my team members have to deal with frequently, due to how we approach the use of add_files. Nice to know that its not just my folks that are experiencing the same!

[jayceslesar]

Looks like the performance hit comes from https://github.com/apache/iceberg-python/blob/main/pyiceberg/table/__init__.py#L850

[jayceslesar]

I think also the more files you can add in a single call for the file_paths argument, the more performant it will be as we have to re-compute the known data files for the table with each add_files call

[kevinjqliu]

@MrDerecho @ForeverAngry can you help me understand the use case and expected behavior?

on occasion, there will be a duplicate file, I load so many files that I can't use the duplicate check due to performance constraints (500-2000 files at a time).

The duplicate check is an anti-join between the "files to be added" to the table and the existing data files of the table. The existing data files is from reading all the manifest list and manifest files. I think the real bottleneck here might be from reading the manifests.

Is that what youre seeing?

Note: AWS Athena doesn't have an issue with duplicate loads, however upstream Snowflake external tables and Databricks Delta uniform tables do.

Does Athena have the add_files feature? I'm also curious what the issue with duplicate loads is in Snowflake external tables and Databricks Delta uniform tables.

Often time the error messages provided have the S3 URI regarding the impacted file- I just need a process to surgically de-dupe the file in question from the metadata in-situ

What is the error exactly? 503?

[kevinjqliu]

I think a pseducode snippet of how youre using add_files would be really helpful here!

[MrDerecho]

@kevinjqliu, for context I am referring to Trino (Athena) tables can deal with duplicate files referenced in the metadata- other upstream consumers i.e. snowflake external iceberg and databricks uniform cannot, so if at some point during the batch loading with the pyiceberg add files a duplicate is loaded then these (upstream of pyiceberg iceberg tables) consumers can no longer consume or update the iceberg tables. Today, I run enterprise grade ETL with the add_files method, considering I have large homogenously partitioned parquet files created through a separate provider. The error messages snowflake and databricks provide are generic application errors but do provide the offending s3_uri.

Basicallly, all I would need is a method that allows me to prune these s3_uri's from the manifest files and commit an update surgically to the table itself. Former methods of remedy include copying all files from the partition- deleting the partition, and reloading the partition (which depending can take sometime). The existing duplicate check- even using the better method would likely still take way too long between batches- as I use the same to reconcile my s3 objects with my tables and is time intensive. Right now, I am just using the existing add_files method and was hoping there was an easy way to traverse and remove the individual offending file from the metadata on the basis of existing methods.

[ForeverAngry]

So @MrDerecho , are you saying that you are looking for a function that can remove a DataFile entry, and then create a new snapshot with an updated ManifestFile?

Does this illustration capture the problem?

graph TD
  subgraph Iceberg Table Metadata
    manifest1["ManifestFile"]
    snapshot1["Snapshot"]
    dataFile1["DataFile A"]
    dataFile2["DataFile B"]
    parquetFile["Parquet File (s3://bucket/path/to/data.parquet)"]
  end

  snapshot1 --> manifest1
  manifest1 --> dataFile1
  manifest1 --> dataFile2
  dataFile1 --> parquetFile
  dataFile2 --> parquetFile

  note1["Note: Both DataFile A and B point to the same Parquet file"]
  note1 --- parquetFile

Loading

Which i think could include situations like this as well:

graph TD
  subgraph Iceberg Table Metadata
    snapshot1["Snapshot"]
    manifest1["ManifestFile A"]
    manifest2["ManifestFile B"]
    dataFile1["DataFile A (in Manifest A)"]
    dataFile2["DataFile B (in Manifest B)"]
    parquetFile["Parquet File (s3://bucket/path/to/data.parquet)"]
  end

  snapshot1 --> manifest1
  snapshot1 --> manifest2
  manifest1 --> dataFile1
  manifest2 --> dataFile2
  dataFile1 --> parquetFile
  dataFile2 --> parquetFile

  note1["Note: Both Manifest Files refer to DataFiles that share the same physical Parquet file"]
  note1 --- parquetFile

Loading

[MrDerecho]

@kevinjqliu , @ForeverAngry - yes this correct, both of the above scenarios (one where two references to the same file exist in the same manifest and one where across two manifests the same file is referenced. Ultimately, is there a way to remove a singular datafile from these manifests in place? Typically, it's been my experience the only way to trigger a delete-files is to delete an entire partition at once. Thanks.

[ForeverAngry]

@kevinjqliu can you assign me to this?