docs/dab_failure_analysis.md

# DataAgentBench failure analysis — runs of 2026-06-05

Trajectory analysis of full `--all` sweeps, scored with DAB's own
per-task `validate.py`. Captured with `--log-dir` (full per-turn prompts,
raw LLM responses, tool args, and untruncated results); diagnoses below
cite those traces.

## Run config

```
python -m benchmark.dab.runner --all --llm lexsi --log-dir artifacts/dab_runs/new_tool
```
- 5 of 17 datasets runnable (sqlite/duckdb); 12 skipped (postgres/mongo).
- **Score: 10/17 tasks passed** — `consolidate_tables` (fix B) recovered
  stockmarket/query4 + query5 over the pre-tool baseline of 8/17 (both
  confirmed using the tool; see §B).

> **Measurement note.** Single-run pass@1 carries per-task sampling
> noise — any system-prompt change re-rolls multi-step trajectories, so a
> few tasks can flip run-to-run. Use `--iterations 3` (majority vote) to
> pin a stable number before trusting a future delta.

## Results

✅ = pass, ❌ = fail, 🎯 = recovered by `consolidate_tables`.

| Dataset | Query | Result | Validator reason / note |
|---|---|---|---|
| DEPS_DEV_V1 | query1 | ❌ | Missing name `@dmrvos/infrajs>0.0.6>typescript` (§C/§D) |
| DEPS_DEV_V1 | query2 | ❌ | Missing project `mui-org/material-ui` (§D) |
| GITHUB_REPOS | query1 | ❌ | No value rounds to 0.33 — wrong join (§A note) |
| GITHUB_REPOS | query2 | ✅ | Fuzzy matched `swiftandroid/swift` |
| GITHUB_REPOS | query3 | ✅ | Found 1077 |
| GITHUB_REPOS | query4 | ✅ | All repo names matched |
| music_brainz_20k | query1 | ✅ | Ground truth found |
| music_brainz_20k | query2 | ✅ | Ground truth found |
| music_brainz_20k | query3 | ❌ | Wrong song (`Zo gaat het leven…`) (§D) |
| stockindex | query1 | ✅ | `399001.SZ` primary |
| stockindex | query2 | ❌ | `IXIC` not in first 200 chars (§C) |
| stockindex | query3 | ✅ | All name-country pairs matched |
| stockmarket | query1 | ✅ | 18.44 ≈ 18.44 |
| stockmarket | query2 | ❌ | Missing number 31 — consolidated, but count SQL still wrong (§B) |
| stockmarket | query3 | ❌ | `Apex Global Brands Inc` — consolidated, but avg-volume SQL still wrong (§B) |
| stockmarket | query4 | 🎯✅ | `MFA Financial, Inc` found — **recovered via `consolidate_tables`** |
| stockmarket | query5 | 🎯✅ | `Synthesis Energy Systems, Inc` found — **recovered via `consolidate_tables`** |

**Total: 10/17** — 8 baseline passers + 2 🎯 recoveries via `consolidate_tables`.

---

## Failure taxonomy

### A. Harness bug — history truncates tool-call args, agent thinks its SQL is broken (1 task, systemic)

**GITHUB_REPOS/query1.** The agent wrote a complete 960-char query and
`run_sql` **executed it fine** (returned `1 row × 3 cols`). But the loop
echoes each tool call into the observation history truncated to 400
chars ([loop.py:236](../lexsi_ds/agent/loop.py#L236)):

```python
f"TOOL CALL: {tool_name}({json.dumps(args_obj, default=str)[:400]})\n"
```

The 400-char cut lands mid-`JOIN`, and the appended `)` makes the echo
read as a query that ends `…JOIN artifacts_database_contents c ON c)` —
i.e. **truncated mid-join**. The agent re-read this, concluded the
tooling was broken, re-ran the same query 10 times, and gave up:

> "every `run_sql` execution shown is truncated mid-join
> (`JOIN artifacts_database_contents c ON c)`), which causes the query
> to effectively fail" — *agent's final answer*

So a runnable task was lost to a display artifact, not a real error.
(The SQL logic was also likely wrong — proportion ≠ 0.33 — but the agent
never got far enough to find out.)

**Fix (done).** [loop.py](../lexsi_ds/agent/loop.py) now echoes args via
`_echo_args()`: full up to an 8000-char cap, and when it must truncate
it appends an explicit `… [+N chars truncated for display only; the full
args above were executed]` marker — so a display cut can never read as a
broken statement. The 960-char query that triggered this is now echoed
in full.

### B. Scale — per-shard table explosion vs the 12-step budget (4 tasks) — FIXED via `consolidate_tables`

**The problem.** stockmarket materializes **2753 `stocktrade_database_<ticker>`
tables** (one per symbol, identical schema `Date,Open,High,Low,Close,
Adj Close,Volume`, ~410 rows each) plus one `stockinfo` table. The shard
key (ticker) lives in the **table name, not a column**. A question like
"across all NYSE stocks…" is a `GROUP BY ticker` that can only be
expressed as a 2753-way `UNION ALL` — unwriteable in a 12-step budget.
All four tasks hit step 13 and surrendered:

> "the query requires scanning/UNIONing thousands of
> `stocktrade_database_<ticker>` tables, and I ran out of allowed tool
> calls" — *query3, baseline*

**The fix (done): a generic `consolidate_tables` tool**
([consolidate_tables.py](../lexsi_ds/agent/tools/consolidate_tables.py)).
It folds a sharded family into one table in a single call — the giant
`UNION ALL BY NAME` is generated in our code, never against the agent's
step budget:

```json
{"tool": "consolidate_tables",
 "args": {"pattern": "stocktrade_database_*", "into": "stocktrade", "key_column": "ticker"}}
```

It auto-derives the key from the pattern's literal prefix (→ bare
tickers), schema-guards (unions only same-schema shards, reports the
rest), and defaults to a cheap VIEW. Generic by design: per-ticker,
per-day, per-region — any BYO datalake with sharded tables. Planner
rule 14 tells the agent to reach for it when `inspect_data` shows many
same-prefix tables.

**Measured:** 2753 tables → one 6.47M-row view in **1.2s**; the
previously-timed-out cross-ticker query runs in **~1s**. On the
`new_tool/` sweep the agent called it in all 4 stockmarket tasks and
**recovered query4 + query5** (the name-lookup tasks). query2 (count off
by the "31") and query3 (avg-volume per company) still fail: the tool
removes the *structural* blocker, but the agent must still write correct
analytical SQL on top — which it nailed for q4/q5 and missed for q2/q3
(the latter compounded by §E coded values).

### C. Output format / "primary answer" expectations (2 tasks)

- **stockindex/query2** — the answer was **correct** (`IXIC`: 44 up / 31
  down) but the validator requires the target index in the first 200
  chars as the *primary* answer; the agent buried it after a prose
  preamble and a markdown table, and added a second index (`GSPTSE`).
  Penalized on presentation, not correctness.
- **DEPS_DEV_V1/query1** — the agent's query *did* surface the
  composite-key rows (`@dmrvos/infrajs>0.0.5>typescript`) but (a) chose
  version **0.0.5**, not the ground-truth **0.0.6** ("latest release"
  logic wrong — see §D), and (b) hedged/refused because the values
  "look like dependency paths rather than just the package name".

**Fix:** a benchmark-mode answer directive — lead with the bare
answer/identifier in the exact requested format, then optional detail.

### D. Wrong query semantics → confidently wrong values (2 tasks)

- **DEPS_DEV_V1/query2** — returned `react-component/picker`, `tailwindcss/typography`, …;
  ground truth is `mui-org/material-ui`. The "marked as release" filter
  + fork-count join produced a different top-5.
- **music_brainz_20k/query3** — answered `"Groovey" by Rich Matteson,
  $4,128.59` in a single `run_sql`; ground truth is
  `"Zo gaat het leven aan je voor"`. Revenue was almost certainly
  aggregated by the wrong entity (track title vs. recording, or not
  summed across stores). The agent was overconfident — 4 steps, one
  query, no cross-check.

**Fix:** these need the DAB `db_description` field-semantics to be
weighted more (e.g. how "latest release" / "revenue" are defined), and a
self-check step before finalizing on aggregation questions.

### E. Coded-value / data-semantics gaps (compounds B)

**stockmarket/query5, query3** — the agent filtered on
`Listing Exchange='Q' AND Market Category='QCM'`, got 0 rows, and
concluded the data didn't match. `Listing Exchange`/`Market Category`/
`Financial Status` are **coded** columns whose codes differ from the
agent's assumptions. It spent steps probing codes instead of answering,
which (with §B) guaranteed a timeout.

---

## Priorities

| Fix | Status | Impact |
|---|---|---|
| **A.** `_echo_args()` no longer cuts SQL mid-string | ✅ done | Removes false "truncated SQL" failures; helps any long-SQL task |
| **B.** `consolidate_tables` folds sharded families into one table | ✅ done | **+2** (stockmarket q4, q5); generic for any sharded datalake |
| **D/E.** Lean on `db_description` semantics + a pre-final self-check | open | DEPS/query2, music_brainz/query3, stockmarket q2/q3 SQL logic |
| **C.** Benchmark answer-format directive (lead with bare answer) | open | stockindex/query2 (+ partial DEPS/query1) |

**Net effect of A + B: 10/17.** A is a correctness floor (no more false
failures); B is the +2 (stockmarket q4, q5). The remaining levers are
**D/E** (analytical-SQL / coded-value semantics — the dominant failure
mode now that the structural blockers are gone) and **C** (output
format).

**Measure with `--iterations`.** Single-run pass@1 is noisy — a prompt
change re-rolls every multi-step task. Run `--iterations 3` (majority
vote) before trusting a future delta.

## Reproduce / inspect

```bash
# Noise-adjusted measurement run (majority vote per task):
python -m benchmark.dab.runner --all --llm lexsi --iterations 3 \
    --log-dir artifacts/dab_runs/measure_v1

# Inspect one trajectory end-to-end (prompts, raw responses, SQL, payloads):
python -c "import json; d=json.load(open('artifacts/dab_runs/new_tool/stockmarket_query4_iter0.json')); \
print(d['reason']); [print(s['n'], s['tool'], (s['args'] or {}).get('sql','')[:80]) for s in d['run']['steps']]"

# Confirm the new tool fired on the stockmarket tasks:
grep -l consolidate_tables artifacts/dab_runs/new_tool/stockmarket_*.json
```