Design a CDC Pipeline

Every analytics stack has the same beating heart: an operational database that runs the business, and a lakehouse that answers questions about it. Change data capture (CDC) is the artery between them. It's how the orders, payments, and inventory that live in a transactional database show up, minutes later, in the tables your analysts and finance team query. Get it wrong and every downstream number is quietly stale or quietly double-counted.

PhoenixWorld's nightly batch pull is starting to hurt. It runs a big SELECT * FROM orders WHERE updated_at > ... against production Postgres every night, and the CFO now wants revenue that's minutes-fresh, not a day stale. Priya's first instinct is to run that query every five minutes instead. Then she reads the plan: a scan of a 400-million-row table, competing with live checkout traffic, twelve times an hour. The DBA would revoke her access by lunch.

The problem isn't the frequency. It's the whole approach. Polling a database for changes is the wrong tool. The database already writes down every change it makes, in order, for its own crash recovery. That log is right there. This chapter is about reading it, and about all the ways a naive read of it corrupts your data.

Step 1 - Understand the Problem and Establish Design Scope

A vague prompt like "replicate our database to the lake" hides five different systems. The clarifying questions pin down which one.

Candidate: What's the source, and how big is it?

Interviewer: Postgres, running orders, payments, and inventory. Start with the orders table: about 400 million rows, roughly 5 million new orders a day, and each order gets a few status updates over its life.

Candidate: How fresh does the lakehouse copy need to be?

Interviewer: A change committed in Postgres should be queryable in the lakehouse within a few minutes. This is for dashboards and finance reporting, not sub-second serving.

Candidate: Do we need inserts, updates, and deletes, or just appends?

Interviewer: All three. An order gets updated as it moves from placed to shipped, and a cancelled order needs to disappear, or at least be marked, downstream.

Candidate: How correct does it have to be? Can we tolerate a small amount of drift?

Interviewer: The final table must match the source. Finance reconciles revenue against it, so a row that's silently lost or double-counted is a real problem. Eventual consistency within the freshness window is fine; a wrong total is not.

Candidate: How much load can we put on the production database?

Interviewer: As close to zero query load as possible. That's the whole reason we're moving off the nightly SELECT.

Candidate: Will the source schema change over time?

Interviewer: Yes. Adding a column is common and shouldn't page anyone. Occasionally something breaking happens, like a column rename, and that's allowed to require coordination.

Candidate: A few edge cases I want to confirm are in scope: events arriving out of order, duplicate events on retries, and recovering after a component is down for a while.

Interviewer: All in scope. Those are the hard part.

With that, the requirements are clear.

Functional requirements

• Replicate a set of Postgres tables (starting with orders) into a lakehouse table that reflects current state.
• Capture inserts, updates, and deletes. A cancelled order must be removed or marked downstream.
• Bootstrap a table from scratch (a full initial load) and then stay current.
• Survive the source schema changing over time, at least for additive changes.
• Support filtering and analytical queries on the replicated table by any column.