A Freight Knowledge Graph, Multi-Hop Questions SQL Cannot Answer
Pull-quote: “A flow table can tell you what moved from A to B. It struggles to tell you what depends on what, and dependency is the shape of every question that matters during a disruption.”
Why this matters
Freight data arrives relational: flow tables keyed by origin zone, destination zone, commodity, mode, year. For the questions the tables were built for, how much moved, between where, by what, relational form is ideal, and no graph will beat an indexed SQL query at them. But planners and analysts ask a second family of questions: which regions depend, through any chain of movements, on this port zone; which commodities reach this metro area only through corridors that share one river crossing; which inbound flows are exposed, two or three legs upstream, if this junction degrades. These are dependency questions. Their shape is a path through relationships, the path length varies by question, and that variability is precisely what plain SQL handles worst.
Where joins give out
A two-hop dependency question in SQL is a self-join. Three hops is two self-joins with careful aliasing. Variable depth, find every upstream exposure, however many legs away, is a recursive query that most warehouses execute reluctantly and most analysts write incorrectly on the first three attempts. The deeper problem is conceptual: the relational schema encodes flows as rows, so relationships between flows, sharing a corridor, feeding the same destination, substituting for one another, exist only in the analyst’s head and must be reconstructed join by join, per query, forever. Every new dependency question starts from zero, which is why disruption studies that should take an afternoon get scoped in weeks.
| Question | Relational shape | Graph shape |
|---|---|---|
| Tonnage from A to B by mode | One indexed query, SQL wins | Unnecessary |
| Corridors serving a destination’s top commodities | Multi-way join, manageable | Two-hop traversal |
| Regions exposed to a port zone through any chain | Recursive CTE, fragile | Variable-depth traversal, native |
| Substitute routings for a disrupted flow | Not expressible without modeling routes as data | Alternative-path search |
The graph model
Zone ── ships ──► Flow ◄── receives ── Zone
│ │
carries │ │ moves-by
▼ ▼
Commodity Mode
│
Flow ── rides-on ──► Corridor ── connects ──► Zone
│
shares-segment
▼
CorridorZones, commodities, modes, flows, and corridors become nodes; ships, carries, rides-on, and connects become edges. The payoff is that dependency questions become what they always were conceptually: traversals. Which regions depend on this port is a walk from the port zone along inbound edges, however many hops the dependency actually runs. The query matches the question’s shape instead of fighting it.
The graph and the analyst
A freight knowledge graph of zones, commodities, modes, and flows is best built over the same unified model of public federal freight data, the FAF5 core of roughly 5.7 million flow records, that feeds everything else. The graph is not a replacement for the relational store; aggregation questions still run as queries against the flow model, at interactive speed. The graph is the substrate an AI analyst should reach for when a plain-English question has dependency structure in it, and it pairs naturally with flow-similarity search that finds comparable lanes: similarity proposes candidates, the graph verifies how they connect.
Closing
Keep the flow tables; they are the right tool for the questions they were designed for. The knowledge graph exists for the other family: multi-hop, variable-depth, dependency-shaped questions that recursive SQL answers badly and late. Model zones, commodities, modes, and flows as the graph they already are, and the question every disruption raises, what depends on this, becomes a traversal instead of a research project.