I'm trying to build an event aggregation solution that follows a pattern of change data capture (CDC) in MS SQL Server + Debezium MS SQL Server Connector to publish CDC as events to a kafka topic + aggregator service that consumes CDC events, aggregates them and publishes the aggregates to a different topic.
At the moment, I'm looking for a definitive answer to a question if Debezium`s SQL Server connector, that is tracking more than a single table and uses topic routing transform to route all the messages to the same topic, guarantees that it will publish events from all the tracked tables in a "global" order (like if change to table A happened before change to table B, event about the change in table A is guaranteed to be published first).
I've read through the docs here and did some testing and it seems like it is the guarantee that Debezium provides, but the wording in the docs is a little ambiguous.
The connector sorts the changes that it reads in ascending order, based on the values of their commit LSN and change LSN. This sorting order ensures that the changes are replayed by Debezium in the same order in which they occurred in the database.
In simpler words, it is based on the order of change if it is within the same transaction. If it is not, it will be based on the time the transaction is completed.