Design a Streaming ETL Pipeline with Exactly-Once

A raw event stream is rarely the thing anyone actually wants to consume. The orders topic coming off the CDC pipeline is thin: an order id, a customer id, a product id, an amount, a status. Every team that reads it wants the same three things bolted on - the customer's tier, the product's category, the seller's region - and every team joins those in slightly differently, on slightly different snapshots of the dimension tables, and gets slightly different answers. Streaming ETL is the job of doing that enrichment once, correctly, and publishing a single clean stream everyone can trust.

Rafael already owns the ad-click counting. Now the platform team hands him a related mess: four different services each re-derive an "enriched order" from the raw topic. Fraud joins orders to the customer table to score risk. The live ops dashboard joins to the seller table for a by-region view. Analytics joins to everything for the warehouse. Finance has its own copy. When fraud flags an order as high-tier-customer and the dashboard shows it as standard, nobody can say which is right, because they read the customer table at different moments and a tier had changed in between.

The fix is one enrichment pipeline that produces one canonical enriched stream. But the moment you build it, the hard questions arrive. Which snapshot of the customer table do you join against - the one at order time, or the one now? What happens to a downstream fraud alert when the pipeline crashes mid-flight and replays the same order? This chapter is about building that pipeline and defending every one of those choices.

Step 1 - Understand the Problem and Establish Design Scope

"Enrich the order stream" hides two genuinely hard problems - how to join a stream to slowly-changing tables, and how to not corrupt downstream systems on a replay. The clarifying questions separate them.

Candidate: What's the source, and what does a raw event look like?

Interviewer: The orders Kafka topic, fed by the CDC pipeline. Each event is thin: order id, customer id, product id, seller id, amount, status, and an event timestamp. About 5 million orders a day.

Candidate: What do we enrich with, and where do those dimensions live?

Interviewer: Three dimension tables - customer (tier, country), product (category, unit cost), seller (region). They live in the lakehouse as Iceberg tables and are themselves kept fresh by CDC. They change slowly but they do change: a customer gets upgraded to gold, a product gets recategorized.

Candidate: Here's the subtle one. When an order from last Tuesday flows through, do we enrich it with the customer tier as it was last Tuesday, or as it is right now?

Interviewer: As it was at order time. If a customer was standard when they ordered and gold today, fraud scoring and revenue attribution both need the standard-at-the-time value. Point-in-time correctness matters.

Candidate: Who consumes the output, and how fresh does it need to be?

Interviewer: Two kinds of consumer. Real-time services - fraud, the live ops dashboard - want the enriched event within seconds, as a stream. And analytics wants the same enriched data as a queryable table in the lakehouse. Same data, two shapes.

Candidate: How bad is a duplicate? If the pipeline replays an order after a crash, what breaks?

Interviewer: Depends on the consumer. If the fraud service sees the same enriched order twice, it might fire two alerts or double-count a risk budget - that's a real incident. The analytics table just needs the row to end up correct; seeing it twice on the way there is fine as long as the final state is right.

Candidate: So the two outputs have different correctness bars. Good - that shapes everything.

Interviewer: It does. Say more when you get there.

That last exchange is the whole design in miniature.

Functional requirements

• Consume the raw orders stream and enrich each event with attributes from the customer, product, and seller dimension tables.
• Enrich with the dimension values that were valid at the order's event time, not the current values.
• Publish the enriched stream to a Kafka topic for real-time consumers.
• Land the same enriched data in an Iceberg table for analytical queries.
• Survive the enrichment schema and the dimension schemas evolving over time.