Checking GTFS Referential Integrity in Python

Declare the feed’s foreign keys as a table of relationships, build one key set per referenced table, and test every referencing column against its set in a single vectorised pass. Then report the orphans per relationship, with a sample of the offending values, and classify each broken relationship as fatal or quarantinable — because a trip with no route cannot run at all, while a stop time pointing at a missing stop only breaks that trip. This is the integrity layer of GTFS validation rules and common schema errors.

Root Cause Analysis

GTFS is a relational model shipped as loose CSV files with no database to enforce it. Every join a consumer performs — trips to routes, stop times to stops, trips to the service calendar — depends on a foreign key that nothing in the format guarantees resolves. Schema validation checks that a column exists and holds the right kind of value; it does not check that the value points at anything.

Broken references arise in a few recognisable ways. A partial export drops rows from one table and not another. A feed assembled by merging two sources without namespacing identifiers collides keys. An agency deletes a discontinued route and leaves its trips behind. All three produce a feed that loads without complaint and is quietly incomplete.

What makes the resulting bugs hard is that the damage is silent and downstream. A stop_times row whose trip_id is not in trips.txt disappears the moment anyone does an inner join — which is what every reasonable pipeline does — so the row count drops, the schedule is short, and nothing anywhere says why. The count of missing calls is not reported by any tool that does not go looking.

Two implementation mistakes then make the checking itself unreliable. The first is checking relationships one at a time in hand-written code, which grows to a dozen near-identical blocks that drift apart. The second is performing the membership test row by row, which turns a sub-second check into a multi-minute one on stop_times.txt and encourages people to skip it.

Production-Ready Python Implementation

"""Check every GTFS foreign key in one indexed pass."""
from __future__ import annotations

import logging
from dataclasses import dataclass

import pandas as pd

log = logging.getLogger("gtfs.integrity")

FATAL, QUARANTINE = "fatal", "quarantine"


@dataclass(frozen=True)
class ForeignKey:
    child_table: str
    child_column: str
    parent_table: str
    parent_column: str
    severity: str
    required: bool          # False when an empty value is legal
    note: str


# The feed's relational model, stated once. Extending the specification means
# adding a row here, not another near-identical block of checking code.
FOREIGN_KEYS = (
    ForeignKey("routes.txt", "agency_id", "agency.txt", "agency_id", QUARANTINE, False,
               "optional in a single-agency feed"),
    ForeignKey("trips.txt", "route_id", "routes.txt", "route_id", FATAL, True,
               "a trip with no route cannot be presented at all"),
    ForeignKey("trips.txt", "service_id", "calendar.txt", "service_id", FATAL, True,
               "a trip with no service never runs; check calendar_dates.txt too"),
    ForeignKey("trips.txt", "shape_id", "shapes.txt", "shape_id", QUARANTINE, False,
               "geometry is optional; the trip still runs without it"),
    ForeignKey("stop_times.txt", "trip_id", "trips.txt", "trip_id", FATAL, True,
               "calls belonging to no trip can never be scheduled"),
    ForeignKey("stop_times.txt", "stop_id", "stops.txt", "stop_id", FATAL, True,
               "a call at an undefined place"),
    ForeignKey("stops.txt", "parent_station", "stops.txt", "stop_id", QUARANTINE, False,
               "breaks station grouping, not the schedule"),
    ForeignKey("stops.txt", "level_id", "levels.txt", "level_id", QUARANTINE, False,
               "affects pathway modelling only"),
    ForeignKey("frequencies.txt", "trip_id", "trips.txt", "trip_id", FATAL, True,
               "a frequency band with no trip to expand"),
    ForeignKey("transfers.txt", "from_stop_id", "stops.txt", "stop_id", QUARANTINE, True,
               "the rule is unusable; routing falls back to defaults"),
    ForeignKey("transfers.txt", "to_stop_id", "stops.txt", "stop_id", QUARANTINE, True,
               "as above"),
    ForeignKey("calendar_dates.txt", "service_id", "calendar.txt", "service_id",
               QUARANTINE, True,
               "legal on its own in a calendar_dates-only feed"),
    ForeignKey("fare_rules.txt", "fare_id", "fare_attributes.txt", "fare_id",
               QUARANTINE, True, "the journey is unpriced, not unrunnable"),
    ForeignKey("fare_rules.txt", "route_id", "routes.txt", "route_id", QUARANTINE, False,
               "empty means the rule is unconstrained by route"),
    ForeignKey("pathways.txt", "from_stop_id", "stops.txt", "stop_id", QUARANTINE, True,
               "the pathway edge is dropped"),
    ForeignKey("pathways.txt", "to_stop_id", "stops.txt", "stop_id", QUARANTINE, True,
               "as above"),
)


@dataclass
class Violation:
    key: ForeignKey
    orphan_rows: int
    distinct_values: int
    sample: list[str]

    def __str__(self) -> str:
        return (f"[{self.key.severity}] {self.key.child_table}.{self.key.child_column} "
                f"-> {self.key.parent_table}.{self.key.parent_column}: "
                f"{self.orphan_rows} row(s), {self.distinct_values} distinct value(s) "
                f"e.g. {self.sample} — {self.key.note}")


def check_integrity(tables: dict[str, pd.DataFrame],
                    extra_service_ids: set[str] | None = None) -> list[Violation]:
    """tables: member name -> frame. Absent members are simply skipped."""
    violations: list[Violation] = []
    key_sets: dict[tuple[str, str], set[str]] = {}

    def keys_for(table: str, column: str) -> set[str] | None:
        cached = key_sets.get((table, column))
        if cached is not None:
            return cached
        frame = tables.get(table)
        if frame is None or column not in frame.columns:
            return None
        built = set(frame[column].dropna().astype(str))
        key_sets[(table, column)] = built
        return built

    for fk in FOREIGN_KEYS:
        child = tables.get(fk.child_table)
        if child is None or fk.child_column not in child.columns:
            continue
        parent_keys = keys_for(fk.parent_table, fk.parent_column)
        if parent_keys is None:
            log.debug("%s absent — skipping %s.%s", fk.parent_table,
                      fk.child_table, fk.child_column)
            continue

        # calendar_dates.txt legitimately defines service_ids of its own.
        if fk.parent_table == "calendar.txt" and extra_service_ids:
            parent_keys = parent_keys | extra_service_ids

        column = child[fk.child_column]
        present = column.notna() & (column.astype(str) != "")
        if fk.required and not present.all():
            missing = int((~present).sum())
            log.warning("%s.%s is empty on %d row(s) but is required",
                        fk.child_table, fk.child_column, missing)

        candidate = column[present].astype(str)
        orphan_mask = ~candidate.isin(parent_keys)      # one vectorised pass
        if not orphan_mask.any():
            continue

        orphans = candidate[orphan_mask]
        distinct = sorted(set(orphans))
        violations.append(Violation(key=fk, orphan_rows=int(orphan_mask.sum()),
                                    distinct_values=len(distinct),
                                    sample=distinct[:5]))

    fatal = sum(1 for v in violations if v.key.severity == FATAL)
    log.info("integrity: %d broken relationship(s), %d fatal", len(violations), fatal)
    return violations


def is_loadable(violations: list[Violation]) -> bool:
    return not any(v.key.severity == FATAL for v in violations)

Step-by-Step Walkthrough

The relational model is data. FOREIGN_KEYS is sixteen rows describing the whole feed, each carrying a severity and a note explaining what actually breaks. Adding a relationship as the specification grows is one line; the checking logic never changes and cannot drift between relationships.

keys_for caches per referenced column. stops.stop_id is referenced by five different relationships, and building that set once instead of five times is most of the function’s performance. On a feed with 8,900 stops it is not a large saving; on trips.trip_id with 31,000 entries referenced by three tables, it matters.

The membership test is Series.isin(set), run once per relationship. Against a Python set this is a hashed lookup per row in C, so stop_times.trip_id — 1.8 million rows against 31,000 keys — completes in well under a second. The naive alternative, a per-row in inside apply, takes minutes and is why so many pipelines skip integrity checking.

Empty and orphaned are separated. A column that is required and empty is a different defect from one that holds a value pointing nowhere, and the second test runs only on rows that actually carry a value. Conflating them reports missing optional shape_id values as thousands of broken references.

calendar.txt gets special handling through extra_service_ids. A calendar_dates-only feed defines every service_id in the exceptions file, so checking trips.service_id against calendar.txt alone would report every trip as orphaned. Passing the union in is the one place the generic machinery needs help.

Severity is a property of the relationship, not of the count. One orphaned trip_id in stop_times is fatal for that trip whether it appears once or ten thousand times; a missing shape_id is cosmetic at any volume. is_loadable reads that classification rather than a threshold.

Verification and Output

def verify(violations: list[Violation]) -> None:
    for v in violations:
        assert v.orphan_rows > 0, "a violation with no orphaned rows"
        assert v.distinct_values <= v.orphan_rows, "more distinct values than rows"
        assert v.key.severity in (FATAL, QUARANTINE), "unknown severity"
        assert len(v.sample) <= 5

    seen = {(v.key.child_table, v.key.child_column, v.key.parent_table) for v in violations}
    assert len(seen) == len(violations), "the same relationship reported twice"

A healthy feed reports nothing at all. A feed assembled from a partial export:

INFO gtfs.integrity: integrity: 3 broken relationship(s), 1 fatal

[fatal] stop_times.txt.trip_id -> trips.txt.trip_id: 41208 row(s), 1846 distinct value(s) e.g. ['1128_1420', '1128_1455', '1131_1425', '1131_1500', '1134_1430'] — calls belonging to no trip can never be scheduled
[quarantine] trips.txt.shape_id -> shapes.txt.shape_id: 2104 row(s), 38 distinct value(s) e.g. ['s_1041', 's_1042', 's_1043', 's_1044', 's_1101'] — geometry is optional; the trip still runs without it
[quarantine] stops.txt.parent_station -> stops.txt.stop_id: 12 row(s), 12 distinct value(s) e.g. ['place_astor', 'place_bowdn'] — breaks station grouping, not the schedule

Three lines that say exactly what to do: reject the feed because 1,846 trips’ worth of calls have no trip, chase the missing geometries with the publisher, and expect station grouping to be incomplete until twelve parents are supplied.

Gotchas and Edge Cases

Identifiers compared across dtypes. A stop_id read as int64 in one table and string in another will never match. Casting both sides with astype(str), as above, is a blunt instrument that happens to be exactly right here — and the underlying fix is to read every identifier as text in the first place.
Leading and trailing whitespace. STOP_1 and STOP_1 are different keys. Strip identifier columns at read time, or the check reports orphans that a human inspecting the file cannot see.
Self-referencing keys. stops.parent_station points into stops.stop_id, and the code handles it without special casing because the parent set is built from the same frame. The cycle detection that self-references also need is a separate check.
Enormous distinct counts. A feed where every trip_id in stop_times is orphaned has a distinct count in the tens of thousands, and the sample of five is the only part anyone will read. Keep the sample small and the count exact.
Optional tables that are present but empty. keys_for builds an empty set, so every reference to them becomes an orphan. Skipping empty parent tables entirely would hide a real problem; reporting them as quarantine and letting a human look is the safer default.

Frequently Asked Questions

Which GTFS foreign key breaks most often?

stop_times.trip_id pointing at a trip that is not in trips.txt, usually because a feed was assembled from a partial export. It is also the most damaging, because those calls can never be scheduled and every count derived from stop_times is then wrong.

Should a broken foreign key fail the whole ingest?

It depends on which one. A trip referencing a missing route or service cannot run and should be rejected; a stop_time referencing a missing stop is fatal for that trip but not for the feed. Classify the relationship rather than applying one rule to all of them.

Why declare the foreign keys as data?

Because there are more than a dozen of them and they change as the specification grows. A table of relationships can be extended, tested and printed; the same logic spread across a dozen hand-written checks cannot.

Is a set membership test fast enough for 1.8 million rows?

Yes, when the set is built once. Testing a column against a Python set of a few thousand identifiers with pandas isin runs in well under a second on the largest GTFS table. Building the set per row is what makes it slow.

How to Validate a GTFS Feed with Python — the schema and semantic layers either side of this one
Building a GTFS Data Quality Scorecard — turning these findings into a number that trends
Up: GTFS Validation Rules and Common Schema Errors — where integrity sits among the validation layers
Section: GTFS Feed Architecture & Fundamentals · Home