Commentary: the probe-count Pareto frontier and pit_of_success_for_tpc_ds.md

Working notes for integration — not a publishable draft. Data: pit_of_success_pareto_data.json (per-leg metrics, per-question probe lists, metric definitions, and caveats — all generated from the eval run reports in pytrilogy/evals/tpcds_agent/results/20260812-*).

The finding, in blog terms

We built a messy warehouse to prove SQL agents drown in it. They don't. We scaled irrelevant tables from 42 to 600, renamed every column, shifted every benchmark parameter, and planted in-domain trap tables (_bak, _staging, stale _v2 copies, near-correct daily rollups). Five escalating treatments, five null results: a modern model resolves anything resolvable by reading.

What fell out instead is a cleaner economic story with three regimes and two currencies:

regime	pass	cache-adj tokens (20q)	DB queries per question
bare discovery (no schema given)	18–19	0.82–1.06M	19–22
full schema injection	16–18	0.92–2.71M (linear in schema size)	7.3–8.8
enriched Trilogy	20	2.08M (flat — schema-size-independent)	4.7–5.1

• Bare discovery buys token flatness with database queries. No schema handed over means the warehouse is the schema document: ~20 probes per business question, at every warehouse size.
• Schema injection is the worst of both at scale. At 600 tables it is strictly dominated by the enriched path on all three axes — more tokens (2.71M vs 2.08M), more DB queries (147 vs 101), fewer correct answers (16 vs 20).
• The enriched path is probe-minimal and accuracy-maximal. The semantic model answers the exploration questions that would otherwise be probes; trilogy run fires ~5 times per question, only to validate near-final drafts. Its cost is flat in warehouse size because the agent never reads the warehouse schema at all.

So the frontier has two anchors: discovery is the token-minimal choice, enriched is the probe-and-accuracy choice, and you pay ~2.3x the discovery agent's tokens to cut warehouse queries 4x. Full-schema injection — the default "best practice" everyone reaches for — stops being on the frontier at all past a few hundred tables.

The warehouse-bill framing (this is the hook). All of this was measured on SF=1 DuckDB, where a probe costs milliseconds and nobody cares. The token axis is priced in dollars already; the probe axis is priced in warehouse. On BigQuery or Snowflake each agent probe carries a latency floor (queue + compile + scan, seconds each) and a billing surface (bytes scanned / warehouse-seconds — and an exploring agent loves SELECT *). Twenty probes per question is minutes of wall-clock and real money per business question; five validation runs is not. The honest phrasing: on a laptop, tokens are the only bill, and discovery wins it; on a cloud warehouse, the probe axis acquires a price, and the frontier tilts toward the semantic layer. We should present this as framing, not measurement — we did not run against BQ — but the counts themselves are measured and the per-probe floors are public knowledge.

Audit: where the current post stands relative to this data

The post's arc is hubris → diagnosis → fix, and it closes on two forward-looking claims that this experiment directly tests:

1. Line ~97: "we expect to pull further ahead with messier schema, more tables, caching, etc." — Half-refuted, half-confirmed, and the memo should say so in the post's own self-deprecating voice. Messier schema and more tables did not hurt the SQL agent: accuracy stayed flat through 600 tables and every trap we set (the discovery agent even beat injection at high dose, 18–19 vs 16). But "caching, etc." matured into something better than we predicted: the probe-count Pareto. The claim to retire is agent insulation from mess; the claim that replaces it is probe economics + flat cost in warehouse size + aggregate routing.
2. The tip box (~line 107): "We're working on additional benchmarks that better represent a messy warehouse." — This happened. The new section is its payoff, and the Hex link stays relevant (their synthetic-benchmark argument is exactly what our confusable-noise arm implements).
3. The existing chart tpcds_cost_quality.svg plots pass rate vs total tokens for the four setups. The new chart is its natural sibling — same family, one new axis — and the two should visually rhyme (same palette per setup, same SVG treatment).
4. Internal consistency note: the post reports pass rates from the 99-query runs (80–90%); the Pareto data is from 20-question waves (16–20/20). Don't mix the two scales in one sentence — cite the wave data as "the messy-warehouse waves" with n=20 acknowledged.
5. Typos to fix while in the file: "how we Trilogy from" (missing verb, line 9), "workgin" (47), "withotu" (71), "triped"/"spend lot of tokens"/"isn't a perfect for" (87), "wrong uers" (105), "unambigious" (131).

Where it fits

Recommendation: a new section in this post, after "The numbers" and before "Lessons", roughly titled "The economics: what the messy warehouse actually showed." Three to five paragraphs plus the Pareto chart. The material has a second act (wave-by-wave nulls: dose sweep, discovery regime, column renames, parameter shifts, confusable traps) that deserves its own follow-up post — don't cram it here. This post only needs:

• One paragraph of setup: we built the messy warehouse expecting SQL agents to drown; they read their way out of everything we threw at them. (This is the same "hubris and shame" register as the opening — it strengthens the post's credibility rather than undercutting it.)
• The three-regime table or chart.
• The Pareto statement: discovery anchors the token end, enriched anchors the probe-and-accuracy end, injection exits the frontier at scale.
• The DuckDB-vs-cloud-warehouse framing paragraph as the closer — it converts a benchmark artifact ("probes are free at SF=1") into the real-world pitch.
• Rewrite the line-97 sentence to match ("we expected messier schemas to break SQL agents; they didn't — what changed at scale was the bill").

The "Lessons" section then lands harder: "any measure is a proxy" now has a second worked example (we optimized for insulation; the market metric is probe economics).

Visualization

Primary: the Pareto scatter. One chart carries the whole section.

• x-axis: DB queries per business question (0–25, linear).
• y-axis: cache-adjusted tokens per question (use per-question, k tokens — 41k–136k — so readers aren't parsing "M per 20 questions"). Do NOT use raw tokens; the post should stay consistent with cache-adjusted pricing (and the data file carries both).
• One point per leg, color by regime (three hues — reuse the setup colors from tpcds_cost_quality.svg where they overlap: database-only ≈ bare discovery, database-plus-schema ≈ injection, enriched Trilogy ≈ enriched).
• The dose structure is the visual argument: injection's four points (x4→x48) stack vertically at x≈7.5 — probes flat, tokens climbing — label the top point "600 tables". Bare's four points cluster tightly at x≈20, y≈45k. Enriched sits alone at (5.0, 104k). The three clusters ARE the three regimes; no trend lines needed.
• Draw the frontier as a light step line through the bare cluster and the enriched point (lower-left is better on x, lower is better on y — annotate "Pareto frontier"). Injection x48 sits visibly inside; annotate it: "full schema at 600 tables: dominated on tokens, probes, AND accuracy".
• Accuracy encoding: keep it out of position — annotate pass counts as small text labels (e.g. "20/20") rather than sizing points; a 16-vs-19 size difference is unreadable and the replicate noise (enriched swung 16↔20 on identical inputs) means size would overclaim. A footnote-level caveat under the chart: "accuracy differences of ≤4/20 are within replicate noise."
• Format: single theme-aware SVG matching the existing tpcds_*.svg treatment (the other TPC-DS charts are single SVGs, not the _dark/_light PNG pairs used elsewhere in the blog).

Secondary (optional, only if the section runs long-form): dose-flatness small multiples. Two panels sharing an x-axis of warehouse table count (42→600): left panel adjusted tokens, right panel probes per question; three series each (bare, injection, enriched-as-dashed-horizontal-reference). Story in one glance: the only line that moves anywhere is injection's token line. This is the better chart if the integrating agent wants to show the null result explicitly rather than assert it.

Skip: per-question probe distributions (in the data file as db_queries_per_question_list if wanted) — the medians are so separated (5 vs 8 vs 20) that a distribution adds ink, not information.

A concrete "translate the axis" sidebar (optional but punchy): a tiny table converting probe counts at, say, 2s/probe latency floor — discovery ≈ 40s of warehouse wall-clock per question vs enriched ≈ 10s — clearly labeled as illustrative arithmetic, not measurement.

Claims discipline (for whoever integrates)

Things this data does NOT support, which the section must not say:

• "The semantic layer saves tokens" — it costs ~2.3x a discovery agent's adjusted tokens. It saves tokens only vs full-schema injection at scale.
• "SQL agents get confused by messy warehouses" — we tried hard to confuse them and failed; say the opposite, it's more credible.
• "Enriched is more accurate" as a headline — 20/20 vs 18/20 at n=20 with a 16↔20 replicate swing is directional. Fine to state with the caveat attached.
• The 2026-08-11 legs in the data file have no measured cache fields (cache_adjusted_tokens: null) — plot only the 08-12 legs on the adjusted-token axis.
• Probe counts are the fair cross-regime unit, not probe wall-time: bare's probes skew to cheap information_schema pages, enriched's runs carry ~1.6s of client-side compile. The cloud-warehouse framing should lean on latency floors and scan billing, which hit both kinds.