# Two Systems, One Room Count > Two booking systems. Rooms do not duplicate themselves. Canonical URL: Domain: Pipeline Design · Difficulty: hard · Seniority: L6 ## Problem We operate a hotel booking marketplace with two independently evolving inventory databases: one from a legacy on-prem system and one from a newer cloud platform. Both are continuously updated by hotel partners, but they use different schemas for the same logical entities. Design a system to synchronize both sources into a unified, consistent inventory view that the booking platform and analytics team can query. ## Worked solution and explanation ### Why this problem exists in real interviews Two source databases for the same rooms means three problems at once: lag (which causes overbookings), conflicts (which need a deterministic resolution), and audit (which legal expects). The trap is leaning on last-write-wins because it's the natural distributed-systems instinct. Last-write-wins is exactly the rule the requirements forbid; the business has chosen which source wins for which property and that has to be encoded. The simple answer is to CDC both sources into one merged table with last-write-wins on each row. The booking engine reads from the merged table and feels fast. Then both sources update the same room within a second of each other; the merge picks whichever arrived second. The cloud system's price wins one minute, the legacy system's price wins the next, and a guest is quoted two different rates. The conflict resolution is invisible because nothing wrote it down. A source falls a few minutes behind and the booking engine doesn't notice until a customer overbooks. > **Trick to Solving** > > CDC both sources into a merge that follows the precedence rule, write every conflict to an immutable log, alert on lag before customers do. > > 1. Both sources captured by CDC into a single change stream, with each event tagged with its source. > 2. The merge is a deterministic per-property rule (the business's precedence list), not 'whichever arrived last.' The same conflict resolved at any time produces the same outcome. > 3. Every conflict resolution writes a row to an append-only audit log with both source values, the winning value, and the rule applied. Legal queries the log; nothing overwrites it. > 4. Lag monitoring is on each CDC stream separately. When either source's lag crosses the threshold, ops is paged, before downstream notices. --- ### Walk the requirements #### Step 1: Both sources stream into the unified view in tens of seconds CDC connectors on both inventory sources emit changes onto a single stream; a stream processor merges by room id and writes to the unified store. The booking engine reads from the unified store, not from either source directly. End-to-end is on the order of tens of seconds, fast enough that lag-induced overbookings stop happening. Without a streaming/sub-minute path the unified view is built on whatever cadence polling supports, which is the version that overbooks; without a warehouse / serving tier the unified view has nowhere to live. #### Step 2: Deterministic precedence per property, not last-write-wins When both sources update the same room concurrently, the business has chosen which source wins for which property: maybe the legacy system owns price for legacy-managed properties and the cloud system owns availability across the board. The merge applies that precedence rule per property, not per row, and not by timestamp. The same two updates in the same order always produce the same result. A 'last-write-wins on the row' shortcut is the version that quotes two different prices on consecutive page loads. #### Step 3: Every conflict written to an immutable audit log Each merge operation that resolves a conflict writes a row to an append-only audit log: room id, both source values, the winning value, the rule applied, the timestamp. The log is in cold storage with retention; nothing overwrites a record. Legal queries the log when a dispute comes up. Without the log, 'why did this room show that price' becomes a forensic exercise nobody can finish; with it, the answer is a query. #### Step 4: Per-source lag alerting before downstream notices Lag is the named cause of overbookings. Each source's CDC stream has a freshness SLA; when lag crosses the threshold (calculated from the stream's last-acked timestamp, not from the booking engine's complaint), ops is paged within minutes. The alert is per-source so ops can tell which connector is behind. A 'we'll notice when overbookings happen' posture is exactly what the requirement forbids; lag has to be visible before customer impact. --- ### The shape that fits > **What this design gives up** > > Two CDC connectors plus a stream processor plus an append-only audit log is more pieces than 'merge nightly.' The precedence-per-property rule is configuration that has to be reviewed every time the business changes its mind about ownership. Operational simplicity is the cost; the win is an inventory view that doesn't overbook, conflict resolution legal can review, and lag alerts that fire before customers do. > **What reviewers check** > > A reviewer looks at the canvas for these properties: > - A change-data-capture path off each source feeds a sub-minute merge into the unified inventory store. > - Conflicts resolve by a deterministic precedence rule, not by last-write timestamp. > - Every conflict resolution writes to an immutable audit log retained for the legal window. > - Per-source lag alerting fires before downstream notices a discrepancy. > **The mistake that ships** > > What goes out the door first uses CDC into one merged table with last-write-wins. Customers start seeing different prices on consecutive page loads because two updates flip-flopped during a busy minute. Legal asks for the history of a conflict and there is none, because last-write-wins overwrote the loser. A connector falls behind and the team doesn't notice until customer support gets the first overbooking call. The team rebuilds with precedence rules, an audit log, and per-source lag alerting. The fix touches every layer of the design after data is already flowing through it. --- ## Common follow-up questions - Operations changes which source owns 'price' for a class of properties. What in this design changes, and what stays? _(Tests whether the candidate sees the precedence rule as configuration on the merge step, not as code throughout the pipeline. Updating the rule is a config change with an audit-log record; the storage layout, the CDC connectors, and the consumers don't change.)_ - Both sources go offline at the same time during a partner outage. What does the booking engine see, and what does ops do? _(Tests whether the candidate has a stale-data policy: when both sources are stale past a threshold, the booking engine either falls back to a 'safe' availability (don't sell what we can't confirm) or surfaces an explicit warning. The lag alert fires immediately; the engine shouldn't keep selling against frozen inventory.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/two_systems_one_room_count) - [System Design Interview Questions](https://datadriven.io/data-engineering-system-design) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.