An airline's operations team wants to join its flight schedule to this airport registry on the IATA code to put every flight on a map. The integration engineer will not do it until someone says how many rows cannot join and how many would join to the wrong place. You are that someone.
Audit the code columns and report:
iata_missing — airports with no iata_codeiata_duplicated_codes — how many distinct IATA codes are used by more than one airportiata_bad_format — non-null IATA codes that are not exactly three capital lettersgps_missing — airports with no gps_code (the ICAO code)position_approximate — airports whose location is approximateEach is a different failure for the join: a missing code drops the row, a duplicate code attaches a flight to two airports, a malformed code matches nothing, and an approximate position puts a matched flight in the wrong place on the map.
These are where the teaching is. Read them twice.
Every integration on a shared identifier starts with this audit, and skipping it is how a flight ends up drawn in the wrong hemisphere.
Work the problem in whatever tool you like, then enter the answers here.
iata_missingiata_duplicated_codesiata_bad_formatgps_missingposition_approximate5 scored, 0 informational
Airports without an IATA code
20%IATA codes used by more than one airport
30%Malformed IATA codes
20%Airports without an ICAO code
10%Airports with only an approximate position
20%