Lesson 12 · Transform, warehouse & the whole pipeline

Operating the pipeline

The two things you'll actually do — add a table, and keep it healthy — and the whole course in one view.

Your win: add a new table to CDC the safe way, describe how the pipeline monitors and heals itself, and recite the whole pipeline end to end.

Adding a table to CDC — the three steps

The most common change. It threads together everything you've learned, and it's carefully designed to never drop a slot:

Step	What	Lesson
1	Add `public.<table>` to the source's `table.include.list` and the publication (a SQL migration)	2
2	Add a `pipelines:` entry (source_table → sink DBs) in the source DB's `<db>.yaml`, and regenerate	7
3	Deploy → `hephaestus_upsert_kafka_connect --sendIncrementalSnapshot`	3, 8

Why this sequence is safe On step 3, hephaestus updates the source connector's config (new table in the include list) — and because --sendIncrementalSnapshot is set and the connector is a source, it detects the genuinely new table (one not already in a Kafka topic) and fires an incremental-snapshot signal to back-fill its existing rows. The source connector is updated, never recreated, so its replication slot survives and no changes are lost. That's Lessons 3 and 8 working together: reconcile the config, then back-fill without touching the slot.

Keeping it healthy

🔁 Self-healing

An auto-heal cron runs every 10 minutes: it lists connector status, finds tasks in FAILED/UNASSIGNED, and POSTs a restart. Transient failures recover with no human.

📊 Monitoring

A JMX→Prometheus exporter scrapes each Connect cluster (lag, task state). Kafka UI shows connector status. IsConnectorReady gates deploys on connector + all tasks RUNNING.

🔔 Guardrails

A validate job dry-run-diffs on-disk JSON vs the live cluster; a Slack alert fires whenever a connector is deleted; accuracy jobs reconcile warehouse row counts (Lesson 10).

🏛️ The whole pipeline, in one view

CAPTURE (Part 1) service Postgres WAL → Debezium source (pgoutput, slot, publication-by-migration) snapshots: initial | incremental (dbz_signals signal) → add a table without recreating the slot → Avro change event (envelope before/after/op) on topic {env}.{org}.{db}.{schema}.{table} CONNECT + SINK (Part 2) Kafka Connect (distributed, REST :8083) runs sources & JDBC sinks sink: unwrap + route → UPSERT on PK (idempotent); deletes dropped (soft-delete) ~248 connectors GENERATED (env×org×table×sink, schema-aware) ; reconciled by HEPHAESTUS git-as-desired-state; delete-guard = only JDBC sinks, never sources/ksqlDB TRANSFORM + WAREHOUSE (Part 3) ksqlDB: stream → table (LATEST_BY_OFFSET) → join → dim_*/fact_* ; owns its own sinks lmsdwh star schema (medallion: raw → reshaped → gold) ; chained CDC → partner DWHs (tenant filter) at-least-once + idempotent upsert = effectively-once ; CDC avoids the dual-write problem THREE STACKS: cross-service data-sync · KEC warehouse · lmsdwh — same tech, three deployments

The five ideas that carry the interview 1. Log-based CDC — read the WAL via pgoutput; slots + publications make it reliable. 2. Snapshots — incremental + signal table adds a table without dropping the slot. 3. Connect + idempotent sinks — upsert on PK makes at-least-once effectively-once; deletes soft. 4. Generate + reconcile — 248 connectors from YAML, hephaestus converges git-as-desired-state with a slot-protecting delete-guard. 5. Transform + warehouse — ksqlDB reshapes into a star schema; chained CDC + tenant filters serve many partners. And the "why": CDC beats hand-published events (the dual-write problem).

Operating Debezium & Connect

Adding tables (signalling), and monitoring connectors/tasks in production.

→ Debezium — signalling (add a table)
→ Confluent — monitoring Connect · in-repo features/hephaestus/incremental_snapshot_new_table.feature

Check yourself (from memory)

Q1. When you add a table, why isn't the source connector recreated?

--sendIncrementalSnapshot: update the include list, back-fill the new table via a signal — slot preserved, no data lost.

Q2. What does the auto-heal cron do?

Every 10 min it lists status, finds FAILED/UNASSIGNED tasks, and POSTs a restart — transient recovery without a human.

Q3. Which is a real weakness of this pipeline worth naming?

No DLQ (Lesson 6) — a poison message fails the task (the cron restarts it). Naming the gaps is the mark of real understanding.

Recall: operating the pipeline + the whole-course spine.

add-a-table + health + the spine, then reveal

Add a table (3 steps): ① add public.<t> to the source table.include.list + publication (SQL migration) → ② add a pipelines: entry in <db>.yaml + regenerate → ③ deploy → hephaestus_upsert_kafka_connect --sendIncrementalSnapshot (updates the source, detects the new table, back-fills via incremental snapshot — slot preserved, never recreated). Health: auto-heal cron (restart FAILED/UNASSIGNED every 10 min); JMX→Prometheus; IsConnectorReady; validate dry-run; Slack on delete; accuracy row-count reconcile. Gap: no DLQ. The spine: WAL → Debezium (slot/publication) → Avro topic → [ksqlDB stream→table→join] → JDBC upsert sink → destination/warehouse; generated + reconciled by hephaestus; at-least-once + idempotent = effectively-once; CDC avoids dual-write. Three stacks.

🎓🏁 Course complete — CDC / Streaming-ETL Twelve lessons: CDC foundations & the source (the map, Debezium/WAL, snapshots, change events) → Kafka Connect & the sink (the framework, JDBC sinks, the generation system, hephaestus) → transform, warehouse & the whole pipeline (ksqlDB, the warehouse, the end-to-end trace, operations). You can now trace any row change across the platform, read any connector config, and explain the control plane — and name what's bespoke here (the three stacks, publications-by-migration, generation + git-as-desired-state, the delete-guard, chained CDC + tenant filters, deletes-dropped, no DLQ). Interview-grade fluency in the repo's data pipeline.

That's the build. The one thing that would prove it stuck is a retention check: ask me to run a mock interview across this course (source → Connect/sink → transform/warehouse, cold, no peeking) and I'll write the first real learning records showing where you're solid and where to review. You've built the knowledge; let's confirm it's yours. Ask me to run it.

1. Debezium — signalling, Confluent — monitoring. In-repo: features/hephaestus/incremental_snapshot_new_table.feature, cmd/server/hephaestus/.