description: "Valid SQL and correct Python can still produce false business conclusions. Learn to test metric definitions, join fanout, grain, and data context in AI agents."
tags: ai, dataengineering, sql, agents
published: false
How Semantic Errors Propagate Through Multi-Step AI Analytics
A successful query is not proof of a correct analysis. And correct Python cannot repair a broken business assumption.
On October 9, 2026, Databricks announced code execution for Genie One in Public Preview. It's a useful signal: enterprise analytics agents are moving beyond returning SQL results toward multi-step calculations, statistical analysis, and scenarios.
That introduces an important engineering question: what happens when step 1 returns plausible but semantically wrong data, and steps 2–5 faithfully build on it?
Consider:
Question → Metric resolution → Table selection → JOIN → SQL
↓
Python analysis
↓
Chart / Forecast
↓
Business conclusion
Every component can run successfully while the final decision is wrong. I call this analytical error propagation—a general architectural risk, not a claim about a specific defect in Genie One.
A minimal example: correct SQL syntax, inflated revenue
Imagine an order with two order lines and three payment records.
order_lines
order_id | line_id | amount
42 | 1 | 100
42 | 2 | 50
payments
order_id | payment_id | paid_amount
42 | 10 | 60
42 | 11 | 50
42 | 12 | 40
The order-line total is 150. Payment total is also 150.
Now suppose an agent generates:
SELECT SUM(ol.amount) AS revenue
FROM order_lines ol
JOIN payments p ON p.order_id = ol.order_id
WHERE ol.order_id = 42;
The join returns six rows:
2 order lines × 3 payments = 6 joined rows
So SUM(ol.amount) returns 450, not 150. The SQL is valid; the JOIN is legal; the database behaves correctly. But the result is inflated 3×.
The immediate fix for this metric is not to add DISTINCT indiscriminately. It is to use the correct fact grain and avoid joining payment rows if the approved revenue metric does not require them:
SELECT SUM(ol.amount) AS order_line_total
FROM order_lines ol
WHERE ol.order_id = 42;
If the analysis actually needs payment data, aggregate each fact to a compatible grain before combining them:
WITH line_totals AS (
SELECT order_id, SUM(amount) AS line_total
FROM order_lines
GROUP BY order_id
),
payment_totals AS (
SELECT order_id, SUM(paid_amount) AS payment_total
FROM payments
GROUP BY order_id
)
SELECT l.order_id, l.line_total, p.payment_total
FROM line_totals l
JOIN payment_totals p ON p.order_id = l.order_id
WHERE l.order_id = 42;
Important: order_line_total is not automatically the company's recognized revenue. That depends on the approved business definition. Correct grain solves the fanout problem; it does not settle accounting semantics.
How the error spreads
Suppose an agent uses the inflated query as the source for a Python notebook:
revenue = run_sql(generated_sql) # returns inflated figures
by_country = revenue.groupby("country")["revenue"].sum()
growth = by_country.pct_change()
forecast = forecasting_model.fit(revenue)
summary = llm.summarize(by_country, growth, forecast)
The Python may be bug-free. But now the agent can produce:
- Incorrect regional revenue totals
- Misleading country rankings
- Distorted growth rates (especially when fanout differs by period)
- Forecasts trained on invalid baselines
- Convincing explanations of a decline or recovery that never occurred
Notice the crucial detail: if the multiplication factor changes between periods, even percentage growth becomes wrong. This is not merely a cosmetic scale error.
Four correctness boundaries
Before giving an agent more analytical autonomy, I would put explicit checks at four boundaries.
1. Business meaning
What does revenue mean?
Possible candidates include booked orders, invoiced amounts, recognized revenue, and cash collected. They may be valid metrics, but not interchangeable.
Represent the selected definition explicitly:
{
"metric_id": "recognized_revenue",
"owner": "finance",
"aggregation": "SUM",
"time_dimension": "recognition_date",
"status": "approved"
}
This is an illustrative design object, not a claimed vendor API.
Check: If two metrics could reasonably match the question, clarify or apply a governed default. Do not silently select by column-name similarity.
2. Physical mapping
What fields actually implement the selected business metric?
Business term → Governed metric → Physical columns
Check: Verify source fields, types, filter mappings, and date semantics. “Europe” may mean customer location, sales territory, or legal entity.
3. Relationships and grain
A valid key relationship is not enough to guarantee a valid analytical join.
Check: Examine join cardinality, fact grain, bridge tables, fanout risk, and null behavior.
Useful assertions include:
assert result.row_count >= 0
assert plan.metric_grain == "order"
assert plan.join_path_is_approved
assert plan.fanout_risk == "none"
These fields are conceptual; in a real system, implement the checks against metadata and actual query plans rather than trusting LLM-produced flags.
For example, a practical preflight can compare row counts and distinct business keys before and after a proposed join. Any unexpected multiplicative increase should trigger investigation.
4. Analytical execution
Only after the query foundation is checked should the agent run further calculations.
Check: Preserve the resolved metric, dimensions, filters, time window, and dataset identity across every step. A later forecasting tool should not silently switch from recognized revenue to invoice amounts.
Treat the query plan as a contract
Rather than passing only SQL from one agent stage to another, preserve a structured analytical contract:
{
"question": "Why did revenue decline in Europe last quarter?",
"metric": "recognized_revenue",
"metric_owner": "finance",
"dimension": "sales_region",
"period": "previous_fiscal_quarter",
"fact_grain": "order",
"join_path": [
"customer -> order",
"order -> revenue_fact"
],
"validation": {
"metric_approved": true,
"grain_checked": true,
"join_cardinality_checked": true
}
}
Again, this is a proposed reference design. The true values must be produced by real validation routines, not asserted by the language model.
A useful pipeline looks like:
Natural-language question
↓
Resolve business meaning
↓
Map concepts to physical data
↓
Retrieve candidate relationships
↓
Validate cardinality and grain
↓
Build and validate query plan
↓
Execute SQL
↓
Run Python / statistics / forecasts
↓
Return conclusion with evidence
This separates finding a plausible path from proving that the path is safe for the metric.
Where Semora and IntaLink fit
This distinction also explains how complementary components can help.
Semora manages its own data-source connections and query context. Its documented capabilities include business semantics, terminology and mappings, metric/dimension definitions, ambiguity handling, and SQL generation/validation.
IntaLink discovers candidate table/field relationships from metadata and data characteristics, and exposes relationship paths through APIs. Semora maps the corresponding data source by data-source ID, requests relationship information from IntaLink, and continues managing the actual data access and query execution independently.
User question
↓
Semora: business semantics + physical mappings
↓
IntaLink API: relationship candidates / paths
↓
Semora: query construction and validation
↓
Query result
This is a useful foundation for trustworthy querying, not a claim that either platform already provides Genie-style sandboxed Python execution, automated forecasting, or complete analytical error-propagation detection.
The missing engineering work for a more autonomous analytics agent is to turn relationship evidence into validated analytical plans, with explicit grain and metric checks.
Five tests I would run before production
| Test | Deliberate trap | What success looks like |
|---|---|---|
| Metric ambiguity |
invoice_amount and recognized_amount both exist |
Correct governed metric or clarification |
| Join fanout | Two order lines and three payments | No multiplication of revenue |
| Grain mismatch | Customer metric built from line-level facts | Correct aggregation grain |
| Multi-step consistency | SQL result feeds a forecast | Same metric, filters, and period throughout |
| Correction and rerun | User corrects a metric choice | Recompute from source; don't just rewrite the summary |
A useful sixth test is counterfactual consistency: replace a wrong relationship with the correct one and check whether every downstream chart, statistic, and narrative is regenerated rather than cached from the previous run.
Don't measure only execution success
For production analytics agents, I would track:
SQL execution success
Metric-resolution accuracy
Join/cardinality validation pass rate
Grain-validation coverage
Multi-step context consistency
End-to-end answer correctness
Execution success is a necessary operational metric. It is not a business-correctness metric.
Final thought
As AI agents become capable of running code, forecasting, and explaining trends, it's tempting to focus on the sophistication of the analysis engine.
But the dangerous failure mode may be much simpler: the agent starts with the wrong metric or the wrong join, then performs increasingly sophisticated operations on incorrect data.
Better reasoning cannot repair an incorrect understanding of the data.
The engineering priority is to validate business meaning and relational structure before amplifying their outputs through multi-step analysis.
Top comments (0)