sql-r1: BIRD Text-to-SQL Distillation

Distill DeepSeek-V4-Pro's SQL reasoning into Qwen2.5-Coder-7B-Instruct via LoRA SFT, running entirely on Apple Silicon (M-Ultra 64 GB) with MLX-LM. Final result: 47.50% → 55.00% pass@1 on a 200-question held-out slice of BIRD (+7.50 pp), for $5.83 of OpenRouter spend and ~14 hours of local GPU time.

Trained adapter on Hugging Face: AbhiPoluri/qwen25-coder-7b-bird-sft-v4pro

This is a pivot from an earlier chess-tactics distillation that hit a 0% baseline ceiling on small reasoning models — see LOG.md for the full rationale.

TL;DR

• Task: BIRD natural-language → SQL on 200 held-out questions, scored by deterministic execution match.
• Student: Qwen/Qwen2.5-Coder-7B-Instruct (LoRA, rank 16, ~11M trainable params).
• Teachers tried: deepseek/deepseek-chat-v3.1 and deepseek/deepseek-v4-pro via OpenRouter.
• Winner: SFT on 708 V4-Pro traces, LR=5e-6, 300 iters, iter-50 checkpoint (not the last save).
• Spend: $5.83 OpenRouter total across 8 runs.

What actually mattered

Three things, in order of impact:

1. Teacher quality > teacher quantity. V4-Pro alone (708 traces) beat V3.1+V4-Pro combined (1404 traces). Doubling data with a second, weaker teacher cost more and gave less.
2. Checkpoint selection > training length. Same training run, evaluating iter-50 vs the default last save: +6 pp. The default adapters.safetensors file MLX-LM writes is always the last save, which on a U-shaped val-loss curve is the worst checkpoint.
3. DPO is not free. Both DPO runs (from base, and from the SFT-V4Pro winner) regressed below baseline. KL leash β=0.1 was insufficient to prevent collapse on this dataset size.

Pipeline overview

BIRD dev set (1534 questions, 11 SQLite DBs)
        │
        ▼
  filter.py  ──► data/bird_filtered.csv
        │
        ▼
 make_eval.py  ──► data/eval.jsonl (200 held-out)  +  data/eval_qids.txt
        │
        ▼
gen_traces.py  (DeepSeek-V4-Pro via OpenRouter, parallel, resumable)
        │       — execute each predicted SQL against the SQLite DB
        │       — keep only execution-match traces
        │
        ▼
 data/sft_v4pro.jsonl   (708 accepted traces from ~1500 attempts)
        │
        ▼
 train_sft.py  ──► runs/sft-v4pro/   (MLX-LM LoRA SFT, 300 iters, LR=5e-6)
        │           ↳ writes 0000050_adapters.safetensors, 0000100_..., ..., adapters.safetensors
        │
        ▼
 swap iter-50 checkpoint into adapters.safetensors  (THE KEY STEP)
        │
        ▼
   eval.py     ──► results/eval_sft_v4pro.json   →  55.00% pass@1

Results across 8 runs

Run	pass@1	Δ baseline	Note
SFT-V4Pro iter-50 (winner)	55.00%	+7.50	708 traces, LR=5e-6, best per val loss
SFT-Combined iter-300	54.00%	+6.50	1404 traces, monotonic curve, last ckpt is best
SFT-V3.1 iter-50	49.50%	+2.00	696 traces, weaker teacher
SFT-V4Pro iter-300 (overfit)	49.00%	+1.50	Same training as winner, wrong checkpoint
Baseline	47.50%	—	Qwen2.5-Coder-7B-Instruct, no training
SFT-Hard (163 narrow, no KL)	47.50%	0.00	Gains evaporated on a too-narrow slice
DPO from SFT-V4Pro	27.50%	−20.00	KL leash insufficient
DPO from base (no SFT)	24.00%	−23.50	DPO without SFT collapses

Per-difficulty breakdown

The +7.5 pp lift holds across every BIRD difficulty band:

Cost-efficiency frontier

How to reproduce

For the full step-by-step (including the critical iter-50 checkpoint swap), see REPRODUCING.md. The TL;DR:

python3.11 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
cp .env.example .env  # add OPENROUTER_API_KEY

mkdir -p data
curl -L -o data/bird_dev.zip https://bird-bench.oss-cn-beijing.aliyuncs.com/dev.zip
python filter.py
python make_eval.py

TEACHER_TAG=v4pro \
TEACHER_MODEL=deepseek/deepseek-v4-pro \
SFT_TARGET=1500 \
python gen_traces.py                    # ~10 min, ~$4

python eval.py --model Qwen/Qwen2.5-Coder-7B-Instruct \
               --output results/eval_baseline.json     # ~30 min

TEACHER_TAG=v4pro python train_sft.py   # ~17 min

# THE KEY STEP — swap iter-50 in as the active adapter
cp runs/sft-v4pro/0000050_adapters.safetensors \
   runs/sft-v4pro/adapters.safetensors

python eval.py --model runs/sft-v4pro \
               --output results/eval_sft_v4pro.json    # 55.00%

Configuration knobs (env vars)

Var	Default	Notes
STUDENT_MODEL	Qwen/Qwen2.5-Coder-7B-Instruct	base for SFT/DPO
TEACHER_MODEL	deepseek/deepseek-v4-pro	trace generator
TEACHER_TAG	v4pro	output filename suffix
SFT_TARGET	1500	max accepted traces (resumable)
CONCURRENCY	20	parallel teacher calls
MAX_SPEND_USD	20	hard cap on OpenRouter cost
DPO_PAIRS	500	(chosen, rejected) pairs

Files

sql-r1/
├── README.md             — this file
├── REPRODUCING.md        — step-by-step recipe for the +7.5 pp result
├── LOG.md                — full chronicle of 8 runs, dead ends, framework quirks
├── docs/
│   ├── PRIMER.md         — gentle intro: BIRD, distillation, LoRA
│   ├── INTERNALS.md      — deep technical: LoRA math, SFT loss, DPO derivation
│   └── figures/          — generated charts (results_bar, val_loss_curves, …)
├── filter.py             — BIRD dev → CSV with db_paths
├── make_eval.py          — carve out 200 held-out questions
├── gen_traces.py         — teacher → SFT traces (parallel, resumable, streaming writes)
├── eval.py               — execution-accuracy on data/eval.jsonl
├── train_sft.py          — MLX-LM LoRA SFT
├── train_dpo.py          — DPO via MLX-LM (also generates rejected pairs)
├── train_dpo_trl.py      — DPO via HuggingFace TRL on MPS (MLX-LM 0.31.3 has no DPO)
├── eval_trl.py           — eval for TRL-trained adapters
├── sql_utils.py          — schema extraction, SQL parsing, execution-match
├── dashboard.py          — live HTTP status page (port 8766)
├── scripts/make_figures.py — regenerate the figures in docs/figures/
├── run.sh                — orchestrator
├── requirements.txt
└── data/                 — BIRD content, sft_*.jsonl, dpo_*.jsonl, eval.jsonl

Further reading

• LOG.md — every run, every wrong turn, every "wait, what does this MLX flag actually do"
• docs/PRIMER.md — gentle intro to LoRA, SFT, DPO, KL divergence, checkpoint selection
• docs/INTERNALS.md — the math
• REPRODUCING.md — exact commands to hit 55.00%