Understanding Retract Updates in FlinkSQL: Append vs Retract Modes

If you encounter an error like

Table is not an append-only table. Use the toRetractStream() in order to handle add and retract messages.

while using FlinkSQL, you need to understand the concept of retract updates.

Introduction : In simple terms, a "retract update" corresponds to the traditional update operation in databases; in streaming scenarios, a retract represents how data updates are processed.

When converting a table to a stream in FlinkSQL, you can choose between toAppendStream and toRetractStream. Selecting toAppendStream by mistake will cause errors for update or delete operations.

Difference

Append Mode : This mode can only be used when a table is strictly append‑only, i.e., it only receives INSERT statements and never updates previously emitted results. Attempting updates or deletions in this mode will fail.

Retract Mode : This mode is always available. It returns a boolean flag where true indicates an insertion and false indicates a retraction, thus supporting updates and deletions.

According to the official documentation, if data is only being appended, use Append mode; otherwise, use Retract mode, especially when the SQL contains COUNT() or GROUP BY clauses.

Below is an example illustrating the output of retract updates:

The result shows groups of operations where true represents a write and false represents a retraction.

We will publish a separate article detailing how FlinkSQL implements retract updates.