Skip to main content

Digging the Pit Of Success for TPC-DS

Digging the Pit Of Success for TPC-DS

In a companion post we argued that building tools for agents has actually simplified UX design for infra - you can force agents to run repeated reps in a way that would be inhumane for your testers, they read the context you give them, they have reproducible context. This makes UX an optimization problem just like performance, reliability, etc - though at the cost of tokens.

In that post we also laid out what we focused on for optimization (success rate, wall clock, expense) and the three levers to pull to improve those (model selection, harness, tool performance).

This post is different - it's about a bit of hubris and our shame.

Trilogy was supposed to be good for agents - but when we measured it, it was strictly worse than raw SQL. Here's how we Trilogy from a 25% agentic success rate to 80% while cutting tokens and - hopefully - making it better for people too - and what TPC-DS had to do with both the progress and the pain.

First, some context

Trilogy is SQL without the tables: you write SQL against a metadata layer that late-binds to physical tables to fulfill your query. This has a lot of nice properties, most of which seem tailor-made for agents:

  • Rich context by default, with tools for progressive disclosure.
  • No fan-out and chasm traps.
  • Automatic aggregate resolution.
  • Strong typing and linting for fast feedback.
  • All the power and expressiveness of SQL - which agents know like the back of their hand.

It's a no-brainer that it's better than raw SQL for agents, right? Everyone else selling a semantic layer agreed with us on this; our implementation was obviously better; we just had to prove it.

Aside

Trilogy has existed since before agents were a thing. It turns out many of the properties that make it better for agents were ALSO things we thought would make SQL better for humans - and humans are still the goal!

So we set up a scrappy test harness, ran our first round of evals, and got a resounding... 25% success rate. Give the same harness raw SQL access and no Trilogy, and it scored 50%. More tokens, worse output, more time - exactly the opposite of our naive expectations.

What went wrong? To answer that, we had to follow the steps - find our agent proxy; fix our harness, and last make the tool work.

Lever 1: Model selection

The temptation is to reach for the smartest model available, because that makes your tool look great. It's bad for optimization, though - a smarter model papers over harness and tool deficiencies and in the process hides the signal you need. If the agent silently recovers from a bad error message, you never learn the error message was bad. You want testers just barely smart enough to succeed.

So we test on the model that can just do the simplest version of the task ~99% of the time. Smart enough not to fight us on the basics but not so smart it rescues us from our own sharp edges. Agent-level recovery from deficiencies is exactly what we're trying to measure and remove, not benefit from.

Tips

Historical aside: we're blessed to be past the days of fighting agents to output properly formatted JSON for tool calls. Basics covers a lot more than it used to, which means you can focus on higher level optimization.

Lever 2: The harness

The harness is just the prompt, the toolset, and the lifecycle loop. Ours had to do three things:

  • Expose the minimal sufficient set of tools. Every extra tool is another way for the agent to branch. If a problem can be solved multiple ways, the agent will try multiple ways over multiple iterations, and your optimization signal is volatile as a result. We focused on one golden path with tight iterative loops over the minimal toolset (pruned over time as we proved that less tools didn't hurt performance). Less is more, listen to Marie Kondo.
  • Compel the agent forward. We give the agent an explicit tool to call when it's "done." Any response that isn't that call gets a nudge from the harness to continue. This was really critical with earlier agents; the most recent ones have less of a problem with workgin autonomously.
  • Protect the agent. Avoid context overload and huge text dumps, filter out irrelevant info, and handle retries and backoff so the agent never has to.

If you're feeling fancy, this is where multi-agent patterns live - dedicated sub-agent flows for context-hungry tasks, a review step, secondary agents, fan out, all that jazz. We generally prefer to fix the root cause upstream: shrink the context a task needs and make the tools push toward correct solutions, rather than bolting on a second agent to verify the first. Every probabilistic step you add is another place for you to run into frustration and volatility.

Your mileage may vary; there are genuine cases for fan-out workflows, and when you hit them you'll want real harness separation between coordinator and worker agents. Come back here after you've gone full Gastown; it's fun out there but you can get lost in optimizing the machine not the results.

Lever 3: Tool performance

For tools, the whole game is context management: give the agent just enough to get started, then feed it the right correction in the most targeted dose possible. The major levers:

  • Better defaults.
  • Error messages that suggest the next step.
  • Progressive disclosure.
  • Making sure the information needed to progress exists somewhere.
  • Agent-friendly formatting.

Agent self-evals are a trap. (LLM as a judge is... scary). It's a fine place to start, but:

  • Agents massively overindex on the case in front of them. Let one tune a tool and you get a mishmash of hyper-specialized guidance bolted on for one query it just saw.
  • They are poor diagnosticians. Agents can do reasonable trajectory analysis under close supervision, but accepting their default root-cause analysis without manually confirming is a recipe for sadness. They tend to speculate withotu confirming.
  • They are not objective oracles Claude, for instance, tends to assume a task failure means the model underneath is bad (it was quite sure DeepSeek was the problem, as an inferior model), rather than investigating what ended up being very clear framework issues.

So why did we score so low?

With the levers in hand, the diagnosis came into focus: agents do a random walk through your tools, and we didn't have a pit of success. Let's abuse the metaphor:

  • Unclear path to the pit We gave agents no concrete examples of complex syntax, so they were unlikely to luck into it whenever it varied even slightly from SQL.
  • Shallow walls When an agent hit an error, the message didn't reliably guide it toward a fix - so it would misdiagnose the problem and spiral.
  • Lots of spikes We had an enormous surface area that had never been pressure-tested against the ways a query can be mis-written. It's hard to imagine every wrong path when you already know the language. Agents were tremendously good at finding the unexpected sharp corners - and genuine bugs.
  • Bad context from humans With Trilogy we ship a semantic model to the agent and discourage it from poking at the raw database. So when the model had bad context, the agent made bad choices. Our auto-generated semantic model was initially better than our hand-curated one - humans had simply been lazy. Patching up the hand-curated model closed that gap and was a key to finally beating raw SQL.

And then there was the test itself

The last culprit was the benchmark. Our evals were built on the TPC-DS suite - the same one we use to verify the language generates correct, performant SQL. It's a fantastic tool for that: a complex snowflake schema with reference outputs, and comes with an extension we can use to validate.

But TPC-DS is, in many ways, deeply artificial. Some queries have row duplication, weird joins, and cryptic intent (and we were trying to reverse-engineer benchmark questions). Agents are surprisingly triped up by this - they can spend lot of tokens probing to understand why it's getting odd results - because it assumes the oddity is its own fault. The benchmark that was perfect for stress-testing the language isn't a perfect for the actual cases where we think we'd do better than raw SQL.

The numbers

Netted out - fixing questions, raising the scale factor of TPC-DS, and tuning questions so all had valid results returned: This lifted raw SQL generation to 80%, schema supported raw sql to 85-90. Trilogy lagged with hand-curated schema + ingest at around ~70% and ~50% respectively.

Fixing the language, syntax, edge cases, lints got us to ~90% and ~80% - consistent wins of the equivalent raw SQL. That's the baseline bar we want - we expect to pull further ahead with messier schema, more tables, caching, etc - but if we can't compete on the basics, what's the point?

Lessons

Any measure is a proxy for your end user experience; optimizing for the wrong measure will optimize for the wrong uers. We're not mad that we started with tpc-ds - it taught us a lot - but probably wouldn't be where I'd start with agentic evals.

Tips

We're working on additional benchmarks that better represent a messy warehouse. Hex has some fantastic descriptions of how they needed to create a synthetic benchmark.

Some Concrete Examples

New Syntax

Trilogy isn't SQL. Agents, even with examples, tend to fall back to familiar paths. We have two methods to coerce them:

  • Better Errors
  • Better Language

Better Errors

Errors need to point to how to fix the solution, if the agent doesn't know it. Including direct syntax suggestions, links to CLIs to help, etc are good. Ensure that errors are returned together as a unit so multiple rounds aren't required to discover them is very useful. Broadly, we want all syntax errors to return together; then all semantic errors; then all execution errors. (hopefully the latter doesn't happen!).

Better Language

Add missing features. When an agent wants something that should exist, give it to the agent. This is frustrating and a taste line - don't blindly implement what they reach for, but sometimes it is a genuine gap. (For us, agents pushed for some major simplification and streamlining of join syntax, which we're quite happy with the results of).

Relax constraints. When an agent reaches for not what you want but a reasonable alternative syntax, we can consider extending grammar to handle it. We're pretty torn on this one; but "friendly SQL" is winning out so as long as the language flexibility doesn't compromise precision.

A fun specific one there was namespace referencing for CTEs. Trilogy supports nested access - order.customer.first_name and namespacing a CTE, producing cte.order.customer.first_name. For non transformed columns, agents would default to cte.first_name instead of the full path. When told to do the full path, they'd say "that's too much work" and try a different path. So we allowed resolution of cte.first_name when it was unambigious.

This turned out to be a lot nicer to write - so long and thanks for the laziness, Claude.