Data Career Hub
Menu
SQLPythonPySparkApache SparkKafkaAirflow
ProjectsProject 6 of 8

Advanced project · Project 6 of 8

Change Data Capture Pipeline

Replicate an operational PostgreSQL table into a lakehouse table within minutes, including updates and deletes, so analysts query current data without touching the production database.

  • Advanced
  • PostgreSQL · A log-based CDC connector (for example Debezium) · Kafka · Spark Structured Streaming · Delta Lake · Docker Compose
  • 2 min read
  • Updated Oct 2026

Requirements

  • Run PostgreSQL with logical replication enabled
  • Capture changes with a log-based CDC connector into Kafka
  • Apply changes to a Delta table with MERGE, ordered by source log position
  • Handle deletes
  • Take an initial snapshot without missing concurrent changes

Technology stack

PostgreSQL, A log-based CDC connector (for example Debezium), Kafka, Spark Structured Streaming, Delta Lake, Docker Compose

Dataset

Create your own orders table and a small script that inserts, updates and deletes rows continuously.

Business context

Analysts need current operational data, but querying the production database directly risks slowing the application. CDC keeps an analytical copy up to date from the database’s own change log, without extra query load.

Architecture

  1. PostgreSQL writes changes to its write-ahead log.
  2. The CDC connector reads the log and publishes change events to Kafka.
  3. A streaming job reads micro-batches and deduplicates to the latest change per key.
  4. MERGE applies inserts, updates and deletes to the Delta table.
  5. Analysts query the Delta table.
Changes flow from the database log to the lakehouse with per-key ordering.

The design follows the CDC system design case study; read it before building.

Build steps

  1. Start PostgreSQL, Kafka and the CDC connector with Docker Compose
  2. Create a table and a change generator script
  3. Configure the connector and inspect change events in Kafka
  4. Write a streaming job that parses events and keeps the latest per key per micro-batch
  5. Apply with MERGE: update when the incoming position is newer, insert when new, delete on delete events
  6. Verify the Delta table matches PostgreSQL
  7. Restart components and verify nothing is lost or duplicated

Testing

  • Compare row counts and checksums between source and target after a run
  • Replay a topic from the beginning and confirm the target is unchanged

Data quality

  • Primary keys unique in the target
  • No rows exist in the target that were deleted in the source

Monitoring

  • Connector lag
  • Consumer lag
  • Time from source commit to target visibility

Cost considerations

  • Runs locally in containers
  • Kafka retention must cover the longest consumer outage you want to survive

Interview questions this project prepares you for

  • How do you keep updates in order?
  • How did you handle deletes?
  • How does the initial snapshot avoid gaps?
  • Why are replays safe?

Resume bullets (add only what you measured)

Use these as structure. Replace the wording with results you actually produced and can show; never claim numbers you did not measure.

  • Built a log-based CDC pipeline from PostgreSQL through Kafka to Delta Lake that applies inserts, updates and deletes with ordered, idempotent MERGE
  • Verified correctness by reconciling source and target and by replaying change topics without altering the target

Extensions

  • Handle schema changes in the source table
  • Keep a history table of every change for auditing

By Data Career Hub Editorial · Last reviewed Oct 2026

Progress is saved in this browser only. No account needed.

Search
Filter by type