"How many orders did we have last month?"
5,898 (+27%)
Stated with high confidence and no mention of internal accounts, which this backend can neither see nor exclude.
Case study 02 of 03 · Semantic AI benchmark
Before an AI agent uses your metrics, prove the metrics are right.
I rebuilt the semantic layer from case study 01 on the stack most mid-sized data teams already run: dbt Core and MetricFlow, on a single DuckDB file. Same frozen data, same five planted problems, same metric contract. This part tests the layer itself against independent ground truth, which is the precondition for trusting any agent built on top of it.
dbt Core 1.12
MetricFlow 0.213
DuckDB
Claude Opus 5
theLook eCommerce
41 / 41
layer checks within tolerance of ground truth; all but one exact to floating-point rounding
0 of 44
aggregates differ from the BigQuery star schema built in case study 01
70 tests
in a 93-node dbt build that goes green from an empty file in about five seconds
€0
cloud cost to run the stack; it runs offline on a laptop, only the LLM is paid
Scope, stated up front
Everything here was measured: layer correctness, build determinism, engine parity and one end-to-end smoke test. The full paid agent evaluation (about $55 in API tokens) was built and tested but deliberately not run, so this page contains no agent pass rates for this stack and nothing on it implies otherwise.
My role: solo build, covering the dbt project, the MetricFlow model, the backend, the tests and the comparison tooling.
The question
Most teams will not adopt a new semantic layer. They will put AI on the dbt project they already have.
Case study 01 tested BigQuery with Cube, a separate service with its own model files. Here the metric definitions live in the same repository as the transformation code, reviewed in the same pull requests and checked by the same CI. That changes where "governed" actually lives.
The thesis stays the same: a semantic layer makes answers consistent, but it is only as correct as the judgment written into it. Building the same layer twice also shows something one build cannot: each stack has its own ways to get a metric subtly wrong.
Same benchmark, second stack
Only the stack changed. Everything that decides the score stayed frozen.
The data, the planted problems, the 20 questions, the scorers, the judge and the system prompt are identical to case study 01, checked against a git tag of its final state.
| Case study 01 | Case study 02 | |
|---|---|---|
| Warehouse | BigQuery (EU) | DuckDB, one local file |
| Transformation | SQL views applied by a script | dbt Core with dbt-duckdb |
| Semantic layer | Cube Core | MetricFlow |
| Agent model | Gemini on Vertex AI | Claude (Opus 5) |
| Cloud cost for the stack | BigQuery and Vertex AI | None; API tokens only |
How it is built
From flawed Parquet files to governed metrics, with truth kept outside the system.
The planted problems
The same five traps, handled the dbt way.
Some are fixed by design, one is deliberately left open, and one cannot be fixed at all. Saying which is which is part of the work.
| ID | What is wrong in the data | Handled? | How, in dbt and MetricFlow |
|---|---|---|---|
| P1 | "Revenue" is ambiguous. Gross includes returned items, net does not. | Left open | Both metrics exist under separate names, so the test is whether the agent notices the split. |
| P2 | A category is renamed mid-window (Sweaters to Knitwear). | By design | A hand-maintained dbt seed maps both names to one category family; a test checks the seed against the benchmark settings. |
| P3 | Consent loss nulls the acquisition channel and session source. | Visible only | NULL becomes an explicit "Unattributed", and a coverage metric exposes the gap. The true source is unrecoverable by design. |
| P4 | Internal and test accounts inflate order counts and distort repeat rate. | By design | A rule-based flag, filtered on every metric. A dbt test checks it flags exactly the 1,000 injected users. The raw-table baseline cannot exclude them. |
| P5 | A small, real cohort returns far more items, invisible at channel level. | Structurally | A cohort mart lets the question be asked at the right grain. The agent still has to choose to look there. |
Governance in dbt
Every dbt test can pass for a net revenue that forgets to subtract returns.
Results
The layer matches ground truth, and it matches the first stack.
Every catalog metric over the full 24-month window, compared with a number recomputed from the clean world by separate code. The same suite runs against case study 01's Cube layer. No LLM is involved.
| Layer | Checks | Largest deviations from truth | Tolerance |
|---|---|---|---|
| MetricFlow | 41 | Repeat purchase rate 0.22%; every other check about 1e-14 (floating-point rounding) | 0.5% |
| Cube (case study 01) | 41 | Repeat purchase rate 0.22%, orders 0.077%, purchasing customers 0.051%, the rest near exact | 0.5% |
The 0.22% gap is the same in both stacks: the shared cohort rule counts a second order within 90 calendar days, while truth uses exact timestamps. MetricFlow is exact on orders and purchasing customers because they are defined on order items; Cube defines them on the orders table and is off by a few zero-price orders. Neither is wrong; the test shows which one is exact.
| fct_order_items | 175,804 | dim_customer | 97,447 (1,000 internal) |
| fct_orders | 119,574 | dim_product | 30,858 |
| fct_sessions | 663,100 | dim_date | 2,829 |
| mart_customer_cohorts | 97,446 | dim_distribution_center | 11 |
One question, end to end
The same question, asked once of Claude on each backend.
A smoke test, not a result: it shows the mechanism, not a pass rate. Truth is 4,633 orders.
"How many orders did we have last month?"
4,633 (exact)
The layer excludes internal and test accounts by construction, and the agent's stated definition says so.
Two stacks, same benchmark
What building the same layer twice showed.
Engineering findings
Found while building, not in the documentation.
LLM filter values can become code
MetricFlow renders where-clauses as Jinja, so a value like a template expression from the model would be evaluated. The backend rejects template and control characters, NaN and non-ISO dates, and escapes quotes.
A tolerance can hide a missing day
A BETWEEN on timestamps stopped at midnight and dropped 31 August, about 0.14%, well inside the 0.5% tolerance. Now the upper bound is exclusive.
Dates depended on the laptop's time zone
DuckDB derives dates from Parquet timestamps in the local zone; BigQuery always uses UTC. Staging casts to UTC and the profile pins the zone.
Rebuilds were quietly non-deterministic
An arbitrary-value aggregate and 91 users with two orders at the same instant made rebuilds differ. A deterministic aggregate and an order id tie-breaker fixed it.
An expected failure hid a crash
A strict xfail reported success while the test itself crashed. It surfaced only by listing what was actually collected; the test now asserts it found something.
The docs example did not parse
In dbt-core 1.12.5 the aggregation time dimension sits beside the semantic model, not inside it, and MetricFlow needs a time spine, derived here from the date dimension.
Honest limitations
What this case study cannot tell you yet.
Privacy
Everything runs locally except the LLM calls. The agent receives metric metadata and aggregated results only, never raw rows. Claude is called through the Anthropic API under its commercial terms. The data is Google's public, synthetic theLook dataset; no real customer data is involved at any point.
The series
01: the same benchmark on BigQuery and Cube, answered by Gemini, with full agent results. 03: Power BI with the model defined in TMDL. Same data, same questions, same judge, so the stacks can be compared fairly.