Preserving Transaction Order When Migrating Postgresql to S3 with DMS

We are currently in the process of migrating a Postgresql database from RDS to an S3 data lake. During our data migration, we noticed an important aspect related to transaction order preservation that we need to address.

Upon inspecting the CDC data stored as parquet files in S3, we discovered that multiple rows with the same identifier (id) are committed within the same transaction. This means that they share the same commit timestamp, making it challenging for us to identify the latest change for each id. The distinguishing factor among these rows is the 'modified_at' column, and there may be other relevant columns as well. Here's an example:

Our goal is to retrieve the latest change for each id while considering all relevant columns. However, we can't rely solely on partitioning by id + commit_timestamp + modified_at because there may be other columns with timestamps that could influence the order, and we don't know which columns those might be.

We've observed that the data in the parquet files on S3 seems to be ordered as we expect in some cases, but we can't be entirely certain if this is always the case, given that we have many tables replicated to S3. We've manually inspected approximately 10 files, and they appear to be in order. We also noticed a setting called "PreserveTransactions" that can be used on the S3 target. However, according to the documentation, the results are stored in .csv transaction files, represented as operation, table_name, database_schema_name, field_value. Unfortunately, this doesn't address our specific issue because the transaction .csv files do not contain the id (PK) of the row, and we're unsure how to correlate this information with the parquet data.

Our key question is this: Is there a specific setting within Postgresql source, AWS Database Migration Service (DMS), or the S3 target that can guarantee the preservation of transaction order when migrating data from Postgresql to S3 in parquet format? Alternatively, is transaction order already preserved by default, and if so, how can we ensure this consistently across our migration process?