CDC Failure Modes and the Retention Deadline

1. Source tables

   holds Current state only. Every intermediate value has been overwritten.

   could corrupt Nothing from this domain's side — but it is also the proof that a re-snapshot recovers state and never history (Slowly Changing Dimensions).

   ↑ reads from
2. Source transaction log

   holds Every committed change, from the oldest retained position forward. Short window, production disk.

   could corrupt A dropped slot or an expired retention removes changes permanently. This is the deadline; everything else in the chain is downstream of it surviving.

   ↑ reads from
3. Connector position

   holds One number: where capture has confirmed up to.

   could corrupt Advancing it manually skips changes silently. Two position stores can disagree and skip the difference.

   ↑ reads from
4. Broker topics

   holds Every published change for the broker's own retention — usually much longer than the source's, and much cheaper.

   could corrupt Compaction that keeps only the latest value per key preserves state and discards the history a transition metric needs (Retention and Replay).

   ↑ reads from
5. Raw landing zone

   holds Every event that was ever consumed, retained as long as object storage retention allows.

   could corrupt Being deduplicated or transformed on write, which destroys the evidence needed to diagnose the incident in the first place (Keeping Raw History: The Recovery Position and the Liability).

   ↑ reads from
6. Current-state model

   holds One row per key at its highest applied position.

   could corrupt A replay into an append-only sink; a manual repair that leaves the row at a position matching nothing in the stream.

   ↑ reads from
7. Serving tables and dashboards

   holds Whatever was last published, rendered with complete confidence.

   could corrupt Nothing new — but this is where a permanent gap is finally noticed, typically weeks later and by a person (Stale Dashboards).

Two retention windows, two owners. During an incident, establish which of them still contains the affected period before deciding anything else — that single fact determines whether this is a restart or a re-bootstrap.

1-- 1. Which incident is this? Position distance per reader,
2--    plus how much log is being retained on its behalf.
3--    Catalog names differ per engine; the three facts do not.
4SELECT
5    r.reader_name,
6    r.confirmed_position,
7    s.current_log_position,
8    s.current_log_position - r.confirmed_position AS position_distance,
9    r.retained_log_bytes,
10    r.is_active
11FROM   replication_readers r
12CROSS  JOIN log_state s
13ORDER  BY position_distance DESC;
14 
15-- Read it as a decision, not as a number:
16--   is_active = false and retained bytes growing -> the source is
17--       paying for your outage; the deadline has not passed yet.
18--   confirmed_position older than the oldest retained position
19--       -> the deadline HAS passed. Replay does not exist.
20--       Re-snapshot, and tell consumers history is missing.
21 
22-- 2. Position flat while the connector is healthy?
23--    Capture cannot advance past the oldest open transaction.
24SELECT
25    session_id,
26    state,
27    now() - transaction_started_at AS open_for,
28    query_text
29FROM   active_sessions
30WHERE  transaction_started_at IS NOT NULL
31ORDER  BY transaction_started_at
32LIMIT  10;

Neither query is exotic and both are usually written for the first time during the incident they were needed for. The one that pages should be the first: alert on position_distance against the retention bound, early enough that a human can still act, and never on seconds-behind — which reads zero on an idle source and is silent through exactly the overnight stall it exists to catch.