AlioLeuchtmann/qwen-3.5-sql-27B-Distill-2B

Qwen3.5-2B · StructCoT SQL (27B-Distilled)

The smallest model in the top tier of the BIRD single-model leaderboard
A 2B-parameter text-to-SQL model that outperforms models over 500× its size on the official BIRD benchmark.

Qwen3.5-2B fine-tuned for SQLite text-to-SQL through execution-verified structured chain-of-thought distillation from a Qwen 27B teacher. Evaluated officially by the BIRD team on the hidden test set.

• No reinforcement learning — no GRPO/PPO/DPO stage; the entire gain comes from execution-verified rejection-sampled distillation
• No test-time thinking — non-thinking decoding with a compact structured analysis (~500 output tokens), not long reasoning chains;

Official BIRD test-set results (https://bird-bench.github.io/)

Evaluated by the BIRD team (majority@7 self-consistency, single model, single A100):

metric	simple (949)	moderate (555)	challenging (285)	total (1789)
Execution Accuracy (EX)	77.13	62.70	50.88	68.47
Soft F1	77.46	63.87	53.41	69.41
R-VES	69.25	55.93	46.47	61.49

Size-for-score context (official BIRD test EX, Single Trained Model Track)

model	size	test EX
Infly-RL-SQL-32B (inftech.ai)	32B	70.60*
Arctic-Text2SQL-R1-7B (Snowflake)	7B	70.43*
Claude Opus 4.6 (baseline)	frontier API	70.15
XiYanSQL-QwenCoder-32B (Alibaba)	32B	69.03*
Arctic-ExCoT-70B (Snowflake)	70B	68.53
this model	2B	68.47*
Arctic-ExCoT-32B (Snowflake)	32B	68.19
Qwen3-Coder-480B-A35B (baseline)	480B (MoE)	68.14
Align-SQL-7B	7B	67.97*
Claude 4.5 Sonnet (baseline)	frontier API	66.85
Align-SQL-3B	3B	66.41*
GLM-4.7 (baseline)	frontier	62.94
DeepSeek-R1 (baseline)	671B (MoE)	60.93
Kimi-K2-Thinking (baseline)	~1T (MoE)	59.87

* Few (1–7 candidates) · ** Many (8–32) · *** Scale (>32) · no mark = no self-consistency

At 2B parameters this model ties a 70B specialist, and outperforms frontier general-purpose LLMs — including Claude 4.5 Sonnet, DeepSeek-R1, and Kimi-K2-Thinking — as well as GPT-4o-based multi-stage pipelines and is only beaten by sophisticated specialists at least 2.5x times its Size. (Frontier-LLM rows are their official BIRD baseline entries; this model is a task-specialized fine-tune with self-consistency @7, declared as "Few" on the single-model track.)

Why this matters: distillation as a path to expert small models

This model is evidence for a broader thesis: in specialized domains, knowledge distillation can compress most of a large model's task competence into a model orders of magnitude smaller — here, ~93% of a 27B teacher inside 2B parameters, with no reinforcement learning and no test-time reasoning chains. The key enabler is that text-to-SQL is a verifiable domain: every candidate trace can be executed against the real database and checked against the gold result, so the training corpus can be filtered to contain only demonstrably correct reasoning. Wherever such a verifier exists — SQL execution, compilers, type checkers, simulators — the same recipe applies: sample the teacher broadly, keep only what provably works, and fine-tune small.

The practical upside is significant. A 2B specialist runs on a single consumer GPU (or CPU/edge hardware), answers in a few hundred tokens instead of thousand-token thinking chains, keeps data on-premises, and costs a fraction of a cent per API call — while, on its domain, outperforming general-purpose models hundreds of times its size. General frontier models remain the right tool for open-ended work, but for a fixed, well-defined task, this result suggests the efficient frontier is not a bigger generalist — it is a small model taught by one.

How it was trained

Every training target is an execution-verified chain-of-thought trace: a trace only enters the corpus if its final SQL, executed against the real database, reproduces the gold result set. No unverified text is ever trained on.

Stage 1 — Distillation SFT. 50,572 structured-CoT traces rejection-sampled from a Qwen 27B teacher over BIRD train, Spider, and synthetic schema corpora (multiple sampling rounds per item; longest correct trace kept). Each trace follows a 7-section template: question/hint mapping → schema selection → join path → filters → aggregation → edge checks → final checklist + SQL. 3 epochs, lr 2e-5, effective batch 32, NEFTune 5, bf16.

Stage 2 — Column-description anneal. BIRD databases ship per-column descriptions (database_description/*.csv); the schema prompt is augmented with them as comment blocks, and the model is annealed for 1 epoch at lr 3e-6 (cosine→0) on BIRD-train traces re-paired with these enriched prompts, plus 10% original-format replay. This stage alone adds ≈ +2.5 EX.

How to use it

The model expects the OmniSQL-style prompt (schema DDL + example rows + question + evidence hint) and answers with a structured analysis ending in a fenced SQL block — always extract the last sql fence. Generate in non-thinking mode.

"""Prompting qwen-3.5-sql-27B-Distill-2B (BIRD-style text-to-SQL)."""
import re, sqlite3
from collections import Counter
from transformers import AutoTokenizer
from vllm import LLM, SamplingParams

MODEL = "AlioLeuchtmann/qwen-3.5-sql-27B-Distill-2B"
BT = "`" * 3                      # code-fence marker used inside the prompt template
FENCE = re.compile(rf"{BT}(?:sqlite|sql)?\s*(.*?){BT}", re.S)

TEMPLATE = """Task Overview:
You are a data science expert. Below, you are provided with a database schema and a natural language question. Your task is to understand the schema and generate a valid SQL query to answer the question.

Database Engine:
SQLite

Database Schema:
{db_details}
This schema describes the database's structure, including tables, columns, primary keys, foreign keys, and any relevant relationships or constraints.

Question:
{question}

Instructions:
- Make sure you only output the information that is asked in the question. If the question asks for a specific column, make sure to only include that column in the SELECT clause, nothing more.
- The generated query should return all of the information asked in the question without any missing or extra information.
- Before generating the final SQL query, please think through the steps of how to write the query.

Output Format:
In your answer, please enclose the generated SQL query in a code block:
{fence}sql
-- Your SQL query
{fence}

Take a deep breath and think step by step to find the correct SQL query."""


def build_schema(db_path, descriptions=None, num_rows=3, max_cell=64):
    """DDL + optional column-description comments + a few example rows per table."""
    conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
    conn.text_factory = lambda b: b.decode("utf-8", errors="replace")
    cur = conn.cursor()
    cur.execute("SELECT name, sql FROM sqlite_master "
                "WHERE type='table' AND name != 'sqlite_sequence'")
    blocks = []
    for table, ddl in cur.fetchall():
        block = (ddl or "").strip()

        # column descriptions (BIRD ships these in database_description/*.csv)
        desc = (descriptions or {}).get(table.lower(), {})
        if desc:
            cols = [c[1] for c in cur.execute(f'PRAGMA table_info("{table}")')]
            lines = [f"-- {c}: {desc[c.lower()]}" for c in cols if c.lower() in desc]
            if lines:
                block += f"\n/* Column descriptions for {table}:\n" + "\n".join(lines) + "\n*/"

        # example rows
        try:
            cur.execute(f'SELECT * FROM "{table}" LIMIT {num_rows}')
            rows, names = cur.fetchall(), [d[0] for d in cur.description]
            if rows:
                def clip(v):
                    s = "NULL" if v is None else str(v).replace("\n", " ")
                    return s[:max_cell] + "..." if len(s) > max_cell else s
                header = " | ".join(names)
                body = "\n".join(" | ".join(clip(v) for v in r) for r in rows)
                block += (f"\n/* {len(rows)} example rows from {table}:\n{header}\n"
                          f"{'-' * min(len(header), 120)}\n{body}\n*/")
        except sqlite3.OperationalError:
            pass
        blocks.append(block)
    conn.close()
    return "\n\n".join(blocks)


def build_prompt(schema, question, evidence=""):
    """Evidence/hint is appended to the question, exactly as in BIRD."""
    if evidence and evidence.strip().lower() not in ("", "none"):
        question = f"{question}\n\nHint: {evidence}"
    return TEMPLATE.format(db_details=schema, question=question, fence=BT)


def extract_sql(text):
    """The model emits a structured analysis first — always take the LAST fence."""
    blocks = FENCE.findall(text or "")
    return blocks[-1].strip() if blocks else ""


# --------------------------------------------------------------- inference
tok = AutoTokenizer.from_pretrained(MODEL)
llm = LLM(model=MODEL, dtype="bfloat16", max_model_len=16384)


def chat(prompt):
    return tok.apply_chat_template(
        [{"role": "user", "content": prompt}],
        tokenize=False, add_generation_prompt=True,
        enable_thinking=False,        # non-thinking mode: the model is trained this way
    )


def generate_sql(prompt):
    """Greedy decoding — one query."""
    out = llm.generate([chat(prompt)], SamplingParams(temperature=0.0, max_tokens=2048))
    return extract_sql(out[0].outputs[0].text)


def majority_at_7(prompt, db_path, k=6, temperature=0.6):
    """Self-consistency: 1 greedy + k sampled candidates, voting by executed result set.
    This is how the reported scores were produced (~+5 EX over greedy)."""
    greedy = llm.generate([chat(prompt)], SamplingParams(temperature=0.0, max_tokens=2048))
    sampled = llm.generate([chat(prompt)],
                           SamplingParams(n=k, temperature=temperature, max_tokens=2048))
    candidates = [extract_sql(greedy[0].outputs[0].text)] + \
                 [extract_sql(o.text) for o in sampled[0].outputs]

    def run(sql):
        try:
            conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
            conn.text_factory = lambda b: b.decode("utf-8", errors="replace")
            rows = frozenset(conn.execute(sql).fetchall())
            conn.close()
            return rows
        except Exception:
            return None

    results = {sql: run(sql) for sql in dict.fromkeys(candidates)}   # dedupe, keep vote weight
    votes = Counter()
    for sql in candidates:
        if results[sql] is not None:
            votes[results[sql]] += 1
    if not votes:
        return candidates[0]                                         # nothing ran: greedy fallback
    winner = votes.most_common(1)[0][0]
    return next(sql for sql in candidates if results.get(sql) == winner)


# --------------------------------------------------------------- example
db = "california_schools.sqlite"
prompt = build_prompt(
    build_schema(db),
    "What is the highest eligible free rate for K-12 students in Alameda County?",
    evidence="eligible free rate for K-12 = `Free Meal Count (K-12)` / `Enrollment (K-12)`",
)

print(generate_sql(prompt))            # greedy
print(majority_at_7(prompt, db))       # self-consistency (reported setting)

Tips for best results

• Take the last ```sql fence. Everything before it is the model's reasoning; naive first-match extraction will grab a fragment from the analysis.
• Include 3 example rows per table. They are part of the training-time prompt format.
• Pass column descriptions when the database has them. BIRD ships them in database_description/*.csv; the model was explicitly trained to exploit them (worth ≈ +1.4 EX on its own).
• Keep enable_thinking=False. The whole lineage is trained on plain structured CoT, not on <think> traces.
• Majority@7 voting by executed result set gives ≈ +5 EX over greedy — that is the setting used for the reported benchmark numbers.

Intended use & limitations

Built for SQLite text-to-SQL (BIRD-style analytical questions with evidence hints). Not instruction-tuned for general chat. Performance on the challenging tier (50.9 EX) still trails large models — complex multi-step reasoning remains the 2B's bound.

Acknowledgements

Built on Qwen3.5-2B with a Qwen 27B teacher. Evaluated by the BIRD team (bird-bench.github.io). Developed by Alio Leuchtmann.