How to Insert Tombstone into a Table in ksqlDB

Tombstones are used to delete rows from a table.

We send NULL as the record value. Let’s see how.

We need to create a new table named table_name_tombstones. Without this table, we wouldn’t be able to send NULL values to the underlying topic.

CREATE TABLE scores_table_tombstones (
  student_id BIGINT PRIMARY KEY,
  tombstone STRING
) WITH (
  KAFKA_TOPIC = 'scores_topic',
  VALUE_FORMAT = 'KAFKA'
);

Note that the table has :

• the same primary key fields as in the original table. It’s important to use the same names and types. See how to find the schema of a table to confirm you are using the right ones.
• exactly one non-primary key field of type STRING or VARCHAR. This field will be used to send the NULL value to the message body.
• the same underlying topic as in the original table. Don’t worry, the table we will create won’t do anything to the topic, it’s just metadata for ksqlDB to know how to operate with it.

Read about how to find the underlying topic of a table and how to find the schema of an existing table.

Use an INSERT statement to send tombstones.

INSERT INTO scores_table_tombstones (
  student_id, tombstone
) VALUES (
  1, null
);

As an example, a table that initially looks like this:

SELECT * FROM scores_table;

+------------+-------+------+
|STUDENT_ID  |NAME   |SCORE |
+------------+-------+------+
|1           |Alice  |4.0   |
|2           |Bob    |5.0   |

After sending the tombstone for Alice with student_id=1 as in the example in the previous section, the same select statement now returns:

+------------+-------+------+
|STUDENT_ID  |NAME   |SCORE |
+------------+-------+------+
|2           |Bob    |5.0   |