Define what real time means for the consumer
A change-data-capture pipeline can move events quickly while delivering an incorrect view of the business. A consumer may replay an update twice, process a delete before an older insert, or report low Kafka lag while the serving database remains several minutes behind.
This guide is for data engineers and technical decision makers designing Kafka and CDC pipelines. The objective is to preserve the meaning and order of source changes through ingestion, processing and serving, with a recovery procedure that works when a connector or consumer stops at an inconvenient moment.
Key takeaways
- Define freshness from source commit to consumer-visible state.
- Preserve stable keys, source positions and explicit operation types.
- Understand ordering within partitions and avoid assuming a global order.
- Make sink updates idempotent and reconcile uncertain outcomes.
- Test snapshots, replay, schema changes and deletion behavior together.
Start with the source and its change contract
CDC observes changes from a source system and represents them as events. Depending on the connector and database, those events may include an operation type, before and after values, source position and transaction metadata. The exact envelope and guarantees are connector-specific, so inspect real messages and the documentation for your chosen version.
Decide whether downstream consumers need current state, a history of transitions or both. A current-state table can replace an older value with a newer one. An audit history must preserve distinct changes. Using the same append behavior for both goals creates either duplicates in the current view or missing history in the event record.
Specify how inserts, updates and deletes are represented. Some pipelines distinguish a delete event from a tombstone used for log compaction. Consumers need to understand both rather than treating every null payload as an ordinary empty record. Also define what happens when the source lacks a stable primary key.
+------------------+ +--------------------+
| Source database | --> | CDC connector |
| Commit log | | Snapshot + offsets |
+------------------+ +---------+----------+
|
+---------v----------+
| Kafka partitions |
| Key + event schema |
+---------+----------+
|
+---------v----------+
| Validate + transform|
+---------+----------+
|
+---------v----------+
| Idempotent sink |
| State + checkpoints|
+---------+----------+
|
+---------v----------+
| Consumer-visible |
| freshness checks |
+--------------------+
Treat the initial snapshot as part of the change stream design. The connector must coordinate the snapshot and ongoing changes so that the resulting view is complete and consistent according to its supported semantics. Do not implement an independent bulk export and start reading new events later without understanding the gap or overlap between them.
Choose a business freshness objective
An inventory alert may need a different delay budget from a daily analytical dashboard. Define the required source-to-visible freshness and the response when it is exceeded. The label real time is not a service objective until the consumer can say which timestamp starts the clock and which observable result stops it.
Partition and order by the entity that changes
Kafka preserves ordering within a partition, not across an entire topic. Choose keys so changes that need relative ordering reach the same partition. An order identifier may be appropriate for order state, while a customer identifier may be necessary for a customer-level workflow. The right key follows the consistency requirement.
Changing partition counts or partitioning logic can affect where future events land. Plan such changes carefully when consumers rely on per-key ordering across the transition. A throughput improvement is not useful if it introduces ambiguous update order at the sink.
The Kafka design documentation explains delivery semantics and the boundaries of transactional processing. Do not extend an exactly-once claim to an arbitrary external database merely because Kafka transactions are enabled. The sink's commit behavior and consumer offset coordination still determine end-to-end correctness.
| Delivery approach | Typical behavior | Consumer responsibility |
|---|---|---|
| At most once | Loss is possible, duplicates avoided by delivery strategy | Decide whether loss is acceptable |
| At least once | Events may be delivered again | Deduplicate or apply idempotently |
| Kafka transactional processing | Coordinates supported Kafka reads and writes | Respect isolation and transaction boundaries |
| External sink integration | Depends on connector and destination | Verify atomicity, retries and recovery |
A source position is not always a simple globally increasing integer. It may be meaningful only within a partition, table or connector stream. Preserve the original representation and compare it according to the source's semantics. If you introduce your own sequence, document the scope in which ordering is valid.
Distinguish event time from processing time
Event time describes when the business event occurred, while ingestion and processing timestamps describe movement through the pipeline. Late arrival is possible even with a healthy stream. Windowed aggregates need a policy for allowed lateness, correction and finalization rather than assuming arrival order is business order.
Build an idempotent sink
A practical current-state sink often stores a stable key and the latest accepted source position. An incoming change is applied only if it is newer according to the defined ordering rule. This makes replay safe when the same event arrives again and prevents an older update from overwriting newer state.
The following runnable Python example uses an illustrative integer sequence scoped to each entity. It keeps deletion markers so an older insert cannot resurrect a deleted record. It is an in-memory demonstration; production state and sequence checks must be committed atomically in durable storage.
state = {}
def apply_event(event):
key = event["id"]
previous = state.get(key)
if previous and event["sequence"] <= previous["sequence"]:
return False
state[key] = {
"sequence": event["sequence"],
"deleted": event["operation"] == "delete",
"value": event.get("value"),
}
return True
assert apply_event({"id": "o1", "sequence": 2,
"operation": "update", "value": 25})
assert apply_event({"id": "o1", "sequence": 3,
"operation": "delete"})
assert not apply_event({"id": "o1", "sequence": 1,
"operation": "insert", "value": 20})
assert state["o1"]["deleted"]
print(state)
A PostgreSQL sink can express the same conditional update with an upsert. The SQL below is runnable in a test database and uses temporary tables. The ordering field remains illustrative; replace it with a representation appropriate to the actual source and connector.
CREATE TEMP TABLE current_orders (
order_id text PRIMARY KEY,
source_sequence bigint NOT NULL,
amount_minor bigint,
deleted boolean NOT NULL
);
INSERT INTO current_orders AS current
(order_id, source_sequence, amount_minor, deleted)
VALUES ('o1', 2, 2500, false)
ON CONFLICT (order_id) DO UPDATE SET
source_sequence = EXCLUDED.source_sequence,
amount_minor = EXCLUDED.amount_minor,
deleted = EXCLUDED.deleted
WHERE EXCLUDED.source_sequence > current.source_sequence;
SELECT * FROM current_orders;
Committing the sink and recording the consumed offset separately creates a failure window. If the process stops after the sink commit but before offset acknowledgement, the event may return. Idempotency handles that duplicate. If offsets advance before durable sink completion, data may be lost. Verify the exact connector and consumer behavior instead of relying on a diagram.
Handle schema changes and bad records explicitly
Version the event contract and classify changes by consumer compatibility. Adding a field may be acceptable to tolerant consumers but break strict deserializers. Renaming a field or changing its units is a semantic migration even if the encoded type remains the same. Test old and new consumer versions during rollout.
A schema registry can help validate supported compatibility rules, but it cannot establish every business invariant. An amount changing from dollars to cents may still be encoded as an integer. Keep unit definitions, key meaning and deletion semantics in the contract, and test them in transformations and sinks.
When an event fails validation, decide whether to stop the partition, isolate the record or publish an explicit incomplete state. Skipping a failed update may make later updates impossible to interpret correctly, especially if events contain partial changes. The right policy depends on whether each event is a complete state or a delta.
A dead-letter destination needs ownership, retention and a replay procedure. Include the original source position, schema version, error category and protected payload reference. Replaying a corrected event should preserve its original identity so deduplication and ordering continue to work.
Keep transaction boundaries in view
Some business operations update multiple source rows together. Per-key ordering alone does not guarantee that downstream readers see those changes atomically. Decide whether consumers can tolerate intermediate states or need transaction-aware processing and publication. State that requirement explicitly before selecting a connector or sink design.
For analytics, a reconciled batch publication may be acceptable even if ingestion is continuous. For an operational decision, partial transaction visibility may be unacceptable. Streaming transport does not remove the need to define the consistency model exposed to consumers.
Rehearse replay and monitor the whole path
Recovery begins with knowing what data is still available. Kafka retention, source log retention and connector offsets all affect whether a stopped pipeline can resume. Monitor the distance between the consumer's recovery point and the oldest retained data. A connector that is down long enough may need a new snapshot rather than a simple restart.
Track lag in several forms. Consumer offset lag indicates queued records, while source-to-sink time indicates freshness. A low-volume stream can have small offset lag and still be stale. Add heartbeat or source-progress signals where appropriate, and verify that they represent meaningful progress rather than a healthy process with no new source data.
The data contracts guide explains how to turn freshness, schema and completeness expectations into actionable checks. For a CDC pipeline, those checks should include delete propagation and reconciliation between source and destination, not just connector uptime.
Run a controlled recovery drill
Use a staging source with synthetic entities. Insert a row, update it twice, delete it and capture the expected final state. Stop the consumer after a sink write, restart it and replay the retained events. Verify that the final state and event history match their separate contracts and that no deleted entity is resurrected.
Next, test a schema change and an invalid event. Confirm that the documented quarantine or blocking policy occurs and that a corrected replay restores consistency. Record the recovery steps and the time spent identifying the failed boundary. These observations are operational evidence, not a performance benchmark.
Finally, compare representative source and sink keys, counts and business totals. Sampling can identify drift, but the reconciliation method should reflect the consequences of missing data. Some tables need complete reconciliation at defined intervals, while others can use partition-level checks with targeted investigation when differences appear.
Common mistakes
- Measuring only Kafka lag and ignoring source-to-consumer freshness.
- Assuming ordering across partitions or comparing source positions outside their scope.
- Acknowledging offsets before the destination has committed durable state.
- Dropping deletion markers so older replayed events recreate removed records.
- Sending failures to a dead-letter topic without ownership or a replay plan.
Checklist
- Define source event semantics and the consumer's freshness objective.
- Verify snapshot coordination, stable keys and ordering scope.
- Test the actual connector, serialization and sink guarantees.
- Make updates and deletion handling idempotent under replay.
- Specify schema migration and bad-record behavior.
- Monitor retention headroom, source progress and sink visibility.
- Rehearse interruption, replay and reconciliation with synthetic data.
FAQ
Does Kafka guarantee exactly-once updates in my database?
Not automatically. Kafka's supported transactional guarantees have specific boundaries, and an external sink needs its own compatible commit or idempotency strategy. Test the entire source-to-sink path.
Is CDC the same as event sourcing?
No. CDC observes database changes, while event sourcing models domain events as the authoritative record of state changes. A row update may not contain the business intent represented by a domain event.
Can I ignore delete events for analytics?
Only if the dataset's documented semantics explicitly allow retaining those records. Otherwise, ignoring deletes can produce incorrect counts, stale dimensions or inappropriate retention of removed data.
How should I choose a Kafka message key?
Choose the entity or scope within which changes require ordered processing. Then evaluate throughput and partition distribution while preserving that consistency requirement.
Final thoughts
Reliable real-time pipelines preserve meaning through retries, reordering and recovery. Define the contract, prove the sink behavior and monitor when consumers can actually see a correct result.
For help designing that pipeline, explore my data engineering service and book a conversation.
Comments
No comments yet. Start the conversation.
Leave a comment