logbook.md

# Text-to-SQL Post-Training

# Text-to-SQL Post-Training

> 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.

## Experiments

| Status | Experiment | Owner |
| --- | --- | --- |
| **Week 1 — Foundations & baselines** | | |
| done | [Build execution-accuracy eval harness](#/build-execution-accuracy-eval-harness) | Ana |
| done | [Zero-shot baselines across open models](#/zero-shot-baselines-across-open-models) | Ana |
| done | [Clean data: dedup + dialect filtering](#/clean-data-dedup-dialect-filtering) | Ana |
| done | [QLoRA SFT baseline](#/qlora-sft-baseline) | Ravi |
| in-progress | [LR & LoRA-rank sweep](#/lr-lora-rank-sweep) | Ravi |
| planned | [Prompt format ablation (chat vs completion)](#/prompt-format-ablation-chat-vs-completion) | to assign |
| **Week 2 — Scaling & data** | | |
| in-progress | [Synthetic data augmentation (self-instruct)](#/synthetic-data-augmentation-self-instruct) | Ravi |
| planned | [Add Spider + WikiSQL to the eval suite](#/add-spider-wikisql-to-the-eval-suite) | Ana |
| planned | [Curriculum: order by join complexity](#/curriculum-order-by-join-complexity) | to assign |
| planned | [Distill from a larger open model](#/distill-from-a-larger-open-model) | Ravi |
| blocked | [Long-context schema eval @32k](#/long-context-schema-eval-32k) | to assign |
| **Week 3 — Hardening & release** | | |
| planned | [Full fine-tune vs LoRA comparison](#/full-fine-tune-vs-lora-comparison) | Ravi |
| planned | [Error taxonomy & failure analysis](#/error-taxonomy-failure-analysis) | Ana |
| planned | [CPU latency & throughput](#/cpu-latency-throughput) | to assign |
| planned | [Final model card + release](#/final-model-card-release) | Ana |

# Build execution-accuracy eval harness

---

### Harness: execution accuracy over SQLite

`Jul 02, 2026 · 06:24 UTC`

Execution 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).


````python title=eval.py
import sqlite3
from datasets import load_dataset

def execution_accuracy(preds, golds, schemas):
    """Build an in-memory SQLite DB per example, run gold vs pred, compare result sets."""
    correct = 0
    for pred, gold, schema in zip(preds, golds, schemas):
        con = sqlite3.connect(":memory:")
        con.executescript(schema)
        try:
            got = con.execute(pred).fetchall()
            want = con.execute(gold).fetchall()
            correct += set(map(tuple, got)) == set(map(tuple, want))
        except sqlite3.Error:
            pass
    return correct / len(preds)

````

- https://github.com/huggingface/trl

# Zero-shot baselines across open models

---

### Baselines: 28.9% best zero-shot

`Jul 02, 2026 · 06:24 UTC`

Zero-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.

| Model | Exec. accuracy | Exact match |
| --- | --- | --- |
| google/gemma-3-270m | 12.1% | 0.1% |
| meta-llama/Llama-3.2-1B-Instruct | 21.7% | 3.2% |
| Qwen/Qwen2.5-1.5B-Instruct | **28.9%** | 4.4% |

Target to beat with SFT: **28.9%**.

- https://huggingface.co/Qwen/Qwen2.5-1.5B-Instruct
- https://huggingface.co/meta-llama/Llama-3.2-1B-Instruct
- https://huggingface.co/datasets/gretelai/synthetic_text_to_sql

# Clean data: dedup + dialect filtering

---

### Data: 42k clean SQLite-executable examples

`Jul 02, 2026 · 06:24 UTC`

Filtered 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.

# QLoRA SFT baseline

---

### QLoRA baseline: 51.3% exec acc

`Jul 02, 2026 · 06:24 UTC`

First 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.


````python title=train.py
import trackio
from datasets import load_dataset
from trl import SFTConfig, SFTTrainer
from peft import LoraConfig

def main(model="Qwen/Qwen2.5-1.5B-Instruct", r=16, lr=2e-4):
    ds = load_dataset("gretelai/synthetic_text_to_sql", split="train")
    trackio.init(project="text2sql", config={"model": model, "r": r, "lr": lr})
    cfg = SFTConfig(learning_rate=lr, num_train_epochs=3,
                    per_device_train_batch_size=16, report_to="trackio")
    peft = LoraConfig(r=r, lora_alpha=2 * r, task_type="CAUSAL_LM")
    SFTTrainer(model, args=cfg, train_dataset=ds, peft_config=peft).train()

if __name__ == "__main__":
    main()

````

- https://huggingface.co/spaces/abidlabs/gemma-text2sql-trackio

# LR & LoRA-rank sweep

---

### Sweep: r=16, lr=5e-4 wins

`Jul 02, 2026 · 06:24 UTC`

Swept 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.

- media/lr_rank_sweep.png
- https://huggingface.co/spaces/abidlabs/gemma-text2sql-trackio

# Prompt format ablation (chat vs completion)

# Synthetic data augmentation (self-instruct)

---

### Synth data: +3.1% exec acc (early)

`Jul 02, 2026 · 06:24 UTC`

Generating 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.


````python title=gen_synth.py
"""Self-instruct augmentation: sample real schemas, prompt a teacher model for
new (question, SQL) pairs, then keep only pairs whose SQL executes."""
import json, sqlite3, random
from huggingface_hub import InferenceClient

client = InferenceClient()

def augment(schemas, n_per_schema=8):
    out = []
    for schema in schemas:
        prompt = f"Given this schema, write {n_per_schema} diverse NL questions "\
                 f"and their SQLite queries as JSONL.\n{schema}"
        for line in client.text_generation(prompt, max_new_tokens=1024).splitlines():
            try:
                ex = json.loads(line)
                sqlite3.connect(":memory:").executescript(schema).execute(ex["sql"])
                out.append({**ex, "schema": schema})
            except Exception:
                continue
    return out

````

- https://huggingface.co/jobs/abidlabs/6a45b02733c08a2c0dae0348
- https://huggingface.co/buckets/abidlabs/jobs-artifacts

# Add Spider + WikiSQL to the eval suite

# Curriculum: order by join complexity

# Distill from a larger open model

---

### Plan & hypothesis

`Jul 02, 2026 · 06:24 UTC`

Plan: 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.

# Long-context schema eval @32k

# Full fine-tune vs LoRA comparison

# Error taxonomy & failure analysis

# CPU latency & throughput

# Final model card + release