On-Premise, RAG-Grounded Natural-Language-to-SQL for Regulated Batch Manufacturing

1. Introduction

Pharmaceutical and other regulated manufacturers increasingly depend on data-driven decision-making [1,2], yet extracting timely insight from the large, complex process data generated on the manufacturing floor remains difficult. A single tablet-compression line, for instance, produces high-frequency sensor time series alongside raw-material attributes, in-process controls, and laboratory quality results for every batch [3]. Traditional analytics workflows require specialists to translate operational questions into SQL queries that span many interdependent tables, debug their syntax, and validate the results manually. Natural-language interfaces to databases have long been proposed to remove this barrier [4]. Recent shop-floor studies confirm that limited digital literacy and rigid interfaces still prevent operators from querying manufacturing data without external technical support [5]. This manual path can take hours or days, creates a dependency on a small pool of SQL-proficient staff, and delays the interventions that prevent costly batch failures and compliance issues.

Large language models (LLMs) could shorten this path. They are already being adapted to manufacturing tasks, from domain-specific fine-tuning [6] to conversational machine monitoring over real-time shop-floor data [5]. Cloud-hosted natural-language and LLM services, however, conflict with the data-sovereignty, real-time, and compliance requirements of regulated production. Sending prompts that contain proprietary process records to third-party providers exposes them to confidentiality and data-leakage risks that are well documented in the LLM security literature [7]. Standards such as ISA-88 for batch control emphasize structured data, traceable process flows, and validated reporting [8,9]. Generative models, by contrast, introduce the risk of hallucinations: outputs that are plausible but factually incorrect or unfaithful to their source [10,11]. For organizations operating under good manufacturing practice, sending batch data to external providers is frequently not permissible. Any model output that informs a regulated decision must also be traceable and verifiable, consistent with the ALCOA+ data-integrity principles and computerized-system requirements enforced by regulators [12,13,14]. Regulators have recently made this position explicit for generative AI. The draft EU GMP Annex 22 excludes LLMs from critical GMP applications and confines them to non-critical uses in which qualified personnel remain responsible for the suitability of the output [15].

These constraints motivate a locally deployed alternative. A schema-aware LLM can convert natural-language questions into SQL within client-controlled infrastructure if it is grounded in validated documentation through retrieval-augmented generation (RAG) [16] and constrained by a human-in-the-loop review step, in line with the human-centric orientation of Industry 5.0 [17]. We are careful not to overstate what such a system can achieve. On realistic, multi-table schemas, text-to-SQL is far from solved [18,19]. On the enterprise-grade Spider 2.0 benchmark, an agentic framework built on a frontier reasoning model solved only 21.3% of tasks, against 91.2% on the earlier Spider 1.0 [20], and locally hostable models trail cloud frontier systems on the hardest queries. The contribution of this work is therefore not state-of-the-art accuracy. It is a demonstration that a deliberately lightweight, auditable pipeline is feasible, usable, and reliable enough to be valuable in a setting where the realistic alternative is no AI assistance at all.

Our contributions are fourfold. First, the approach prioritizes local deployment and data sovereignty: the LLM, vector store, and database all run within client-controlled infrastructure, removing cloud dependencies and keeping sensitive manufacturing data inside the organization's controlled environment. Second, it employs a schema-aware, RAG-grounded pipeline that retrieves ISA-88 documentation, schema metadata, and validated query examples from a local vector database before generation, improving grounding for complex cross-table questions. Third, it incorporates reliability safeguards: per-query confidence scoring, execution-based fidelity checking against ground-truth results, and a human-in-the-loop review step. Together, these keep outputs advisory and auditable rather than prescriptive, as current regulatory guidance expects for generative models [15]. Finally, it provides an empirical evaluation under realistic constraints. A tiered 48-question benchmark over the ISA-88 schema compares three context configurations (schema-only, controlled vocabularies, and retrieved validated examples) across two locally hostable open-weight models. The evaluation also covers execution-guided self-consistency, an adaptation of self-consistency decoding [21] to SQL generation, and a schema-specialized fine-tuned model, establishing which interventions improve accuracy and which do not.

The remainder of the paper is organized as follows. Section 2 reviews related work across four dimensions and identifies the gap addressed here. Section 3 describes the system design and implementation. Section 4 defines the evaluation methodology and Section 5 reports the results. Section 6 discusses the findings against the literature, and Section 7 and Section 8 present limitations, future work, and conclusions.

2. Background and Related Work

Large language models have opened new opportunities for data access, analysis and decision support in industrial environments. In regulated batch manufacturing, where structured data, compliance and data integrity are critical, LLMs offer a way to bridge natural-language questions and complex operational datasets. This section reviews LLM-based interfaces, hallucination mitigation, retrieval-augmented generation, and SQL generation, and situates these advances within ISA-88 structured manufacturing systems.

2.1. Key Dimensions of LLM-Based Industrial and Pharmaceutical Analytics

2.1.1. LLMs as Natural-Language Interfaces

Recent work positions LLMs as interaction layers that translate unstructured user intent into structured analytical operations rather than as replacements for existing pipelines. In pharmaceutical settings, Salami et al. [22] show that foundational models can support deviation investigations by extracting root causes and comparing similar events through semantic embeddings, reducing analyst effort while leaving some reliability concerns. Beyond deviations, Kadi et al. [23] combine LLMs with RAG to extract IDMP-aligned fields from SmPC documents at high F1, and Kim et al. [24] answer questions over FDA and ICH guidance with a dual-track retrieval framework. Vieira-Vieira et al. [25] introduce PRINCE, a multi-agent engine that unifies preclinical safety data through LLMs, RAG and text-to-SQL, while Taskiran et al. [26] integrate ontologies with LLM reasoning in a drug-discovery chatbot. Industrial neighbours extend the same idea to the plant floor: an LLM-enabled agent architecture provides real-time decision support over batch-process sensor streams in a clean-in-place case study [27], and a knowledge-graph-grounded interface lets manufacturing planners query simulation output in natural language while preserving human oversight [28]. Collectively, these studies show a clear trend toward LLMs as conversational front-ends, but most target unstructured text, sensor streams, or preclinical data rather than structured relational batch-manufacturing databases. Table 1 summarizes the main contributions.

2.1.2. Hallucinations, RAG, and Hybrid Architectures

A dominant concern in regulated applications is the risk of hallucinated or unverifiable output. The literature responds by embedding LLMs in retrieval-augmented and hybrid architectures that ground generation in external knowledge. Salami et al. [22] analyze how hallucinations can mislead deviation investigations when models infer missing detail, and Kadi et al. [23] motivate RAG and careful prompting to constrain extraction. Fan et al. [33] survey retrieval-augmented LLMs, showing that retrieval and augmentation design strongly affect reliability and that high-quality, well-aligned evidence substantially reduces hallucination. Domain-specific work extends this: ClinicalRAG cross-checks labelling statements against clinical tables through hierarchical RAG [34]; QA-RAG retrieves with both the user query and a generated hypothetical answer [24]; and Wan et al. [29] combine knowledge graphs with dense retrieval for manufacturing Q&A, while Choi and Jeong [35] propose frameworks for keeping RAG knowledge current. These works, however, target document-level Q&A and summarization rather than executable SQL over structured production data. Table 2 summarizes the contributions.

2.1.3. Text-to-SQL, ISA-88, and Structured Batch Data

Text-to-SQL is the natural bridge between natural-language interfaces and structured manufacturing databases. Surveys trace the shift from rule-based parsers to neural and LLM-based approaches that use schema-aware prompting and RAG [19,36,37], identifying schema linking, metadata retrieval and hallucination control as critical open challenges in complex, domain-limited deployments. Early rule-based interfaces [32] demonstrated feasibility but lacked flexibility, and large benchmarks such as BIRD [18] expose how hard realistic SQL grounding is, with even strong models achieving only modest execution accuracy on noisy, multi-table databases owing to incorrect joins, value-type errors and missing domain knowledge.

It is important to situate the present work against the current frontier, which has moved well beyond single-shot prompting. On the enterprise-grade Spider 2.0 benchmark, accuracy collapses relative to the older Spider 1.0, and leading systems are multi-call agentic pipelines: ReFoRCE [38] tops the leaderboard through systematic schema exploration and self-correction, Alpha-SQL [39] uses Monte Carlo Tree Search for zero-shot generation, and autonomous schema-linking methods address very large schemas. These systems optimize benchmark accuracy at the cost of many model and database calls per question, non-determinism, and limited auditability. In parallel, small, locally hostable models have become viable: a 7B code-specialized model with lightweight query pre-synthesis [40] reaches competitive execution accuracy, although self-hosted models still trail cloud frontier systems on the hardest queries. Recent analyses also caution that text-to-SQL benchmark annotations themselves contain substantial errors, which affects how leaderboard numbers should be interpreted. The design adopted here deliberately trades agentic accuracy for determinism and auditability, and uses a code-specialized small model as a comparator to reflect current local-model practice.

Manufacturing data add further complexity. Virta et al. [9] describe how ISA-88 and ISA-95 models are implemented through OPC UA and service-oriented architectures, emphasizing that batch records are deeply hierarchical and semantically interdependent across unit procedures, phases and equipment modules; Murali and Anu Priya [41] show that pharmaceutical process data exhibit multivariate, nonlinear and time-varying behavior. Vichev and Marchev [42] demonstrate that embedding models differ widely in retrieving correct schema components and that retrieval errors cascade into malformed SQL, reinforcing the need for schema-aware prompting and dynamic retrieval. PRINCE [25] shows SQL-generating pipelines can operate in regulated R&D contexts, but on preclinical rather than ISA-88 batch data. No prior LLM-based system has targeted ISA-88-structured batch-manufacturing records, and existing text-to-SQL work relies on generic benchmarks that do not reflect regulated batch environments. Table 3 summarizes the contributions.

2.1.4. AI in Regulated Environments

Regulated manufacturing stresses industrial LLM systems with stringent demands for traceability, accuracy and compliance, and the literature shows a shift from generative convenience toward verification-centric, compliance-aware architectures. Zhou et al. [34] demonstrate safe label quality control through hierarchical RAG with explicit evidence checking; Kim et al. [24] address regulatory change through enhanced retrieval; and Kadi et al. [23] convert SmPC documents into IDMP-compliant data with expert-in-the-loop validation. From an architectural standpoint, Dogruluk and Acikgoz [43] survey edge-centric federated learning for industrial LLMs, arguing that privacy-preserving, low-latency intelligence should run at the industrial edge and noting compliance with standards such as IEC 62443 and GDPR. Kavasidis et al. [14] predict ALCOA+ data-integrity compliance from heterogeneous plant data, reinforcing that pharma AI must respect data-integrity principles and remain auditable. The local system developed here aligns with these trends by running an on-premise, containerized LLM that performs NL-to-SQL translation inside the plant network. Table 4 summarizes the contributions.

2.2. Summary and Identification of the Gap

Three research streams converge on this problem but none addresses it directly. First, text-to-SQL has advanced rapidly, yet recent benchmarks show the task is far from solved on realistic schemas: leading systems reach only modest accuracy on enterprise-grade Spider 2.0, and the gains come from heavyweight, multi-call agentic pipelines optimized for benchmark accuracy rather than for auditability or local deployment. Second, LLM-based natural-language interfaces for manufacturing are now an active area, including agent-based decision support for batch processes and knowledge-graph-grounded interfaces for production planning, but these target document Q&A, sensor and SCADA streams, or simulation output, not text-to-SQL over the hierarchical relational records defined by ISA-88. Third, privacy-preserving and on-premise LLM deployment is increasingly studied for industrial settings, yet existing demonstrations are not evaluated against the validation, traceability and usability requirements of regulated manufacturing. No prior work, to our knowledge, combines all three: a locally deployed, schema-aware, RAG-grounded text-to-SQL system over an ISA-88-structured batch database, evaluated for SQL-level fidelity and for usability with non-programmer plant personnel under regulated-environment constraints.

3. System Design and Implementation

This section describes the architecture and approach used to design and implement a localized AI-driven analytical system for pharmaceutical manufacturing and quality contexts. It explains how the components translate natural-language questions into executable SQL over an ISA-88-aligned batch database, in a form suitable for regulated manufacturing. All components run on a local server under client control, preserving data sovereignty.

The architecture is intentionally lightweight rather than maximally accurate. State-of-the-art text-to-SQL systems achieve their results through multi-call agentic pipelines that iteratively explore the schema, execute candidate queries and self-correct, often issuing many model and database calls per question. In a validated, regulated environment these properties are liabilities as much as assets: each additional autonomous step expands the surface that must be qualified, monitored for drift and explained to an auditor, and non-deterministic search complicates reproducibility and change control. We therefore adopt a deterministic, single-pass generation step grounded by retrieval and bounded by an explicit human-in-the-loop confidence check, prioritizing predictability, traceability and ease of validation over benchmark accuracy; a multi-sample, execution-guided variant is evaluated separately as a controlled refinement (Section 4.5). For the same reason of deployability under client control, all models are open-weight and served entirely on-premise on a single workstation-class GPU, and a general-purpose model is compared with a code-specialized one of the same architecture, alongside a smaller model fine-tuned on the schema (Section 3.5). The aim is not to surpass cloud frontier systems on accuracy, which would be unrealistic given the gap reported between frontier and locally deployable models on complex queries, but to characterize the accuracy, reliability and usability that a fully on-premise, auditable pipeline can deliver.

3.1. System Overview

The system integrates natural-language processing, SQL generation and retrieval to improve data accessibility and accuracy while remaining compatible with ISA-88 batch-control practice. Altra Systems' experience in ISA-88 standards, MES integration and SQL-based data warehousing informed the selection of components so that AI-generated outputs remain consistent with established batch records and equipment models. As shown in Figure 1, the system comprises four main components. The LLM engine is a locally served open-weight model that receives user questions and, using schema-aware prompts, generates candidate SQL.

The retrieval layer augments the prompt with controlled domain vocabularies and a small number of validated worked examples, selected per question by lexical similarity from a curated store held out from the benchmark; this improves grounding and reduces hallucinations on complex questions. The SQL database layer is a locally hosted relational database containing the ISA-88-aligned dataset, against which generated queries are executed and benchmarked. Finally, the user interface is a lightweight dashboard where users submit questions and inspect the generated SQL, the confidence score and a plain-language result summary.

The interface sits at the top, the LLM and retrieval components in the middle logic layer, and the relational database and exemplar store at the bottom data layer. This separation supports modular tuning, such as improving prompts or retrieval without changing the interface, and keeps control over where regulated data resides.

While Figure 1 sets out the static component architecture, Figure 2 traces how these components act on a single query.

A user's natural-language question is first grounded by retrieval from the exemplar store; the retrieved schema metadata, controlled vocabularies and validated query patterns augment the prompt, and a locally served open-weight model then generates SQL that is execution-checked and returned with a confidence score for human review. The diagram thus shows how the elements described above combine into a single deterministic, retrieval-grounded pass from question to validated output.

3.2. Dataset

To evaluate the system on pharmaceutical data, we use the openly published tablet-manufacturing dataset of Žagar and Mihelič [3], comprising 1,005 production batches of a film-coated tablet collected over roughly thirty months, with incoming raw-material analyses, intermediate and final product quality, and tablet-compression process time series at ten-second resolution. Because the published data are delivered as flat files oriented to quality prediction, we transformed them into a normalized, ISA-88-aligned schema of sixteen interdependent tables spanning product and recipe, equipment hierarchy and sensors, material genealogy, procedural execution (a compression operation with start-up and production phases), process data and quality results; Appendix B (Figure B1) gives the full schema, organized into the ISA-88 sub-models with the batch table as the central hub.

3.3. Prompt Engineering and Query Pipeline

The pipeline converts a user question into executable SQL through controlled prompt steps. The model is first primed with schema information (table names, relationships, primary keys and key attributes) so it can reason about which tables and joins are relevant. Structured prompt templates then guide it to identify the required tables, construct a syntactically valid query, and avoid inventing columns or tables. Retrieval is treated as an experimental condition: in the schema-plus-RAG configuration, the system retrieves ISA-88 definitions, table descriptions and validated example patterns from the vector store and adds them to the prompt to clarify ambiguous terms (for example, batch outcome versus equipment failure); in the schema-only configuration this retrieval is disabled. After generation, the output is compared against analyst-validated reference SQL using both semantic similarity and execution-result comparison, and a confidence score is assigned (Section 4.4). Low-confidence or divergent outputs are flagged in the interface for review or rejection, keeping humans in the loop for critical decisions.

3.4. User Interface

The interface is a simple dashboard for engineers, quality specialists and managers who may not have programming backgrounds. Users enter a natural-language question and the dashboard displays the generated SQL, its confidence score and a short structured summary of the results side by side. When retrieval is active, it also lists the worked examples and vocabulary entries supplied to the prompt, increasing transparency. Review takes place in the same dashboard: users compare generated queries against pre-validated SQL and mark outputs as correct, partially correct or incorrect, and approved queries are added to the exemplar store for subsequent prompt tuning and retrieval adjustment.

3.5. Models Evaluated

To reflect current local-model practice, the system is evaluated with open-weight models that can be served entirely on-premise. The primary comparison uses two members of the Qwen3 30B-A3B family [44]: a general-purpose instruction model (Qwen3-30B-A3B-Instruct) and a code-specialized model of the same architecture (Qwen3-Coder-30B-A3B-Instruct). Both are mixture-of-experts models with 30.5 billion total but only about 3.3 billion active parameters per token, so that in 4-bit quantization they run on a single workstation-class GPU; sharing architecture and scale, they isolate the effect of code specialization. To probe schema adaptation, a smaller code model, Qwen2.5-Coder-7B-Instruct [45], is additionally fine-tuned on the schema (Section 4.6). All models are served under identical retrieval and prompting conditions, and serving and version details are given in Appendix A.

4. Evaluation Methodology

4.1. Datasets and Question Bank

The system is evaluated on the ISA-88-aligned relational database derived from the pharmaceutical tablet-manufacturing data of Žagar and Mihelič [3], comprising 1,005 production batches across 25 product sub-families. Over the pharmaceutical schema we constructed a benchmark of 48 natural-language questions reflecting realistic operational and quality queries, spanning batch genealogy, raw-material and product quality, process features, equipment and sensor relationships, campaign roll-ups, and temporal analysis. Each question is labelled with one of three complexity tiers: single-table or simple-aggregation (T1, n = 13), two- to three-table joins (T2, n = 15), and multi-join queries involving aggregation, filtering, temporal logic, or sub-queries (T3, n = 20). The full question bank, with per-question tier and category labels, is provided in Appendix B. The benchmark is deliberately domain-specific rather than drawn from generic corpora such as Spider or BIRD, because the contribution concerns querying ISA-88-structured batch records; it is extensible, and the released version provides the seed set used here.

4.2. Ground-Truth Construction

Each question is paired with a reference (gold) SQL statement authored against the schema and validated by execution: every reference query was run against the database and its returned result set inspected for correctness, and a programmatic verifier confirms that all 48 queries execute and return the intended rows. Because recent analysis has shown that text-to-SQL benchmark annotations frequently contain errors [46], this execution-based verification was treated as a required step rather than an afterthought. Where a quantity could not be reconstructed exactly from the raw time series without the original feature-extraction routines, the published per-batch values of the source dataset were taken as ground truth and the raw series retained separately (Section 3.2).

4.3. Metrics

The primary metric is execution accuracy: a generated query is correct if, when executed, it returns the same result set as the reference query. Result sets are compared as multisets of rows, insensitive to row and column order and with floating-point values rounded to four decimal places, so that a logically equivalent query phrased differently is still scored correct. As a partial-credit measure we report fidelity, the Jaccard overlap between the returned and reference row multisets. Each outcome is assigned to one category (exact match; value mismatch, where the query executes but returns different data; SQL or execution error, such as referencing a non-existent column or an invalid join; empty result; or no prediction) to support the failure-mode analysis in Section 5.

4.4. Confidence Scoring and Human Oversight

At inference the system assigns each generated query the confidence score defined in Section 3.3 and surfaces low-confidence outputs for human review, keeping the pipeline advisory and auditable. In the offline evaluation, execution-based comparison against ground truth provides the objective correctness signal, while the confidence mechanism is assessed by its agreement with that signal.

4.5. Models and Experimental Configurations

The system was evaluated with locally deployable, open-weight models so that every reported configuration could in principle run on-premise without a cloud dependency. The primary comparison used two members of the Qwen3 30B-A3B family: a general-purpose instruction model (Qwen3-30B-A3B-Instruct) and a code-specialized model of the same architecture (Qwen3-Coder-30B-A3B-Instruct). Both are mixture-of-experts models with 30.5 billion total but only about 3.3 billion active parameters per token, so that in 4-bit quantization they occupy on the order of 18 GB and run on a single workstation-class GPU. Using two models that share architecture, scale and active-parameter count and differ only in training emphasis isolates the effect of code specialization.

Three context configurations were compared, holding the model, prompt template and scoring constant and varying only the information supplied alongside the question. The schema-only configuration supplied the table, column and foreign-key definitions. The vocabulary configuration additionally supplied controlled domain vocabularies (the characteristic catalogue, the stage and statistic enumerations, the material codes, the product strengths and the process-feature names). The retrieval configuration additionally supplied a small number of validated worked examples retrieved per question from a held-out exemplar store by a lightweight lexical-similarity retriever, drawn from a pool disjoint from the evaluation set so that no benchmark answer was leaked. Two further refinements were evaluated as additions to the retrieval configuration. First, a set of curated analytical views was added to the database (one row per batch, with the entity-attribute-value quality measurements pivoted into named columns), present in both contexts while the reference queries continued to use the base tables. Second, an execution-guided self-consistency procedure was evaluated, in which several candidate queries were sampled, executed against the database, repaired when they raised an error or returned no rows, and resolved by majority vote over executed result sets, with the fraction of candidates agreeing on the selected result reported as a confidence score. All sampling used a temperature of zero for the deterministic first candidate and a moderate temperature for additional candidates, and the database, schema context and scoring were identical across configurations. Table 5 summarizes the models and context configurations evaluated. The fine-tuned model is described in Section 4.6.

4.6. Model Adaptation by Fine-Tuning

To address the residual failures on the most complex queries (Section 5.4), a smaller open-weight model was fine-tuned on the schema. The base model was Qwen2.5-Coder-7B-Instruct, selected because a seven-billion-parameter model is inexpensive to fine-tune on a single 16 GB GPU and is straightforward to deploy on-premise, making it the most faithful artefact for the deployment setting.

The training set was generated synthetically and validated by execution. A library of parameterized templates spanning the schema's query patterns (single-table aggregates, two- and three-table joins, raw-material genealogy, the stage- and statistic-filtered quality measurements, process-feature filters, grouped aggregation with HAVING, sub-queries comparing a group to an overall average, conditional pass-rate calculations, stage comparisons, temporal grouping, and joins over the analytical views) was instantiated over real entities drawn from the database, so that each generated query was correct by construction; every query was then executed and retained only if it ran successfully. Each template was tagged by complexity tier, and generation enforced an intentionally compositional profile (approximately 10%, 35% and 55% for tiers T1, T2 and T3) rather than the distribution that is easiest to enumerate, because the complex T3 queries were the principal failure mode and therefore required over-representation; per-family balancing within each tier prevented high-cardinality lookup patterns from dominating. Natural-language diversity was increased by generating several meaning-preserving rewrites of each question with a language model while leaving the validated SQL unchanged, so that label correctness was retained. The 48 benchmark questions and the retrieval exemplars were excluded from the training set, and the data were formatted using the same prompt template employed at inference.

The model was adapted using QLoRA [47]: the base was loaded in 4-bit precision and low-rank adapters of rank 16 were trained on the attention and feed-forward projection matrices, with the loss computed only on the SQL completion. Training ran for two epochs with an effective batch size of eight; the validation loss decreased monotonically and plateaued below 0.01, indicating stable convergence on the synthetic distribution. The resulting adapter is a few tens of megabytes and is loaded on top of the open base model, so the adapted system remains fully self-hostable.

5. Results

This section reports the evaluation on the 48-question benchmark of Section 4.1. Execution accuracy is the primary metric: a generated query is correct when its executed result set matches that of the reference query, compared as an order-insensitive multiset with floating-point values rounded. Fidelity (the Jaccard overlap of result rows) and an outcome category (exact match, value mismatch, SQL error, empty result, or no prediction) support the failure analysis.

5.1. Effect of Retrieval Grounding

Table 6 reports execution accuracy for the two locally deployable models under the three context configurations. Supplying controlled vocabularies produced no meaningful improvement over the bare schema (one additional question for the general model, none for the code-specialized model). Retrieving validated worked examples, by contrast, approximately doubled execution accuracy for both models, from 23-25% to 46-48%. The improvement was statistically significant and free of regressions: a paired McNemar test on per-question correctness identified eleven questions that changed from incorrect to correct with example retrieval and none that regressed, for each model (exact two-sided p approximately 0.001). The two retrieval conditions thus differed in kind rather than degree: vocabulary lists indicate which entities exist, whereas worked examples demonstrate how to compose them, and only the latter affected accuracy on this schema.

5.2. Performance by Query Complexity

Table 7 decomposes the schema-only and retrieval configurations by complexity tier for the code-specialized model; the general model followed the same pattern. The benefit of retrieval was concentrated at the intermediate tier, where accuracy rose from 27% to 80%, recovering most two- and three-table join queries. Single-table queries (T1) were already handled moderately well and improved slightly, whereas the complex multi-join queries (T3) remained near the floor, rising only from zero to two of twenty. Accuracy therefore degraded sharply with structural complexity, and the residual difficulty was almost entirely confined to the most complex queries. Because two- and three-table joins of this kind, which link a batch to its product, material lots or process features, account for the bulk of routine operational and quality questions, the retrieval configuration answered roughly four-fifths of that tier and therefore handled most of the everyday queries a plant analyst would pose; its overall accuracy of 46-48% was held down by the hardest analytical queries (T3), so in practice the system is dependable for common look-ups and aggregations while the complex compositional queries remain the cases that require human review.

5.3. Model Specialization and Additional Refinements

Across all configurations the code-specialized model did not significantly outperform the general-purpose model of the same architecture and scale (48% versus 46% with retrieved examples; 25% versus 23% with the schema alone), and a paired McNemar test found no significant difference in either condition (p = 1.0). For natural-language-to-SQL over this schema, retrieval grounding rather than code specialization determined accuracy.

Two refinements were evaluated as additions to the retrieval configuration, and neither improved accuracy. Adding the curated analytical views reduced accuracy from 46-48% to 42% for both models. The mechanism was visible in the generated queries: the models adopted the convenience columns but frequently applied them incorrectly, selecting a column that was null for the requested metric or mismatching a view to a question, which raised the SQL-error rate in the schema-only condition to 19%, while the additional column names enlarged the prompt without commensurate benefit. Denormalizing the schema into wide reporting views was therefore counterproductive. Layering execution-guided self-consistency (five sampled candidates with up to two repair rounds) on the view-augmented configuration produced at most a negligible gain: accuracy changed from 20 to 21 of 48 for the code-specialized model and from 20 to 20 for the general model (a paired McNemar test found no question changing in either direction), while median per-query generation time increased three- to fivefold. Self-consistency and execution repair help only when failures are non-executable or inconsistent across samples, whereas the dominant residual failures here were executable and stable (Section 5.5), so the majority vote tended to agree on an incorrect result.

The confidence score produced by the voting procedure was, in both models, weakly but positively calibrated. Correct predictions carried higher mean confidence than incorrect ones (0.98 versus 0.88 for the code-specialized model; 1.00 versus 0.93 for the general model), and the point-biserial correlation between confidence and correctness was 0.36 and 0.22 respectively. However, both models assigned the maximum confidence to a large majority of questions, including many that were wrong, so the signal is usable as a soft indicator but does not by itself constitute a reliable correctness filter. This supports retaining human validation of low-confidence outputs while cautioning against treating model confidence as a guarantee.

5.4. Schema-Specialized Fine-Tuning

Table 8 reports the fine-tuned seven-billion-parameter model in two configurations, evaluated with the schema-only context it was trained on and with retrieved examples added at inference, against the strongest off-the-shelf configurations. Fine-tuning achieved the objective for which it was designed: with schema-only context it raised accuracy on the most complex queries from two to four of twenty (10% to 20%), and adding retrieved examples raised it further to five of twenty (25%), the highest T3 accuracy of any configuration evaluated, including the 30B-A3B model with retrieval. The deliberately T3-weighted training set thus improved performance precisely where the system had previously failed most, and the two interventions proved complementary: relative to the tuned model alone, adding retrieval recovered intermediate-tier accuracy (T2 from 9 to 11 of 15) while preserving the T3 gain (T3 from 4 to 5).

Figure 3 visualizes these configurations across the three complexity tiers. The fine-tuned model did not exceed the 30B-A3B retrieval configuration in overall accuracy (46% versus 48%).

Two observations qualify this parity in the tuned model's favor. First, it is a seven-billion-parameter model, roughly a quarter of the active size of the 30B-A3B model, so matching the larger model overall while surpassing it on the hardest tier is a favorable deployment trade-off. Second, the modest overall figure masks a redistribution: adding retrieval to the tuned model lowered single-table accuracy (T1 from 8 to 6 of 13), as the worked examples appear to distract the model on trivial queries, offsetting part of the gains at the higher tiers. The strongest configuration for complex analytical queries is therefore the fine-tuned model with retrieval, while the highest overall accuracy remains the larger model with retrieval; which to prefer depends on the query mix and the deployment constraints.

5.5. Failure Analysis

Categorizing every outcome clarified the accuracy ceiling. In the schema-only configuration the errors were dominated by two patterns rooted in the normalized schema: queries that omitted the stage and statistic filters required by the entity-attribute-value design of the quality table (the single largest cause, accounting for the entire set of measurement questions that require such filters), and queries that assumed a flatter schema than exists, for example treating an attribute stored in the characteristic catalogue as a direct column. Retrieving worked examples that demonstrate these patterns removed most of them, which is the source of the intermediate-tier recovery in Section 5.2. After retrieval, the residual failures were concentrated in a single category: value mismatches accounted for roughly twenty of the forty-eight questions for both models, while empty results and SQL errors each fell to five or fewer. These were queries that were syntactically valid, executed without error, and returned a plausible but incorrect result set, typically through an incorrect aggregation grain, a missing or over-broad join predicate, or a misreading of the intended grouping. Such errors are by construction invisible to execution-guided methods, because the database raises no error and independently sampled candidates tend to repeat the same misreading. The ceiling on this benchmark is therefore one of semantic rather than syntactic correctness, most pronounced on the complex queries that require multi-step compositional reasoning, and fine-tuning was the only intervention that moved it.

Recent text-to-SQL studies indicate that low-to-moderate execution accuracy is common once models are evaluated beyond simplified academic benchmarks. On BIRD, even GPT-4 reached only 54.89% execution accuracy against 92.96% human performance [18]. At the enterprise-workflow level, Spider 2.0 makes the difficulty starker still: the best agentic configuration solved only 21.36% of tasks, and traditional text-to-SQL methods dropped below 6% execution accuracy on Spider 2.0-Lite despite much higher results on Spider 1.0 and BIRD [20]. Even ReFoRCE, a state-of-the-art agentic framework using schema compression, self-refinement, consensus voting and column exploration, achieved only 35.83% and 36.56% execution accuracy on Spider 2.0-Snow and Spider 2.0-Lite respectively [38]. The semantic ceiling observed here is therefore in line with the difficulty these benchmarks report.

6. Discussion

The evaluation yields a coherent and, in places, deliberately negative account of what does and does not improve natural-language-to-SQL on a realistic, ISA-88-aligned manufacturing database. The single largest lever was retrieval of validated query examples, which approximately doubled execution accuracy with a significant, regression-free effect and recovered most intermediate-complexity queries. Controlled vocabularies, code specialization, denormalized analytical views, and execution-guided self-consistency each added little or nothing, the last confirmed across both models, and the analytical views were mildly counterproductive. Targeted fine-tuning of a small model on a compositional, execution-validated synthetic dataset was the only intervention to improve the hardest tier, and it was complementary to retrieval: the fine-tuned seven-billion-parameter model combined with retrieved examples achieved the highest accuracy on complex analytical queries of any configuration while matching the much larger model overall, a favorable result for on-premise deployment even though absolute accuracy on the hardest queries remained limited. These findings are consistent with the broader text-to-SQL literature, in which accuracy on realistic enterprise schemas remains well below that on simpler academic benchmarks (Section 5.5), in which retrieval and in-context demonstrations are strong contributors, and in which locally deployable open-weight models trail frontier systems while remaining attractive where data cannot leave the premises; the limitations observed on the present ISA-88 benchmark are thus consistent with broader challenges in realistic deployment, particularly when schemas are large, normalized, domain-specific and require multi-step analytical reasoning.

For practice, the results suggest a pragmatic on-premise configuration: a single-GPU open-weight model grounded with retrieved validated examples answers the great majority of the routine two- and three-table operational queries that dominate day-to-day use (about four-fifths of that tier) with fully auditable behavior, even though overall accuracy (46-48%) is held down by the hardest analytical queries, and a small fine-tuned model extends reach on those complex queries while remaining inexpensive to host. Relative to the closest industrial neighbors, which provide natural-language access over sensor streams [27] or simulation output via knowledge graphs [28], the contribution here is executable SQL over hierarchical relational batch records with execution-based fidelity checking. The weak calibration of the model's confidence signal reinforces the design choice to keep a human in the loop: confidence is useful for triage but is not a substitute for validation, and the execution-based comparison against ground truth, rather than the model's self-assessment, is what provides an objective correctness signal.

7. Limitations and Future Work

Several limitations bound the claims of this study and should be read as design boundaries. The benchmark comprises 48 questions over a single production line, so absolute accuracies should be read as indicative rather than definitive, and broader benchmarks across plants and processes are needed. The fine-tuning data were synthetic; although every query was validated by execution and the human-curated benchmark was held out, the training distribution may not fully reflect the questions practitioners ask, and overfitting to the synthetic distribution remains a risk that a larger and more varied training set, or additional human-authored evaluation items, would mitigate.

Future work follows from these boundaries. The accuracy ceiling is semantic rather than syntactic, and closing it on complex compositional queries will require approaches beyond context engineering: richer and larger fine-tuning datasets, larger schema-adapted models, and query-decomposition or plan-then-generate strategies that target multi-step reasoning. Schema linking, which supplies fewer question-relevant columns rather than more, is a promising alternative to the denormalized views that proved counterproductive here. Validation on additional plants and processes would further strengthen external validity.

8. Conclusions

This study implemented and evaluated a fully on-premise, schema-aware, RAG-grounded LLM system that translates natural-language questions into SQL over an ISA-88-aligned batch-manufacturing database, with confidence scoring and human-in-the-loop validation. On a tiered 48-question benchmark, retrieving validated query examples was the decisive intervention, approximately doubling execution accuracy to 46-48% with a significant, regression-free effect, while code specialization, denormalized analytical views and execution-guided self-consistency added little; a small code model fine-tuned on a tier-balanced, execution-validated synthetic dataset was the only intervention to improve the most complex queries and, combined with retrieval, achieved the highest accuracy on them while a seven-billion-parameter model matched a far larger one overall. Rather than competing with cloud frontier systems on accuracy, the system demonstrates that a deliberately lightweight, auditable and sovereign pipeline is feasible and usable for regulated batch-manufacturing analytics, and it maps the query classes for which human review remains essential. The approach offers lessons for other regulated, data-rich industrial domains where data sovereignty and validatability outweigh raw model performance.