Is there a way to implement a conditional update particularly where a record doesn't exist?

I'm working with Cassandra and need to handle a specific use case:

1. Consume messages from a Kafka Topic.
2. Either insert a new record into Cassandra or update an existing record based on the same ID. The goal is to append data to an object identified by the ID in the Cassandra table.

The issue I'm encountering arises when the ID doesn't exist in the table yet. If multiple Kafka consumers receive the same event simultaneously, both consumers attempt to fetch the record by ID (which doesn't exist), and then try to update it, resulting in a race condition.

Is there a way to implement a conditional update in Cassandra, especially in cases where the record doesn't already exist? I'm not sure how to approach this.

Thanks in advance.

The quick answer is Cassandra provides a mechanism for conditional statements with lightweight transactions (LWTs). You can add conditions like IF EXISTS or IF NOT EXISTS to your CQL statements so they only get executed if the condition is true.

A typical use case is when allocating a new username to a user only if it is not already in use. For example:

INSERT INTO users_by_username (username, firstname, lastname)
  VALUES ('alice123', 'Alice', 'Wonderland')
  IF NOT EXISTS

In the example above, the username alice123 will only get added if it doesn't already exist in the table.

LWTs have their uses but they should only be used when necessary because they are expensive. LWTs require a read-before-write -- Cassandra has to perform a read to evaluate the condition before it can perform the write.

On the other hand, use a simple UPDATE where possible since Cassandra will perform an "upsert" regardless of whether the record exists or not (does not do a read-before-write) so it is very fast. Cheers!