Acquire / file · All operations

Read change messages as inserts, updates and deletes

A variant of Read a JSON file

Read JSON change-data-capture messages so each one becomes an insert, update or delete row.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Read change messages as insert, update-before, update-after and delete rows

SQL
-- change-data-capture messages: op c creates, u updates (before and after images), d deletes (before image only)
COPY (SELECT * FROM (VALUES
  ('{"op":"c","ts":1,"before":null,"after":{"id":1,"customer":"Alice Ng","amount":120}}'),
  ('{"op":"u","ts":2,"before":{"id":1,"customer":"Alice Ng","amount":120},"after":{"id":1,"customer":"Alice Ng","amount":130}}'),
  ('{"op":"c","ts":3,"before":null,"after":{"id":2,"customer":"Bob Okafor","amount":50}}'),
  ('{"op":"d","ts":4,"before":{"id":2,"customer":"Bob Okafor","amount":50},"after":null}')) t(line))
  TO '<your_scratch_dir>/changes.json' (FORMAT csv, HEADER false, QUOTE '', ESCAPE '', DELIMITER '~');
CREATE TEMP TABLE result AS
WITH messages AS (SELECT json AS m FROM read_json_objects('<your_scratch_dir>/changes.json'))
SELECT (m->>'$.ts')::INTEGER AS ts, kind, rec->>'$.id' AS id, rec->>'$.customer' AS customer, (rec->>'$.amount')::INTEGER AS amount
FROM (
  SELECT m, 'insert' AS kind, m->'$.after' AS rec FROM messages WHERE m->>'$.op' = 'c'
  UNION ALL SELECT m, 'update_before' AS kind, m->'$.before' AS rec FROM messages WHERE m->>'$.op' = 'u'
  UNION ALL SELECT m, 'update_after' AS kind, m->'$.after' AS rec FROM messages WHERE m->>'$.op' = 'u'
  UNION ALL SELECT m, 'delete' AS kind, m->'$.before' AS rec FROM messages WHERE m->>'$.op' = 'd');
SELECT * FROM result ORDER BY ts, kind DESC;
SELECT CASE WHEN ((SELECT count(*) FROM result) = 5 AND (SELECT list(kind ORDER BY ts, kind DESC) FROM result) = ['insert', 'update_before', 'update_after', 'insert', 'delete'] AND (SELECT list(amount ORDER BY ts, kind DESC) FROM result) = [120, 120, 130, 50, 50]) THEN 'as expected' ELSE error('the four messages did not become an insert, the before and after rows of an update, an insert and a delete') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes changes.json itself, in <your_scratch_dir>.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the four messages did not become an insert, the before and after rows of an update, an insert and a delete”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache SeaTunnel (5)
  • Canal Formatread 2026-09-27

    Read Canal JSON change messages as insert, update and delete rows, and write them back

  • Debezium Formatread 2026-09-27

    Read Debezium JSON change messages as insert, update and delete rows, and write them back

  • Kafka source compatible kafka-connect-jsonread 2026-09-27

    Read Kafka messages that a Kafka Connect source produced

  • MaxWell Formatread 2026-09-27

    Read Maxwell JSON change messages as insert, update and delete rows, and write them back

  • Ogg Formatread 2026-09-27

    Read Oracle GoldenGate JSON change messages as insert, update and delete rows

Background

Apache SeaTunnel reads Debezium's JavaScript Object Notation (JSON) change messages as INSERT, UPDATE and DELETE messages.[1] Change data capture (CDC) is a set of software design patterns used to determine and track the data that has changed, so that action can be taken using it.[2]

When to use it

In Apache SeaTunnel, reading change messages as inserts, updates and deletes serves cases such as synchronising incremental data from databases to other systems, auditing logs and real-time materialised views.[1] CDC can be used for incremental update of data loading, and occurs often in data warehouse environments.[2] Reach for it when a feed carries changes rather than snapshots, and you need the table those changes add up to or the history of how it got there. (Meridian's view)

In Debezium's change events for MySQL, the op field is c when a row is created, u when it is updated, d when it is deleted and r when it is read during a snapshot.[3] After a delete event, the Debezium MySQL connector by default emits a tombstone event with the same key and a null value, so that Apache Kafka log compaction can remove all earlier messages with that key.[3] Skip tombstone messages when reading change rows, rather than turning them into empty rows. (Meridian's view) In Apache SeaTunnel, UPDATE messages must contain a complete row image, and partial updates containing only changed fields are not supported.[1]

The method

Two techniques for tracking changes are in common use: database triggers, and reading the transaction log as, or shortly after, it is written.[2] The structure, contents and use of a transaction log are specific to a database management system, and no standard exists for transaction logs.[2]

Compared with the alternatives

CDC and the slowly changing dimension (SCD) model are similar in that both can detect changes in a data set, and SCD type 2 can be useful if history is needed in the target system.[2] CDC overwrites in the target system, akin to SCD type 1, and is ideal when only the changed data needs to arrive at the target.[2]

Sources

  1. [1]Debezium Format | Apache SeaTunneldocumentation
  2. [2]Change data capture - Wikipediasecondary source
  3. [3]Debezium connector for MySQL :: Debezium Documentationdocumentation