Scenarios

1% of rows change. With a merge penalty of 20×, the crossover is at 5%, so 1% is well inside. An incremental run costs about 1% × 20 = 0.2 of a full run, about 1.8 hours, plus fixed costs that are justified at this scale.

Log-based CDC with before-images; a lock-free incremental snapshot to bootstrap; staged changes deduplicated to one row per key at the highest log position; merge-on-read or rewriting whole partitions, depending on how changes spread across files; and the consumer's position committed together with the state.

The source's log retention is now coupled to the consumer (alert on it, and cap it); partitioning the transport by key breaks transaction atomicity; schema changes need an agreement with the DBA.

A nightly checksum per partition and a monthly full rebuild. Go incremental, but budget for reconciliation from the start.

15% of space left at 4% an hour: about 3.75 hours until the disk is full.

Decide between catching up and cutting losses. Restarting the reader works if it can read faster than the source writes; check its throughput. If it cannot catch up within about 3 hours, drop the replication slot to protect the primary, accept that the table must be re-bootstrapped with an incremental snapshot, and tell the DBA. The primary's availability outranks the pipeline.

Alert on the age of the oldest unread log position, cap log retention on the source, write a runbook for re-bootstrapping, and add a health check on the reader.

The consumer's liveness is the source's availability problem.

Compute a count and a checksum per day on both sides to find which days differ: far cheaper than a full comparison.

For each differing day: writes that bypassed capture (direct-path loads, TRUNCATE, restores), a consumer position committed before its state when it crashed (loss), polling watermarks skipping long transactions, a schema change that silently dropped a column, or a gap in retention.

Repair only the differing ranges. Then reconcile nightly, rebuild fully every month, and require that operations which bypass capture are announced.

The pipeline cannot detect this on its own: a missing trace looks exactly like no change.

Partitioning the transport by key splits a transfer's two rows across partitions.

1. Partition by transaction: keeps a transfer together, but serializes each transaction and can lose per-account order when two transfers touch one account in different partitions.
2. Buffer each transaction at the sink and commit it atomically: latency grows with the largest transaction and memory is unbounded for a huge one, so cap transaction size and route oversized ones to a slow path.
3. Accept intermediate states, but publish a marker "consistent up to position P", the lowest position across all partitions, and have dashboards read only up to P.

Option 3 with a short delay, since a few seconds is the stated tolerance; option 2 only for the one report that needs a strict balance.

One stalled partition holds P back for everyone, so alert on the spread between partitions.

It needs before-images, order per key and an atomic close-and-insert, so log-based CDC with partitions ordered by key; polling cannot do it.

MAX has no inverse. Options: rescan a city when its maximum is deleted (linear in the city's rows; fine if cities are small or deletes rare); keep every value, or the top K, per city (storage proportional to input, or to K × cities); or recompute each minute only the cities that were touched. With about 500 cities and rescans of the hot ones, this is cheap.

Each commit has a fixed cost, so choose a cadence, say 20–30 seconds, where the overhead stays small; "within a minute" is a floor to meet, not a free setting.

Capture once, feed both, and reconcile nightly.

A dual write has a crash window between its two writes, and retrying inside the same process does not help if the process dies in that window. The loss rate is the failure rate times the window, on every write.

1. Retry plus periodic reconciliation, which hides the problem without removing it.
2. A transactional outbox: write the event in the same transaction as the business row, and publish from the outbox with a tailing process or CDC.
3. CDC directly from the business tables.

The outbox adds one write per event and needs old rows cleaned up, and delivery is at least once, so consumers must be idempotent, using the event ID.

The outbox: it gives an explicit, controlled event schema rather than one borrowed from the tables.

You can replay only as far back as retention: 14 days.

Rebuild the older 36 days, and in practice the whole range, by full recomputation from the source table or archived snapshots. Build into a shadow table; start the live pipeline earlier than the backfill's end so the seam overlaps deliberately; use an idempotent, keyed upsert sink; run old and new in parallel and compare until there are no differences; then swap by pointer. Throttle the backfill so it does not starve the live pipeline or the source.

Raise retention to cover the longest repair you are willing to do, or archive the topic to cheap storage, accepting a cost that grows with time kept.

Incremental cost = changed fraction × size × cost per changed row + fixed cost. For small tables the fixed cost dominates: a 2-minute rebuild cannot be beaten by a system that needs infrastructure and an on-call rota. For tables with high churn, the changed fraction is above the crossover: 20% daily change with a 20× penalty costs 4 times the rebuild.

A rebuild corrects itself every night. An incremental pipeline is a running total over all history, correct only if nothing was ever lost, duplicated or reordered, and it fails silently.

Decide per table: large tables with low churn go incremental, with reconciliation; append-only partitioned data uses simple watermarks; everything else is rebuilt.

Log-based CDC, reading the database's write-ahead log: it is complete (deletes, every intermediate state, commit order) and cheap for the source. Query-based polling is acceptable only if you consume full-state upserts and can tolerate missing deletes.

The replication slot keeps write-ahead log on disk while the consumer is down. Set max_slot_wal_keep_size, monitor slot lag, and decide whether you would rather lose the slot (and take a new snapshot) than the database.

The first full copy needs chunked snapshotting with watermarks (record a low watermark, read a chunk, record a high watermark, and drop snapshot rows whose key changed between the two), so that it has no gaps and no ordering errors.

They arrive as events, so define the policy in advance: fail, evolve, or quarantine.