Do the keys line up?

Four files from one agency, one month. For each pair, the share of keys on the left that can be found on the right.

Blue is a clean join. Orange is partial. Grey is no match at all. Hover a bar for the counts and the reason.

The vehicle and passenger files agree with each other: same trip key, same stop numbers, same days. Neither agrees with the timetable on trips, and the fare file uses a third route key of its own.

Inside one agency this is a known quirk, handled by whoever has done it before. Across seven parties it is the whole problem, and it is why the exchange owns a semantics layer: a contract has to say which identifier is the identifier, and each party's adapter has to produce it.