Two Systems, One Room Count
A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.
- Domain
- Pipeline Design
- Difficulty
- hard
- Seniority
- L6
Problem
Our booking pages and analyst reports disagree on which hotel rooms are free, since partners update both a legacy on-prem inventory database and a newer cloud one, each with its own schema. Design how both feed one inventory view, at a peak of about 5,000 updates per second across 2 million rooms.
Two booking systems. Rooms do not duplicate themselves.
Business requirements
- Hotel partners update both inventory databases all day, and the unified view has to keep up with both of them.
- The two systems describe the same rooms with different schemas, and the unified view has to describe each room one way.
- The unified view must be consistent: the same room arriving from both systems is still one room.
- The booking platform and the analytics team both need to query the unified view, not the two source systems.
Worked solution and explanation
What this really is
This is two-master replication in a hotel-booking costume. Two systems both claim to know the truth about the same room, and the booking engine has to act on one answer within seconds. Everyone draws CDC into a merged table. The real difference between candidates is the merge rule. Reach for last-write-wins and the winner depends on which event happened to arrive second. The same room then flips price between page loads, the losing value is overwritten with no record, and a lagging connector quietly sells rooms that are already gone.
Build it in order
Step 1: Capture each source with its own change stream
Run log-based change capture on both databases, one streaming connector each, and tag every event with its source and log position. Separate connectors stall and lag independently, so a slow side never hides behind the healthy one. Polling either side on a schedule brings back the stale window that causes overbookings.
Step 2: Reconcile schemas before comparing anything
The two systems describe the same room differently. schema_reconciliation maps both into one canonical event keyed by room_id, so the resolver compares like with like. If you skip this step, every apparent conflict is half schema mismatch and half real disagreement.
Step 3: Resolve per field, upsert by key
conflict_resolver holds state by room_id and applies the ownership rule field by field: legacy owns price on its own hotels, cloud owns availability everywhere. It then upserts into unified_inventory. Because the write is keyed, a redelivered event is harmless.
Step 4: Serve each reader from the store it needs
The booking engine looks up single rooms thousands of times a second, so it reads unified_inventory directly. Analysts scan the whole inventory on an hourly refresh, so analytics_warehouse takes a copy of the same merged rows. Both read the merged view, and neither touches a source database.
- legacy_inventory (source, PostgreSQL)
- cloud_inventory (source, MySQL)
- cdc_capture_legacy (transform, Flink)
- cdc_capture_cloud (transform, Flink)
- schema_reconciliation (transform, Flink)
- conflict_resolver (transform, Flink); idempotency: upsert
- unified_inventory (storage, PostgreSQL)
- analytics_warehouse (storage, warehouse)
- booking_engine (consumer, API)
- analytics_team (consumer, dashboard)
- legacy_inventory -> cdc_capture_legacy
- cloud_inventory -> cdc_capture_cloud
- cdc_capture_legacy -> schema_reconciliation
- cdc_capture_cloud -> schema_reconciliation
- schema_reconciliation -> conflict_resolver
- conflict_resolver -> unified_inventory
- unified_inventory -> booking_engine
- unified_inventory -> analytics_warehouse
- analytics_warehouse -> analytics_team
- Last-write-wins merge
- The winner is whichever event the stream delivered second. Delivery order changes on a retry or a partition rebalance, so the same conflict can resolve differently. The losing value is overwritten and gone.
- Precedence merge
- The winner comes from the ownership rule for that field. Any order and any replay produce the same result, and the losing value is known at the moment of resolution if anyone ever needs to keep it.
Common follow-up questions
- The business moves ownership of price for one class of properties to the cloud system. What changes? (Only the rule that `conflict_resolver` reads changes. The capture, storage and readers stay as they are.)
- Legal now wants every overridden value kept for years. Where does it come from? (Tests capturing the losing value at the point of resolution, appended to a long-retention store, instead of reconstructing it later.)
- How would on-call learn that one connector has stopped? (Per-connector lag measured from the last committed log position, paging when either side falls behind.)
- Both connectors stall during a partner outage. What does the booking engine do? (Tests for a stale-data policy: stop selling rooms whose last update is older than the threshold, rather than trusting a frozen view.)