Technical Summary: Benchmarking Local LLMs for Natural-Language-to-SQL in Biopharmaceutical Manufacturing

Problem Statement

Biopharmaceutical manufacturing operates under strict regulatory frameworks (FDA 21 CFR Part 11, EU GMP, EU AI Act) that impose data residency and sovereignty requirements, creating significant barriers to adopting cloud-based AI services. While locally deployed Large Language Models (LLMs) offer a privacy-preserving alternative for Natural Language to SQL (NLQ-to-SQL) generation, there is a lack of empirical evidence regarding their feasibility and performance on consumer-grade hardware within this specific domain. Existing benchmarks (e.g., Spider, BIRD, BiomedSQL) focus on general-purpose datasets or scientific reasoning over genomic data, failing to address the specific column vocabularies, query patterns, and regulatory contexts of Manufacturing Execution Systems (MES) and batch records. Furthermore, it remains unclear whether domain-specific biomedical pre-training provides an advantage over general-purpose or code-tuned models for structured T-SQL generation.

Methodology

The study evaluated four open-source, 7B–8B parameter LLMs deployed locally via the Ollama inference engine on consumer-grade hardware (Intel Core i7-9750H, 32GB RAM, NVIDIA GTX 1650 4GB VRAM). The models assessed were:

• Qwen 2.5 Coder 7B: Code-centric, heavily trained on programming and SQL.
• Llama 3.1 8B: General-purpose instruction-tuned with a large context window.
• Mistral 7B: General-purpose with sliding-window attention.
• Meditron 7B: Medically fine-tuned variant of Llama 2 (pre-trained on PubMed/clinical guidelines).

Experimental Setup:

• Dataset: A synthetic MS SQL Server database named PharmaBatchDB containing approximately 63,000 rows across seven tables spanning three modules: Batch, MES, and Clean-In-Place (CIP).
• Evaluation Set: 60 domain-specific NLQ-to-T-SQL question-reference pairs, stratified by module (20 per module) and difficulty (18 easy, 24 medium, 18 hard).
• System Architecture: A local pipeline (PharmaBatchDB AI) built with FastAPI. It enforces a read-only database user, validates SQL via sqlglot AST inspection (blocking non-SELECT statements and out-of-scope tables), and maintains append-only audit logs.
• Prompting: Zero-shot strategy with a system prompt containing T-SQL syntax rules and the full schema for the active module. Temperature was set to 0.1 for near-deterministic output.
• Metrics:
  • SQL Extraction Rate: Proportion of questions yielding extractable SQL.
  • SQL Compliance Rate: Proportion of extracted SQL that is valid, SELECT-only, and module-scoped.
  • Factual Consistency (FC): Jaccard similarity between result sets of generated vs. reference queries (primary quality metric).
  • ROUGE-L: Lexical similarity to reference queries (secondary metric).
  • Hallucination Rate: Proportion of extracted SQL that fails compliance.
  • Performance: Tokens Per Second (TPS) and Time to First Token (TTFT).

A follow-up experiment was conducted on Meditron 7B using a truncated schema prompt (<1,000 tokens) to isolate context-window limitations from generation capability deficits.

Key Results

1. Model Performance Tiers

• Top Performers: Qwen 2.5 Coder 7B and Llama 3.1 8B achieved 100% SQL Extraction Rates.
  • Llama 3.1 8B showed the highest SQL Compliance at 93.33% (56/60) with a Hallucination Rate of 6.67%.
  • Qwen 2.5 Coder 7B achieved 88.33% Compliance (53/60) with a Hallucination Rate of 11.67%.
  • Statistical Note: The 5% difference in compliance between Llama and Qwen was not statistically significant (Fisher's exact p = 0.529). Both models significantly outperformed Mistral and Meditron.
• Mid-Tier: Mistral 7B achieved 100% extraction but only 25.00% Compliance (15/60). It demonstrated high sensitivity to schema complexity, with compliance dropping from 40% on the simple Batch module to 15% on the complex MES module.
• Bottom Tier: Meditron 7B achieved 0.00% SQL Compliance across all 60 questions in the standard condition. It extracted SQL from only 1/60 questions due to context-window overflow (2,048-token limit).

2. Impact of Context Window and Domain Training

The Meditron 7B follow-up experiment (truncated schema) revealed that while reducing the prompt size allowed the model to extract SQL from 28/30 questions (up from 1/30), all 28 extracted queries failed compliance validation (100% hallucination rate). This indicates that the failure was not solely due to context overflow; the model lacked the inherent capability to generate valid T-SQL for manufacturing schemas, suggesting that biomedical pre-training on the Llama 2 base did not confer SQL generation skills and may have diluted them.

3. Difficulty and Module Sensitivity

• Llama 3.1 8B maintained consistent compliance across difficulty levels (94.4% on Hard, 91.7% on Medium).
• Qwen 2.5 Coder 7B showed a notable drop in performance on medium-difficulty questions (83.3% compliance), which involved multi-table aggregations and conditional filtering.
• Mistral 7B performance degraded monotonically with difficulty, dropping to 5.56% compliance on hard questions.

4. Performance Metrics

• Factual Consistency: Qwen (0.34) slightly outperformed Llama (0.30), though both scores are modest, indicating that even compliant queries often retrieved partial or slightly incorrect result sets.
• Latency: On consumer CPU hardware, TPS ranged from 3.2 to 4.4 tokens/second, with TTFT around 11 seconds. This is sufficient for analytical workflows but not for interactive dashboards.

Key Contributions

1. Domain-Specific Benchmark: Introduction of PharmaBatchDB AI, a synthetic benchmark specifically designed for pharmaceutical manufacturing NLQ-to-SQL tasks, addressing a gap in existing literature.
2. Empirical Evidence on Local Deployment: Demonstration that a fully local, GxP-aligned NLQ pipeline (with audit logging, read-only access, and AST validation) is feasible on consumer-grade hardware.
3. Model Selection Insights: Evidence that code-tuned general-purpose models (Qwen, Llama) substantially outperform domain-specific biomedical models (Meditron) on structured T-SQL tasks in this context, primarily due to context window capacity and base model capabilities rather than domain pre-training.
4. Regulatory Alignment Framework: A mapping of system features to specific regulatory requirements (FDA 21 CFR Part 11, EU AI Act), providing a blueprint for compliant local AI deployment.

Significance and Claims

The paper claims that while local 7B–8B LLMs can serve as query-drafting tools within a regulated environment, they are not ready for unsupervised operational decision-making. The modest Factual Consistency scores (0.30–0.34) necessitate human review and a downstream validation layer for any GxP-relevant use.

The study concludes that:

• Biomedical pre-training is not a substitute for SQL capability: Meditron 7B's failure highlights that domain-specific fine-tuning on older architectures (Llama 2) without adequate context windows can result in a loss of structured generation skills.
• Generalist models are viable candidates: Llama 3.1 8B and Qwen 2.5 Coder 7B are plausible candidates for deployment, with Llama showing a numerically (though not statistically) higher compliance rate, making it a potentially lower-risk default in the absence of robust validation layers.
• Synthetic data is a valid pre-certification tool: The synthetic benchmark allows for technical validation and evidence assembly for regulatory submissions before touching production data, lowering adoption barriers.

The authors emphasize that the fine-grained ranking between Llama and Qwen remains statistically unresolved at the current sample size (n=60) and that future work must validate these findings against real-world production schemas and multi-author reference queries.