How contextualai/bird-sql works
Project type and purpose
• This is a staged, full-pipeline text-to-SQL system built in Python. It ingests the BIRD benchmark, prepares schemas, generates SQL candidates via a large language model, executes those candidates on a database, scores them with a second “reward” model, and finally performs analysis to choose the best SQL.
Main components
a. Data & Schema Preparation
• Script: src/prep_data.py
• Inputs: Raw BIRD JSON, few_shots/mschema definitions
• Outputs: data/ directory with processed JSONL and schema files
b. Generator Inference
• Script: src/generate.py
• Outputs: SQL candidate files under output/generations/
c. SQL Execution
• Script: src/process_sqls.py & helper src/process_sqls_helper.py
• External Service: SQL database instance (e.g., Postgres or SQLite)
• Outputs: Results + filtered candidates under output/with_results/
d. Reward Scoring
• Script: src/reward.py
• Model: models/reward (ctx-bird-reward)
• Outputs: Scored candidates in output/with_rewards/
e. Analysis & Selection
• Script: src/analysis.py
• Inputs: Scored candidates + gold SQLs
• Outputs: Final analysis report under output/analysis/
Relationships & Data Flow
• Data Prep → Generator → SQL DB → Reward → Analysis
• The generator stage reads processed JSONL and schema files, writes SQL candidates.
• The SQL execution stage reads those candidates and writes execution outputs.
• The reward stage reads execution outputs and computes a score per candidate.
• The analysis stage reads scores and ground-truth SQL to pick winners.
Architectural patterns & principles
• Pipeline (linear, stage-based) with explicit data hand-off via JSONL files.
• Modular scripts grouped under src/ for single responsibility.
• External dependencies (HuggingFace model store, SQL database) are decoupled from core code.
• Parallelism hints: generator & execution can run concurrently; reward uses multi-GPU; analysis uses multi-CPU.
Technologies & frameworks
• Python 3.10 scripts
• HuggingFace CLI & transformers models
• VLLM or native GPU model inference
• JSONL for intermediate data serialization
• Relational database (Postgres, SQLite) for SQL execution
• Requirements managed via requirements.txt
Essential details to capture
• Input directory layouts (data/, few_shots/)
• models/generator vs. models/reward separation
• use of JSONL files as contract between stages
• timeout tuning parameter at SQL execution
• final output directory structure under output/