abidlabs/text2sql-logbook

1# Text-to-SQL Post-Training2 3# Text-to-SQL Post-Training4 5> A multi-week campaign to post-train a small open model into a strong text-to-SQL generator, scored by **execution accuracy** (run gold vs predicted SQL against a real SQLite DB). Click an experiment to open its page.6 7## Experiments8 9| Status | Experiment | Owner |10| --- | --- | --- |11| **Week 1 — Foundations & baselines** | | |12| done | [Build execution-accuracy eval harness](#/build-execution-accuracy-eval-harness) | Ana |13| done | [Zero-shot baselines across open models](#/zero-shot-baselines-across-open-models) | Ana |14| done | [Clean data: dedup + dialect filtering](#/clean-data-dedup-dialect-filtering) | Ana |15| done | [QLoRA SFT baseline](#/qlora-sft-baseline) | Ravi |16| in-progress | [LR & LoRA-rank sweep](#/lr-lora-rank-sweep) | Ravi |17| planned | [Prompt format ablation (chat vs completion)](#/prompt-format-ablation-chat-vs-completion) | to assign |18| **Week 2 — Scaling & data** | | |19| in-progress | [Synthetic data augmentation (self-instruct)](#/synthetic-data-augmentation-self-instruct) | Ravi |20| planned | [Add Spider + WikiSQL to the eval suite](#/add-spider-wikisql-to-the-eval-suite) | Ana |21| planned | [Curriculum: order by join complexity](#/curriculum-order-by-join-complexity) | to assign |22| planned | [Distill from a larger open model](#/distill-from-a-larger-open-model) | Ravi |23| blocked | [Long-context schema eval @32k](#/long-context-schema-eval-32k) | to assign |24| **Week 3 — Hardening & release** | | |25| planned | [Full fine-tune vs LoRA comparison](#/full-fine-tune-vs-lora-comparison) | Ravi |26| planned | [Error taxonomy & failure analysis](#/error-taxonomy-failure-analysis) | Ana |27| planned | [CPU latency & throughput](#/cpu-latency-throughput) | to assign |28| planned | [Final model card + release](#/final-model-card-release) | Ana |29 30# Build execution-accuracy eval harness31 32---33 34### Harness: execution accuracy over SQLite35 36`Jul 02, 2026 · 06:24 UTC`37 38Execution accuracy is the right metric: exact string match is near-zero because the model writes semantically-equivalent but syntactically-varied SQL. The harness builds an in-memory SQLite DB from each example's schema, runs gold and predicted queries, and compares result sets (order-aware only when the gold has ORDER BY).39 40 41````python title=eval.py42import sqlite343from datasets import load_dataset44 45def execution_accuracy(preds, golds, schemas):46    """Build an in-memory SQLite DB per example, run gold vs pred, compare result sets."""47    correct = 048    for pred, gold, schema in zip(preds, golds, schemas):49        con = sqlite3.connect(":memory:")50        con.executescript(schema)51        try:52            got = con.execute(pred).fetchall()53            want = con.execute(gold).fetchall()54            correct += set(map(tuple, got)) == set(map(tuple, want))55        except sqlite3.Error:56            pass57    return correct / len(preds)58 59````60 61- https://github.com/huggingface/trl62 63# Zero-shot baselines across open models64 65---66 67### Baselines: 28.9% best zero-shot68 69`Jul 02, 2026 · 06:24 UTC`70 71Zero-shot execution accuracy on the 800-example held-out set. Instruct variants lead; the 1.5B instruct model is the best base to fine-tune from.72 73| Model | Exec. accuracy | Exact match |74| --- | --- | --- |75| google/gemma-3-270m | 12.1% | 0.1% |76| meta-llama/Llama-3.2-1B-Instruct | 21.7% | 3.2% |77| Qwen/Qwen2.5-1.5B-Instruct | **28.9%** | 4.4% |78 79Target to beat with SFT: **28.9%**.80 81- https://huggingface.co/Qwen/Qwen2.5-1.5B-Instruct82- https://huggingface.co/meta-llama/Llama-3.2-1B-Instruct83- https://huggingface.co/datasets/gretelai/synthetic_text_to_sql84 85# Clean data: dedup + dialect filtering86 87---88 89### Data: 42k clean SQLite-executable examples90 91`Jul 02, 2026 · 06:24 UTC`92 93Filtered the training set to examples whose gold query executes cleanly in SQLite (~78% do; the rest use non-SQLite dialects), then deduped against the eval prompts. Final training set: 42k examples.94 95# QLoRA SFT baseline96 97---98 99### QLoRA baseline: 51.3% exec acc100 101`Jul 02, 2026 · 06:24 UTC`102 103First SFT pass: QLoRA (r=16) on Qwen2.5-1.5B-Instruct, 3 epochs, completion-only loss. Execution accuracy 28.9% → **51.3%**. Live metrics on the Trackio dashboard.104 105 106````python title=train.py107import trackio108from datasets import load_dataset109from trl import SFTConfig, SFTTrainer110from peft import LoraConfig111 112def main(model="Qwen/Qwen2.5-1.5B-Instruct", r=16, lr=2e-4):113    ds = load_dataset("gretelai/synthetic_text_to_sql", split="train")114    trackio.init(project="text2sql", config={"model": model, "r": r, "lr": lr})115    cfg = SFTConfig(learning_rate=lr, num_train_epochs=3,116                    per_device_train_batch_size=16, report_to="trackio")117    peft = LoraConfig(r=r, lora_alpha=2 * r, task_type="CAUSAL_LM")118    SFTTrainer(model, args=cfg, train_dataset=ds, peft_config=peft).train()119 120if __name__ == "__main__":121    main()122 123````124 125- https://huggingface.co/spaces/abidlabs/gemma-text2sql-trackio126 127# LR & LoRA-rank sweep128 129---130 131### Sweep: r=16, lr=5e-4 wins132 133`Jul 02, 2026 · 06:24 UTC`134 135Swept learning rate {1e-4, 2e-4, 5e-4} × rank {8, 16, 32}. r=16 / lr=5e-4 is the clear winner; r=8 underfits and lr>5e-4 destabilizes late in training.136 137- media/lr_rank_sweep.png138- https://huggingface.co/spaces/abidlabs/gemma-text2sql-trackio139 140# Prompt format ablation (chat vs completion)141 142# Synthetic data augmentation (self-instruct)143 144---145 146### Synth data: +3.1% exec acc (early)147 148`Jul 02, 2026 · 06:24 UTC`149 150Generating extra (question, SQL) pairs by prompting a larger open model on real schemas, keeping only pairs whose SQL executes. Running as an HF Job; outputs land in a bucket. Early signal: +3.1% exec acc when mixed 1:4 with real data.151 152 153````python title=gen_synth.py154"""Self-instruct augmentation: sample real schemas, prompt a teacher model for155new (question, SQL) pairs, then keep only pairs whose SQL executes."""156import json, sqlite3, random157from huggingface_hub import InferenceClient158 159client = InferenceClient()160 161def augment(schemas, n_per_schema=8):162    out = []163    for schema in schemas:164        prompt = f"Given this schema, write {n_per_schema} diverse NL questions "\165                 f"and their SQLite queries as JSONL.\n{schema}"166        for line in client.text_generation(prompt, max_new_tokens=1024).splitlines():167            try:168                ex = json.loads(line)169                sqlite3.connect(":memory:").executescript(schema).execute(ex["sql"])170                out.append({**ex, "schema": schema})171            except Exception:172                continue173    return out174 175````176 177- https://huggingface.co/jobs/abidlabs/6a45b02733c08a2c0dae0348178- https://huggingface.co/buckets/abidlabs/jobs-artifacts179 180# Add Spider + WikiSQL to the eval suite181 182# Curriculum: order by join complexity183 184# Distill from a larger open model185 186---187 188### Plan & hypothesis189 190`Jul 02, 2026 · 06:24 UTC`191 192Plan: use the best open model as a teacher (rationale + SQL), distill into the 1.5B student. Hypothesis: closes most of the gap to the teacher at a fraction of the cost.193 194# Long-context schema eval @32k195 196# Full fine-tune vs LoRA comparison197 198# Error taxonomy & failure analysis199 200# CPU latency & throughput201 202# Final model card + release203