S04 · messy data simulation

Booking events that arrive twice, late and out of order

People, data and outputs in this scenario are simulated. Each stage is recorded when you commit; the reveal comes after.

4stages
35 minabout
0stages committed

The brief

The synthetic airline streams booking events into the warehouse. Each event has an event_id, a booking reference (pnr), a type (CREATE or CANCEL), a seat count, event_ts (when it happened in the booking system) and ingest_ts (when it landed in the warehouse). Delivery is at-least-once: the producer retries on timeouts. A daily job at 00:05 UTC computes "net seats booked yesterday" per flight, which feeds the morning pickup report and a demand-forecast feature. All data in this scenario are simulated.

Stage 1 of 4

For 2024-05-06 the morning report showed 1,912 net seats booked across the network. A backfill run a week later on the same table shows 2,254 for the same day. Nobody changed the query.

What is the most likely family of causes?

Events for 2024-05-06 that landed after the 00:05 job ran (late arrivals).A bug in the backfill job; it may handle some event types, such as cancellations, differently from the nightly run.Random noise in bookings.