S04 · messy data simulation

A raw flight table and a Friday deadline

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

5stages
40 minabout
0stages committed

The brief

You are the analyst preparing a modelling table for a synthetic airline's demand team (simulated company). The input is flight_day_raw, a messy copy of the flight-day table for 2022-2023: one row per flight and date, with passengers, load factor, fare, revenue, route and market. The demand team will build a revenue forecast on whatever you hand over on Friday, and nobody has looked at the extract yet.

The extract is the course's synthetic data with known defects injected. For this exercise a clean reference copy of the same period exists; you can only see it after you commit to a decision at each stage.

Stage 1 of 5

Your first profile of the extract (counts computed on flight_day_raw):

Check	Result
Rows	13,887
Distinct (date, flight) pairs	13,777 (110 rows repeat a pair)
Expected pairs (19 flights x 730 days) not present	93
Rows with missing `avg_fare` and `revenue`	281
Route codes in lower case (for example `ist-esb`)	124
`load_factor` above 1	6
Negative `bookings_final`	2
`passengers` above 400 (one row: 840 on a 180-seat flight)	1

How do you proceed with the cleaning?

Drop every row that fails any check, so that what you hand over is guaranteed to satisfy all of them and nobody downstream has to think about quality.Fix the obvious ones in place (case, duplicates) and fill the missing and impossible values with the route mean, so that the table is complete and ready to use on Friday.Hand over the raw table with the list of issues and let the demand team decide how to treat each of them, since they know their own use best.Leave the raw table untouched, build a staging table with a flag per rule, and record each rule's row count and revenue share before deciding what to do with it, so that every decision is reviewable.