TL;DR
Researchers at IBM and Confluent have introduced Self-Refining Prompt Induction (SRPI), an offline framework that automatically converts LLM generation failures into reusable, dialect-specific instructions. By iteratively refining a system prompt using a panel of LLM judges and a regression guard, SRPI significantly boosts Text-to-SQL performance on complex streaming dialects like Flink SQL without the need for expensive model fine-tuning.
What Problem Does This Paper Address?
While LLMs are increasingly capable of generating standard SQL, specialized dialects—particularly those for streaming data like Confluent Cloud Flink SQL—present a steep challenge. Streaming SQL introduces complex temporal semantics, such as windowing rules (TUMBLE, HOP, SESSION) and engine-specific timestamp handling (e.g., $rowtime).
Generic prompts often fail on these "long-tail" constructs. Traditional solutions like Supervised Fine-Tuning (SFT) are costly, require massive datasets, and must be repeated every time a base model is updated. The authors sought a way to encode engine-specific expertise into a static, reusable prompt that could be versioned, audited, and transferred across different LLMs.
Methods
SRPI is an offline, multi-stage workflow designed to "teach" an LLM the nuances of a dialect through self-correction. The process, illustrated in Figure 2, consists of four main components:
- The Generator: An LLM that attempts to write SQL based on a natural language question and table definitions (DDL).
- The Judges: A panel of three LLMs that compare the generated SQL against a "gold" reference. They provide feedback (Yes, Maybe, No) and detailed explanations of errors.
- The Refiner: When a generation fails, the Refiner LLM analyzes the failure and the judges' feedback to rewrite the entire system prompt, adding or clarifying rules to prevent similar errors in the future.
- The Regression Guard: To ensure new rules don't break what already works, this component re-tests the new prompt against a sample of previously successful cases.
Once the prompt is sufficiently refined, a separate Compressor stage reduces the prompt's length (which can grow to over 70k characters) to fit within practical context windows while preserving the core semantic constraints.
Datasets
The authors evaluated SRPI using a controlled synthetic workload:
- Seed Data: 30 expert-written question-SQL pairs validated on a live Flink cluster.
- Induction Set: 335 synthetic examples used for the refinement loop.
- Evaluation Set: 257 questions from held-out domains (education, insurance, retail) to test generalization.
- Complexity Tiers: Examples were categorized into Tier 1 (single construct), Tier 2 (two constructs), and Tier 3 (three or more constructs, like joins combined with windowing).
Key Insights and Ideas
- Prompt Transferability: One of the most significant findings is that a prompt induced using one model (e.g., GPT-6.1 Sol) works remarkably well across other models (Claude, Gemini, DeepSeek). This suggests that the induced rules capture fundamental dialect constraints rather than just model-specific quirks.
- The "Regression Burden": The authors found that local fixes often break earlier successes. The Regression Guard rejected nearly 60% of candidate rewrites in later rounds, highlighting the necessity of global consistency checks in automated prompt engineering.
- Decoupled Compression: You don't need to worry about prompt length during the induction phase. By allowing the prompt to grow "messy" and then using a dedicated compression step, they achieved a ~53% reduction in length with negligible impact on accuracy.
- Beyond Execution: Simply checking if a query runs isn't enough. The study showed that many queries execute successfully but produce semantically incorrect results (e.g., using the wrong timestamp). LLM judges are crucial for catching these subtle logic errors.
Key Results
The SRPI-induced prompt (specifically the "SRPI-60k" version) provided substantial gains across all seven tested generators:
- Accuracy Boost: Mean judge scores improved by 7.1 to 19.2 percentage points.
- Induction Model Success: The model used for induction (Sol) saw its score jump from 85.3% to 97.5%.
- Complex Queries: The gains were most dramatic for Tier 3 queries, where scores for some models increased by over 50 percentage points.
- Generalization: As shown in Figure 6, the performance gains were consistent across a wide range of LLM families, proving the prompt's robustness.
Limitations and Future Work
- Reference Bias: The system relies on "gold" reference SQL. If the reference contains a specific convention not explicitly in the DDL, the Refiner might turn that convention into a hard rule, potentially causing issues for schemas that don't follow that convention.
- Cost and Latency: While SRPI simplifies the inference pipeline (no retrieval or repair needed), the resulting prompt is long (~7,400 words), which adds to the input token cost and can increase latency for some models.
- Human-in-the-loop: The authors plan to integrate real-world user logs to further refine the prompt, moving from a purely synthetic induction set to one informed by actual user failures in production.
Summary updated