This is a multi-hop query, and it is where both traditional BI and large language models fail. The failure is rarely loud. The query runs. A number appears. The chart renders. The number is just wrong, usually inflated, because a join fanned out and a measure got counted twice. Call it the Silent Join Problem: the system returns a plausible answer that violates the structure of the data, and nobody notices until a board deck contradicts the finance close.
Single-hop is solved. Counting rows in one table is trivial. Reasoning correctly across five tables, choosing the right path among several, respecting cardinality so totals stay honest, is the hard part, and it is exactly the part business users now demand in plain language. That is why multi-hop is the frontier.
The Silent Join Problem, Concretely
Here is the trap in eight lines of SQL. Orders are one-to-many with line items:
-- Looks right. Is wrong.
SELECT SUM(o.amount) AS total_revenue
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id;
-- Order #1 has two line items, so its amount is summed twice.
-- total_revenue is inflated. No error is thrown.The join fanned out. orders.amount lives at the order grain, but the join multiplied it to the line-item grain, so every multi-item order is counted once per item. The query is syntactically perfect and semantically broken. This is a fan trap, and it is the single most common way multi-hop analytics lies to you.
Why Multi-Hop Is Hard: Three Mechanics
1. Multiple valid paths through a schema. In a normalized schema there is usually more than one way to join two tables. The classic join ambiguity: two tables connected by two different relationships, so a query touching both has no single correct interpretation. BI patents have described this for decades, along with the standard remedy: model the schema as a graph, find a tree connecting all referenced tables, and raise an error when more than one tree exists. The number of candidate paths grows combinatorially with schema size; on an enterprise schema with thousands of columns, naive path enumeration explodes and pruning becomes mandatory. An LLM handed raw schema does not search this space carefully. It pattern-matches on column names and writes a join that looks plausible. When two foreign keys could connect the same tables, it picks one, silently.
2. Cardinality determines whether your totals are honest. The most expensive multi-hop bug is the fan trap shown above: chained one-to-many joins that repeat a measure living at a coarser grain. The chasm trap is its mirror, two many-to-one joins converging on one table and double-counting across both branches. These are not exotic edge cases. They are the default outcome of joining tables at different grains without knowing the cardinality. Looker built an entire feature, symmetric aggregates, to defend against exactly this, and it works only when the developer has declared the join relationship and a unique primary key on every joined view. Looker's own documentation shows a two-table example where a naive SUM(total) returns 223.44 instead of the correct 124.84. Without declared cardinality, even an expert SQL writer slips, and an LLM has no chance, because cardinality is invisible in column names.
3. Grain and disambiguation are reasoning, not syntax. Join a transaction table (one row per line item) to a monthly target table (one row per region per month) without aligning grain and you get a row-count explosion and a meaningless sum. Many-to-many relationships make it worse: you need a bridge table and weighted aggregation, or the totals are arbitrary. Grain changes must be deliberate and documented; when grain alteration is an accidental side effect of a careless join, it is the root cause of most incorrect aggregates in analytics.
Where Traditional BI and Text-to-SQL Both Fail
The benchmark collapse. The cleanest evidence comes from Spider 2.0, the enterprise text-to-SQL benchmark built from real databases that routinely exceed 1,000 columns. On the older Spider 1.0, GPT-4 solved about 86.6% of tasks. On Spider 2.0, the best agent (built on o1-preview) solved only 21.3% of tasks. Schema linking, just choosing the right tables and columns, accounted for about 27.6% of the SQL errors.
BIRD, built on messy real-world databases, tells the same story: human experts reach about 92.96% execution accuracy, while GPT-4 reached only 54.89% even when handed curated external knowledge, dropping to 34.88% without it.
The most direct measurement of the multi-hop failure is the data.world enterprise benchmark by Juan Sequeda, Dean Allemang, and Bryon Jacob, run on an OMG Property & Casualty insurance schema. Using GPT-4 with zero-shot prompts directly on the SQL database, accuracy was 16.7%. Representing the same database as a knowledge graph (a semantic layer) lifted accuracy to 54.2%, more than triple. The multi-hop punchline, in the authors' words: "When a question requires more than 5 tables to provide the answer, the accuracy drops to zero." On the high-schema-complexity quadrants, raw SQL answered zero questions correctly, while the semantic-graph version still answered roughly 35-39%.
Why LLMs specifically break on multi-hop. In "Measuring and Narrowing the Compositionality Gap in Language Models" (Findings of EMNLP 2023), Ofir Press and colleagues measured the fraction of multi-hop questions a model gets wrong even when it answers every single hop correctly. They found this compositionality gap "remains at a roughly constant 40% between different model sizes and training techniques, with no apparent improvement from scale." Bigger models memorize more facts; they do not get proportionally better at composing them. Apply that to joins. Each hop is a chance to pick the wrong path or the wrong direction, and errors cascade: in step-by-step multi-hop pipelines, one incorrect intermediate corrupts everything downstream. Snowflake's engineering team named the failure modes plainly: with LLM-based text-to-SQL, "fan traps, chasm traps and join path hallucinations are more prevalent, as models are prone to making mistakes or hallucinating," leading to "double counting and flawed query logic, fundamentally compromising the trustworthiness of the generated SQL."
Graph-Based Query Planning: The Real Fix
Every serious system converges on the same remedy: stop asking the model to write joins from scratch, and make join paths first-class, proven objects in a graph. Entities become nodes; relationships become typed edges carrying cardinality and grain. Answering a multi-hop question becomes a constrained graph traversal: find a path connecting all referenced entities, prune paths that violate grain, prune paths whose cardinality expands beyond a threshold, eliminate cycles, and, if no unique valid path exists, fail loudly instead of guessing.
Competitor approaches: dbt Semantic Layer (MetricFlow) validates queries for "ambiguous joins or paths in multi-hop joins" and surfaces errors before execution. Cube treats join paths as explicit, ordered sequences and uses join hints to resolve diamond-subgraph ambiguity. Looker uses declared relationships plus symmetric aggregates to keep sums correct across fan-outs. Snowflake Cortex Analyst rebuilt its join engine on directed graphs to support complex multifact schemas and explicitly "prevent join hallucinations and double counting."
The measured payoff. When the LLM reasons over a semantic model instead of raw schema, accuracy jumps. dbt's 2026 benchmark (ACME Insurance dataset, 11 questions run 20 times each) found that with current models, raw text-to-SQL hit 84.1% to 90.0%, while the same questions answered through the Semantic Layer hit 98.2% to 100%, because "the Semantic Layer's deterministic query generation means the LLM can't produce subtly wrong results." Snowflake reported roughly a 20% accuracy lift from adding a semantic model over a vanilla LLM, and 90%+ SQL accuracy on real-world use cases. The data.world result triples accuracy on the same data with the same model. The lesson is consistent: the gain comes from better context and a deterministic compiler between the question and the SQL, not from a bigger model.
The Business Case: Why Multi-Hop Accessibility Is the Future
The cost of the bottleneck. Multi-hop questions are exactly the ones thrown over the wall to the data team today. Practitioner surveys put analyst time spent on ad hoc requests at 30-50% in stable environments and 50-70% in fast-paced industries like retail, finance, and tech. The backlog is tangible: data teams routinely carry dozens of open requests, with multi-week wait times reported as typical.
The cost of the wrong answer. The silent failure is more expensive than the slow one. Gartner's 2020 *Magic Quadrant for Data Quality Solutions*, based on a survey of 154 reference customers, found that "poor data quality costs organizations an average of $12.9 million" per year. Forrester's *Data Culture and Literacy Survey, 2023* found that more than one-quarter of global data and analytics employees estimate losing more than $5 million annually to poor data quality, with 7% reporting losses of $25 million or more.
The convergence: AI meets BI. The market is moving decisively toward conversational, agentic analytics. Looker's Conversational Analytics reached general availability in 2025, letting users ask questions across pre-joined explores. Gartner's 2025 predictions hold that half of business decisions will be augmented or automated by AI agents. The vision is identical everywhere: any business user asks a question in plain language and gets a trustworthy answer in seconds. That vision is impossible without solving multi-hop. As AtScale put it, the critical enabler for agentic BI "isn't the language model or the interface. It's the semantic layer."
How Colrows Makes Multi-Hop Deterministic
Colrows is a semantic execution layer. Where most BI tools resolve meaning at presentation time and most LLM tools generate SQL and hope, Colrows compiles every query against a typed semantic graph before any byte is read from the warehouse. The mechanics map directly onto the three failures above.
Proven join paths, not guessed ones. When a query references entities across datasets, Colrows must "prove, not guess, that a deterministic join path exists." Joins are solved as a constrained graph traversal over the semantic graph with three kinds of pruning: paths that violate declared grain are discarded, paths that introduce cardinality expansion beyond allowed thresholds are pruned, and cycles are eliminated. If a symbol cannot be resolved to a unique semantic node, or no valid path exists, compilation fails and the agent receives a structured error, not a fabricated answer.
Cardinality and grain as typed edges. Colrows' structure layer stores entities, edges, join paths, and cardinality as first-class objects. Because grain compatibility is encoded on the edge itself, the fan trap and chasm trap are caught at compile time rather than discovered in a board meeting.
Empirical path ranking. A behavior layer records distributions, value frequencies, and access patterns, the empirical fingerprint Colrows uses to rank the correct join path when several are valid, and to detect drift as the schema changes so the graph keeps itself current.
Determinism for agents and humans alike. A CFO typing a question and an AI agent calling through MCP resolve the same way. "Churn risk" is a versioned concept; "EU BFSI segment" resolves to entities with provable relationships. Ambiguous paths fail compilation rather than silently producing wrong numbers, and every query emits an audit record capturing the graph version, identity context, resolved entities, proven join paths, and compiled SQL, so any historical answer can be reproduced exactly.
The Bottom Line
Single-table BI is solved. Multi-hop is the frontier, and it is hard for a structural reason: joins have direction and cardinality, schemas have multiple valid paths, and both LLMs and traditional BI fail silently when those facts are not made explicit. The benchmarks are unambiguous: raw text-to-SQL collapses on enterprise schemas (21.3% on Spider 2.0, 16.7% on the data.world benchmark), while the same questions answered over a semantic graph more than triple in accuracy and approach 100% for covered queries. The organizations that win the next decade will be the ones whose business users can ask multi-hop questions in plain language and trust the answer, because a semantic execution layer proved the path before the query ever ran.
What is multi-hop query understanding?
A multi-hop query is not just a query with more joins. It is a question that requires the system to reason across multiple layers of meaning - entities, events, time, relationships, and business rules - before arriving at an answer.
Take a question that sounds natural to any business user: "Show me customers at risk of churn who experienced failed payments after upgrading their plan last quarter." The user isn't thinking about tables or schemas. They are thinking about customers, churn, payments, upgrades, and sequencing over time. Multi-hop understanding is the system's ability to infer those paths rather than waiting for them to be spelled out.
Why does BI break at multi-hop questions?
Traditional BI systems are excellent at projection - they can take known metrics and dimensions and present them in different shapes. But they struggle the moment relationships are implicit rather than explicit. When the path between concepts is not predefined, the burden shifts back to the user. The system is no longer assisting reasoning - it is merely executing instructions. This is why complex questions often lead to brittle dashboards, overly complex SQL, or long back-and-forths between business and data teams.
Why does this matter more than ever?
Modern enterprises operate in a world of interdependent systems and continuous change. Decisions are rarely driven by isolated metrics anymore - they are driven by sequences of events and their downstream effects. Understanding what changed is no longer enough; leaders want to know what triggered the change, what it affected, and what it signals about the future. These are not visualisation problems. They are reasoning problems.
Does multi-hop reasoning need semantics or SQL?
SQL is powerful, but it assumes the path is already known. It requires someone to define how concepts connect before a question can be answered. Multi-hop reasoning flips that assumption - it requires the system to explore relationships, infer roles, and apply context dynamically. That kind of understanding cannot emerge from flat metric catalogs or static dashboards. Multi-hop reasoning requires a semantic layer that models the enterprise as a connected system of meaning - not a collection of tables. Relationships must be first-class citizens, not implicit joins.
What role do semantic graphs play in multi-hop reasoning?
When analytics is built on a semantic graph, entities, events, metrics, and business concepts are explicitly connected, allowing the system to traverse meaning instead of assembling joins. In this model, answering a question is less about executing a predefined query and more about navigating a path through related concepts. This is the direction platforms like Colrows have taken - treating analytics as a graph traversal and reasoning problem rather than a query templating exercise.
Why aren't LLMs alone enough?
Large language models make this transition feel deceptively close. They can translate natural language into SQL and generate plausible answers quickly. But without semantic grounding, LLMs guess. They assume relationships, invent joins, and apply logic that sounds right but may violate business reality. LLMs become truly powerful when they operate on top of a semantic graph - when they reason with enterprise meaning rather than free-form text.
Is BI becoming a reasoning layer?
Business intelligence is evolving from dashboards to decision support, from queries to paths, and from metrics to meaning. Multi-hop query understanding marks the point where this evolution becomes unavoidable. Colrows models enterprise knowledge as a living semantic graph - connecting entities, events, metrics, and relationships in a way that enables true multi-hop understanding. The goal isn't to replace BI, but to give it a reasoning backbone that understands business context, evolves as the enterprise changes, and grounds AI in how the organisation actually operates.
Enterprises that embrace reasoning-first analytics will ask better questions, get clearer answers, and operate with greater confidence. Those that don't will continue to force complex reasoning into tools built for simple aggregation. This is the new frontier of BI - and it belongs to systems that understand meaning, not just data.
