A trip to China is 311 trips, not 44,473 — and four of the six line items are constants

I expected the expensive part of building a travel-cost dataset to be the flights. It turned out to be the opposite: the flights were the only part that needed real modelling, because everything else collapsed into a constant.

The table

44,473 priced routes. 88 origin countries. 311 Chinese destinations. Each row itemises six things — flights, accommodation, food, local transport, attractions, fixed costs (visa, insurance, eSIM, card fees) — and the six sum exactly to the stored total.

The first thing I did after loading it was check whether that "exactly" was true:

SELECT COUNT(*) AS bad FROM cost_routes WHERE flight_low + hotel_low + food_low + local_low + acts_low + fixed_low <> total_low; -- 0 

Good. The totals are derived, not typed in.

Then the interesting query

I picked Beijing, because it has 143 origin cities — the deepest slice in the set — and asked how many distinct values each line takes:

SELECT COUNT(*) AS routes, COUNT(DISTINCT flight_low) AS flight_values, COUNT(DISTINCT hotel_low) AS hotel_values, COUNT(DISTINCT food_low) AS food_values, COUNT(DISTINCT local_low) AS local_values, COUNT(DISTINCT acts_low) AS acts_values FROM cost_routes WHERE dest_slug = 'beijing'; 

routes 143 | flight 126 | hotel 1 | food 1 | local 1 | acts 1 

Four of the six take one value. Not a tight range — one integer, shared by a route starting in Seoul and a route starting in Buenos Aires.

That is not a coincidence of Beijing. Run the same count grouped by destination:

SELECT COUNT(*) AS destinations, SUM(CASE WHEN hv = 1 AND fv = 1 AND lv = 1 AND av = 1 THEN 1 ELSE 0 END) AS all_constant FROM ( SELECT dest_slug, COUNT(DISTINCT hotel_low) hv, COUNT(DISTINCT food_low) fv, COUNT(DISTINCT local_low) lv, COUNT(DISTINCT acts_low) av FROM cost_routes GROUP BY dest_slug ); -- 311 destinations, 311 all_constant 

All of them. 311 destinations, and not one has a hotel band that varies by origin.

Why that is the correct model, not a shortcut

It would be easy to read this as laziness — "they just didn't compute per-origin hotel prices". The opposite is true: the on-the-ground cost genuinely belongs to the destination.

Every input to those four lines is a property of the city:

• Accommodation from the star mix and the depth of compliant supply. A city with few registered hotels has less to choose from, so the room you actually book costs more.
• Food from restaurant density. A dense market prices lower, a thin one prices higher.
• Activities from the 5A/4A count, because a 5A ticket is dearer than a 4A one.
• Local transport from the size of the built-up area.

None of these change because your flight departed from a different continent. Storing them per-route would mean 143 copies of the same six numbers, and 143 chances to drift out of sync.

Where the 39% goes

Averaged across the whole set:

The flight is the largest single line and the only one with real spread. On Beijing it runs $183 from Seoul (951 km) to $1,117 from Buenos Aires (19,266 km) — a 6.1× range on one line item, which produces the entire $934 gap between a $1,636 trip and a $2,570 one.

The second variable is your passport

Fixed costs are the only other origin-dependent line, and they take 7 values: $63 for visa-free passports, tiered fees up to $163, and $203 for the United States. An American passport adds $140 against a visa-free one — the second-biggest origin effect in the model, and still under a sixth of the widest flight spread.

What I'd carry to another dataset

The check that paid for itself was asking how many distinct values each field takes after grouping by the entity it should vary within. One value where you expected hundreds tells you the field belongs to a different entity than you assumed. In this case four fields belong to the destination, one to the origin, and one to the passport — three different owners inside what looked like a single flat route table.

The routes are browsable at sinostay — Beijing from Seoul and from Buenos Aires are /cost/145.html and /cost/3313.html.