Let’s break CDC (Change Data Capture) in a way you can explain confidently in interview + implement in real systems.

⸻

🚀 What is CDC (Simple Definition)

CDC (Change Data Capture) is a technique to capture only the changes (INSERT, UPDATE, DELETE) from a data source instead of reloading the full dataset.

⸻

🧠 Why CDC is Needed

❌ Without CDC • Full table load every time • Expensive • Slow • Not real-time

✅ With CDC • Only changes are processed • Efficient • Near real-time pipelines • Scalable

⸻

📦 Real Example (VERY IMPORTANT)

🏦 Example: Employee Table

Source Table (HR DB)

emp_id name salary 1 John 1000 2 Amy 2000

⸻

Now changes happen: 1. John salary updated → 1000 → 1200 2. New employee added → Bob 3. Amy deleted

⸻

🎯 CDC Output (Event Stream)

op emp_id name salary UPDATE 1 John 1200 INSERT 3 Bob 1500 DELETE 2 Amy null

👉 This is what CDC captures — only changes

⸻

⚙️ How CDC Works Internally (Deep Level)

There are 3 main approaches

⸻

1️⃣ Log-Based CDC (BEST — Most Used)

🔥 How it works • Databases maintain transaction logs: • MySQL → binlog • Postgres → WAL • CDC tools read logs directly • Convert changes into events

⸻

📌 Flow

Database → Transaction Log → CDC Tool → Kafka → Data Platform

⸻

💥 Example

UPDATE employees SET salary = 1200 WHERE emp_id = 1;

👉 Log entry:

{ “op”: “UPDATE”, “before”: { “salary”: 1000 }, “after”: { “salary”: 1200 } }

⸻

✅ Pros • Real-time • No load on DB • Accurate

⸻

2️⃣ Timestamp-Based CDC

How it works • Use column like updated_at

SELECT * FROM employees WHERE updated_at > last_run_time;

⸻

❌ Problem • Miss deletes • Time sync issues

⸻

3️⃣ Trigger-Based CDC

How it works • DB triggers capture changes

CREATE TRIGGER log_update AFTER UPDATE ON employees

⸻

❌ Problem • Slows DB • Hard to maintain

⸻

🔄 CDC → Data Warehouse (IMPORTANT)

How CDC is applied

You don’t directly store events — you apply them

⸻

🔥 Example using MERGE

MERGE INTO employees t USING cdc_events s ON t.emp_id = s.emp_id

WHEN MATCHED AND s.op = ‘UPDATE’ THEN UPDATE SET salary = s.salary

WHEN MATCHED AND s.op = ‘DELETE’ THEN DELETE

WHEN NOT MATCHED THEN INSERT (emp_id, name, salary)

⸻

🧠 CDC + SCD (INTERVIEW GOLD)

👉 Key point: • CDC = how data is captured • SCD = how data is stored

⸻

Example:

CDC event:

emp_id=1 salary 1000 → 1200

SCD Type 2 table:

emp_id salary start_date end_date 1 1000 old now 1 1200 now null

⸻

⚠️ Real Problems in CDC (VERY IMPORTANT)

⸻

❓ Duplicate Events

Cause • retries • network issues

Solution • dedup using: • primary key • timestamp

⸻

❓ Out-of-Order Events

Cause • distributed systems

Solution • ordering by timestamp/version

⸻

❓ Late Arriving Data

Solution • reprocessing window • watermark

⸻

❓ Schema Changes

Solution • schema registry • backward compatibility

⸻

🏆 Interview-Level Answer (Use This)

If asked:

👉 “Explain CDC”

Say:

“CDC captures incremental changes like insert, update, and delete from source systems, usually by reading database transaction logs. These changes are emitted as events and applied downstream using merge logic. This allows us to build efficient, real-time pipelines instead of full data reloads.”

⸻

🔥 Advanced (Director-Level Insight)

Drop this line:

“CDC shifts data pipelines from batch-based movement to event-driven architectures, enabling near real-time analytics and AI use cases.”

⸻

🚀 If You Want Next Level

I can now:

✅ Give CDC interview questions (hard level) ✅ Show end-to-end architecture (Kafka + Iceberg + Trino) ✅ Give real coding + SQL CDC problems

Just say:

👉 “Deep CDC interview questions”