AWS Glue Job Bookmarks Failing in Glue 5.0 but Working in Glue 4.0 with Snowflake JDBC

Question

**Problem Summary:**

I have successfully implemented AWS Glue Job Bookmarks for incremental processing from Snowflake to Iceberg using AWS Glue 4.0. However, the same code fails when running on AWS Glue 5.0 with JDBC-related errors.
Working Configuration (Glue 4.0):

```
dynamic_frame = glue_context.create_dynamic_frame.from_options(
    connection_type="custom.jdbc",
    connection_options={
        "useConnectionProperties": "true",
        "dbTable": "DB_NAME.MY_SCHEMA.TEST_BOOKMARK_DEMO",
        "connectionName": "SnowflakeJdbc",
        "jobBookmarkKeys": ["CREATED_AT"],
        "jobBookmarkKeysSortOrder": "asc"
    },
    transformation_ctx="source_test_bookmark_demo"
)
```
This works perfectly in Glue 4.0 with --job-bookmark-option=job-bookmark-enable, correctly processing only new/changed records based on the CREATED_AT column.

**Failure in Glue 5.0:**

The exact same code fails in Glue 5.0 with:
`java.lang.RuntimeException: Glue ETL Marketplace: failed to retrieve last row from source.
driverClassName: net.snowflake.client.jdbc.SnowflakeDriver, 
error message: JDBC driver internal error: exception creating result 
java.lang.ExceptionInInitializerError at 
net.snowflake.client.jdbc.Snowflake-Result-Set-SerializableV1.(Snowflake-Result-Set-SerializableV1.java:365)`

This suggests AWS Glue might be generating invalid SQL syntax when appending bookmark-related ORDER BY clauses to custom queries.

**Questions:**
Is this a known compatibility issue between Glue 5.0 and Snowflake JDBC for Job Bookmarks?
Are there specific Snowflake JDBC driver versions or connection parameters required for Glue 5.0?
Is there an updated pattern for implementing Job Bookmarks with JDBC sources in Glue 5.0?
Are there any workarounds besides downgrading to Glue 4.0?

**Environment:**
AWS Glue Version: 5.0 (Python 3.9, Spark 3.5)
Snowflake JDBC Connection (using AWS Secrets Manager for credentials)
Bookmark keys properly defined in connection options
Any guidance would be greatly appreciated!

**I've added some hifens in the problematic snowflake driver class name in the error message because the anti span filter was not allowing to post it as it's.**

re:Post Agent · Answer

Based on the information provided, there appears to be a compatibility issue with job bookmarks when using Snowflake JDBC connections in AWS Glue 5.0.

For JDBC sources with job bookmarks, AWS Glue has specific requirements:

1. If your script doesn't specify columns as bookmark keys (which yours does with "jobBookmarkKeys"), the table's primary key needs to be sorted in increasing or decreasing order without gaps.

2. When using user-defined bookmarks as keys (as in your case with "CREATED_AT"), these keys should be sorted in increasing or decreasing order (which you've correctly specified with "jobBookmarkKeysSortOrder": "asc").

3. You should avoid using columns with case-sensitive names as bookmark keys.

While your configuration works correctly in Glue 4.0, the error in Glue 5.0 suggests there might be an internal issue with how the newer version interacts with the Snowflake JDBC driver when processing bookmarks.

For Snowflake connections specifically, AWS Glue provides built-in support in version 4.0 and later. Instead of using "custom.jdbc" as the connection type, you might want to try using the native Snowflake connector that AWS Glue provides.

To do this:
1. Create a Snowflake connection in AWS Glue Data Catalog (make sure to select "SNOWFLAKE" as the connection type, not "JDBC")
2. Use this connection in your Glue job

This approach might avoid the JDBC-specific bookmark issues you're encountering.

If you need to continue using the JDBC approach, you could try:
- Ensuring your Snowflake JDBC driver is compatible with Glue 5.0
- Checking if there are any case sensitivity issues with your bookmark key column
- Temporarily downgrading to Glue 4.0 until the compatibility issue is resolved

As this appears to be a specific interaction issue between Glue 5.0 and the Snowflake JDBC driver when processing bookmarks, you might need to contact AWS Support for further assistance if the native Snowflake connector approach doesn't resolve the issue.  
**Sources**  
[Troubleshoot AWS Glue job bookmarks | AWS re:Post](https://repost.aws/knowledge-center/glue-job-bookmarks)  
[Connecting to Snowflake in AWS Glue Studio - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/connecting-to-data-snowflake.html)  
[Creating a Snowflake connection - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/creating-snowflake-connection.html)

rePost-User-8807689 · Answer

Hello,

The issue you are facing is likely due to the fact that the AWS Marketplace Snowflake Connector for AWS Glue only supports Glue 4.0, Glue 3.0, and Glue 1.0/2.0, but not Glue 5.0. This means that the connector does not have support for the newer version of Glue that you are using.

You can find more information about the supported versions on the AWS Marketplace page for the Snowflake Connector: https://aws.amazon.com/marketplace/pp/prodview-xdyn5il56d7a2

Instead of using the marketplace connector, I would suggest looking into using the native AWS Glue connection for Snowflake, which is documented here: https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connect-snowflake-home.html

This native Glue connection for Snowflake supports both Glue 4.0 and Glue 5.0.

However, it's important to note that the native Glue Snowflake connector does not support job bookmarks. This is a known limitation, as documented here: https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connect-snowflake-home.html#aws-glue-programming-etl-connect-snowflake-limitations

Hence, to answer your question, currently using Snowflake with Job bookmarking is unfortunately not supported, but this feature will be added in the coming days

AWS Glue Job Bookmarks Failing in Glue 5.0 but Working in Glue 4.0 with Snowflake JDBC

Add your answer

Relevant content