The Booking That Came Three Ways

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

L5

Asked in

Cloudbeds

Problem

Your hospitality platform receives booking events from three distinct source systems, each with different delivery mechanisms, update frequencies, and data formats. All events must land in a low-latency lookup store queryable by property and date. Design the ingestion, normalization, and write strategy.

PMS, OTA, and website all think they took the reservation first.

Business requirements

• When a hotel's PMS confirms a booking, availability has to update right away or the next customer can book the same room.
• Concurrent writes for the same property and date can't result in two bookings winning the same room.

Worked solution and explanation

What this really is

This is a write-contention problem wearing an ingestion costume. Fanning three feeds into one store is the easy half. What separates candidates is the moment a PMS webhook and an OTA row for the same property and date land a few milliseconds apart. If both writers simply insert, the store holds two bookings for one room, the single error the business will not forgive. If you dodge the race by batching the PMS feed, the next guest books a room that was taken a minute ago.

The default reach is three loaders, one per source, each writing straight into the lookup on its own schedule. It works in the demo. In production the loaders race each other on the same row. Each source's records also arrive in their own shape, so one booking shows up under two keys, and the PMS path inherits the cadence of the slowest feed.

Walk the requirements

Step 1: Put the PMS path on a stream

PMS webhooks enter event_bus and are consumed continuously by normalize_and_merge, a Flink job, so a confirmation reaches availability_lookup within seconds rather than at the next scheduled run. The minute budget rules out any batch loader on this path. It does not require anything fancier than a streaming consumer.

Step 2: Normalize before anything is written

The three feeds disagree on delivery, cadence and field names: one OTA's booking_ref is another's reservation_id. Mapping every event into one record shape inside normalize_and_merge, before the write, is what lets a webhook, a file row and a website event about the same room-night collide on the same key instead of sitting side by side.

Step 3: Make the write idempotent per property and date

Because event_bus is partitioned by property, every event for one property and date passes through one ordered path. The stage writes availability_lookup with an upsert on that key, so a retried webhook, a redelivered file, or a second channel selling the same night lands on the existing row. One row per room-night means one winner per room-night.

The shape that fits

Insert per source

Each loader inserts its own rows. A retried webhook adds a duplicate, and an OTA row and a PMS confirmation for the same night both land. The lookup shows a room sold twice.

Normalize, then upsert by key

Every event becomes one record shape keyed by property and date. It is read in order from one partition and written with an upsert. Duplicates overwrite, and the later event for a room-night is the one that stands.

Common follow-up questions

• A property's PMS goes offline for an hour while OTAs keep selling its rooms. What does the lookup show, and what happens when the PMS reconnects and replays its backlog? (Tests whether the upsert on property and date makes the replay safe, and whether the candidate acknowledges stale availability during the outage.)
• Analysts now want a property's bookings across a year of dates, refreshed hourly. Does this lookup serve them, or do they need their own store? (Tests whether the candidate keeps a point-lookup serving store separate from an analytical read path instead of overloading it.)
• Fifty newly acquired properties each book ten times the average hotel. Where does this design feel it first? (Tests reasoning about hot partitions when the queue is keyed by property.)