The Double Count

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L5

Problem

Each flight record is one departure between two airports on one day, flying that airport pair's fixed published distance in miles with hundreds of passengers aboard, while a passenger can connect across several flights and every ticket's fare has already been split into a share for each flight its passenger flies. From these records the warehouse must show how late each flight pushed back and why, how many seats it offered and how full it flew, and the revenue earned per seat mile, keeping what happens to a flight separate from what each passenger flies so no seat, mile or fare is counted twice. The raw operational numbers are kept as recorded so they still roll up correctly across routes and days.

One flight carries hundreds of seats; one ticket spans many flights. Model them so neither gets counted twice.

Worked solution and explanation

What this problem really is

Strip the airline costume and this is a grain collision: three stakeholders each imply a different unit of analysis in one prompt. Operations lives at one row per flight leg (load_factor, delay_minutes), while revenue lives at one row per passenger per leg. Passenger-to-flight is many-to-many, so the tempting move, folding both onto one wide row, forces a double count. Get it wrong and you either count each seat once per passenger onboard or count each fare once per leg flown, and finance and operations report different totals from the same table.

Building it, decision by decision

Step 1: Declare the flight-leg grain out loud

fact_flights is one row per scheduled flight leg, keyed by flight_key. Say it before drawing anything: every measure on this table must be additive at the leg grain, which immediately rules out anything passenger-shaped living here.

Step 2: Resolve the many-to-many with a bridge fact

A leg carries hundreds of passengers; an itinerary spans many legs. fact_ticket_segments links dim_passengers to fact_flights at one row per passenger per leg. This is the bridge that keeps fares off the flight row and keeps seat counts off the fare row.

Step 3: Store load factor's ingredients, not the ratio

Keep seats_available and passengers_boarded as separate raw columns and let consumers form the ratio. A pre-computed percentage cannot re-aggregate across routes or days, because averaging ratios is not the ratio of sums.

Step 4: Store raw delay, not a verdict

Keep delay_minutes (or the scheduled and actual departure times) plus a scheduled_dep_date_key FK to dim_date. A single on_time boolean answers 'was it late' but throws away magnitude, so average delay by fleet type and on-time rate by route both become unanswerable.

Step 5: Model delay cause as a leg attribute

Carrier, weather, and NAS reasons attach to the leg as delay_cause, an attribute on the fact row. That lets analysts slice performance by why a departure slipped without a separate bridge, since a cause is one value per leg.

Step 6: Conform the shared dimensions

dim_aircraft, dim_routes, dim_airports, and dim_date are shared by both facts. Conforming them means finance and operations join the same calendar and the same airports, so their numbers reconcile by construction instead of in a meeting.

The reference design

Grain anchors the split: `fact_flights` is one row per leg, `fact_ticket_segments` is one row per passenger per leg.
  • dim_date: date_key INT PK, full_date DATE, day_of_week INT, is_holiday BOOLEAN
  • dim_airports: airport_key INT PK, iata_code TEXT, city TEXT, country TEXT, timezone TEXT
  • dim_routes: route_key INT PK, origin_key INT FK, destination_key INT FK, distance_miles INT
  • dim_aircraft: aircraft_key INT PK, tail_number TEXT, fleet_type TEXT, total_seats INT
  • dim_passengers: passenger_key BIGINT PK, loyalty_tier TEXT, home_airport_key INT FK
  • fact_flights: flight_key BIGINT PK, route_key INT FK, aircraft_key INT FK, scheduled_dep_date_key INT FK, seats_available INT, passengers_boarded INT, delay_minutes INT, delay_cause TEXT
  • fact_ticket_segments: segment_key BIGINT PK, flight_key BIGINT FK, passenger_key BIGINT FK, fare_class TEXT, revenue_usd FLOAT
  • dim_routes.origin_key references dim_airports.airport_key
  • dim_routes.destination_key references dim_airports.airport_key
  • dim_passengers.home_airport_key references dim_airports.airport_key
  • fact_flights.route_key references dim_routes.route_key
  • fact_flights.aircraft_key references dim_aircraft.aircraft_key
  • fact_flights.scheduled_dep_date_key references dim_date.date_key
  • fact_ticket_segments.flight_key references fact_flights.flight_key
  • fact_ticket_segments.passenger_key references dim_passengers.passenger_key

The analysis pattern

Load factor, delay, and RASM by route and month
SELECT
    r.route_key,
    DATE_TRUNC('month', d.full_date) AS month,
    SUM(f.passengers_boarded)::NUMERIC / NULLIF(SUM(f.seats_available), 0) AS load_factor,
    AVG(f.delay_minutes) AS avg_delay_minutes,
    SUM(ts.revenue_usd) / NULLIF(SUM(f.seats_available * r.distance_miles), 0) AS rasm
FROM fact_flights f
JOIN dim_routes r ON r.route_key = f.route_key
JOIN dim_date d ON d.date_key = f.scheduled_dep_date_key
JOIN fact_ticket_segments ts ON ts.flight_key = f.flight_key
GROUP BY r.route_key, DATE_TRUNC('month', d.full_date)

Each measure aggregates at its own fact's grain, then joins on flight_key, so nothing double counts.

Two-fact star
Clean grain per fact, conformed dimensions, additive measures. Cost: revenue queries need a join and ETL maintains two load paths. Evolves cleanly when new passenger attributes arrive.
Single wide fact
One row per passenger per leg with flight attributes denormalized on. Lower join cost for passenger queries, but operational metrics need COUNT(DISTINCT flight_key), double counting is one GROUP BY slip away, and any flight-level attribute change rewrites billions of rows.

Common follow-up questions