Glue 4.0 vs Glue 5.0 Iceberg Merge statement error.

When upgrading from AWS Glue 4.0 to 5.0, you're encountering an issue with the Iceberg MERGE statement that worked fine in the previous version. This is likely due to changes in the underlying Spark version, as AWS Glue 5.0 upgraded from Apache Spark 3.3.0 to 3.5.4.

The error message is somewhat paradoxical because it's suggesting column names that include the very column it claims cannot be resolved (t.id). This suggests there might be an issue with how the query parser is handling the table aliases in Spark 3.5.4.

To resolve this issue, try one of these approaches:

1. Use fully qualified column references by including the table name:

MERGE INTO glue_catalog.curation_db_prod.gjp_mapped_flat AS t
USING source AS s
ON t.id = s.id

2. Try removing the table aliases and use the full table names instead:

MERGE INTO glue_catalog.curation_db_prod.gjp_mapped_flat
USING source
ON glue_catalog.curation_db_prod.gjp_mapped_flat.id = source.id

3. If you're using a temporary view for the source table, try registering it as a proper table first.

4. Ensure that both tables have the correct schema and that the 'id' column exists in both tables with compatible data types.

When migrating to AWS Glue 5.0, it's important to note that while most job parameters and features from Glue 4.0 are preserved, there are some changes in the underlying dependencies that might affect SQL syntax handling. The error suggests that the SQL parser in the newer Spark version might be more strict about how it resolves column references in MERGE statements.
Sources
Migrating AWS Glue for Spark jobs to AWS Glue version 5.0 - AWS Glue
Upgrading from AWS Glue 4.0 to 5.0: PROXY Error and GlueBootstrap Bootstrapping Failure | AWS re:Post

Not using aliases:

QUERY: 
                MERGE INTO glue_catalog.curation_db_prod.gjp_mapped_flat
                USING source
                ON glue_catalog.curation_db_prod.gjp_mapped_flat.id = source.id
                WHEN MATCHED THEN UPDATE SET 
                    schema_key = source.schema_key,
                    curated_value = source.curated_value,
                    total_matches = source.total_matches,
                    file_name = source.file_name,
                    updated_at = source.updated_at
                WHEN NOT MATCHED THEN INSERT (
                    id,
                    source_hash,
                    source_name,
                    dt,
                    genesis_path,
                    genesis_value,
                    genesis_value_type,
                    is_transformed,
                    schema_key,
                    curated_value,
                    total_matches,
                    file_name,
                    created_at,
                    updated_at
                ) VALUES (
                    source.id,
                    source.source_hash,
                    source.source_name,
                    source.dt,
                    source.genesis_path,
                    source.genesis_value,
                    source.genesis_value_type,
                    source.is_transformed,
                    source.schema_key,
                    source.curated_value,
                    source.total_matches,
                    source.file_name,
                    source.created_at,
                    source.updated_at
                );

the same error:

pyspark.errors.exceptions.captured.AnalysisException: [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name `glue_catalog`.`curation_db_prod`.`gjp_mapped_flat`.`id` cannot be resolved. Did you mean one of the following? [`glue_catalog`.`curation_db_prod`.`gjp_mapped_flat`.`id`, `glue_catalog`.`curation_db_prod`.`gjp_mapped_flat`.`dt`, `glue_catalog`.`curation_db_prod`.`gjp_mapped_flat`.`file_name`, `glue_catalog`.`curation_db_prod`.`gjp_mapped_flat`.`created_at`, `glue_catalog`.`curation_db_prod`.`gjp_mapped_flat`.`updated_at`].; line 4 pos 19;
'MergeIntoTable ('glue_catalog.curation_db_prod.gjp_mapped_flat.id = 'source.id), [updateaction(None, assignment('schema_key, 'source.schema_key), assignment('curated_value, 'source.curated_value), assignment('total_matches, 'source.total_matches), assignment('file_name, 'source.file_name), assignment('updated_at, 'source.updated_at))], [insertaction(None, assignment('id, 'source.id), assignment('source_hash, 'source.source_hash), assignment('source_name, 'source.source_name), assignment('dt, 'source.dt), assignment('genesis_path, 'source.genesis_path), assignment('genesis_value, 'source.genesis_value), assignment('genesis_value_type, 'source.genesis_value_type), assignment('is_transformed, 'source.is_transformed), assignment('schema_key, 'source.schema_key), assignment('curated_value, 'source.curated_value), assignment('total_matches, 'source.total_matches), assignment('file_name, 'source.file_name), assignment('created_at, 'source.created_at), assignment('updated_at, 'source.updated_at))]
:- SubqueryAlias glue_catalog.curation_db_prod.gjp_mapped_flat

Hi, in case you did not find out about this already, maybe check if it is related to this issue in spark 3.5 with the property write.spark.accept-any-schema: https://github.com/apache/iceberg/issues/9827

Regards