Lesson 11 · Transform, warehouse & the whole pipeline
The end-to-end trace
One row change, followed through every hop — and the delivery guarantees that make the whole chain correct.
Your win: trace a single bob.users change to auth.users
through every stage you've learned, and defend the pipeline's delivery semantics (at-least-once +
idempotent upsert) and why CDC beats writing events by hand.
One change, all the hops
A row updates in bob's users table. Here's its whole journey — every stage is a lesson
you've done:
① CHANGE UPDATE bob.public.users … (Lesson 1)
│
② CAPTURE WAL → slot local_manabie_bob, publication debezium_publication, (Lessons 2–3)
│ plugin pgoutput → Debezium source connector emits a change event
│
③ TOPIC Avro-encoded envelope → topic local.manabie.bob.public.users (Lesson 4)
│ (source route SMT renamed it; schema in the registry)
│
④ TRANSFORM ── warehouse path only ──▶ ksqlDB stream→table→join → dim_user (Lesson 9)
│ ── cross-service sync ──▶ (no ksqlDB; straight to the sink)
│
⑤ APPLY sink connector bob_to_auth_users: unwrap (envelope→row) → (Lessons 6–8)
│ route $5 → "users" → JDBC UPSERT on user_id → auth's Postgres
▼ (reconciled onto the cluster by hephaestus, git-as-desired-state)
⑥ RESULT auth.public.users now has the updated row
Every arrow is a config you've read. The same trace, on the warehouse stack, adds the
ksqlDB hop at ④ and lands in dim_user instead — then chains onward to partner DWHs
(Lesson 10). Three stacks, one shape.
Delivery semantics: at-least-once, made safe by upsert
The guarantee, and why it's enough
Kafka Connect delivers at-least-once — a change event can be delivered more than
once (a task restart, a rebalance, a retry re-reads from the last committed offset). There's
no exactly-once/transactional config in this pipeline. So how is it correct? Because the
sink does an idempotent upsert on the primary key (Lesson 6): re-applying the same
change just rewrites the row to the same value — a no-op. At-least-once delivery +
idempotent apply = effectively-once result. That pairing is the single most important
design idea in the whole pipeline, and a classic interview answer.1
(This is also why ordering per key matters and holds: Debezium keys each event by
primary key, Kafka preserves order within a partition, and the upsert applies them in that order — so
the last write wins, correctly.)
Why CDC at all: the dual-write problem
Step back to why this whole machine exists instead of each service just publishing its own
events. Imagine a service that, on a change, does two things: write to its DB, and publish to Kafka.
Those are two separate operations — and either can fail while the other succeeds:
write DB ✓ … publish Kafka ✗ → DB has it, downstream doesn't (silent drift)
write DB ✗ … publish Kafka ✓ → downstream has a change that never happened
CDC's answer
This is the dual-write problem. CDC eliminates it: the event is derived from
the committed database change (the WAL), so there's exactly one source of truth and no second
operation to fail. If the DB commit happened, the event will be produced; if it didn't, it won't.
(The application-level cousin is the outbox pattern — write the event to an outbox
table in the same transaction, then CDC/relay it. This repo has an outboxevents
golib for exactly that.)2
Read this next
The dual-write problem & the outbox pattern
Why deriving events from the DB beats publishing them separately — the reliability argument for
CDC.
Q1. The pipeline delivers at-least-once. Why is the result still correct?
At-least-once + idempotent upsert = effectively-once. There's no
exactly-once machinery; the upsert makes it unnecessary.
Q2. What is the dual-write problem?
Two independent operations (DB write + event publish) can succeed
apart, causing drift. CDC derives the event from the committed change — one source of truth.
Q3. On the cross-service sync path, which hop is absent?
ksqlDB is the warehouse-only transform. Plain data-sync captures,
transports, and applies — no reshape in between.
Recall: the end-to-end trace + delivery semantics + why CDC.
the hops + at-least-once/upsert + dual-write, then reveal
Trace (bob.users → auth.users): ① change → ② capture (WAL/
slot/publication/pgoutput → source connector) → ③ Avro topic local.manabie.bob.public.users
→ ④ transform (warehouse: ksqlDB stream→table→join→dim_user; sync: skip) → ⑤ apply (sink
unwrap+route $5 → JDBC upsert on user_id → auth DB; reconciled by
hephaestus) → ⑥ auth.users updated. Delivery: at-least-once (no exactly-once
config); safe because the sink upsert is idempotent → at-least-once + idempotent =
effectively-once. Per-key ordering holds (keyed by PK, ordered in the partition, last-write-wins).
Why CDC: avoids the dual-write problem (DB write + separate
publish can half-fail → drift); CDC derives the event from the committed WAL = one source of truth.
App cousin: the outbox pattern (outboxevents golib).
🎯 Interview one-liner
"What delivery guarantees does your CDC pipeline give?" → "At-least-once. We don't use
exactly-once — instead the sinks upsert on the primary key, so a redelivered change is a no-op:
at-least-once plus idempotent apply gives an effectively-once result, with per-key ordering
preserved. And CDC itself solves the dual-write problem — the event is derived from the committed
WAL, so there's no separate publish to fall out of sync."
Last lesson: operating the pipeline — adding a table, monitoring, self-healing —
and the whole-course recap. Ask me to trace a change on the warehouse stack if
you'd like to see the ksqlDB hop in the flow.