Pyiceberg allows dropping the sort order column and causes table corruption on AWS Glue Catalog

[mwa28]

Apache Iceberg version

0.9.0

Please describe the bug 🐞

Hello,

I am currently using v0.9.0rc3 and trying to evolve the schema for an existing non-empty table living the aws glue catalog.

It seems that

table.update_schema().add_column("col_name", StringType()).commit()

will successfully add the column to the glue catalog table however, this will cause the table to become corrupt and no longer useable.

Trying to query it from Athena gives the following :

[ErrorCode: INTERNAL_ERROR_QUERY_ENGINE] Amazon Athena experienced an internal error while executing this query. Please contact AWS support for further assistance. You will not be charged for this query. We apologize for the inconvenience.

I raise the issue with AWS support and tried to do a
https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-alter-table-add-columns.html
on another table and it works fine.

It seems that something from pyiceberg is currently causing the table to become corrupt maybe ?

For reference

table.update_schema().delete_column("col_name").commit()

causes no harm and the table is updated automatically in the catalog and querying it works fine as well.

I will maybe try to provide a follow-up once AWS support shares their feedback on the matter.

Please feel free to close this issue if it can be confirmed that the issue is on AWS's side solely.

Willingness to contribute

• I can contribute a fix for this bug independently
• I would be willing to contribute a fix for this bug with guidance from the Iceberg community
• I cannot contribute a fix for this bug at this time

[mwa28]

As of 2025-07-01, support has replied with the following :

Using my support tool I noticed that Athena actually detected this table as corrupted.

In order the investigate further, please explain the Iceberg version used to compare with the default version of Iceberg used by Athena, which is 1.4.2 [1].

Also please explain how was this Iceberg table created and the latest metadata file that corresponds to this table.

Related common issues are encountered when using Iceberg S3 tables, please confirm if this is the case.

[jayceslesar]

I am unable to reproduce with the following on v0.9.0rc3 or on main -- can you provide some more details if possible?

Let me know if there are any modifications I should make to the following:

from pyparsing import results
from pyiceberg import catalog
from pyiceberg.schema import Schema, NestedField, StringType
import pyathena
import pyarrow as pa

glue_catalog = catalog.load_catalog(**{"type": "glue"})

if glue_catalog.table_exists("make_me_corrupt.corrupt_me"):
    glue_catalog.drop_table("make_me_corrupt.corrupt_me")

glue_catalog.create_namespace_if_not_exists("make_me_corrupt")

schema = Schema(
    NestedField(field_id=1, name="field1", field_type=StringType(), required=True),
)

table = glue_catalog.create_table(
    "make_me_corrupt.corrupt_me",
    schema=schema,
    location="s3://my-bucket/jayce",
)

cursor = pyathena.connect(
    s3_staging_dir="s3://my-bucket/jayce/athena_results/",
    region_name="us-east-1",
).cursor()

cursor.execute("SELECT * FROM make_me_corrupt.corrupt_me")

# assert table is empty...
results = cursor.fetchall()
assert results == []

# write a single row
table.append(
    pa.Table.from_pylist([{"field1": "test_value"}]).cast(table.schema().as_arrow())
)

cursor.execute("SELECT * FROM make_me_corrupt.corrupt_me")
results = cursor.fetchall()
assert results == [("test_value",)]

table.update_schema().add_column("col_name", StringType()).commit()

cursor.execute("SELECT * FROM make_me_corrupt.corrupt_me")

results = cursor.fetchall()
assert results == [('test_value', None)]

[mwa28]

Interesting, here is AWS support's take on the matter:

Using my support tools, I observed that you executed a query running

SELECT * FROM "iceberg_database"."icebergCorrupted" limit 10 

followed by query executing

OPTIMIZE iceberg_database.icebergCorrupted REWRITE DATA USING BIN_PACK WHERE col_9 = '2025-06-30'

After the optimization operation, Athena detected the table as corrupted.

To investigate this issue further, I replicated your table structure using the manifest file you provided. I created a test table using the following PyIceberg script:

from pyiceberg.catalog import load_catalog
from pyiceberg.schema import Schema, NestedField
from pyiceberg.types import StringType
from pyiceberg.partitioning import PartitionSpec, PartitionField
from pyiceberg.transforms import IdentityTransform
from pyiceberg.table.sorting import SortOrder, SortField

glue_catalog = load_catalog(
    'iceberg_database',
    **{
        'client.access-key-id': '[REDACTED]',
        'client.secret-access-key': '[REDACTED]',
        'client.region': 'ca-central-1'
    },
    type='glue'
)

# Define your schema with all required fields
schema = Schema(
    NestedField(1, "col_1", StringType(), required=True),
    NestedField(2, "col_2", StringType(), required=True),
    NestedField(3, "col_3", StringType(), required=True),
    NestedField(4, "col_4", StringType(), required=True),
    NestedField(5, "col_5", StringType(), required=True),
    NestedField(6, "col_6", StringType(), required=True),
    NestedField(7, "col_7", StringType(), required=True),
    NestedField(8, "col_8", StringType(), required=True),
    NestedField(9, "col_9", StringType(), required=True),
    NestedField(10, "col_10", StringType(), required=True),
    identifier_field_ids=[8],  # 'col_8' is the primary key
)

# Table location
table_location = "s3://<my-bucket>/icebergCorrupted/"

# Create the table
iceberg_table = glue_catalog.create_table(
    identifier=("iceberg_database", "icebergCorrupted"),
    schema=schema,
    partition_spec=PartitionSpec(
        PartitionField(
            source_id=9,
            field_id=1000,
            transform=IdentityTransform(),
            name="col_9",
        ),
    ),
    sort_order=SortOrder(SortField(source_id=10, transform=IdentityTransform())),
    location=table_location,
    properties={
        "write.format.default": "parquet",
        "write.target-file-size-bytes": 134217728,  # 128 MB
        "write.metadata.delete-after-commit.enabled": True,
        "write.metadata.previous-versions-max": 5
    },
)

print(f"Iceberg table 'icebergCorrupted' created successfully in database 'iceberg_database' at: {table_location}")

Following the table creation, I performed schema modifications by adding four additional columns and removing the sort column through the Glue Catalog console.

Subsequently, I executed the following code to verify the schema changes:

# Load an existing table
table = glue_catalog.load_table(("iceberg_database", "icebergcorrupted"))

# Get table metadata
print(f"Table name: {table.name()}")
print(f"Table schema: {table.schema()}")

Root Cause Identified:

The schema retrieved through PyIceberg did not reflect the changes made via the Glue Catalog console. This behavior is consistent with a known issue where schema changes made through the AWS Glue Data Catalog UI or PyIceberg library do not properly update the Iceberg table metadata files in S3, causing inconsistencies when querying through Athena.

You can verify this issue by making schema changes through the Glue Data Catalog console and observing that no new manifest metadata files are generated in the S3 metadata directory.

Recommended Solution:

To resolve this issue and prevent future table corruption, all schema changes must be performed using Athena ALTER TABLE statements [1]. This approach ensures proper metadata updates and maintains consistency across all query engines.

Example of proper schema modification using Athena:

ALTER TABLE your_database.your_table_name 
ADD COLUMN new_column_name string;

This workaround will ensure that your Iceberg table metadata remains consistent and prevents the corruption issues you have experienced.

[mwa28]

I am unable to reproduce with the following on v0.9.0rc3 or on main -- can you provide some more details if possible?

Maybe try to add a few more columns and remove some and maybe add a partition column to your tests ? See the above AWS support's test that causes the corruption from their side as well. The table got corrupt after an optimize from their side on a partition. My table has partitioning as well.

It seems that even through the glue catalog in the console you can corrupt the table not just with pyiceberg and apparently it is a known issue which is awful given that they do not put out a disclaimer anywhere on this topic.

[jayceslesar]

The schema retrieved through PyIceberg did not reflect the changes made via the Glue Catalog console. This behavior is consistent with a known issue where schema changes made through the AWS Glue Data Catalog UI or PyIceberg library do not properly update the Iceberg table metadata files in S3, causing inconsistencies when querying through Athena.

Will need to do some more testing on the pyiceberg side but agreed that is frustrating from AWS.

Thank you for the detailed information!

[mwa28]

I figured it out, it is not a simple schema update from outside Athena that causes corruption but rather deleting the column that was set as Sort Order Key.

This behavior is not allowed within Athena via

ALTER TABLE make_me_corrupt.corrupt_me DROP COLUMN col_2

as it gives :

Failed: Input does not satisfy Iceberg requirement

Meanwhile with pyiceberg it is allowed. Here is my test :

import unittest

from pyathena.error import OperationalError
from pyathena import connect
from pyiceberg.catalog import load_catalog
from pyiceberg.schema import Schema
from pyiceberg.table.sorting import SortField, SortOrder
from pyiceberg.transforms import IdentityTransform
from pyiceberg.types import NestedField, StringType


class TestPyicebergAthenaCorrupt(unittest.TestCase):
    def setUp(self):
        self.catalog = load_catalog("glue", **{"type": "glue"})

        self.TABLE_NAME = "make_me_corrupt.corrupt_me"

        self.schema = Schema(
            NestedField(1, "col_1", StringType(), required=True),
            NestedField(2, "col_2", StringType(), required=True),
            identifier_field_ids=[1],  # 'col_1' is the primary key
        )

        self.iceberg_table = self.catalog.create_table(
            identifier=self.TABLE_NAME,
            schema=self.schema,
            sort_order=SortOrder(
                SortField(
                    source_id=2, transform=IdentityTransform()
                )  # col2 is the sort key
            ),
            location="s3://<my-bucket>/icebergCorrupted/",
            properties={
                "write.format.default": "parquet",
                "write.target-file-size-bytes": 134217728,  # 128 MB
                "write.metadata.delete-after-commit.enabled": True,
                "write.metadata.previous-versions-max": 5,
            },
        )
        assert self.iceberg_table.schema() == self.schema

        self.cur = connect(
            s3_staging_dir="s3://<my-bucket>/athena-query-result/",
            region_name="eu-west-1",
        ).cursor()

    def tearDown(self):
        # Delete the table and drop it from the catalog
        try:
            self.catalog.drop_table(self.TABLE_NAME)
        except Exception as e:
            print(f"Error during teardown: {e}")

    def test_corrupt_table(self):
        # Assert table exists
        self.assertTrue(self.catalog.table_exists(self.TABLE_NAME))
        # Drop the sort order column
        self.iceberg_table.update_schema().delete_column("col_2").commit()

        new_schema = Schema(
            NestedField(1, "col_1", StringType(), required=True),
            identifier_field_ids=[1],  # 'col_1' is the primary key
        )

        self.assertEqual(self.iceberg_table.schema(), new_schema)

        with self.assertRaises(OperationalError) as context:
            self.cur.execute(f"SELECT * FROM {self.TABLE_NAME}")

        print(context.exception)


if __name__ == "__main__":
    unittest.main()

I will update the issue title to match my findings.

[jayceslesar]

Nice, I was able to reproduce and also confirm that trino sees it as corrupted! Good find

[jayceslesar]

Here is what the metadata looks like before:

{
    "location": "s3://my-bucket/jayce-test-again",
    "table-uuid": "c467f3f1-f0d3-4b1d-a1d4-d1981ec3bce2",
    "last-updated-ms": 1751474855599,
    "last-column-id": 2,
    "schemas": [
        {
            "type": "struct",
            "fields": [
                {
                    "id": 1,
                    "name": "col_1",
                    "type": "string",
                    "required": true
                },
                {
                    "id": 2,
                    "name": "col_2",
                    "type": "string",
                    "required": true
                }
            ],
            "schema-id": 0,
            "identifier-field-ids": [
                1
            ]
        }
    ],
    "current-schema-id": 0,
    "partition-specs": [
        {
            "spec-id": 0,
            "fields": []
        }
    ],
    "default-spec-id": 0,
    "last-partition-id": 999,
    "properties": {
        "write.format.default": "parquet",
        "write.target-file-size-bytes": "134217728",
        "write.metadata.delete-after-commit.enabled": "true",
        "write.metadata.previous-versions-max": "5"
    },
    "snapshots": [],
    "snapshot-log": [],
    "metadata-log": [],
    "sort-orders": [
        {
            "order-id": 1,
            "fields": [
                {
                    "source-id": 2,
                    "transform": "identity",
                    "direction": "asc",
                    "null-order": "nulls-first"
                }
            ]
        }
    ],
    "default-sort-order-id": 1,
    "refs": {},
    "statistics": [],
    "format-version": 2,
    "last-sequence-number": 0
}

And after the removal of the column (corrupted)

{
    "location": "s3://my-bucket/jayce-test-again",
    "table-uuid": "c467f3f1-f0d3-4b1d-a1d4-d1981ec3bce2",
    "last-updated-ms": 1751474858863,
    "last-column-id": 2,
    "schemas": [
        {
            "type": "struct",
            "fields": [
                {
                    "id": 1,
                    "name": "col_1",
                    "type": "string",
                    "required": true
                },
                {
                    "id": 2,
                    "name": "col_2",
                    "type": "string",
                    "required": true
                }
            ],
            "schema-id": 0,
            "identifier-field-ids": [
                1
            ]
        },
        {
            "type": "struct",
            "fields": [
                {
                    "id": 1,
                    "name": "col_1",
                    "type": "string",
                    "required": true
                }
            ],
            "schema-id": 1,
            "identifier-field-ids": [
                1
            ]
        }
    ],
    "current-schema-id": 1,
    "partition-specs": [
        {
            "spec-id": 0,
            "fields": []
        }
    ],
    "default-spec-id": 0,
    "last-partition-id": 999,
    "properties": {
        "write.format.default": "parquet",
        "write.target-file-size-bytes": "134217728",
        "write.metadata.delete-after-commit.enabled": "true",
        "write.metadata.previous-versions-max": "5"
    },
    "snapshots": [],
    "snapshot-log": [],
    "metadata-log": [
        {
            "metadata-file": "s3://my-bucket/jayce-test-again/metadata/00000-eec106a1-7c00-434e-9a95-fbe8df8f269a.metadata.json",
            "timestamp-ms": 1751474855599
        }
    ],
    "sort-orders": [
        {
            "order-id": 1,
            "fields": [
                {
                    "source-id": 2,
                    "transform": "identity",
                    "direction": "asc",
                    "null-order": "nulls-first"
                }
            ]
        }
    ],
    "default-sort-order-id": 1,
    "refs": {},
    "statistics": [],
    "format-version": 2,
    "last-sequence-number": 0
}

[geruh]

Hey @mwa28 nice find! Seems to be an issue with PyIceberg. The Java library throws a validation exception when dropping a column that is used for the sort order.

https://github.com/apache/iceberg/blob/main/core/src/test/java/org/apache/iceberg/TestSortOrder.java#L304-L314

[jayceslesar]

@geruh thanks for linking -- I think we should look at that test suite and see what other operations throw errors and incorporate those into the test suite here! @mwa28 what do you think?

[mwa28]

Hey @mwa28 nice find! Seems to be an issue with PyIceberg. The Java library throws a validation exception when dropping a column that is used for the sort order.

https://github.com/apache/iceberg/blob/main/core/src/test/java/org/apache/iceberg/TestSortOrder.java#L304-L314

That's actually really interesting to know !

@geruh thanks for linking -- I think we should look at that test suite and see what other operations throw errors and incorporate those into the test suite here! @mwa28 what do you think?

Yes it seems on this file alone there are 7 occurrences of assertThatThrownBy and it would be much smarter to do it this way.

[geruh]

Currently, this behavior can also be observed with partitioning, where it's possible to drop a field used for partitioning and then define a PartitionSpec using a non-existent column name. A part of this fix can be to add validation during the construction of new table metadata to ensure that the schema includes all fields referenced in the sort order and partitioning.

We could potentially implement a check similar to the compatibility methods shown here:
https://github.com/apache/iceberg/blob/b38573dcfb4a5467a70c28573a2d39874718a2a1/core/src/main/java/org/apache/iceberg/TableMetadata.java#L1557

[geruh]

Hi @mwa28, have you started working on this? It would be great to get it in before the next release. If not, I can take it up.