How to use Glue bookmark to track last processed row using timestamp per grouping column

AWS Glue uses one or more columns as bookmark keys to determine new and processed data. But this does not work as you intend to use it.

• For single columns used as a bookmark, Glue considers these as unique IDs and read all IDs greater than the last val
• For multiple columns listed as bookmarks, it works to identify the last value from both columns. The docs are not detailed as to how they work, but my test did not pick up all cases where there were new id's and timestamps.

You can specify jobBookmarkKeys and jobBookmarkKeysSortOrder in the following ways:

create_dynamic_frame.from_catalog — Use additional_options.

create_dynamic_frame.from_options — Use connection_options.

Use the below example when using from_catalog

datasource0 = glueContext.create_dynamic_frame.from_catalog(
    database = "devices", table_name = "device_reading",
    transformation_ctx = "datasource0",
    additional_options = {
        "jobBookmarkKeys": ["device_id","timestamp"],
        "jobBookmarkKeysSortOrder": "asc"
    }
)

Please follow this reference for any more information https://docs.aws.amazon.com/glue/latest/dg/monitor-continuations.html

Glue job bookmark works in this fashion:

• For Amazon S3 Data sources, AWS Glue job bookmarks check the last modified time of the objects to verify which objects need to be reprocessed.

• For JDBC data stores Job Bookmarks you can specify the column names to use as bookmark keys. By default PRIMARY key is used but each bookmark keys has to follow a rule that either they are increasing or decreasing with no gap.

Thus there is no issue when it come for S3 data sources, But if it the case that it is JDBC data source you have to compound keys as your timestamp column is not contiguous or try to use a single column which have contiguous data.

additional_options = {
        "jobBookmarkKeys": ["device_id","timestamp"],
        "jobBookmarkKeysSortOrder": "asc"
    }