How to achieve exactly-once at the consumer level in Kafka when saving to a database (to avoid duplicates)?

To implement exactly-once semantics when reading from Kafka and saving data to a database at the consumer level in Java, transactions and idempotency are usually used.

Main approaches:

1. Using Kafka Consumer with manual offset commits after successful database write.

   • First, process the message and save it to the database.
   • After successful saving, commit the offset.
   • However, this does not guarantee exactly-once, as a failure between the write and commit can cause duplication.

2. Idempotent database writes.

   • Add a unique key (e.g., message ID from Kafka) to the table.
   • On retry, the operation with the same key will not change the data.

3. Using Kafka transactions and database transactions (Two-phase commit).

   • Kafka supports transactional producers, but this is more complex for consumers.
   • External systems supporting transactions or special libraries (e.g., Debezium, Kafka Connect with transactional connectors) can be used.

4. Using a "consume-process-produce" pattern with a transactional Kafka producer.

   • The consumer reads a message, processes it, and sends the result to another topic transactionally.
   • Another service reads from this topic and saves to the database with idempotency.

Example of idempotent database write in Java using a unique key from Kafka:

String messageId = record.key(); // unique message key
try (PreparedStatement ps = connection.prepareStatement(
    "INSERT INTO processed_messages (message_id, data) VALUES (?, ?) ON CONFLICT (message_id) DO NOTHING")) {
    ps.setString(1, messageId);
    ps.setString(2, record.value());
    ps.executeUpdate();
}
// After successful write, commit the offset
consumer.commitSync();

Thus, combining idempotent writing and manual offset commits allows achieving exactly-once at the consumer level.