PaperlayerView exact revision on arXiv

Source-linked AI summary

From Questions to Queries to Insights: A Schema-Grounded Agentic Workflow for Natural-Language Crash Data Analysis

Jingwen Zhu, Keshu Wu, Steven T. Parker, Bin Ran, David A. Noyce

arXiv:2610.35054v1cs.CL

TL;DR

Crash-database questions beyond predefined analyses often require technical specialists, and executable SQL can still compute the wrong measure. CrashDB-QA uses a locally deployed, schema-grounded workflow with deterministic controls and optional execution-informed verification. Across its evaluation, retrieval was generally more reliable than reasoning, while comparisons between groups or periods remained difficult.

  • Problem

    Questions beyond predefined crash-data analyses often require specialists to identify fields, interpret coded values, and write SQL, while executable queries can still compute the wrong measure.

    Source: §1 · p. 1, §2.1 · p. 3

  • Method

    CrashDB-QA uses specialized components for question interpretation, schema-grounded SQL generation, validation, read-only execution, and explanation, with optional question–query–result verification and repair.

    Source: §1 · p. 2, §5 · p. 18

  • Results

    Retrieval was generally more reliable than reasoning, and group- or period-comparison tasks remained difficult; Qwen-2.5-Coder 7B achieved 80.7% core accuracy at a median latency of 9.2 s.

    Source: §5 · p. 18

  • Takeaways & Limitations

    The evaluated workflow combines local, auditable crash-data analysis with domain grounding and deterministic controls, while its findings identify analytical composition and verification as continuing challenges.

    Source: §5 · p. 18

    Benchmark scores measure agreement with documented operational definitions, and a wrong query can coincidentally match the reference result on the single snapshot.

    Source: §3.4 · p. 10

Abstract

from arXiv · show

Transportation agencies maintain extensive crash databases to identify safety priorities, evaluate countermeasures, and guide resource allocation. Making these data accessible to practitioners requires flexible ways to translate analytical questions into reliable results. Existing dashboards support predefined analyses, while additional requests often depend on specialists who understand database queries, coded fields, and agency conventions. This study presents CrashDB-QA, a fully local, schema-grounded agentic workflow that enables natural-language analysis of crash databases. Specialized components interpret questions, generate and validate read-only SQL, execute queries, and explain the results. A shared knowledge base grounds query construction in the agency's data dictionary and safety-analysis conventions, while recorded intermediate outputs support inspection and auditing. An optional extension, CrashDB-QA+, checks the alignment among the question, query, and execution result and supplies evidence for iterative repair. We evaluate the workflow on a frozen snapshot of 4.4 million Wisconsin crash records using a new benchmark of 300 questions across ten analytical categories and nine local open-weight models. Domain grounding substantially improves query accuracy, and a code-specialized 7B model provides the best accuracy-latency balance among the tested models. Compositional analyses remain more difficult than routine retrieval, particularly comparisons between groups or periods. Verification improves performance when the Judge uses authoritative field definitions and the Refiner receives execution evidence, whereas additional checking alone offers no consistent benefit. These findings demonstrate the potential of locally deployed, domain-grounded workflows to broaden access to crash analysis and identify the reasoning and verification capabilities needed for reliable use.

1. Introduction

Crash databases can answer safety questions beyond predefined dashboards, but reliable analysis requires interpreting agency-specific fields and conventions, not just producing executable SQL. CrashDB-QA grounds a local, auditable workflow in that domain knowledge and evaluates its performance and verification on a new benchmark.

1. Introduction

  • Dashboards serve predefined analyses, but follow-up questions often require specialists to identify fields, interpret coded values, and write SQL.A prior statewide prototype demonstrated natural-language queries and chart generation, but its report did not systematically evaluate answer accuracy.Source: §1 · pp. 1–2
  • Executable SQL can still answer the wrong analytical question when it omits field meanings, coded values, or conventions that define the requested measure.Examples include counting crashes instead of severity-weighting them, grouping a seasonal analysis by month, or applying the same condition to both comparison periods.Source: §1 · p. 2
  • Reliable deployment requires documented agency knowledge and checks grounded in both field definitions and execution evidence, alongside local inference and read-only access.Recorded intermediate outputs make the question-to-answer process inspectable.Source: §1 · p. 2
  • Domain grounding substantially improves compact local models, while comparisons between groups or periods remain a persistent weakness.The evaluation also finds that verification gains depend on grounding the Judge and supplying execution evidence to the Refiner; additional checking alone does not.Source: §1 · p. 2
  • CrashDB-QA combines domain-grounded querying, deterministic database controls, result explanation, and optional execution-informed verification and repair.Its 300-question benchmark spans ten analytical categories and examines accuracy–latency tradeoffs, compositional difficulty, grounding, and iterative repair.Source: §1 · p. 2

2. The CrashDB-QA Framework

CrashDB-QA decomposes natural-language crash analysis into inspectable, controlled stages, grounding SQL in agency-specific schema knowledge and optionally checking question–query–result alignment.

2.1. Problem formulation

  • An admissible query may execute yet compute the wrong quantity, so the workflow targets question–query–result alignment under local, read-only access.Evaluation compares normalized result rows with an analyst-written reference query, allowing equivalent SQL formulations to match.Source: §2.1 · p. 3

2.2. Framework overview

  • Six specialized components separate intent and schema linking, SQL construction and control, execution, and explanation; CrashDB-QA+ adds judgment and refinement before explanation.Model tasks are orchestrated alongside deterministic retrieval, validation, execution, and feedback assembly.Source: §2.2 · p. 3, Figure 1 · p. 4

2.3. Governed-schema knowledge base

  • The shared knowledge base pairs column descriptions and stored formats with synonyms and explicit analytical conventions, including severity weights.Its entries cover all 169 columns, while curated word sets address 28 frequently queried columns.Source: §2.3 · pp. 4–5, Table 1 · p. 5

2.4. Question understanding

  • The Interpreter extracts metric, filters, grouping, period, and ranking, while deterministic schema linking uses documented terms and retrieves fields such as INJSVR for severity requests.The structured intent is separated from SQL so interpretation and field selection can be inspected before query generation.Source: §2.4 · p. 5

2.5. Query generation and execution

  • The Generator uses linked schema knowledge and conventions, while deterministic validation checks read-only access, schema identifiers, and dialect compatibility before execution.Multi-candidate selection groups passing queries by normalized results, but agreement is not a correctness guarantee.Source: §2.5 · pp. 6–7

2.6. Result verification and explanation

  • CrashDB-QA+ assesses analytical alignment using the question, query, and execution evidence, then returns identified issues to guide another attempt.The grounded Judge receives definitions for used columns and related alternatives; evidence-carrying refinement adds result evidence, column facts, repair patterns, and attempted-query history.Source: §2.6 · p. 7
  • Structural checks are heuristics, not proofs of error, because valid analyses can also return empty, zero-valued, or single-row results.The workflow evaluates these checks separately from the LLM Judge.Source: §2.6 · p. 7
  • The Explainer turns the selected result into a concise answer with key values and a relevant limitation, but does not independently establish query correctness.The severity-ranking example shows an admissible raw-count query being revised to a severity-weighted query that passes verification.Source: §2.6 · p. 8, Figure 2 · p. 8

3. The CrashDB-QA Benchmark

The benchmark tests whether natural-language crash-analysis requests can be translated into intended results under a documented schema, emphasizing coded-field interpretation and executable-but-wrong errors. It combines ten analytical categories with reviewed reference queries and execution-based grading on a frozen crash-data snapshot.

3. The CrashDB-QA Benchmark

  • The benchmark evaluates the path from natural-language requests to computed answers, including interpretation of coded fields, domain conventions, and executable-but-wrong queries.Each question is paired with an analyst-reviewed reference query and execution result on a frozen database snapshot.Source: §3 · p. 8

3.1. Analytical taxonomy

  • Categories C1–C5 cover retrieval and aggregation, while C6–C10 require composition of conditions, groupings, or derived quantities.The taxonomy distinguishes analytical structure rather than SQL complexity; both tiers still depend on correct domain interpretation.Source: §3.1 · p. 8, Figure 3 · p. 9

3.2. Question construction and development separation

  • The 300 benchmark questions combine anonymized operational inquiries with expert-authored cases and allocate 30 questions to each category.The balanced design supports category comparisons but does not estimate task frequency in routine agency use.Source: §3.2 · pp. 9–10

3.3. Reference construction and quality control

  • Reference queries underwent drafting, independent review, adjudication when results differed, execution checks, and audits against raw records and published statistics.Execution testing corrected nine draft references that assumed contributing-factor flags used “N,” which was absent from the relevant fields.Source: §3.3 · p. 10

3.4. Execution-based grading

  • Execution match grades normalized result row multisets against references, accepting equivalent SQL while not assessing presentation order or Explainer prose.Scores measure agreement with documented operational definitions on a fixed snapshot; incorrect queries may coincidentally match, and multi-instance testing could provide stronger semantic-equivalence evidence.Source: §3.4 · p. 10

4. Results and Discussion

The evaluation compares the grounded workflow across nine local models, analyzes accuracy, latency, and errors, and tests whether verification and refinement improve results. Stronger models handle routine retrieval more reliably than compositional comparisons, while verification helps when grounded in field definitions and execution evidence.

4.1. Experimental setup

  • The evaluation uses a frozen 4.4-million-record crash-data snapshot, 300 questions, and nine open-weight models under shared prompts, grounding resources, reference answers, and database configuration.Models are evaluated locally; the initial comparison uses the grounded core without semantic verification.Source: §4.1 · pp. 10–11, §4 · p. 10

4.2. Accuracy across models

  • 80.7% overall accuracy: Qwen-2.5-Coder 7B leads the nine-model comparison, and reasoning accuracy trails retrieval by 13–25 percentage points among seven models scoring at least 43% overall.It reaches 87.3% on retrieval and 74.0% on reasoning; no model exceeds 43.3% on C9 comparisons.Source: §4.2 · p. 11, Figure 4 · p. 12, Figure 5 · p. 12

4.3. Latency and cost

  • 9.2 s median end-to-end latency: Qwen-2.5-Coder 7B is the only evaluated model that is both faster and more accurate than every alternative.DeepSeek-Coder 6.7B is about 1.4 s faster but scores 12.4 percentage points lower; these timings describe the evaluated local configuration, not hardware-independent performance.Source: §4.3 · p. 13, Figure 6 · p. 13, Figure 7 · p. 14

4.4. Error analysis

  • Compositional errors dominate stronger models’ remaining mistakes, including missing groupings, truncated trends, misgrouped conditions, and reused predicates in comparisons.Some domain-grounding errors also execute with empty or zero results, while other requests require external data such as population denominators.Source: §4.4 · pp. 13–14

4.5. Case studies

  • The seasonal case shows that a query can select the right records yet answer with monthly rather than seasonal counts, while its explanation adds incorrect totals and minimum claims.By contrast, the severity-weighted county example matches the reference query and result, though its explanation’s caveat is generic.Source: §4.5 · pp. 14–15

4.6. Verification loop

  • The original Judge accepts 60.0% of incorrect executable outputs, and pre-registered verification configurations do not significantly improve core accuracy.Combining an ungrounded Judge, rule checks, and five-candidate voting lowers accuracy to 75.0%; the grounded Judge modestly improves detection, while the evidence-linked checklist performs worse.Source: §4.6 · p. 16, Figure 8 · p. 16, Table 5 · p. 16
  • Grounding the Judge and giving the Refiner execution evidence raise final accuracy to 83.0% and 83.7%, respectively, versus the 80.7% core; these gains are significant against the core, not necessarily against each other.The evidence-carrying configuration raises median latency from 9.2 s to 13.1 s, repairs ten questions, and repairs no C9 comparisons; its same-benchmark diagnostic gains need independent confirmation.Source: §4.6 · pp. 17–18, Figure 9 · p. 17, Table 6 · p. 17

5. Conclusion

CrashDB-QA links natural-language safety questions to auditable database queries and explanations, with an optional execution-informed verification loop. Across nine local models, retrieval was more reliable than analytical composition, and verification improved accuracy only when grounded in field definitions and execution evidence.

5. Conclusion

  • CrashDB-QA combines specialized language-model tasks, deterministic schema validation and read-only execution, and traceable intermediate outputs; CrashDB-QA+ adds verification and repair using field definitions and execution evidence.The workflow was assessed with a 300-question benchmark across ten analytical categories.Source: §5 · p. 18
  • 80.7% core accuracy for Qwen-2.5-Coder 7B provided the best evaluated accuracy–latency balance; grounded judging plus execution evidence increased accuracy to 83.7%.The respective median latencies were 9.2 s and 13.1 s.Source: §5 · p. 18
  • Retrieval tasks were generally more reliable than reasoning tasks, while group or period comparisons remained difficult across all models.Original verification configurations offered no significant improvement, and combining voting with verification reduced accuracy.Source: §5 · p. 18
  • Linking requests to documented definitions, generated SQL, and returned values makes answers reviewable and helps agencies distinguish suitable uses from cases requiring expert review.Strong performance on counts and summaries does not establish competence in comparisons or derived measures.Source: §5 · p. 18
  • The findings are bounded by one statewide schema and frozen snapshot; verification improvements were tested on the same benchmark using one model, so independent confirmation is needed.Execution agreement also cannot establish semantic equivalence across all possible database instances.Source: §5 · p. 18

Related papers

Loading 2610.35054v1…