Adjudicating semantic layers against raw backends

6 August 2026

A semantic layer sits between a user and a database and says: ask in your own words, I will handle the SQL. The pitch is trust — the layer knows the model, so you do not need to. That trust is exactly the hazard I am interested in.

I run an independent evaluation project that measures these surfaces against raw-backend ground truth: every query is executed on the surface under test and re-derived directly against the backend, which is the oracle — around five hundred adjudicated queries across public and synthetic datasets so far. This post is the method, and one worked example that explains better than any abstract framing why the silent failures are the ones that matter. The project itself is documented at the semantic-surface evaluation page.

The method in three steps

  1. Execute the question on the surface under test. Whatever the surface does with it — that is the claim.
  2. Re-derive the same question directly against the raw backend, in hand-written SQL, with no layer involved. The backend is the ground-truth oracle, and the only referee that shares none of the layer's incentives.
  3. Compare. Where they agree, record it. Where they disagree, adjudicate it.

The layer's whole contract is "my answer is the backend's answer, more convenient." When the two disagree, the layer has failed that contract — the only question is how.

A worked example: the fan-out that isn't

Four tables, three rows of data, one aggregate.

parent(id)          = {1}
child_a(parent_id)  = {1, 1}
child_b(parent_id)  = {1, 1}
fact(parent_id, v)  = {(1, 10)}

One parent row; two children each point at it twice; one fact row carrying the value 10.

Ask the layer for the sum of v over this parent. Its model says: parent has many child_a, parent has many child_b, parent has one fact. The layer plans the joins:

SELECT sum(f.v)
FROM fact f
JOIN parent p        ON f.parent_id = p.id
LEFT JOIN child_a a  ON p.id = a.parent_id
LEFT JOIN child_b b  ON p.id = b.parent_id

Execute it and you get 40.

Not 10. 40. And nothing in the response tells you so.

This is join fan-out. The parent row joined to child_a becomes two rows; joining those to child_b doubles them again — four rows total, each still carrying the one fact value v = 10, so sum() adds ten four times. Two independent one-to-many paths, each multiplying the grain, and the aggregate is silently multiplied by their product.

This is a class, not a bug in one SQL dialect. Any time a plan traverses two or more independent to-many paths relative to the grain of an aggregated measure, the additive aggregates are at risk — sum, avg, stddev, count(*). The defensive ones survive: count(distinct ...), min, max. A dashboard consumer cannot see the difference. A human analyst might, eventually, if the number looks odd. An agent consuming the layer through an API has nothing to look at — no dialect SQL, no row counts, no signal. The number is simply plausible.

The part that should make you uncomfortable

The layer that answered this query already knew the cardinalities. Its model declared parent → child_a as one-to-many and parent → child_b as one-to-many — precisely the metadata that makes the fan-out statically detectable at planning time. The multiplication is not random; it follows from relationships the layer itself modeled. A layer that holds declared cardinalities could warn: this plan multiplies the grain of an aggregated measure by a known factor. It does not. It returns 40 with the same confidence it returns 10.

That is the difference between loud and silent. A layer that errors is honest — it tells you it does not know. A layer that returns a plausible wrong number is the hazard: indistinguishable from a correct answer, and the consumer is the last one to find out.

This is why I classify failures by silence, not just by wrongness, on a severity scale from S1 to S4:

  • S1 — silently-wrong value. The number is simply wrong, with no indication. The fan-out above is S1.
  • S2 — false capability claim. The layer advertises a capability it does not actually deliver.
  • S3 — loud bug. A visible error or clearly broken result. Annoying, but honest — and it tends to get fixed.
  • S4 — micro-lie. Small divergences — a default, a label, a silently-applied assumption — that quietly shape wrong conclusions.

And the four classes each finding is adjudicated into: a backend gap (the raw backend itself cannot answer), a surface transpiler bug (the layer rewrites a correct question into a wrong query), a surface grammar limitation (the layer's own language cannot express the question), and an honesty/contract defect (the layer claims more than it delivers, or withholds the signal that should accompany its answer). The S1 fan-out above is a planning defect with an honesty component: the metadata to warn existed, and was not used.

What follows

For the consumer: the number is not the deliverable — the number plus its provenance is. Grain, row counts, completeness metadata should ride along with every answer, especially for agent consumers that have no other way to check.

For the evaluator: the reproducibility rule. Every finding is a minimal repro, executed on the surface and re-derived on the backend, classified and severity-rated. Synthetic examples prove mechanisms; measured numbers carry impact; full transcripts are available on request.

For this project: the defects found this way are reported upstream as correctness reports — free, public, with the repro and the measured impact, and each links back to the method above.

The 40-vs-10 example is three rows of synthetic data; it costs nothing to run in any dialect on any layer. If a layer returns 40, you now know what to look for. If it returns 10 — or warns that it knows it might have returned 40 — you know what a good layer looks like.

← all posts