The Acquisition Still Taking Bookings

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

Since we acquired a hotel chain, its reservation system and ours both take live bookings: about 200 inventory changes a second across 1,500 hotels, 400 of them sold through both. The head of operations wants you to design the pipeline that turns both systems' changes into one inventory picture, with a history revenue management can audit.

Two systems, two schemas. One truth.

Business requirements

Worked solution and explanation

What this is really asking

This is a two-writer race dressed up as an acquisition. Everyone draws change capture off both reservation systems; what separates candidates is where the two streams meet. Let each source write the inventory view on its own and the 400 shared hotels flip between two counts every time either system books a room, so operations sees availability neither system believes. Draw one reconciliation point, decide there which change wins, and keep the history beside the current count rather than inside it.

Step 1: Capture changes, never poll

Both databases are in production and cannot absorb heavy query load, and operations needs minutes, not hours. Change data capture reads each system's change log, so the sources pay almost nothing and every booking arrives as an event, about 200 a second at peak.

Step 2: Send both streams into one reconciler

The reconciler maps each system's field names and room-type codes onto one shape, then resolves the shared hotels in one place. The business rule is that the later change for a hotel wins, so the reconciler compares the change time each source stamped, not the order events happen to arrive. A Flink job keeps the last applied change time per hotel and room type as state, which keeps the view current within seconds.

Step 3: Keep the history apart from the current count

unified_inventory_view holds one row per hotel and room type, overwritten in place: right for operations, useless for 'what was the count at 2pm yesterday'. The reconciler also appends every source update it receives (the source, its value, the resolved count, the change time) to source_update_history, an Iceberg table on object storage. Revenue analysts query that table, so no past count depends on the live view.

The reference design

Both systems' changes meet in one streaming reconciler, which updates the current view operations reads and appends every source update to the history revenue management queries.
  • legacy_reservation_system (source, CDC)
  • acquired_reservation_system (source, CDC)
  • inventory_reconciler (transform, Flink)
  • unified_inventory_view (storage, PostgreSQL)
  • source_update_history (storage, Iceberg)
  • operations_dashboard (consumer, Grafana)
  • revenue_analysis (consumer, Jupyter)
  • legacy_reservation_system -> inventory_reconciler
  • acquired_reservation_system -> inventory_reconciler
  • inventory_reconciler -> unified_inventory_view
  • inventory_reconciler -> source_update_history
  • unified_inventory_view -> operations_dashboard
  • source_update_history -> revenue_analysis
Each source writes the view
Two capture jobs each upsert unified_inventory_view. For the 400 shared hotels the count is whichever system wrote last, and a replay can roll it back. The business rule has no single place to live.
One reconciler writes the view
Both streams enter one stage that maps the schemas, compares change times and writes the view. The rule lives in one place, conflicts resolve the same way every time, and a new source is one more input.

Common follow-up questions