Batch Streaming and Change Data Capture Patterns

Published by Programming Line in Data Engineering in Depth. Last updated .

Purpose and outcome

Data engineering systems move records from operational systems into analytical stores, serving layers, search indexes, and machine learning features. The three core movement patterns are batch, streaming, and change data capture, usually shortened to CDC. Batch pipelines collect data over a bounded interval and process it as a set. Streaming pipelines process events continuously as they arrive. CDC pipelines read database changes, such as inserts, updates, and deletes, and replay those changes into another system.

The practical outcome of this lesson is knowing how to choose and combine these patterns. A daily revenue table may be simplest as batch. A fraud alert pipeline may need streaming latency. A customer dimension copied from PostgreSQL into a warehouse may need CDC so updates and deletes are not lost. In real data platforms, these patterns often work together: CDC captures source mutations, streaming infrastructure carries them, and batch backfills repair or rebuild derived state.

How the patterns work internally

A batch pipeline has a start boundary, an end boundary, and a repeatable input selection. It might read all objects under a date partition such as s3://raw/orders/dt=2026-09-07/, transform them, and write a partition in a warehouse table. Its correctness depends on stable partition definitions, idempotent writes, and late-arriving data policy. Common batch terms include partition, snapshot, backfill, checkpoint, and compaction.

A streaming pipeline treats input as an unbounded sequence. Internally, stream processors split that sequence into offsets or sequence numbers, track progress per partition, and use checkpoints to resume after failure. Events are often processed by event time, the time the event happened, rather than processing time, the time the worker saw it. Windowing groups events into intervals, and watermarks estimate how far event time has advanced. Because retries and rebalances happen, stream consumers must expect duplicate delivery unless the full stack provides transactional exactly-once semantics.

CDC starts at the database log rather than at an application event bus. Relational databases persist changes in write-ahead logs or binary logs before applying them to data files. A CDC connector reads this ordered log, converts each mutation into an event, and records a source position such as a log sequence number. A typical CDC event contains a key, an operation code, the previous or new row image, a commit timestamp, and source metadata. Downstream consumers apply those events in order per key, commonly with upsert and delete semantics.

API and record anatomy

A batch job configuration usually specifies an input path or table, a partition predicate, a transformation, and an output mode. Output mode matters: append creates additional files, overwrite replaces a partition, and merge updates existing rows by key. A streaming job configuration adds a subscription or topic, consumer group, checkpoint location, trigger interval, and state retention policy. CDC configuration adds source database connection details, included tables, snapshot mode, replication slot or log reader identity, and delete handling.

A compact CDC record can be represented as JSON with fields such as key, op, before, after, and lsn. The operation might be c for create, u for update, and d for delete. The downstream merge should compare log positions, not arrival order alone, because network retries can deliver an older event after a newer one.

Example 1: micro-batch deduplication

This first example models a streaming system that processes small batches of events. The same event appears twice because a producer retried after a timeout. The consumer keeps the highest offset it has accepted for each event id and emits each logical event once.

events = [
    {"event_id": "e1", "offset": 10, "amount": 19},
    {"event_id": "e2", "offset": 11, "amount": 7},
    {"event_id": "e1", "offset": 12, "amount": 19},
]

seen = set()
accepted = []
for event in events:
    if event["event_id"] in seen:
        continue
    seen.add(event["event_id"])
    accepted.append(event)

print(sum(event["amount"] for event in accepted))
print([event["event_id"] for event in accepted])

The deterministic output is 26 and ['e1', 'e2']. This is at-least-once delivery with idempotent processing. It does not prove the event was processed exactly once globally; it shows the consumer made its side effect stable when a duplicate arrived.

Example 2: ordered CDC merge

The next example applies CDC records to a customer table. Each key keeps the latest log sequence number. A delete removes the row only if it is newer than the current stored version.

changes = [
    {"key": 1, "op": "c", "after": {"name": "Ada", "tier": "free"}, "lsn": 100},
    {"key": 1, "op": "u", "after": {"name": "Ada", "tier": "pro"}, "lsn": 105},
    {"key": 1, "op": "u", "after": {"name": "Ada", "tier": "free"}, "lsn": 102},
    {"key": 2, "op": "c", "after": {"name": "Lin", "tier": "free"}, "lsn": 110},
    {"key": 1, "op": "d", "after": None, "lsn": 115},
]

state = {}
versions = {}
for change in changes:
    key = change["key"]
    if change["lsn"] <= versions.get(key, -1):
        continue
    versions[key] = change["lsn"]
    if change["op"] == "d":
        state.pop(key, None)
    else:
        state[key] = change["after"]

print(state)
print(versions)

The older update at log sequence number 102 is ignored because version 105 has already been applied. The final delete at 115 removes customer 1, leaving only customer 2. This is the heart of CDC sink logic: use source ordering metadata to make replay, retry, and out-of-order arrival safe.

Example 3: backfill plus live stream

A common production workflow is to load a historical snapshot and then apply live CDC from the snapshot boundary forward. The boundary prevents losing changes that happened while the snapshot was running.

snapshot = [
    {"key": 1, "value": "A", "snapshot_lsn": 50},
    {"key": 2, "value": "B", "snapshot_lsn": 50},
]
cdc = [
    {"key": 2, "op": "u", "after": {"value": "B2"}, "lsn": 49},
    {"key": 3, "op": "c", "after": {"value": "C"}, "lsn": 51},
    {"key": 1, "op": "u", "after": {"value": "A2"}, "lsn": 52},
]

state = {row["key"]: {"value": row["value"]} for row in snapshot}
boundary = max(row["snapshot_lsn"] for row in snapshot)

for change in cdc:
    if change["lsn"] <= boundary:
        continue
    state[change["key"]] = change["after"]

print(state)

The change at 49 is skipped because it is already represented by the snapshot. Changes at 51 and 52 are applied. The result is keys 1, 2, and 3, with key 1 updated to A2. Without a boundary, a pipeline can either double-apply old changes or miss updates that occur during snapshot extraction.

Design choices and trade-offs

Batch is usually cheaper and easier to reason about because the input is bounded. It is strong for audits, recomputation, and large joins. Its weakness is latency: if the job runs hourly, the freshest answer may be nearly an hour old. Streaming lowers latency but introduces state management, watermark tuning, duplicate handling, and operational sensitivity to consumer lag. CDC preserves database mutations more faithfully than periodic extracts, especially deletes and updates, but it couples the pipeline to source log retention, schema evolution, and transaction ordering.

The main design question is not which pattern is best, but what freshness, correctness, and recovery guarantees the data product needs. A metrics dashboard may use batch for stable daily totals and streaming for an approximate current-hour panel. A warehouse dimension may use CDC for current state and batch snapshots for periodic reconciliation. A machine learning feature may need streaming updates for online serving and batch recomputation for training consistency.

Failure modes and troubleshooting

One common batch failure is a partially written partition. The symptom is missing rows or duplicate files after a retry. The cause is usually append output into a destination that already contains partial results. Diagnose by checking job attempt logs, file modification times, and row counts by partition. Correct it with atomic partition replacement, write-then-rename where supported, or a table format that commits transactionally.

A streaming failure often appears as rising consumer lag. The cause may be a slow sink, skewed key distribution, or state store growth. Diagnose by comparing input rate, processing rate, checkpoint age, and partition-level lag. Correction can include increasing parallelism, reducing per-event sink calls through batching, repartitioning hot keys, or shortening state retention when the business rules allow it.

A CDC failure may appear as a connector that cannot resume because the source log position expired. The source database has already removed the log segment needed by the connector. Diagnose by comparing the connector checkpoint with database log retention and source replication status. Correct the immediate problem with a fresh snapshot, then increase log retention or alert before connector lag approaches the retention window.

Schema drift is another frequent CDC issue. A source column is renamed or its type changes, and the sink starts rejecting records. Diagnose by inspecting the first failing record, the source DDL history, and the sink table schema. Correct by applying a compatible schema migration, using a schema registry or DDL capture, and deciding whether incompatible changes should stop the pipeline or route records to quarantine.

Security, performance, and reliability implications

CDC connectors often need elevated read privileges because they access replication logs. Grant only the required replication and table permissions, and avoid giving the connector broad administrative rights. Streaming topics that carry CDC events may contain sensitive before and after images, so access control should be enforced at the topic, storage, and sink layers.

Performance depends on batching and state size. A CDC sink that commits every row separately will overload many warehouses. Batching changes by table and committing them as merges is usually more efficient, but it increases latency. Reliability depends on replayability: retain raw events long enough to recover, store checkpoints outside ephemeral worker disks, and make sink writes idempotent by key and source version.

Hands-on lab: simulate a CDC sink

Prerequisites: a local Python 3 interpreter and a terminal. No external services are required. The goal is to simulate a target table that can tolerate duplicate, stale, update, and delete events.

  1. Create a temporary working directory.
  2. Save the following Python program as cdc_lab.py.
  3. Run python cdc_lab.py.
  4. Verify that the final table contains only account 20 with balance 75.
  5. Cleanup by deleting the temporary directory.
changes = [
    {"account_id": 10, "op": "c", "after": {"balance": 100}, "lsn": 1},
    {"account_id": 10, "op": "u", "after": {"balance": 120}, "lsn": 2},
    {"account_id": 10, "op": "u", "after": {"balance": 110}, "lsn": 2},
    {"account_id": 20, "op": "c", "after": {"balance": 75}, "lsn": 3},
    {"account_id": 10, "op": "d", "after": None, "lsn": 4},
]

table = {}
applied_lsn = {}

for change in changes:
    key = change["account_id"]
    lsn = change["lsn"]
    if lsn <= applied_lsn.get(key, 0):
        continue
    applied_lsn[key] = lsn
    if change["op"] == "d":
        table.pop(key, None)
    else:
        table[key] = change["after"]

print(table)
assert table == {20: {"balance": 75}}

Verification succeeds when the program prints {20: {'balance': 75}} and exits without an assertion error. To experiment, move the delete before the update or duplicate an earlier event. The expected behavior is that only events with a greater source log sequence number change the table.

Assessment exercises

  1. A batch job overwrites yesterday’s partition every hour to catch late events. What must be true about the partition boundary and write mode for retries to be safe?
  2. A CDC consumer receives update LSN 900, then update LSN 870 for the same key. Explain the correct sink behavior and why arrival order is insufficient.
  3. A streaming aggregation uses event-time windows and receives events that are thirty minutes late. What watermark decision controls whether those events update the result or are dropped?
  4. You need a warehouse table that reflects deletes from an operational database. Compare a daily full extract with CDC, including one reliability risk for each.
  5. Design a recovery plan for a CDC connector whose saved log position has expired. What data must be rebuilt, and what setting should change afterward?

Summary

Batch, streaming, and CDC are complementary movement patterns. Batch gives bounded recomputation and auditability. Streaming gives low-latency processing over an unbounded event log. CDC gives ordered database mutation history, including updates and deletes. Strong data engineering designs make the source boundary explicit, preserve ordering metadata, write idempotently, and keep enough raw history to replay after failures.