What change data capture forbids, and what escaping costs
What the area forbids
These are the things no design here can do.
- You cannot detect a change the source did not record. No mechanism recovers an untraced mutation, and none can measure its own blind spot.
- You cannot preserve cross-row transactional atomicity through key-partitioned transport without giving up the parallelism that made the transport worth having.
- You cannot incrementally maintain a non-invertible aggregate in time proportional to the change.
- You cannot replay beyond your retention.
- You cannot verify that derived state matches source state by any means internal to the incremental path. Only a recomputation proves it.
Principle: derived state drifts, and only a full recomputation can prove it has not
Every long-lived incremental system needs a periodic full rebuild or reconciliation. This is not to fix known bugs. It is anti-entropy (a regular process that finds and repairs divergence) against unknown ones. It is the same idea as read repair in replicated databases (checking replicas against each other when you read) and scrubbing in storage (re-reading data to find silent corruption), relocated to the pipeline layer. It is the same answer every time a system cannot verify its own invariant locally.
A practical reconciliation compares cheap summaries, not full data: row counts and a checksum per partition or key range, on both sides. Only ranges that differ are re-copied.
-- run on source and on warehouse, then compare per day
SELECT date_trunc('day', created_at) AS day,
count(*) AS n,
sum(total) AS revenue,
sum(hashtext(order_id::text || status || total::text)::bigint) AS checksum
FROM orders GROUP BY 1;The escapes and their prices
Each limitation above has an escape, and every escape has a price.
| Escape | What it removes | What it costs |
|---|---|---|
| Full recompute | every constraint above | S · c per run. It is the correct answer far more often than the discussion suggests |
| Idempotent keyed sinks | the exactly-once problem | path-dependence: you can never build an audit trail on that sink |
| Retention (keep the log longer) | the aliasing problem, and replay limits | money proportional to time |
| Approximation (sketches) | the inverse requirement | exactness |
| Two-phase commit | dual-write divergence | availability and operational sanity |
| The outbox | the borrowed-interface problem | a write amplification you control |
Every one of these is the conserved-cost rule: the cost moved; none of them deleted it.
Retention is a negotiation
A shape appeared three times here without being new:
- log segments versus the CDC consumer (Detect),
- snapshot expiry versus the table-format reader (Coarse incrementality),
- the replay horizon versus the reprocessing need (Repair).
This is the general rule that every immutable store grows an indirection layer plus a garbage collector, now with a second half: the collector's schedule is set by whoever is furthest behind.