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):

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 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). 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:

{"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

# 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