Change Data Capture Fundamentals

Data Engineering & Modern Data Platforms · Lesson 16 · Data Ingestion, ETL and Orchestration

Lesson Overview

The cohort counter from Data Integration Patterns was specified as a fifteen-minute view of who is enrolled now, including hard unenrollments. The first implementation polled WHERE updated_at > :watermark. It worked in the demo, where every update touched updated_at. In production, support deleted test enrollments with a SQL DELETE, and a back-office screen set status through a path that did not bump updated_at. The poll stayed green. The counter kept the deleted learner and the old status. Instructor operations overfilled a cohort. The daily enrollment snapshot, which the completion mart uses, was a different product and was not wrong. The current-state product was wrong because a query cannot see a row that is gone, and it cannot see a change that does not touch the column you filter on.

Change data capture is the family of designs that read inserts, updates, and deletes as changes, rather than as "rows that currently match a predicate." There are three mechanisms, and they do not fail the same way. A query over a watermark is the one that just failed. A trigger writes an audit row in the same transaction as the change, so deletes are visible and the primary pays for an extra write. A log reader decodes the database's write-ahead log, which already had to record the change for crash recovery, and emits it after commit. PostgreSQL's logical decoding is the log reader this course uses as the concrete engine, because the operational store is PostgreSQL. The teaching model in this lesson is a small, ordered change record you can fold in Python. It is not a vendor envelope.

This is the foundations lesson. It covers snapshots, ordering, deletes, replica identity, and the reason a stalled consumer retains log. It does not deploy a connector. Kafka plus Debezium is lesson 46 in Module 4, after the log itself has been taught. Using a connector config here would skip the failure modes the connector is supposed to hide. ETL and ELT Architecture still applies: landing changes in a raw change table and reducing them in SQL is ELT. Emitting only a finished cohort row and discarding the change is how you lose the replay.

Planning assumptions stay labeled. Enrollment is on the order of the active learner population, not fifty million progress events a day. Progress events are append-only and already have a watermark that matches how they are written. They do not need CDC because a poll can see them. Putting the event table on a replication slot "for consistency" adds disk risk to the primary for a product the batch job already completes by 06:00 UTC.

Learning Objectives

By the end of this lesson you will be able to:

1. Explain why a watermark query misses hard deletes and misses updates that do not touch the watermark column.
2. Compare query, trigger, and log capture on write amplification, delete visibility, and operational risk.
3. Fold an ordered change stream, including a prior snapshot, into current state without letting a late older log sequence number resurrect a row.
4. State what PostgreSQL replica identity must include so an update or delete can find the row downstream.
5. Describe a consistent snapshot plus a change stream, and what a replication slot retains when the consumer stops.
6. Keep a CDC delete on the cohort's current-state table, and keep it off the historical completion fact.

Prerequisites

• Lesson 12: CDC was chosen for the cohort counter, and a daily snapshot was chosen for the completion join. This lesson is why that split exists.
• Relational Database Internals: the write-ahead log is already part of how PostgreSQL survives a crash. Logical decoding reads that log. You do not need to re-derive page layout.
• Lesson 8's vocabulary is enough. /learn/postgresql if PRIMARY KEY and transactions are rusty.
• Python 3.11 dataclasses. /learn/python. The fold is standard library only.
• /learn/kafka for the word "log" as a product. This lesson's log is the database WAL. They are not the same object. Lesson 46 puts a decoded stream onto Kafka.

Detailed Core Concepts

What a change is

A change is a committed modification to a key: an insert, an update, or a delete, at a position in the source log. The position in PostgreSQL is a log sequence number, an LSN. Current state for the cohort counter is what you get by applying those changes in LSN order, starting from a snapshot. History for the completion mart is not that current state. A snapshot retained by extract date answers "who was enrolled that morning." A change stream answers "what happened since the snapshot, in commit order."

Aborted transactions are not changes you apply. Triggers that write an audit row in the same transaction disappear if the transaction rolls back. Logical decoding emits a transaction when it commits, not when the statement ran. Both designs agree that a rollback is not a fact. A query watermark cannot make that distinction for a delete, because there is no row left to be in or out of the transaction you are reading.

Query capture

The poll is:

sqlCopy
SELECT learner_id, course_id, status, updated_at
FROM enrollment
WHERE updated_at > %(watermark)s
ORDER BY updated_at, learner_id, course_id;

It returns rows that exist and whose column moved. A DELETE removes the row, so the next poll does not return it, and the destination still has it. An UPDATE that sets status and forgets updated_at does not return it either. Two rows that share the same updated_at are lost if the next watermark is > the maximum and the first poll stopped mid-tie. Clock skew between application timestamps and the database clock moves rows backward across the watermark. Lesson 12's overlap repairs small skew for append-only events. It does not resurrect a deleted key you never received.

Query capture is still honest when the table is append-only, the watermark column is assigned by the database on every insert, and you do not need deletes. Progress events are that case. Calling the poll "CDC" in a design review is how the cohort bug got a sophisticated name.

Trigger capture

A trigger on INSERT, UPDATE, and DELETE writes a row to enrollment_audit in the same transaction. The audit row for a delete carries the key from OLD. The poll then reads the audit table, which is append-only, with its own watermark on audit_id. Deletes are visible. The change is committed exactly when the business change is committed.

The primary pays for it. Every enrollment write becomes two writes, plus indexes on the audit table. The audit table grows without bound unless you retain it on purpose and prune by a rule that does not run ahead of the consumer. A trigger that calls a network service is a way to stall the learner's request. The trigger writes a table. A separate process publishes. That separate process is at-least-once. The audit identity is the idempotency key.

Triggers also capture only what the function writes. A bulk load that disables triggers, a replica that does not fire them the way you think, or a second application that writes the table through a path you did not instrument, will be invisible. The log does not have that gap, because the log is how the write happened. The trigger has that gap, because the trigger is another write someone can bypass.

Log capture

PostgreSQL records committed changes in the write-ahead log. Logical decoding turns those records into row changes through an output plugin. The built-in plugin is pgoutput, used by PostgreSQL's own logical replication. Other plugins exist as extensions. This lesson does not configure them. The guarantees you need are properties of the mechanism, not of a connector brand.

wal_level must be logical, which includes everything replica records plus the information decoding needs. A replication slot remembers how far a consumer has confirmed. The server retains WAL from the slot's restart position until the consumer moves forward. That retention is the feature that lets you restart. It is also how an abandoned or stalled slot fills the disk and stops the primary from accepting writes. Monitoring the slot is part of choosing CDC. A snapshot job does not have this failure mode.

Decoding emits a transaction at commit, in commit order, and preserves the order of changes inside the transaction. A long transaction is invisible to the stream until it commits, and then it appears as a burst. "Fifteen minutes" is not guaranteed for a transaction that was open for an hour. The cohort contract has to say what it does with that burst, usually "apply it as soon as it commits and accept that the row was not visible while the transaction was open," which is also what a poll of the table would have done.

Replica identity

An update or a delete downstream has to find a row. PostgreSQL's REPLICA IDENTITY controls what the log stores as the old key.

DEFAULT uses the primary key. For enrollment the key is (learner_id, course_id). A delete event can carry that key. It does not carry the old status unless you ask for more.

FULL records the entire old row. Use it when the consumer must see the before-image, for example to compute a delta, and accept the WAL volume.

NOTHING records no old identity. Deletes and updates may be impossible to apply. This is the setting people discover after the cohort counter "receives" deletes it cannot match.

USING INDEX uses a named unique index. It is appropriate when the primary key is not the identity the consumer has. It is not a reason to skip a primary key on the source.

The cohort fold in this lesson needs the key on every delete. It needs the new status on insert and update. It does not need the old status. DEFAULT, with a real primary key, matches that need. A design that consumes status transitions needs FULL or an audit trigger that stores OLD.status. Write the need down before you set the identity. Changing it later does not rewrite WAL you already discarded.

Snapshot, then changes

A new slot does not contain the rows already in the table. The stream is "what happens next." The cohort table needs those current rows. The consistent procedure is:

1. Create a logical replication slot so WAL is retained from that moment. The replication protocol can export a snapshot as part of creating the slot. That option is documented with the slot-creation command. This lesson does not wrap it in a helper.
2. Copy enrollment in a transaction that uses that snapshot, so the copy is a single point in time.
3. Stream decoded commits and apply those that were not already visible in the snapshot.
4. Record the LSN you have applied. On restart, resume from the slot, and skip LSNs you have already folded, or fold them idempotently.

If you copy the table without a snapshot coordinated with the slot, rows committed during the copy are either missing or applied twice. Twice is survivable if the fold is idempotent on the key and the LSN. Missing is not, until a later update happens to mention the key. Deletes during the copy are the case that hurts: the snapshot included the row, the delete was in the stream and you dropped it because you thought the snapshot was "after," and the destination keeps a learner who is gone. The coordination is the design. "We full-refreshed and then turned on the stream" without an LSN handshake is the bug.

Ordering and the resurrection bug

Apply changes for one key in increasing LSN order. The last LSN wins. A delete at LSN 30 and an update at LSN 20, applied in the order they arrived at a parallel worker, writes the update after the delete and resurrects the enrollment. Sorting the batch by LSN before apply fixes the batch. It does not fix two workers who interleave one key. Partition work by the enrollment key, or apply serially per key. Do not partition the cohort stream by a random consumer and call the key "eventually consistent" if the product is "who is enrolled now."

An identical redelivery of the same LSN is a no-op. The same LSN with a different payload is a breach. You do not get two truths for one position in the log.

What you must not delete downstream

A delete event removes the key from the current-state cohort table. It does not delete historical completion facts, and it does not rewrite yesterday's enrollment snapshot partition. Those products are as-of pictures. The learner who completed a lesson on Wednesday and whose enrollment row was deleted on Friday for an account-closure workflow remains complete for Wednesday. Lesson 13's join uses Wednesday's snapshot. The cohort counter on Friday does not show them. Both are correct. A pipeline that "applies CDC to the warehouse" as one table will either destroy history or fail to drop the cohort. The change is an input. The products each decide what a delete means.

Progress events stay off this mechanism. They are immutable, identified by event_id, and extracted by the batch job. A slot on that table would retain WAL for a high-volume append-only log the analytical estate already copies by time. The cost is primary disk and operational load. The benefit is a second copy of a feed you have. Decline it unless a measured consumer needs commit-order changes faster than the batch, and then design that consumer in Module 4.

Architecture and Design Diagrams

The primary stays the source of record. The slot is a read position, not a second primary. Two analytical products branch after the change is durable in a raw change table.

mermaidCopy
flowchart LR
    subgraph primary [PostgreSQL source of record]
        table[enrollment table]
        wal[Write-ahead log]
        slot[Replication slot]
        table --> wal --> slot
    end

    subgraph capture [Capture]
        snap[Consistent snapshot copy]
        decode[Decode commits after the snapshot]
    end

    subgraph raw [Raw changes, retained]
        changes[(Change table ordered by LSN)]
    end

    subgraph products [Separate products]
        cohort[Cohort current state]
        hist[Daily snapshot partitions]
        mart[Completion facts]
    end

    slot --> decode --> changes
    snap --> changes
    changes -->|apply delete| cohort
    table -->|scheduled snapshot, not the slot| hist
    hist --> mart
    cohort -.->|must not delete| mart

The apply order for one key is a straight line. Arrival order is not allowed to be the line.

mermaidCopy
flowchart TD
    read[Read a batch of changes]
    sort[Sort by LSN within the key]
    seen{This LSN already applied with the same payload?}
    skip[Skip]
    breach{Same LSN, different payload?}
    fail[Fail the batch]
    op{Operation}
    put[Upsert after-image]
    drop[Remove key from current state]
    mark[Record applied LSN]

    read --> sort --> seen
    seen -->|yes| skip --> mark
    seen -->|no| breach
    breach -->|yes| fail
    breach -->|no| op
    op -->|insert or update or snapshot| put --> mark
    op -->|delete| drop --> mark

Snapshot handshake, at the altitude this lesson owes you. The protocol details live in the PostgreSQL documentation and, later, in the connector lesson.

mermaidCopy
sequenceDiagram
    participant Job
    participant PG as PostgreSQL
    participant Dest as Cohort state
    Job->>PG: Create logical slot and export a snapshot
    PG-->>Job: Slot retained, snapshot id
    Job->>PG: Copy enrollment in that snapshot
    PG-->>Job: Rows as of the snapshot
    Job->>Dest: Load snapshot as baseline
    Job->>PG: Decode commits from the slot
    PG-->>Job: Changes in commit order
    Job->>Dest: Apply LSNs not already in the snapshot
    Job->>Job: Store applied LSN after the write is durable

Technical Explanation

Commit order is not statement order

A transaction updates a row at 10:01 and commits at 10:06. A poll at 10:03 does not see it, which is correct for read committed, and a decoder does not see it either. Downstream freshness includes commit delay. A second transaction that commits first is delivered first even if its statement started later. The cohort's meaning of "now" is "commits we have applied," not "statements that have been typed."

Inside one transaction, apply the changes in the order the decoder emits them. A transaction that inserts a row and then updates it should leave the updated image. Reordering inside the transaction is the resurrection bug at smaller scale.

Idempotent apply

For a current-state key, the operation is: if the change is a delete, remove the key if the applied LSN is less than this change's LSN; if it is an insert or update, set the after-image if this LSN is newer than the one stored with the row. Storing the LSN on the destination row makes a reordered delivery harmless even if an earlier bug hands you the batch unsorted, as long as every event for that key eventually arrives and you compare LSNs rather than blindly applying. The walkthrough sorts and also refuses a contradictory duplicate LSN. Production should keep the per-row LSN so a replayed snapshot load does not clobber a newer change. A snapshot row is just a change with an early LSN and an after-image.

Slot health is a primary-health metric

pg_replication_slots exposes restart_lsn and confirmed_flush_lsn. The gap between the server's current WAL position and restart_lsn is WAL the primary cannot recycle. Alert on that gap in bytes, not only on "is the connector process up." A process that is up and stuck not confirming is the dangerous one. Dropping a slot you do not understand lets the primary recycle WAL and throws away the consumer's restart point. You then need a new snapshot. That is sometimes the right recovery. It is not a routine cleanup.

wal_level = logical and a slot are server configuration. They belong in the database owner's change, with disk monitoring in place before the first enrollment week. They do not belong in an application migration that runs unattended because a pull request added a publication.

Publications are a table list, not a filter on meaning

sqlCopy
CREATE PUBLICATION enrollment_pub
    FOR TABLE enrollment;

A publication names tables whose changes logical replication will consider. It is not a business filter. Do not assume a publication that lists enrollment has projected away email. If email is a column on the table, the change can contain email. The cohort raw table is still a landing zone. Project it. A row filter, where the PostgreSQL version you run documents one, is a database feature with a version. Read that version's notes before you depend on it. This lesson's safety is the column list on the destination, plus the prohibition on copying the operational table blindly.

Trigger sketch, so the alternative is concrete

The audit table is append-only. The function runs in the caller's transaction. This is ordinary PL/pgSQL, not a connector.

sqlCopy
CREATE TABLE enrollment_audit (
    audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    op text NOT NULL CHECK (op IN ('insert', 'update', 'delete')),
    learner_id text NOT NULL,
    course_id text NOT NULL,
    status text,
    changed_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE OR REPLACE FUNCTION enrollment_audit_fn()
RETURNS trigger
LANGUAGE plpgsql
AS $
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO enrollment_audit (op, learner_id, course_id, status)
        VALUES ('delete', OLD.learner_id, OLD.course_id, OLD.status);
        RETURN OLD;
    END IF;
    INSERT INTO enrollment_audit (op, learner_id, course_id, status)
    VALUES (lower(TG_OP), NEW.learner_id, NEW.course_id, NEW.status);
    RETURN NEW;
END;
$;

You would attach it with CREATE TRIGGER ... AFTER INSERT OR UPDATE OR DELETE ON enrollment FOR EACH ROW EXECUTE FUNCTION enrollment_audit_fn(); on a database you own. The destination polls audit_id, which is a real watermark because the audit row exists for deletes. You still apply in audit_id order, and you still do not delete completion history. The cost is on the primary for every row, whether or not the cohort consumer is keeping up. The log approach costs WAL retention when the consumer stalls, and it does not add a write to the request path beyond what PostgreSQL already logs. Those are different bills. Pick the one the primary owner will accept.

Debezium, named and deferred

Debezium is a log-based CDC system that reads database logs and produces change events, commonly onto Kafka. Its documentation defines an envelope with an operation, a before image, an after image, and a source position. Lesson 46 implements that path. This lesson's Change record uses the words snapshot, insert, update, and delete so the fold is readable. It is a teaching model of the same ideas. It is not a subset of a client library, and code in this lesson does not import one. If a review asks for "the Debezium JSON," the honest answer is to open Debezium's current documentation in Module 4, after the slot, the snapshot, the LSN, and the delete rule are already decided. A connector will not choose replica identity for you.

Practical Implementation or Design Walkthrough

Problem

Fold enrollment changes into cohort state. A delete removes the key. A lower LSN must not resurrect it. The same LSN with two payloads fails. Show, separately, that a watermark query on the surviving rows cannot report the delete.

pythonCopy
from __future__ import annotations

from dataclasses import dataclass


@dataclass(frozen=True)
class Change:
    """Teaching model. Not a connector payload."""

    lsn: int
    op: str
    learner_id: str
    course_id: str
    status: str | None = None


@dataclass(frozen=True)
class LiveRow:
    learner_id: str
    course_id: str
    status: str
    lsn: int


def apply_in_arrival_order(changes: tuple[Change, ...]) -> dict[tuple[str, str], LiveRow]:
    """What a worker does if it trusts arrival. Used to show resurrection."""
    state: dict[tuple[str, str], LiveRow] = {}
    for change in changes:
        _apply_one(state, change)
    return state


def apply_by_lsn(changes: tuple[Change, ...]) -> dict[tuple[str, str], LiveRow]:
    """Sort by LSN, ignore an identical repeat, refuse a contradictory LSN."""
    ordered = tuple(sorted(changes, key=lambda change: change.lsn))
    state: dict[tuple[str, str], LiveRow] = {}
    seen: dict[int, Change] = {}
    for change in ordered:
        previous = seen.get(change.lsn)
        if previous is not None:
            if previous != change:
                raise ValueError(f"contradictory payload at lsn {change.lsn}")
            continue
        seen[change.lsn] = change
        _apply_one(state, change)
    return state


def _apply_one(state: dict[tuple[str, str], LiveRow], change: Change) -> None:
    if change.op not in {"snapshot", "insert", "update", "delete"}:
        raise ValueError(f"unknown op {change.op}")
    key = (change.learner_id, change.course_id)
    current = state.get(key)
    if current is not None and change.lsn < current.lsn:
        return
    if change.op == "delete":
        if current is not None and change.lsn >= current.lsn:
            del state[key]
        return
    if change.status is None:
        raise ValueError("insert, update, and snapshot require status")
    state[key] = LiveRow(
        learner_id=change.learner_id,
        course_id=change.course_id,
        status=change.status,
        lsn=change.lsn,
    )


@dataclass(frozen=True)
class PolledRow:
    learner_id: str
    course_id: str
    status: str
    updated_at: int


def poll_watermark(
    existing_rows: tuple[PolledRow, ...],
    watermark: int,
) -> tuple[PolledRow, ...]:
    """Rows that still exist and moved. Deleted keys cannot appear."""
    return tuple(
        row
        for row in existing_rows
        if row.updated_at > watermark
    )


def _demo() -> None:
    snapshot = Change(10, "snapshot", "lrn_1042", "course_9", "active")
    update = Change(20, "update", "lrn_1042", "course_9", "active")
    delete = Change(30, "delete", "lrn_1042", "course_9")
    key = ("lrn_1042", "course_9")

    ordered = apply_by_lsn((snapshot, update, delete))
    assert key not in ordered

    resurrected = apply_in_arrival_order((snapshot, delete, update))
    assert resurrected[key].status == "active"
    assert resurrected[key].lsn == 20

    safe = apply_by_lsn((snapshot, delete, update))
    assert key not in safe

    repeat = apply_by_lsn((snapshot, delete, delete))
    assert key not in repeat

    try:
        apply_by_lsn((delete, Change(30, "delete", "lrn_1042", "course_9", "active")))
    except ValueError as exc:
        assert "contradictory" in str(exc)
    else:
        raise AssertionError("contradictory lsn was accepted")

    remaining = (
        PolledRow("lrn_2000", "course_9", "active", 40),
    )
    polled = poll_watermark(remaining, watermark=15)
    assert all(row.learner_id != "lrn_1042" for row in polled)
    print(f"cohort_has_1042={key in safe} poll_saw_delete=False")


if __name__ == "__main__":
    _demo()

The arrival-order function applies the update at LSN 20 after the delete at LSN 30 because that is the tuple order, and _apply_one only ignores an LSN older than the row's stored LSN when the row is still present. The delete removed it, so the older update inserts it again. apply_by_lsn sorts first, so the update lands before the delete and the key ends absent. That contrast is the lesson. The per-row LSN guard is not, by itself, a remedy for "delete, then an older event" unless the delete leaves a tombstone that records its LSN. A production current-state table should keep a tombstone until the consumer's watermark passes it, or it should apply only sorted streams partitioned by key. The demo shows both the bug and the sort. It does not pretend the guard alone solves resurrection after a delete has dropped the row.

How you would run it

bashCopy
python3 cdc_fold.py

Expected line: cohort_has_1042=False poll_saw_delete=False. No database is required for the fold. The publication and the trigger are SQL you run, if you run them, on a database you own, with slot monitoring already in place. Do not create a logical slot on a shared primary to "try the lesson." A slot is a disk commitment.

What this walkthrough does not do

It does not start pgoutput. It does not emit Debezium's JSON. It does not write Kafka. It does not drop completion rows. The historical product remains the snapshot partition from lesson 12. The fold's output is the cohort dictionary only.

Enterprise Real-World Scenarios

The cohort counter that could not see a delete

Support removed a test enrollment with DELETE. The row's updated_at path was never executed, because there was no row to update. The poll's watermark advanced on other learners' legitimate updates. The cohort UI still listed the test learner. A second bug sat beside it: a status change that wrote status and not updated_at. The poll missed that too. Both are the definition of query capture.

The repair was log capture for the cohort product only. Replica identity stayed DEFAULT because the primary key is (learner_id, course_id) and the consumer needed the key on deletes and the new status on updates, not the before-image. A snapshot coordinated with the slot loaded current enrollments. The fold applied later commits by LSN. A drilled DELETE removed the key from cohort state within the freshness bound once the transaction committed. The daily snapshot job continued for the completion mart. The slot's WAL gap was added to the primary dashboard before enrollment week, with a page when the gap exceeded a byte threshold the database owner set. Progress events were explicitly left on the batch extract.

The fifteen-minute bound was restated as "within fifteen minutes of commit, assuming the consumer confirms and no single transaction was open longer than that." A bulk back-office update that holds one transaction for an hour shows up when it commits. The UI copy stopped saying "live" and started saying the bound that was true.

Account closure must not erase Wednesday's completion

A learner closed an account on Friday. The operational workflow deleted the enrollment row and the identity row in the product database, under the privacy procedure lesson 61 will expand. CDC correctly emitted a delete. An early consumer applied "CDC to all warehouse tables that mention the learner" and deleted Wednesday's completion fact. Monday's rate moved. The fact had been legitimate under completion-v3 as of Wednesday's snapshot.

The repair separated the products in the handler, not in the log. The delete removes the cohort key and, when the privacy procedure says so, removes or masks personal attributes in serving tables. It does not remove a completion fact whose contract is historical. The snapshot partition for Wednesday stays. The raw progress events stay for the audit window. The change stream is an input to those decisions. It is not a broadcast "forget this key everywhere." The cohort diagram's refusal, cohort must not delete the mart, is that incident in one arrow.

A tombstone recorded in the current-state table, with its LSN, also stopped a late-arriving update from resurrecting the enrollment in the fold. The teaching function sorts instead, and the production note is stronger: persist the tombstone until you will not see an older LSN again. The scenario's business rule and the fold's technical rule are the same idea. Absence has to be stored somewhere until reorder is impossible.

Best Practices

• Use query watermarks for append-only facts whose column actually moves. Do not call that CDC.
• Capture deletes with a trigger audit or with the log. A poll of the live table cannot see them.
• Keep triggers in-transaction and row-local. Publish from the audit table in another process.
• Set replica identity from the columns the consumer must match. DEFAULT needs a primary key. NOTHING is a delete you cannot apply.
• Coordinate the initial copy with the slot's snapshot. Record the LSN you applied.
• Apply one key in LSN order. Partition by key if you parallelize. Keep a tombstone so an older LSN cannot undo a delete.
• Confirm the slot promptly, and alert on retained WAL in bytes. A stalled consumer is a primary incident.
• Project columns at the change landing. A publication is not a privacy filter.
• Apply cohort deletes to cohort state. Leave historical snapshots and completion facts alone.
• Leave append-only progress events on the batch path unless a measured bound forces a stream, and then do that work in Module 4.
• Treat a contradictory payload at one LSN as a breach, not as a latest-write.
• Read the PostgreSQL version's logical replication chapter before you depend on a row filter or a specific plugin.

Common Mistakes and Anti-Patterns

Polling updated_at and calling it CDC. Deletes and untouched columns disappear. The job is green because the query succeeded. The repair is an audit table or the log, and a test that deletes a row and expects the key to vanish downstream.

A slot with no disk alarm. The consumer stops, WAL accumulates, the primary stops accepting writes, and the cohort project has caused an enrollment outage. The repair is a byte threshold, an owner, and a documented choice to drop and resnapshot rather than to fill the disk.

Replica identity NOTHING. The stream contains a delete the applier cannot match, so it skips it, and the cohort row remains. The repair is DEFAULT with a primary key, or FULL when you need the before-image, set before you rely on the stream.

Uncoordinated snapshot. The copy and the stream overlap or gap, and a delete in the gap is lost. The repair is the exported snapshot handshake, plus an idempotent fold so overlap does not duplicate.

Sorting by arrival time. Parallel workers resurrect deleted enrollments with older updates. The repair is LSN order per key and a tombstone that remembers the delete's position.

One CDC pipeline for every table. History, current state, and append-only events get the same delete behavior. Completions vanish or progress volume sits on a slot. The repair is a product decision per table, which lesson 12 already required.

Connector first. A Debezium config lands in a pull request before anyone can say what a delete does to the mart. The repair is this lesson's rules, then lesson 46's implementation. The config does not choose the rules.

Trigger that performs HTTP. The learner's enrollment request waits on the warehouse. The repair is an audit insert in the transaction and a publisher elsewhere.

Troubleshooting and Design Review

Symptom: A deleted enrollment is still in the cohort counter. The poll job succeeded.

Likely cause: the capture is a query on the live table, or the delete event has no key because replica identity is NOTHING, or the applier ignored a delete it could not match.

Investigation: confirm the row is gone in PostgreSQL. Inspect whether any change record exists for the key. Check REPLICA IDENTITY and the primary key. Confirm the consumer's last LSN is beyond the delete's commit.

Corrective action: stop calling the poll CDC. Fix identity, resnapshot if the stream cannot represent the delete, and apply the key removal. Do not update the counter by hand except as a break-glass that you then reconcile to the stream.

Symptom: The primary's disk fills and writes start failing. A CDC consumer was deployed last week.

Likely cause: the replication slot is inactive or not confirming. WAL cannot be recycled.

Investigation: pg_replication_slots for active, restart_lsn, and confirmed_flush_lsn, compared with current WAL. Identify the consumer process.

Corrective action: restore the consumer if the lag is recoverable. If it is not, drop the slot with the database owner's approval, knowing you must snapshot again, and only after the disk can accept writes. Add the byte alert before you recreate the slot.

Symptom: A learner who completed a lesson last week disappeared from the Monday mart after closing their account.

Likely cause: a delete from CDC was applied to the historical fact.

Investigation: find the completion row's absence and the delete's LSN. Identify which handler consumes the change.

Corrective action: restore the fact from the snapshot and raw events for that window. Change the handler so cohort state absorbs the delete and the mart does not. Replay the window. Treat the privacy workflow as a separate, explicit masking job with its own contract.

Symptom: After a restart, some enrollments are duplicated and others are missing.

Likely cause: the snapshot and the stream were not tied to one LSN. Overlap inserted twice where the fold was not idempotent. A gap dropped deletes.

Investigation: compare destination counts to a fresh snapshot of the source for a sample of keys. Check whether the stored LSN on each row is monotonic.

Corrective action: rebuild cohort state from a coordinated snapshot and the slot, with the fold in this lesson. Do not patch individual keys without an LSN.

Symptom: The cohort is fifteen minutes stale even though the consumer is caught up to the slot.

Likely cause: the change is in an open transaction. Decoding emits it at commit. The consumer is caught up to what has committed.

Investigation: look for long-running transactions on the primary. Compare statement time with commit time for one key.

Corrective action: explain the bound as commit time. Shorten the application transaction if the product truly needs the row sooner. A poll would not have shown the uncommitted row either.

A design review asks: Which product is current state, and which is history? How are deletes represented? What is the replica identity, and does a primary key exist? How is the snapshot tied to the slot? What is the apply order for one key? Who is paged when the slot retains WAL, and what is the byte threshold? What is the resnapshot procedure? Which tables are deliberately not on the slot? A review that starts with a connector version has not captured the change.

Interview Questions and Answers

Q1. What is change data capture, and what is a watermark query that people mislabel as CDC?

Change data capture delivers inserts, updates, and deletes as a stream of committed changes with a position. A watermark query returns rows that currently exist and whose watermark column is newer than the last poll. The query misses hard deletes because the row is not there to satisfy the predicate, and it misses updates that do not touch the column. That poll is a valid incremental extract for append-only progress events. It is not a capture of enrollment current state. I use the term CDC when a delete has a representation downstream.

Q2. Compare trigger capture and log capture.

A trigger writes an audit row in the same transaction, so a delete is a row you can poll, and a rollback removes the audit row too. The primary does an extra write on the request path, and any writer that bypasses the trigger is invisible. Log capture decodes the write-ahead log the database already produces, so it sees the writes that committed, including deletes, without a second write in the application. The cost moves to WAL retention: a stalled slot can fill the disk. I choose the log when the primary owner will run wal_level=logical and monitor the slot. I choose the trigger when they will not, and the extra write fits the request budget.

Q3. Why does replica identity matter on a delete?

The downstream applier has to know which key disappeared. REPLICA IDENTITY DEFAULT puts the primary key in the old tuple, so the enrollment key can be removed from cohort state. FULL adds the entire old row, which you want for a before-image and pay for in WAL volume. NOTHING can omit the identity, and the applier then has a delete it cannot match, which looks like a successful consume of a no-op. I set identity from the columns the product compares, and I require a primary key before I trust DEFAULT.

Q4. How do you initialize a destination that has been empty while the source has been live?

I create a replication slot so WAL is kept, copy the table in a snapshot coordinated with that slot, and then apply decoded commits that were not visible in the snapshot, in LSN order, idempotently. An uncoordinated "copy, then turn on the stream" double-applies or drops the deletes that happened during the copy. I record the applied LSN after the destination write is durable. On restart I resume the slot and skip or re-fold those LSNs. The PostgreSQL replication protocol's exported snapshot is the documented handshake. I do not invent a private one.

Q5. A delete event arrives, and then an older update for the same key. What should happen?

The key stays deleted. Apply order is LSN order, not arrival order. If a worker applies the older update after removing the row, it resurrects the enrollment. I partition by key or sort by LSN, and I keep a tombstone with the delete's LSN so a late older event is ignored even if the row was removed from the visible table. An identical redelivery of the delete is a no-op. A different payload at the same LSN is a breach.

Q6. Should the completion mart subscribe to enrollment deletes?

No. The mart's enrollment input is the daily snapshot partition, as-of the event date. A Friday account-closure delete removes the learner from the cohort counter's current state. It does not remove Wednesday's completion. One consumer that applies every CDC delete to every warehouse table will rewrite history or violate a privacy rule in the wrong place. Privacy masking is its own job with its own contract. The change stream is an input to several products, not a single "sync the warehouse" switch.

Q7. Why can a caught-up CDC consumer still be fifteen minutes late?

Because logical decoding emits a transaction at commit. A transaction that started twenty minutes ago and committed just now appears just now. The consumer can be at the tip of the slot and still be waiting on an open transaction it is not supposed to read. I state freshness as commit delay plus apply delay. I look for long transactions before I blame the connector. A query of the live table would also hide that uncommitted work.

Q8. What do you monitor on the primary once a slot exists?

Whether the slot is active, the byte gap between current WAL and restart_lsn, and whether confirmed_flush_lsn advances. I page a human before the disk fills. I also monitor apply lag on the cohort product against its fifteen-minute bound. Process-up checks miss a consumer that is running and not confirming. I do not drop a slot as routine cleanup. Dropping it discards the restart point and forces a new snapshot, which is a recovery action with a named owner.

Q9. Why are progress events a poor CDC candidate in this estate?

They are append-only, identified by event_id, and the batch extract already meets the 06:00 UTC bound. A slot on that table retains high-volume WAL on the primary until a consumer confirms. The analytical copy does not need commit-order deletes, because the events are not deleted as part of the product. I would reopen the decision if a consumer wrote down a minute-level bound and accepted the operational cost. That design is Module 4, not a slot added beside the nightly file.

Q10. How do you test CDC without Debezium?

I fold a fixture of snapshot, update, delete, reordered arrival, identical redelivery, and a contradictory LSN. I assert the key is absent after a delete even if an older update is handed to the sorter later. I assert a watermark poll over the surviving rows does not contain the deleted key. I read pg_replication_slots on a database I am allowed to break, or I do not create a slot. Connector tests belong to lesson 46, against the envelope documented there. The foundations fail if the fold is wrong, regardless of the connector.

Q11. What is the difference between a publication and a privacy projection?

A publication lists tables included in logical replication. It does not, by itself, promise that email is absent from the change. If the column is on the row, assume it can appear. I project at the landing of the change, the same way lesson 13 projects at the file boundary, and I keep that landing private. A database row filter is a versioned feature I read about in the version I run. I do not describe the publication as the prohibited-field control.

Q12. When would you choose a frequent snapshot instead of CDC for enrollment?

When the table is small, the bound is a few minutes, the primary owner will not operate a slot, and a full read of the narrow projection off a replica meets the bound. Absence in the new snapshot is the delete. I still retain the daily historical partitions for the mart. I switch to CDC when the snapshot read no longer fits, or when I need every intermediate update rather than the state at poll time. The lesson 12 review aid prefers CDC when a change feed is allowed. The human note can still say "frequent snapshot until the slot is monitored." That note is part of the design, not a retreat from it.

Hands-On Exercises

Exercise: Fold enrollment changes and state the delete rule

Problem statement. Design current-state capture for an enrollment table, and write down what a delete does to a cohort product and to a historical completion product.

Tasks.

1. Define the key, the after-image you need, and whether you need a before-image. Choose a replica identity and justify it.
2. Show a watermark query missing a hard delete, with a fixture of remaining rows.
3. Implement an LSN-ordered fold. Include a snapshot, an update, a delete, a reordered arrival that must not resurrect, and a contradictory LSN that must fail.
4. Describe the snapshot-plus-slot sequence in your own words, including what you record so a restart does not double-apply.
5. State the slot failure mode in bytes, and who is paged.
6. Write the two product rules: cohort on delete, completion mart on delete. Include one must-not arrow.
7. Explain why the progress-event table is or is not on the same slot, using a labeled volume assumption.
8. Name what you are explicitly deferring to the Kafka and Debezium lesson.

Expected deliverables. The fold, the delete-rule paragraph, the slot operational note, and the diagram.

Evaluation rubric.

You pass when a reviewer can apply a Friday account-closure delete to your design and predict Wednesday's completion row.

Lesson Summary

Change data capture exists so a current-state product can see deletes and can see updates that do not touch a convenient timestamp. A watermark query cannot do that. A trigger can, and it charges the primary an extra write. The write-ahead log already contains the commit, and logical decoding can emit it, if you set replica identity so the key is in the event, coordinate a snapshot with a replication slot, apply each key in LSN order, and monitor the WAL the slot retains.

TechLearningPro's cohort counter is that current-state product. The completion mart keeps using daily snapshots and does not apply cohort deletes to historical facts. Progress events stay on the batch path. The fold in this lesson is the behavior a later connector must preserve.

Lesson 46, in Module 4, puts this stream on Kafka with Debezium. Data Quality Engineering is next, because a change you captured is not a metric until the checks that guard the publish have run.

Further Reading

• PostgreSQL documentation: logical replication — publications, subscriptions, and the row identity that replication uses.
• PostgreSQL documentation: REPLICA IDENTITY — DEFAULT, FULL, NOTHING, and USING INDEX as this lesson uses them.
• PostgreSQL documentation: replication slots — why a slot retains WAL, and the view pg_replication_slots.
• PostgreSQL documentation: logical decoding — output plugins and the fact that transactions are decoded at commit. The command reference for creating a slot is in the same documentation set. Read the page for the version you run.
• Martin Kleppmann, Designing Data-Intensive Applications — replication logs and the difference between a statement and a commit. The resurrection bug is an ordering bug in that sense.
• /learn/kafka — the log you will use in Module 4. It is not the WAL.
• Continue this course: Data Quality Engineering. Debezium's own documentation is assigned with lesson 46, CDC with Kafka and Debezium, not as a substitute for the slot and the delete rule.