logbook.md

# gemma-sql

# gemma-sql

> Index of experiments. Click one to open its page. Edit this page freely — the table is just Markdown.

## Experiments

| Status | Experiment |
| --- | --- |
| in-progress | [Baseline: off-the-shelf Gemma-3-270m](#/baseline-off-the-shelf-gemma-3-270m) |
| done | [Finetune Gemma-3-270m (full SFT)](#/finetune-gemma-3-270m-full-sft) |
| in-progress | [LoRA SFT](#/lora-sft) |

# Baseline: off-the-shelf Gemma-3-270m

---

### Note

`Jul 02, 2026 · 00:28 UTC`

Baseline established. Off-the-shelf google/gemma-3-270m-it, zero-shot, on a fixed 1000-example held-out subset of gretelai/synthetic_text_to_sql (test split, seed 42): execution accuracy 22.6% (175/773), exact-match 3.0%. Metric = execution accuracy: build in-memory SQLite from sql_context (CREATE+INSERT), run gold vs predicted, compare result sets order-insensitively. 227/1000 examples excluded because the gold SQL is not SQLite-executable, so we only score against verifiable ground truth. Ran on HF Jobs (l4x1), 71s. Off-the-shelf outputs are frequently garbled.

- https://huggingface.co/datasets/gretelai/synthetic_text_to_sql
- https://huggingface.co/google/gemma-3-270m-it

---

### Note

`Jul 02, 2026 · 00:36 UTC`

Few-shot baseline: google/gemma-3-270m-it with 2 in-context examples reaches 27.8% execution accuracy (exact-match 8.0%) on the same 773 scored held-out examples, up from 22.6% zero-shot. This is the strongest off-the-shelf reference to beat with finetuning.

# Finetune Gemma-3-270m (full SFT)

---

### Note

`Jul 02, 2026 · 03:30 UTC`

Finetuning complete. Full fine-tune of base google/gemma-3-270m (no LoRA) on 100k gretel train examples, 3 epochs, assistant-only loss, Gemma-3-it chat template, lr 5e-5 cosine, eff batch 32, bf16. Ran 10714s (~3h) on HF Jobs l4x1. Pushed to abidlabs/gemma-3-270m-text2sql. Metrics streamed to Trackio (abidlabs/gemma-text2sql-trackio). Eval on identical held-out set launched next.

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

---

### Note

`Jul 02, 2026 · 06:08 UTC`

Full-SFT eval done: 73.35% execution accuracy (567/773), 40.5% exact-match, zero-shot on the identical 773-example held-out set. Massive jump over off-the-shelf baselines (22.6% zero-shot / 27.8% 2-shot). This is the target for the LoRA run to match at a fraction of the trainable params. Eval ran 3m43s on HF Jobs l4x1.

- https://huggingface.co/datasets/abidlabs/gemma-3-270m-text2sql-eval

# LoRA SFT

---

### Note

`Jul 02, 2026 · 06:09 UTC`

LoRA SFT launched on HF Jobs (l4x1, detached). Same data/template/loss/eval as full SFT — only difference is a LoRA adapter (r=16, alpha=32, dropout=0.05) on all attention + MLP projections (q,k,v,o,gate,up,down), rest frozen. lr 2e-4 (higher than full-FT's 5e-5, standard for LoRA), cosine, 3 epochs, eff batch 32, bf16, assistant-only loss, full ~100k train split. Adapter is merged into base before push so eval loads it identically. Target: match full-SFT's 73.35% exec acc at a fraction of trainable params.


````python title=train_text2sql_lora.py
# /// script
# requires-python = ">=3.10"
# dependencies = [
#   "torch",
#   "transformers>=4.56",
#   "trl>=0.12",
#   "peft>=0.13",
#   "datasets>=3.0",
#   "accelerate",
#   "trackio>=0.21.1",
#   "huggingface_hub",
# ]
# ///
"""
LoRA fine-tune of google/gemma-3-270m for natural-language -> SQL.

Identical data / chat template / loss / eval protocol as the full SFT
(train_text2sql.py) so the two are apples-to-apples; the ONLY difference is
that we train a small LoRA adapter instead of all weights. The adapter is
merged into the base model before push, so the resulting repo is a plain
CausalLM that eval_text2sql.py can load exactly like the full-FT model.

- Base weights: google/gemma-3-270m
- Tokenizer/chat template: google/gemma-3-270m-it (matches baseline eval)
- Data: gretelai/synthetic_text_to_sql train split, formatted as chat messages
- Loss: assistant-only (completion-only)
- Adapter: LoRA on attention + MLP projections
- Metrics streamed to Trackio; merged model pushed to the Hub.
"""
import argparse
from datasets import load_dataset
from peft import LoraConfig
from transformers import AutoModelForCausalLM, AutoTokenizer
from trl import SFTConfig, SFTTrainer

DATASET = "gretelai/synthetic_text_to_sql"
BASE = "google/gemma-3-270m"
CHAT_TOKENIZER = "google/gemma-3-270m-it"

SYSTEM = (
    "You are a text-to-SQL model. Given a database schema and a question, "
    "output a single valid SQLite query that answers the question. "
    "Output only the SQL query, nothing else."
)

def build_user(schema, question):
    return f"Schema:\n{schema}\n\nQuestion: {question}"

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--hub-model-id", default="abidlabs/gemma-3-270m-text2sql-lora")
    ap.add_argument("--epochs", type=float, default=3.0)
    ap.add_argument("--lr", type=float, default=2e-4)  # LoRA likes a higher LR than full FT
    ap.add_argument("--batch-size", type=int, default=16)
    ap.add_argument("--grad-accum", type=int, default=2)
    ap.add_argument("--max-length", type=int, default=768)
    ap.add_argument("--max-train", type=int, default=0, help="0 = full split")
    ap.add_argument("--lora-r", type=int, default=16)
    ap.add_argument("--lora-alpha", type=int, default=32)
    ap.add_argument("--lora-dropout", type=float, default=0.05)
    ap.add_argument("--space-id", default="abidlabs/gemma-text2sql-trackio")
    args = ap.parse_args()

    train = load_dataset(DATASET, split="train")
    if args.max_train:
        train = train.select(range(args.max_train))
    print(f"[train] {len(train)} examples", flush=True)

    def to_messages(ex):
        return {"messages": [
            {"role": "system", "content": SYSTEM},
            {"role": "user", "content": build_user(ex["sql_context"], ex["sql_prompt"])},
            {"role": "assistant", "content": ex["sql"]},
        ]}
    train = train.map(to_messages, remove_columns=train.column_names)

    tok = AutoTokenizer.from_pretrained(CHAT_TOKENIZER)
    model = AutoModelForCausalLM.from_pretrained(BASE, dtype="bfloat16")

    peft_config = LoraConfig(
        r=args.lora_r,
        lora_alpha=args.lora_alpha,
        lora_dropout=args.lora_dropout,
        bias="none",
        task_type="CAUSAL_LM",
        target_modules=["q_proj", "k_proj", "v_proj", "o_proj",
                        "gate_proj", "up_proj", "down_proj"],
    )

    cfg = SFTConfig(
        output_dir="out",
        num_train_epochs=args.epochs,
        per_device_train_batch_size=args.batch_size,
        gradient_accumulation_steps=args.grad_accum,
        learning_rate=args.lr,
        lr_scheduler_type="cosine",
        warmup_ratio=0.03,
        logging_steps=20,
        bf16=True,
        max_length=args.max_length,
        packing=False,
        assistant_only_loss=True,
        save_strategy="epoch",
        save_total_limit=1,
        push_to_hub=False,  # push the MERGED model manually below
        report_to="trackio",
        run_name="gemma3-270m-text2sql-lora",
        project="gemma-text2sql",
        trackio_space_id=args.space_id,
    )

    trainer = SFTTrainer(
        model=model,
        args=cfg,
        train_dataset=train,
        processing_class=tok,
        peft_config=peft_config,
    )
    trainer.train()

    # Merge the LoRA adapter into the base weights so the pushed repo is a
    # plain CausalLM -> eval_text2sql.py loads it identically to the full-FT model.
    print("[train] merging LoRA adapter into base weights", flush=True)
    merged = trainer.model.merge_and_unload()
    merged.push_to_hub(args.hub_model_id)
    tok.push_to_hub(args.hub_model_id)
    print("[train] done, pushed merged model to", args.hub_model_id, flush=True)

if __name__ == "__main__":
    main()

````

- https://huggingface.co/jobs/abidlabs/6a460099fb6818a83db2fd19