Job Bookmark without primary key as bookmark key

Question

Hallo,

I have created Job bookmark with bookmark key provided as "Modtime" field. The primary key in the source table are not increasing monotonically.  For this reason i used "Modtime" fields as bookmark key. When there is new record inserted in source table then this  "Modtime" field will be inserted as     
 DD-MM-YYYY-HH24,MISS.  The  "Modtime" field is not primary key in source table and sometimes there are multiple entries with same  "Modtime" field value. But "Modtime" field are always increasing monotonically with no gaps.

Currently Glue job is running fine with "Modtime" field as job bookmark key. And i did not find any discrepancies or duplicate entries in target table.

I would like to confirm does the job bookmark key should always be primary key in source table or its not mandatory ? In below AWS article its mentioned the job bookmark key should be primary key :
https://docs.aws.amazon.com/glue/latest/dg/monitor-continuations.html

But in below AWS article its mentioned job bookmark key should monotonically increasing but its not necessory to be primary key:
https://docs.aws.amazon.com/glue/latest/dg/programming-etl-connect-bookmarks.html

Below is python script:

```
import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
import datetime

args = getResolvedOptions(sys.argv, ['target_BucketName', 'JOB_NAME'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

outputbucketname = args['target_BucketName']

timestamp = datetime.datetime.now().strftime("%Y%m%d")
filename = f"trt{timestamp}"
output_path_trt = f"{outputbucketname}/{filename}"

AWSGlueDataCatalog_node1712075257312 = glueContext.create_dynamic_frame.from_catalog(database="opo", table_name="_opoh_trt", additional_options = {"jobBookmarkKeys":["MODTIME"],"jobBookmarkKeysSortOrder":"asc"}, transformation_ctx="AWSGlueDataCatalog_node1712075257312")

# Script generated for node Amazon S3 AKS
AmazonS3_node1712075284688 = glueContext.write_dynamic_frame.from_options(frame=AWSGlueDataCatalog_node1712075257312, connection_type="s3", format="csv", format_options={"separator": "®","quoteChar": -1}, connection_options={"path": output_path_trt, "compression": "gzip", "partitionKeys": []}, transformation_ctx="AmazonS3_node1712075284688")
```

job.commit()

re:Post Agent · Accepted Answer

Job bookmarks in AWS Glue do not strictly require the bookmark key to be a primary key in the source table. While using a primary key as the bookmark key is often recommended, it's not mandatory. The most important characteristic of a good bookmark key is that it should be monotonically increasing with no gaps.

In your case, using the "Modtime" field as the bookmark key is a valid approach, especially since you mentioned that it's always increasing monotonically with no gaps. This meets the primary requirement for an effective job bookmark key.

The fact that there can be multiple entries with the same "Modtime" value doesn't necessarily pose a problem, as long as your job logic can handle processing all records with the same timestamp correctly. However, you should be cautious and ensure that your job can consistently process all records, including those with identical "Modtime" values, without missing any data or creating duplicates.

It's worth noting that while your current setup is working fine and you haven't noticed any discrepancies or duplicate entries in the target table, you should continue to monitor the job closely. Ensure that all data is being processed correctly, especially in scenarios where multiple records share the same "Modtime" value.

To summarize, while using a primary key as the job bookmark key is often ideal, it's not strictly necessary. The key requirements for a job bookmark are:

1. It should be monotonically increasing.
2. It should have no gaps.
3. It should allow for consistent and complete processing of all records.

Your current approach using the "Modtime" field appears to meet these criteria. However, always remain vigilant and continue to monitor your job's performance and accuracy to ensure it maintains data integrity over time.  
**Sources**  
[Job runs - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-jobs-runs.html)  
[GetJobBookmark - AWS Glue](https://docs.aws.amazon.com/glue/latest/webapi/API_GetJobBookmark.html)

Job Bookmark without primary key as bookmark key

Repartition the DataFrame to control output files trt

Check for empty partitions and write only if data is present

Add your answer

Relevant content