Skip to content

README.md

Latest commit

 

History

History
160 lines (120 loc) · 6.44 KB

README.md

File metadata and controls

160 lines (120 loc) · 6.44 KB
Raw

Fine-tuning Llama 3.1 8B for Text-to-SQL (QLoRA)

Turn a natural-language question plus a database schema into an executable SQL query.

Fine-tuned with QLoRA (4-bit NF4 base + LoRA adapters) on a single GPU, and evaluated with execution accuracy against real SQLite databases rather than by string-matching the output.

Results

Held-out test set of 500 examples, never seen during training.

Model Execution accuracy Valid SQL Exact match
Llama 3.1 8B — zero-shot 36.5% 96.9% 3.9%
Llama 3.1 8B — 3-shot prompted 59.8% 99.4% 58.2%
Llama 3.1 8B — QLoRA fine-tuned __% __% __%

Trained on 6,000 examples in __ minutes on a __. Adapter: ~40 MB (0.5% of model parameters trained).

loss curve

Reading the baselines

Two things jump out of these numbers.

Execution accuracy and exact match tell completely different stories. The zero-shot model writes valid SQL 96.9% of the time and gets 36.5% of answers right, yet matches the gold query string only 3.9% of the time. In other words, it's frequently writing a correct query in different words (WHERE a=1 AND b=2 instead of WHERE b=2 AND a=1, a different alias, a different but equivalent join). Exact match punishes all of that; execution accuracy doesn't. This gap is the entire reason the project evaluates by running the queries rather than comparing strings.

Three examples buy correctness and conformance. Going from zero-shot to 3-shot lifts execution accuracy from 36.5% to 59.8%. The examples genuinely help the model write better SQL. But the eye-catching jump is exact match: 3.9% → 58.2%. Most of that isn't new SQL knowledge; it's the model learning the house style from the examples (formatting, quoting, aliasing) so its output now lines up with the gold string. Once a model imitates the reference style, exact match and execution accuracy start measuring almost the same thing.

The bar for fine-tuning is 59.8%, not 36.5%. Beating the zero-shot model is trivial and proves nothing. The honest question is whether a fine-tuned adapter can beat a prompted model. So that's the number to compare against.

Note on the test set: 12 of the 500 test examples are excluded from scoring because the gold query itself fails to execute against the schema (malformed rows in the dataset, not model errors). All metrics are computed over the remaining 488.

Why execution accuracy

These two queries are identical:

SELECT player FROM t WHERE points = '25' AND year = '1994'   -- gold
SELECT player FROM t WHERE year = '1994' AND points = '25'   -- prediction

Exact match scores that 0. So the headline metric here is execution accuracy:

  1. Build an in-memory SQLite database from the schema.
  2. Populate it with random rows — seeded with the literals from the gold query, so WHERE year = '1994' actually matches something. Without this step both queries return [] and a broken model scores 100%.
  3. Run the gold query and the prediction. Compare the rows they return.
  4. Repeat across 3 independent random populations. A prediction is correct only if it agrees on all three — two different queries can coincidentally agree on one table, but rarely on three.

Limitation, stated plainly: the databases are synthetic, so this approximates the Spider-style execution accuracy you'd get from real populated databases. The execution_accuracy_informative metric in results/*.json restricts scoring to examples where the gold query returns a non-empty result, which is the stricter reading.

What this project gets right

Things that are easy to get wrong when fine-tuning a causal LM, and that quietly wreck results:

  • EOS on every target. Without it the model never learns to stop and rambles past the end of the query at inference.
  • Loss on the completion only. Prompt tokens are masked with -100. Training on the prompt spends capacity learning to reproduce schemas instead of learning to write SQL.
  • Left padding at generation. Right padding in batched decoder-only generation makes the model continue from pad tokens and emit garbage.
  • Greedy decoding. SQL is structured output. temperature=0.7, top_p=0.9 is for prose; sampling here only injects errors.
  • Seeded splits, and baselines measured first. A fine-tuning result without a baseline isn't a result.

Setup

git clone https://github.com/YOUR_USERNAME/llama-text2sql.git
cd llama-text2sql
pip install -r requirements.txt

Needs a GPU with ≥16 GB VRAM (free Colab T4 is enough).

Usage

# baselines
python -m src.evaluate --mode zero_shot
python -m src.evaluate --mode few_shot --k 3

# fine-tune
python -m src.train --train_size 6000 --num_train_epochs 1

# evaluate
python -m src.evaluate --mode finetuned --adapter outputs/llama31-8b-text2sql-qlora/final

# interactive demo
python -m src.generate --adapter outputs/llama31-8b-text2sql-qlora/final

Or run everything: bash scripts/run_all.sh Or open notebooks/01_finetune_text2sql.ipynb in Colab.

Repo layout

src/
  config.py     prompt template + all hyperparameters (single source of truth)
  data.py       splits, tokenization, completion-only label masking
  model.py      4-bit quantization + LoRA setup
  train.py      QLoRA training loop
  sql_exec.py   synthetic-DB execution matcher  <- the interesting part
  evaluate.py   execution accuracy for all three configurations
  generate.py   interactive demo
notebooks/      Colab walkthrough
results/        metrics, predictions, loss curve

Details
Base model NousResearch/Meta-Llama-3.1-8B (ungated mirror — no HF token needed)
Dataset b-mc2/sql-create-context (78k examples)
Quantization 4-bit NF4, double quant, bf16/fp16 compute
LoRA r=16, α=32, dropout=0.05, on all attention + MLP projections
Optimizer paged AdamW 8-bit, lr 2e-4, cosine schedule, 3% warmup
Effective batch 16 (4 × 4 grad accumulation)

Failure modes

License

MIT