How beachwang/dail-sql works
1. Repository purpose
- This repo implements DAIL-SQL, a text-to-SQL prompt-engineering pipeline for Spider/BIRD-style benchmarks.
- The architecture is not a model trainer; it is an orchestration and prompt-construction system around external LLMs such as GPT-4, GPT-3.5, text-davinci-003, and Vicuna.
2. Top-level pipeline
- The flow is: raw dataset → preprocessing → schema/question linking → example selection → prompt building → LLM call → post-processing / evaluation.
- The key architectural choice is that the repo prepares highly structured prompts and then delegates generation to the LLM API or local model endpoint.
3. Dataset and benchmark boundary
- The main data boundary is the Spider dataset under ./dataset/spider, with support for Spider-dev/test and BIRD-related output formatting.
- utils/datasets/spider.py and utils/data_builder.py indicate a dataset abstraction layer that normalizes benchmark records into prompt-ready examples and database metadata.
4. Preprocessing subsystem
- data_preprocess.py is the offline preparation step that cleans and enriches data before prompt generation.
- This stage likely produces cached artifacts used later for schema linking, question matching, and example retrieval, reducing runtime overhead during LLM calls.
5. Schema linking and lexical analysis subsystem
- The utils/linking_process.py and utils/linking_utils/ package form a distinct schema-linking boundary.
- This subsystem depends on Stanford CoreNLP (nltk_downloader.py, CoreNLP setup in README) and contains RAT-SQL-inspired utilities for tokenization, parsing, serialization, and Spider-specific matching.
- Its job is to connect natural-language question tokens to database schema elements, which is important for compact prompt representations.
6. Question representation subsystem
- The prompt/ package contains multiple templates for how a question can be represented in the prompt:
- PromptICLTemplate.py
- PromptReprTemplate.py
- ExampleFormatTemplate.py
- ExampleSelectorTemplate.py
- prompt_builder.py
- This is a central architectural layer: it turns the same underlying example into different prompt forms such as SQL-only, QA pairs, or richer representations.
7. Example selection subsystem
- generate_question.py is the main entry point for few-shot example retrieval and prompt assembly.
- The README shows several selector strategies, including question similarity, masked question similarity, and combined question+query similarity.
- This layer depends on precomputed similarity signals and ranking logic to choose k-shot demonstrations under token limits.
8. Query-similarity and masking logic
- The repository distinguishes between plain question similarity and SQL-skeleton-aware selection.
- Evidence from filenames and README suggests the system uses masked question forms plus pre-generated SQL results to capture structural similarity while minimizing domain-specific token cost.
- This is a key design boundary: selecting examples is not just nearest-neighbor text retrieval; it is structure-aware retrieval.
9. LLM invocation subsystem
- ask_llm.py is the runtime execution entry point that sends prompts to an LLM and collects answers.
- The README shows support for both single-pass generation and self-consistency voting via multiple samples (n > 1, higher temperature), which is an important runtime branch.
- The LLM layer is externalized through llm/chatgpt.py, which likely wraps OpenAI-style API calls.
10. Post-processing and output formatting
- utils/post_process.py and to_bird_output.py form the output normalization layer.
- This layer is responsible for converting raw model completions into executable SQL or benchmark-specific submission formats.
- It is also the place where any cleanup, extraction, or result alignment with database instances would happen.
11. Scripts, experiments, and reproducibility
- The run_dail_sql*.sh scripts are the operational wrappers for standard runs and self-consistency runs.
- results/ contains stored outputs from the paper’s experiments, which makes the repo partly reproducibility-focused rather than purely code-focused.
- img/ stores evaluation figures used in the README, supporting the experimental narrative.
12. External dependencies and infrastructure
- Two external systems materially affect the architecture:
- OpenAI API for GPT-based execution.
- Stanford CoreNLP server for linguistic preprocessing/schema linking.
- The repo also depends on the Spider database directory and benchmark assets, which are treated as local filesystem inputs rather than embedded resources.
13. Functional boundary summary
- In architectural terms, the repo separates into:
- offline preparation: dataset cleaning, linking, and caching
- prompt construction: representation + retrieval + formatting
- inference orchestration: LLM request/response handling
- result normalization: post-processing and benchmark output conversion
- That separation is what enables the same system to support zero-shot, few-shot, and self-consistency variants across multiple models.
Main entry points: prompt Prompt templates · linking_utils Link utils · generate_question.py Generate question · ask_llm.py Ask LLM
Components 22
Data boundary
Benchmark assets and dataset normalization layer.
- Spider data · Spider/BIRD-style benchmark files and database assets used as the main input boundary.
- Spider adapter · Normalizes Spider records into repo-internal examples and metadata.
utils/datasets/spider.py - Data builder · Builds prompt-ready data structures from benchmark records and database context.
utils/data_builder.py
Linking pipeline
Offline preprocessing and schema-question linking.
- Preprocess · Offline cleaning, enrichment, and cache preparation before prompt generation.
data_preprocess.py - CoreNLP setup · Environment/bootstrap helper for CoreNLP-backed tokenization and parsing.
nltk_downloader.py - Link process · Main schema-linking orchestration across question tokens and database schema items.
utils/linking_process.py - Link utils · RAT-SQL-inspired helpers for tokenization, parsing, serialization, and matching.
utils/linking_utils - CoreNLP client · Wrapper around Stanford CoreNLP used during linguistic preprocessing.
utils/linking_utils/corenlp.py - Serialization · Converts schema and linking structures into compact representations.
utils/linking_utils/serialization.py - Spider match · Spider-specific lexical and structural matching utilities.
utils/linking_utils/spider_match_utils.py
Prompt pipeline
Example selection and prompt construction.
- Prompt templates · Template layer for ICL, representation variants, and example formatting.
prompt - ICL template · Few-shot in-context learning template for assembling demonstrations.
prompt/PromptICLTemplate.py - Repr template · Question representation template used to vary prompt structure.
prompt/PromptReprTemplate.py - Example format · Defines how selected examples are rendered inside the final prompt.
prompt/ExampleFormatTemplate.py - Example select · Template and logic boundary for selecting demonstrations under token limits.
prompt/ExampleSelectorTemplate.py - Prompt builder · Combines inputs, selected examples, and templates into the final LLM prompt.
prompt/prompt_builder.py - Generate question · Main entry point for few-shot retrieval and prompt assembly.
generate_question.py
Inference pipeline
LLM invocation and output normalization.
- Ask LLM · Runtime entry point that sends prompts to a model and collects completions.
ask_llm.py - ChatGPT API · OpenAI-style API wrapper used for GPT-based inference.
llm/chatgpt.py - Post process · Cleans and aligns raw model output into executable SQL or normalized text.
utils/post_process.py - BIRD output · Converts predictions into BIRD-style benchmark submission format.
to_bird_output.py - Run scripts · Shell entry points for standard runs and self-consistency variants.
run_dail_sql.sh
Connections 23
- Spider data → Spider adapter: raw records · Benchmark assets are loaded into the repo's dataset abstraction.
- Spider adapter → Data builder: normalized examples · Adapter feeds normalized examples and metadata into assembly.
- Data builder → Preprocess: prep input · Prepared records are enriched and cached offline.
- Preprocess → Link process: schema context · Preprocessed data is used for schema linking and question analysis.
- CoreNLP setup → CoreNLP client: bootstraps · Environment setup enables CoreNLP-backed processing.
- Link process → Link utils: uses · Linking orchestration depends on utility modules.
- Link utils → CoreNLP client: parse/tokenize · Utilities call into CoreNLP for linguistic analysis.
- Link utils → Serialization: serialize · Schema and linking structures are compacted for later use.
- Link utils → Spider match: match · Spider-specific matching rules support schema/question linking.
- Data builder → Generate question: prompt data · Built examples and metadata become retrieval inputs.
- Generate question → Example select: rank/select · Retrieval logic chooses k-shot demonstrations.
- Generate question → Prompt templates: formatting · The selector and builder consume prompt templates.
- Prompt templates → ICL template: includes · Template package contains the ICL form.
- Prompt templates → Repr template: includes · Template package contains representation variants.
- Prompt templates → Example format: includes · Template package defines example rendering.
- Prompt templates → Prompt builder: includes · Builder composes the final prompt from templates.
- Example select → Prompt builder: chosen demos · Selected examples are inserted into the final prompt.
- Prompt builder → Ask LLM: final prompt · Assembled prompt is sent to the inference runner.
- Ask LLM → ChatGPT API: api call · Inference runner delegates to the external model wrapper.
- Ask LLM → Post process: raw completion · Model output is cleaned and normalized after generation.
- Post process → BIRD output: format submit · Normalized results can be converted to benchmark submission format.
- Run scripts → Generate question: launch retrieval · Operational scripts start prompt construction jobs.
- Run scripts → Ask LLM: launch inference · Operational scripts also start model execution jobs.