distillation-lab

Teach a small open model to write SQL by learning from a much larger one, and measure, honestly, how much it improved.

This is the companion project for the Let's Data Science course Model Distillation: Teach a Small Model to Write SQL. Everything the course shows comes from the scripts here. The final trained adapter is published on Hugging Face: letsdatascience/qwen3.5-0.8b-spider-sql-lora.

Status (October 2026). Stage 1 (training on verified teacher answers) and stage 3 (reinforcement learning with an execution reward) are done on Qwen3.5-0.8B. Stage 2 (on-policy distillation) and BIRD are not run yet. All results below are on Spider dev.

Results (Spider dev, 1,034 questions, one greedy answer each)

Two grading rules, both from executing the SQL on the real database:

• Official (the headline): Spider's execution accuracy, with the default rules of its checker, test-suite-sql-eval. DISTINCT is deleted from both queries before they run, the columns may come in any order, duplicate rows count, and row order counts whenever the gold query contains ORDER BY. sqlexec.is_correct_official implements those rules.
• Lenient (BIRD-style): the same set of rows, columns in order. The teacher-answer filter and the reinforcement-learning reward use this rule.

Model	Official	95% interval	Lenient
Qwen3.6-35B-A3B (second teacher, 4-bit, local)	82.6%	80.3–84.9%	79.1%
DeepSeek V4 Flash (API teacher), SQL only	80.8%	78.3–83.2%	76.9%
Qwen3.5-2B, no training	56.8%	53.8–59.8%	54.2%
Qwen3.5-0.8B, no training	35.7%	32.8–38.6%	35.2%
... trained on the teacher's verified SQL	64.0%	61.1–66.9%	61.7%
... the same, trained again with another seed	65.0%	62.1–67.9%	62.4%
... trained on the teacher's verified plan + SQL	63.4%	60.4–66.3%	59.8%
... trained on the benchmark's own SQL (comparison)	67.0%	64.1–69.8%	65.9%
... the benchmark's SQL, only for the questions the teacher solved	63.7%	60.8–66.6%	60.9%
... every question: teacher's SQL where verified, benchmark's otherwise	70.4%	67.6–73.1%	68.4%
... teacher's SQL verified with the official rule instead	60.3%	57.4–63.3%	57.1%
... benchmark's SQL, then reinforcement learning	69.5%	66.7–72.3%	68.3%
... teacher's verified SQL, then reinforcement learning (final)	71.9%	69.1–74.6%	68.4%

Every 0.8B run uses LoRA (rank 16), the same 2,000 sampled Spider training questions, 2 epochs and the same settings; reinforcement learning is GRPO as mlx-lm-lora implements it (advantage = reward minus group mean, divided by the group's standard deviation; its dr_grpo loss setting only changes length normalisation), 400 steps, 8 tries per question, with the lenient execution reward, on training questions outside the 2,000. Paired differences use exact McNemar tests (questions only the first run got right / only the second).

• Distillation works. Training on the teacher's verified answers took the student from 35.7% to 64.0% (345 / 52).
• Reinforcement learning added most where lessons were missing. On the teacher-answer student: 64.0% to 71.9% (118 / 37, p < 0.0001); GROUP BY questions 144 to 177 right; SQL that fails to run 171 to 105. On the benchmark-answer student: 67.0% to 69.5% (82 / 56, p = 0.033). The final model is not significantly ahead of the second (110 / 86, p = 0.10).
• The gap between human and teacher answers came from the questions the filter dropped, not from the answers. Same questions, different authors: 63.7% vs 64.0% (113 / 116, p = 0.89). Same authors, fewer questions: 67.0% vs 63.7% (125 / 91, p = 0.025). Every question kept, teacher answers where verified: 70.4% vs 67.0% (117 / 82, p = 0.016). The dropped questions are harder and use GROUP BY far more often.
• Training randomness is real. The teacher-answer student trained again with another seed scored 65.0% vs 64.0% (97 / 87, p = 0.51), but the two copies disagree on 184 questions, and the benchmark-answer student's lead over the second copy is not significant (133 / 112, p = 0.20).
• One experiment did not work. Verifying teacher answers with the official rule kept 1,715 lessons (1,624 identical to the lenient set) yet the student scored 60.3%, below both seeds (54 / 102 against seed 8, p = 0.0002). We do not know why.

Reports with paired tests: results/report_spike2000_small_official.md, results/report_spike2000_small_diagnostic_official.md, results/report_spike2000_small_rl_official.md and results/report_spike2000_small_rl_teacher_official.md. An earlier, stricter rule of our own (outermost ORDER BY only, columns in order) is kept in pipeline/09_rescore_strict.py --rule strict so older numbers can be reproduced.

Use the trained model (Apple silicon)

make setup
uv run hf download Qwen/Qwen3.5-0.8B --local-dir models/base/Qwen3.5-0.8B
uv run hf download letsdatascience/qwen3.5-0.8b-spider-sql-lora --local-dir models/adapters/final
scripts/download_data.sh spider
uv run python scripts/ask.py --db data/spider/database/concert_singer/concert_singer.sqlite "Which singers are older than 40?"

ask.py builds the prompt the model was trained on (schema, three example rows per table, the question), asks for one answer, and runs the SQL read-only on your database. Point --db at any SQLite file.

Go further

The results above stop where the runs stopped: one benchmark, and one training run per recipe (the teacher-SQL student was trained twice). Three ways to go further:

Score on BIRD. BIRD dev has 1,534 questions on 11 larger, messier databases; most come with an evidence hint, which the prompt includes. The printed accuracy is BIRD's own rule (same set of rows). Start with a few questions, then drop --limit.

scripts/download_data.sh bird-dev   # 346 MB download, 1.4 GB unpacked, plus the 2025-11-06 corrected questions
uv run python pipeline/04_evaluate.py --student small --adapter models/adapters/final \
  --benchmark bird --split dev --limit 20 --name final_bird_dev

Train with other seeds. The seed sets the LoRA starting values and the lesson order. Build the training sets once (the teacher step needs DEEPINFRA_API_KEY and costs about ten cents), then train, score and compare copies:

make teacher-spike && make sft-spike
for seed in 10 11 12; do
  uv run python pipeline/03_train.py --student small --tag spike2000 --arm teacher_sql --seed $seed
  uv run python pipeline/04_evaluate.py --student small --fmt sql \
    --adapter models/adapters/small_spike2000_teacher_sql_seed$seed --name seed$seed
done
uv run python pipeline/09_rescore_strict.py --eval seed10 seed11 seed12
uv run python pipeline/05_report.py --runs seed10 seed11 seed12 --baseline seed10 --grading official --out seeds

Run reinforcement learning again, or longer. Each RL run was done once, for 400 steps:

uv run python pipeline/07_grpo.py --student small --fmt sql --steps 400 \
  --start models/adapters/small_spike2000_teacher_sql --name my_rl
uv run python pipeline/04_evaluate.py --student small --fmt sql --adapter models/adapters/my_rl --name my_rl_dev

The idea

1. A large teacher model (DeepSeek V4 Flash) answers thousands of practice questions: "here is a database and a question; write the SQL".
2. Every answer is run against the real database and kept only if it returns the right rows.
3. A small student (Qwen3.5, 0.8B and 2B parameters) is fine-tuned on those verified answers.
4. The student is scored on Spider's dev set, which it never trained on. (BIRD and a set of fresh questions are planned, not yet run.)

Every result is compared against controls, so the improvement can be attributed to the teacher and not just to more training:

Arm	What the student learns from
Base	nothing (the starting score)
Gold only	the benchmark's own reference SQL, no teacher
Teacher SQL	the teacher's verified SQL
Teacher plan + SQL	the teacher's short plan followed by its verified SQL

Accuracy is reported with 95% bootstrap intervals, and paired differences with exact McNemar tests.

Layout

configs/spike.toml        experiment settings: teacher, prices, budget, students, sample sizes
src/distill_lab/          the library: datasets, prompts, safe SQL grading, teacher client, evaluation, statistics
pipeline/                 numbered steps, run in order (00 env check, 01 teacher, 02 build training sets, 03 train, 04 evaluate, 05 report)
scripts/                  data and model downloads (exact sources and byte sizes)
research/                 one-off measurements that informed the plan (teacher cost probes)
docs/METHOD.md            the three training stages and why each is there
tests/                    unit tests for grading, prompts and statistics
results/                  committed: small JSON summaries of every run and the teacher spend ledger
data/ models/ runs/       not committed: benchmarks, model weights, per-question logs (see each folder's README)

Quick start (Apple silicon)

make setup      # .venv from uv.lock
make test       # unit tests
make data       # Spider + BIRD into data/ (about 9.5 GB)
make models     # Qwen3.5 0.8B and 2B into models/base/
cp .env.example .env   # add DEEPINFRA_API_KEY
make env        # step 00: machine and data checks
make help       # every other step

Cost controls

The teacher is a paid API. Every call's cost is appended to results/teacher_spend.jsonl, and generation stops when the total reaches budget_usd in configs/spike.toml ($15). make spend prints the running total. Prices in the config are DeepInfra list prices checked on 18 October 2026; recheck them before a large run.

Licenses

• Code: MIT (see LICENSE).
• Spider and BIRD: CC BY-SA 4.0. Datasets we publish that are derived from them use the same license.
• Teacher: DeepSeek V4 Flash, MIT license (permits training other models on its outputs).
• Students: Qwen3.5-0.8B and Qwen3.5-2B, Apache-2.0.

See DATA_LICENSES.md for sources and attribution, HARDWARE.md for measured times and memory, and REPRODUCIBILITY.md for versions and seeds.