Streaming & CDC Reconciliation: Architectural Patterns for Cross-Engine Reconciliation

Streaming and change-data-capture reconciliation is the discipline of proving parity between two systems that never stop moving. Where a batch pipeline reconciles a frozen snapshot, a streaming reconciler must decide whether a source and a target agree while both are being written to — records arrive out of order, a commit lands in the source milliseconds before its replicated copy reaches the target, and “equal” is only ever true within a bounded, explicitly declared window. This reference is written for the data engineers, migration specialists, Python pipeline builders, and platform operations teams who run continuous replication — Debezium into Kafka, Kinesis into a lake, a dual-write cutover — and are asked to guarantee that nothing was silently dropped, duplicated, or reordered along the way.

Part of the Cross-Engine Data Reconciliation knowledge base.

Architectural Mandate: Why Streaming Reconciliation Exists

A batch reconciler answers “do these two datasets agree?” against inputs that hold still long enough to be hashed. Streaming removes that luxury. The source table advances continuously, the replication path adds non-deterministic latency, and any naive attempt to compare “the source now” against “the target now” reports a storm of false discrepancies that are nothing but in-flight records. The streaming reconciliation stage exists to make that comparison meaningful: it aligns both sides to a common notion of time, tolerates the reordering that every distributed log introduces, and reduces the question to a precise, defensible claim — the source and target agreed for every event whose timestamp is at or below watermark W.

Without a disciplined streaming reconciler, three failure classes dominate production. First, phantom discrepancies — alerts fired for records that are not missing, only late, which train operators to mute the channel exactly when it matters. Second, silent gaps — a dropped CDC event or a compacted Kafka tombstone that a snapshot comparison can never see because both sides converged after the loss. Third, duplicate application — an at-least-once sink that applied the same change twice, inflating aggregates in a way row counts alone will never reveal. Every downstream sign-off gate inherits the correctness of this stage, so it must be engineered as the authoritative, replayable record of when two moving systems were last provably equal.

End-to-end streaming reconciliation topology A source database emits a change-data-capture stream into a log, which feeds a watermark aligner and then a windowed comparator. The comparator reads source and target state from a checkpointed state store. It branches two ways: a parity match advances the watermark and commits a checkpoint, while divergence emits a delta manifest into a dead-letter queue for remediation. parity divergence Source DB CDC stream Log Kafka / Kinesis Watermark aligner Windowed comparator Advance watermark commit checkpoint State store source vs target rows Delta manifest → dead-letter queue
Every arrow is a bounded handoff: change events flow through the log to a watermark aligner and a windowed comparator that reads checkpointed state — parity advances the watermark and commits, divergence is serialized to a delta manifest and routed to a dead-letter queue.

Pipeline Topology and Stage Placement

Streaming reconciliation is not a replacement for the batch control plane described in the cross-engine data reconciliation architecture — it is its real-time complement, and the two share the same canonical intermediate representation. Change events are captured from the source’s transaction log, validated so the stream provably reflects committed source state, aligned on event time so late and out-of-order records do not manufacture false differences, and compared inside bounded windows against target state read from a checkpointed store. A parity result advances the low watermark and commits a checkpoint; a divergence is serialized into a discrepancy manifest and routed for remediation exactly as a batch mismatch would be. This shared contract is what lets an organization run continuous reconciliation during a cutover and a periodic full structural diff as a backstop without maintaining two incompatible notions of “equal”.

Core Concepts and Design Constraints

Three constraints define every workload in this stage, each with a streaming-specific meaning:

  • Determinism under reordering. The same set of change events, replayed in any physical order, must produce the same reconciliation verdict. This forces event-time semantics: comparison is keyed on the source commit timestamp and a monotonic log offset, never on wall-clock arrival, so a replayed stream reconciles identically to the original.
  • Idempotency of application. A streaming reconciler is itself a consumer, and consumers crash and restart. Every state mutation — advancing a watermark, recording a discrepancy, committing a checkpoint — must be idempotent so an at-least-once redelivery after a restart cannot double-count. This is the property that makes exactly-once reconciliation with idempotent sinks achievable without distributed transactions.
  • Bounded, replayable state. Watermark alignment requires buffering in-flight events, but an unbounded buffer is an outage waiting to happen. State is bounded by the maximum tolerated lateness and persisted to a checkpoint so a restart resumes from the last committed offset rather than replaying from the beginning of the log.

Canonical Implementation Patterns

The skeleton below is the shape every workload in this stage specializes: a consumer that tracks a low watermark across partitions, releases events once they can no longer be overtaken by an earlier one, and compares them against target state. The comparison itself is delegated to the same equivalence rules the batch engine uses; only the timing discipline is new.

python
import logging
from dataclasses import dataclass, field
from typing import Dict, Iterable, Iterator, Optional

logging.basicConfig(
    format="%(asctime)s | %(levelname)s | %(name)s | %(message)s", level=logging.INFO
)
logger = logging.getLogger("recon.streaming.core")


@dataclass(frozen=True)
class ChangeEvent:
    """A single CDC record keyed on event time and monotonic log offset."""
    key: str
    op: str                      # "c" insert, "u" update, "d" delete
    event_time_ms: int           # source commit timestamp, not arrival time
    offset: int                  # monotonic per-partition log position
    after: Optional[dict] = None


@dataclass
class WatermarkAligner:
    """Release events only once the low watermark guarantees no earlier one follows."""
    max_lateness_ms: int
    _partition_max: Dict[int, int] = field(default_factory=dict)
    _buffer: list = field(default_factory=list)

    def low_watermark(self) -> int:
        # The watermark is the slowest partition's frontier minus tolerated lateness.
        if not self._partition_max:
            return -1
        return min(self._partition_max.values()) - self.max_lateness_ms

    def push(self, partition: int, event: ChangeEvent) -> Iterator[ChangeEvent]:
        self._partition_max[partition] = max(
            self._partition_max.get(partition, event.event_time_ms), event.event_time_ms
        )
        self._buffer.append(event)
        wm = self.low_watermark()
        # Emit in event-time order everything the watermark has now sealed.
        ready = [e for e in self._buffer if e.event_time_ms <= wm]
        self._buffer = [e for e in self._buffer if e.event_time_ms > wm]
        for e in sorted(ready, key=lambda x: (x.event_time_ms, x.offset)):
            yield e

The full workloads — capture validation, alignment tuning, and state recovery — build on this frame with production error handling, metrics, and persistence. The watermark alignment strategies reference specializes the aligner; change-data-capture validation specializes the source-of-truth check; checkpoint and state recovery specializes the persistence and restart path.

Operational Resilience

A streaming reconciler is a long-lived process, so resilience is a first-class design concern rather than an afterthought. Checkpoint the low watermark and consumer offsets atomically on a fixed interval so a restart resumes exactly where it stopped; size the alignment buffer to the maximum tolerated lateness and shed or dead-letter anything older so a stalled upstream partition cannot exhaust memory; and back every discrepancy write with the same fallback chain the batch engine uses, so a transient sink failure quarantines one record instead of halting the stream. Consumer lag, not throughput, is the resilience signal that matters: a reconciler that keeps up but never checkpoints is one crash away from replaying a day of the log.

Observability and Metrics

Expose watermark_lag_ms (wall clock minus low watermark), events_buffered, late_events_dropped_total, discrepancies_emitted_total, and checkpoint_commit_latency_ms. Alert on watermark lag and buffered-event growth rather than raw throughput — a healthy reconciler can process millions of events per second and still be silently broken if its watermark has stopped advancing. Persist every verdict with the watermark and offset range that produced it so any discrepancy is fully replayable, which is the property auditors in regulated environments require and the reason recovering from checkpoint corruption is treated as a named, drilled procedure rather than an emergency.

Security and Compliance Posture

A CDC stream carries the same regulated fields as the source table, so the reconciler inherits its entire compliance surface. Read the change log through a least-privilege role scoped to the reconciled tables only; mask or pseudonymize PII before it lands in any buffer or dead-letter store, reusing the transforms defined in the security boundaries for reconciliation reference; and write every watermark advance and discrepancy verdict to append-only audit storage. Because a delta manifest may contain divergent field values, treat the dead-letter queue as an in-scope data store for retention and access-control policy, not as ephemeral operational exhaust.

Explore the Streaming & CDC Topics

This section breaks continuous reconciliation into three focused workloads, each with its own implementation patterns and failure modes:

Explore this section