CDC with Talend: Change Data Capture Patterns That Scale

"Just load what changed" sounds simple until you try to define changed. This tutorial lays out the change data capture patterns we use in Talend estates, from the cheap and slightly wrong to the robust and more involved, and the warehouse-side merge logic that makes any of them correct.

Two fundamentally different approaches

Query-based CDC asks the source: "give me rows where updated_at > last_run." It needs nothing but a timestamp or version column and read access.

Log-based CDC reads the database's transaction log (MySQL binlog, PostgreSQL logical replication/WAL, SQL Server CDC tables, Oracle redo via LogMiner) and streams every insert, update, and delete as an event, in commit order.

Everything else is a variation on one of these.

Pattern 1: Timestamp / high-water-mark (query-based)

The Talend job:

tDBInput (SELECT MAX(updated_at) FROM target_stg) --> tJavaRow: globalMap.put("hwm", ...)
  --OnSubjobOk--> tDBInput (SELECT * FROM src WHERE updated_at > ? AND updated_at <= ?)
  --Main--> tMap --> tDBOutput / bulk load into staging
  --OnSubjobOk--> MERGE (see below)

Rules that make it reliable:

• Bound the window on both ends. Capture now() from the source database at the start of the run, and query updated_at > hwm AND updated_at <= run_ts. Storing the upper bound as the new high-water mark avoids missing rows committed during the run.
• Subtract an overlap. Query from hwm - 5 minutes and let the MERGE deduplicate. Long-running source transactions can commit rows with an updated_at earlier than rows you already loaded.
• Use a monotonic column if one exists. An integer version or sequence is safer than a timestamp, which can repeat or be set by application code.

What it cannot do: detect hard deletes. If a row disappears from the source, nothing in the query tells you. Pair this pattern with either soft deletes in the source or a periodic key-reconciliation job (load all source keys, anti-join to find deletions).

Pattern 2: Snapshot diff (query-based, no timestamp)

When the source has no reliable change column, load the full table into a staging area and diff against the previous snapshot. In Talend, tMap with an inner-join lookup and reject flows will produce inserts, updates, and deletes; at scale, do the diff in the warehouse with a FULL OUTER JOIN on keys and a hash comparison of the non-key columns:

SELECT COALESCE(n.id, o.id) AS id,
       CASE WHEN o.id IS NULL THEN 'I'
            WHEN n.id IS NULL THEN 'D'
            WHEN n.row_hash <> o.row_hash THEN 'U' END AS op
FROM snapshot_new n
FULL OUTER JOIN snapshot_old o ON n.id = o.id
WHERE o.id IS NULL OR n.id IS NULL OR n.row_hash <> o.row_hash;

It catches deletes, which Pattern 1 cannot. It costs a full extract every run, so it suits tables up to a few tens of millions of rows on a daily schedule.

Pattern 3: Talend's CDC components (log-adjacent)

Talend Studio includes a CDC facility in the repository for supported databases (MySQL, PostgreSQL, Oracle, SQL Server, DB2, and others). Depending on the database, it uses either trigger mode (Talend creates triggers and change tables in the source database) or the database's own log/redo mechanism (SQL Server CDC, Oracle redo). You then use tDBCDC-style input components (for example tMysqlCDC, tOracleCDC, tMSSqlCDC) that read pending changes, with an operation column (I/U/D), and mark them consumed per subscriber.

Good for:

• Shops that want CDC without another piece of infrastructure
• Deletes and updates captured faithfully, in order
• Multiple Talend subscribers consuming the same change stream

Watch out for:

• Trigger mode adds write overhead to the source and needs DBA approval; it is intrusive in ways DBAs rightly dislike.
• Batch, not streaming. You still run a job on a schedule; latency is your schedule interval.
• Retention and cleanup of change tables must be managed, or they grow forever.

It is a reasonable choice for moderate-volume sources on a batch cadence. Check the current component documentation on the Talend help portal for your database's supported mode.

Pattern 4: Debezium-style log streaming

For high volume, low latency, or many downstream consumers, a dedicated log-based CDC platform is the better fit. Debezium reads the transaction log and publishes one event per row change to Kafka (or via Debezium Server to other sinks), with before/after images, the operation, the source LSN/position, and the commit timestamp.

Talend's role in that architecture is usually:

• Consumer: Talend's Kafka components (tKafkaInput) or a Spark streaming job read the change topics and apply them to the warehouse
• Batch applier: land events to object storage / a raw table continuously, then a scheduled Talend or dbt step applies them with the merge pattern below
• Not the capture layer: let Debezium own log reading; it is a hard problem and they have solved it

Rough guidance: under a few million changes a day, batch cadence, one consumer: Patterns 1 to 3. Above that, sub-minute latency, or multiple consumers: Pattern 4.

The merge pattern that makes all of them correct

Whichever capture pattern you use, apply changes in the warehouse with logic that tolerates out-of-order and duplicate delivery.

-- changes: id, op ('I','U','D'), payload columns, change_ts, change_seq
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY change_ts DESC, change_seq DESC) AS rn
  FROM changes_stg
  WHERE load_batch_id = '${batch_id}'
),
latest AS (SELECT * FROM ranked WHERE rn = 1)
MERGE INTO dim_customer t
USING latest s ON t.id = s.id
WHEN MATCHED AND s.op = 'D' AND s.change_ts >= t.last_change_ts THEN
  UPDATE SET is_deleted = TRUE, deleted_at = s.change_ts, last_change_ts = s.change_ts
WHEN MATCHED AND s.op IN ('I','U') AND s.change_ts >= t.last_change_ts THEN
  UPDATE SET name = s.name, email = s.email, is_deleted = FALSE,
             last_change_ts = s.change_ts
WHEN NOT MATCHED AND s.op IN ('I','U') THEN
  INSERT (id, name, email, is_deleted, last_change_ts)
  VALUES (s.id, s.name, s.email, FALSE, s.change_ts);

What this handles:

• Duplicates (the overlap window in Pattern 1, at-least-once delivery in Pattern 4): ROW_NUMBER keeps one event per key per batch, and change_ts >= t.last_change_ts makes re-applying an old event a no-op.
• Late-arriving data: an event older than what is already applied is ignored rather than regressing the row.
• Soft deletes: deletes set a flag and timestamp instead of removing the row, so history and foreign keys survive; downstream models filter WHERE NOT is_deleted.
• Re-inserted keys: an insert after a delete flips is_deleted back.

If your source uses soft deletes itself (a deleted_at or status = 'D' column), treat those rows as op = 'D' in the change feed rather than as updates.

Type 2 history

When you need the history of every change, not just the latest state, replace the MERGE with a snapshot-style model: close the current version (valid_to = change_ts) and insert a new one (valid_from = change_ts, valid_to = NULL) per change event, ordered by change_ts, change_seq. dbt snapshots or a tMap with the current-row lookup both implement this; the warehouse version scales better.

Operational checklist

• Store the high-water mark or log position durably after the merge commits, never before
• Reconcile keys against the source on a schedule (weekly is common) to catch missed deletes in query-based patterns
• Alert on change volume anomalies; zero changes on a Monday morning is almost always a broken capture, not a quiet business
• Keep the raw change feed for a retention period so a bad merge can be replayed

Want CDC that survives contact with production? Contact us.