Kafka ordering (Single partition Vs Multiple partitions)

Hey,

I think I got the solution from excellent discussion thread below on Kafka Ordering via different solutions. But I would like to double check whether my use case analysis is correct or not. People are active on this forum, need some quick help on this, so posting here.

Use case: Database is sending the messages on different status:

  1. ORDER RECEIVED
  2. ORDER ASSIGNED
  3. ORDER PROCESSED
  4. ORDER COMPLETED

We do not have any ordering defined currently, due to the latency on the database side, we are receiving the ORDER PROCESSED (3rd one) message first than ORDER RECEIVED (1st one) due to which the consumer is consuming the same and sending the same WRONG ordered data to downstreams.

Topic has multiple partitions. So my solution is suggesting below changes on both database side and Kafka side:

Database side: Suggest the database team to append the timestamp field to the message while sending to KAFKA.

Kafka side: Kafka Producer config should be changed to include below settings to support no duplication and enables an idempotent producer which ensures that exactly one copy of each message is written to the brokers, and in order. enable.idempotence=true acks=all

NOTE: No need to have single partition topic, rather in the current topic (with multiple partitions), I think I can achieve the strict ordering with above solution.

Appreciate your comments.

@IcyRoad You don’t specify what DB technology you are using, but have you evaluated Change Data Capture (CDC) as a solution for ingesting data from the DB source into Kafka? If CDC is an option for you, you may be able to preserve (or at least ascertain) ordering from the records. Here is a module in a data pipelines course w/ information on CDC: https://developer.confluent.io/learn-kafka/data-pipelines/kafka-data-ingestion-with-cdc/

@rick , thank you.
We are using SAP. Without introuducing any other CDC solutions, cannot I achieve my purpose via any other means?

Due to the current limitations, we cannot able to introduce any other CDC or alternates. Appreciate any other comments.