My semantic layer answers this question confidently. The answer is ambiguous.
I'm building an evaluation harness for AI-generated SQL. While simplifying my semantic layer, I accidentally removed a guardrail I didn't know I had — and discovered a class of question that conventional semantic layers don't handle well.
The question
"What was average order value by product category in 2023?"
This sounds straightforward. It isn't. The problem is that 28.62% of orders in this dataset span multiple product categories. An order with a jacket ($100) and socks ($15) belongs to both Outerwear and Accessories.
So "AOV by category" requires a decision about how to attribute multi-category orders, and there are at least four defensible methods:
Attribute the full order to every category it touches. The Outerwear row shows $115 (the whole order), and so does the Accessories row. Revenue double-counts across categories. AOV is high everywhere.
Attribute only the lines belonging to the category. Outerwear shows $100, Accessories shows $15. Revenue doesn't double-count, but the denominator is "orders touching this category," which still includes the multi-category order in both rows. You're dividing line-level revenue by order-level counts — a mixed-grain ratio.
Split the order proportionally. Outerwear gets 100/115 of the order, Accessories gets 15/115. Clean in theory, almost never how anyone actually computes it.
Exclude multi-category orders. Only single-category orders contribute. Clean, but you've just dropped 28.62% of your data.
Four methods, four numbers, all defensible. The correct behavior for any analytics system is to ask which one the user means.
What my semantic layer does instead
I have a MetricFlow semantic layer defined over fct_order_items at line-item grain. The metric is:
- name: average_order_value
type: ratio
type_params:
numerator: net_revenue
denominator: order_count
Where order_count is COUNT(DISTINCT order_id) at line grain, and category is a dimension on the same table.
When I run:
mf query --metrics average_order_value --group-by category
It returns numbers. Outerwear $110, Socks $13, and so on. No error, no warning, no indication that a choice was made.
What it actually computed: for each category, sum the net revenue of lines in that category, divided by the count of distinct orders that touched that category. That's method 2 from the list above — line-level revenue divided by order-level counts. The denominator counts the multi-category order in both Outerwear and Accessories.
The calculation is mathematically valid. The problem is its interpretation.
It measures something like:
category-line revenue per order that contains this category
That could be a useful metric. It is simply not the only defensible interpretation of "average order value by category."
Nothing in the output signals that an attribution or definitional choice has been made.
Was this caused by bad data modeling?
This is where the distinction between data modeling and semantic modeling matters.
fct_order_items at line-item grain is not inherently a bad model. In fact, it is a natural place to represent product, category, quantity, and line-level revenue.
The problem is that the requested metric and dimension naturally live at different conceptual grains:
AOV is fundamentally an order-level concept.
Category is a line-item-level concept.
An order can contain multiple categories.
A different data model could make one particular definition explicit.
For example, we could create a category-order fact such as:
order_id
category
attributed_revenue
Then category AOV could be defined over that model.
But that doesn't make the ambiguity disappear. We would still have to decide whether attributed_revenue means full-order attribution, line revenue, proportional allocation, or something else.
Better data modeling can encode an attribution decision.
It cannot determine which attribution decision the user intended.
That's why I don't think the right conclusion is "the semantic layer should have been modeled differently." The deeper problem is that the question itself is underspecified.
How I found this
I originally had two semantic models — one at order grain, one at line-item grain. order_count lived on the order model, category lived on the line model. When I asked for AOV by category, MetricFlow refused: no valid join path from an order-grain measure to a line-grain dimension.
That refusal was accidental. I hadn't designed a guardrail — MetricFlow's join resolution just couldn't bridge the two grains for that combination. But it was correct behavior: the system couldn't compute the ambiguous thing, so it didn't.
Then I simplified to a single semantic model at line grain. Everything lives on one table, no cross-grain joins, simpler to maintain. The simplification was an improvement in every other way. But it removed the accidental guardrail, because order_count (now COUNT(DISTINCT order_id)) and category live on the same table. MetricFlow has no join to resolve, so it answers without hesitation.
I ran it, got numbers, and almost moved on. The numbers looked normal. That's the dangerous part.
The semantic-layer gap
This isn't a MetricFlow bug. The metric definition is valid, and the generated calculation is valid for the chosen interpretation.
What the experiment exposes is a gap in conventional semantic-layer abstractions.
Semantic layers are good at describing how a metric is computed: aggregation, filters, measures, dimensions, and relationships between entities.
But that doesn't necessarily tell the system whether a metric is unambiguous for a particular dimension.
For example, a semantic layer could theoretically know that:
average_order_value + month
→ unambiguous
average_order_value + category
→ requires an attribution policy
The challenge is that conventional metric definitions generally don't capture this kind of metric × dimension validity or attribution requirement.
That means a query can pass every technical check:
the metric exists;
the dimension exists;
the relationship is valid;
the SQL is valid;
the aggregation is valid.
And the result can still be semantically ambiguous.
That's a more interesting failure mode for GenBI than broken SQL.
Broken SQL usually announces itself.
A clean query that returns a plausible number for an underspecified business question does not.
What I'm doing about it
I'm not trying to "fix" the metric in the semantic layer. The metric is defined correctly. The computation is correct for one valid interpretation.
The problem is that the question has multiple valid readings and the system picks one without asking.
Instead, I'm putting this question into the evaluation harness I'm building. It's one of 20 "ambiguous tier" questions where the correct system behavior is to ask for clarification, not to answer. The harness will test multiple systems — including MetricFlow, a RAG-based agent, and raw text-to-SQL — on the same question, and measure which ones ask and which ones guess.
I also added a warning to the metric definition:
description: >
Net revenue per order. WARNING: grouping by category produces
"average category-line revenue per order touching that category,"
not true AOV within a category, because 28.62% of orders span
multiple categories.
That warning ends up in the schema context that an LLM reads.
Whether it changes the model's behavior is something the eval will measure.
And that's an important distinction: if the warning makes the model ask for clarification, that's useful evidence that metadata can help. If the model still confidently generates the query, that tells me the problem needs a different mechanism than metric descriptions alone.
Caveats
One dataset, one semantic layer tool.
The 28.62% multi-category rate is specific to this snapshot — a different business might have 5% or 80%, which changes how material the issue is.
The point isn't the specific number.
It's that transactional data frequently contains relationships where a metric defined at one grain becomes ambiguous when grouped by a dimension at another grain.
Whether a semantic layer should encode these ambiguities, whether an agent should detect them dynamically, or whether they belong in an evaluation or governance layer is still an open design question.
That's what I'm testing next.
Part of a project building an evaluation harness for AI-generated SQL.
Repo: github.com/Sean-Liu-GitHub/genbi-ecommerce
Key files: