README.md

---
# ---- Identity -------------------------------------------------------------
base_model: zai-org/GLM-4.7-Flash
base_model_relation: adapter
library_name: peft
pipeline_tag: text-generation
language:
  - en
license: mit   # verified via the Hub API: zai-org/GLM-4.7-Flash reports `mit`.
               # The training data is CC-BY-4.0 — see Licence below.

# ---- Discovery ------------------------------------------------------------
tags:
  - lora
  - qlora
  - peft
  - sft
  - trl
  - text-to-sql
  - sql

datasets:
  - b-mc2/sql-create-context

metrics:
  - exact_match
  - bleu
  - rouge

# ---- Structured evaluation ------------------------------------------------
model-index:
  - name: glm-4.7-flash-sql-create-context-lora
    results:
      - task:
          type: text-generation
          name: Text-to-SQL (natural language + CREATE TABLE schema -> SQL)
        dataset:
          type: sql-create-context-val
          name: sql-create-context derived validation split (400 pairs)
          split: validation
        metrics:
          - type: exact_match
            name: Exact match
            value: 0.805
          - type: bleu
            name: BLEU
            value: 0.940082
          - type: rouge
            name: ROUGE-L
            value: 0.986055
            args:
              rouge_type: rougeL
---

# GLM-4.7-Flash Text-to-SQL (LoRA)

Given a natural-language question and a `CREATE TABLE` schema, emits exactly one
SQL query answering that question against that schema. For natural-language
query interfaces over a known relational schema.

This is a **LoRA adapter for**
[zai-org/GLM-4.7-Flash](https://huggingface.co/zai-org/GLM-4.7-Flash), trained
with **QLoRA (4-bit NF4 base, bf16 compute)** via
[TRL](https://github.com/huggingface/trl) SFT.

## Model details

| | |
|---|---|
| Developed by | SASVA AI Model Cognition Labs (MCL) Team |
| Base model | [`zai-org/GLM-4.7-Flash`](https://huggingface.co/zai-org/GLM-4.7-Flash) |
| Base parameters | 30B total / 3B active (MoE) — 29,943,396,864 in the merged bf16 build |
| Architecture family | `glm4_moe_lite` (`Glm4MoeLiteForCausalLM`), 47 layers, hidden size 2048, vocab 154,880 |
| Adaptation | LoRA (`r=64`, `alpha=128`, `dropout=0.05`) |
| Trainable modules | `q_a_proj`, `q_b_proj`, `kv_a_proj_with_mqa`, `kv_b_proj`, `o_proj`, `gate_proj`, `up_proj`, `down_proj` |
| Training method | `qlora` (4-bit NF4, double quant, bf16 compute) |
| Refinement | none |
| Language | English (questions) / SQL (outputs) |
| License | MIT (inherited from the base model) |

Trainable parameters: **118,140,928** — **0.3945%** of the base. The adapter file
is 472,671,000 bytes (752 fp32 tensors: a `lora_A` + `lora_B` pair for each of
the 8 target modules across all 47 layers).

GLM-4.7-Flash uses Multi-head Latent Attention, so the attention target modules
are the MLA projections (`q_a_proj`/`q_b_proj`/`kv_a_proj_with_mqa`/`kv_b_proj`),
not `q_proj`/`k_proj`/`v_proj`. Targeting the conventional names would silently
adapt nothing.

## Intended use

**Direct use.** Translate one English question plus one `CREATE TABLE` schema
into one SQL query. The model was trained on a specific prompt shape and that
shape is part of the contract:

- System prompt (verbatim): *"You are a text-to-SQL engine. Given a
  natural-language question and a CREATE TABLE schema, output exactly one SQL
  query that answers the question against the provided schema. Output only the
  raw SQL query on a single line with no explanation, no markdown formatting,
  and no additional text."*
- User turn: the question, a blank line, then the schema inside a fenced code
  block.
- Applied through the tokenizer's chat template (`chat_template.jinja`, shipped
  in this repo) with `enable_thinking=False`. Do not concatenate strings by hand.
- The query is the **first line** of the generation; discard anything after it.

**Out of scope.**
- **Not validated against a live database.** The model is scored on string
  similarity to a reference query, never on execution. A syntactically perfect
  query can still be semantically wrong. Parse and, where you can, dry-run
  against the real schema before trusting output.
- **Never interpolate output into a privileged connection.** Treat generated SQL
  as untrusted input: run it read-only, with least privilege, on a connection
  that cannot write or drop.
- Multi-table joins, CTEs, window functions, subqueries, and DDL/DML are largely
  out of distribution — the training data is dominated by single-table
  `SELECT`s. Measured join accuracy is poor (see Limitations).
- Dialect is not controllable. The model reproduces the source corpus's
  conventions (double-quoted string literals, lower-cased comparison values),
  which are not portable to every engine.
- Not a general-purpose assistant. It emits a bare query, never prose.

## How to get started

```python
import torch
from transformers import AutoModelForCausalLM, AutoTokenizer, BitsAndBytesConfig
from peft import PeftModel

BASE = "zai-org/GLM-4.7-Flash"
ADAPTER = "SASVAAI/GLM-4.7-Flash-sql-create-context"

# 4-bit NF4 matches the numerics the adapter was trained against. A bf16 base
# also works and scores the same (see Merged-weights equivalence) but needs
# ~60 GB rather than ~22 GB.
bnb = BitsAndBytesConfig(
    load_in_4bit=True,
    bnb_4bit_quant_type="nf4",
    bnb_4bit_compute_dtype=torch.bfloat16,
    bnb_4bit_use_double_quant=True,
)

tokenizer = AutoTokenizer.from_pretrained(ADAPTER)
model = AutoModelForCausalLM.from_pretrained(
    BASE, quantization_config=bnb, dtype=torch.bfloat16, device_map="auto"
)
model = PeftModel.from_pretrained(model, ADAPTER)
model.eval()

SYSTEM = (
    "You are a text-to-SQL engine. Given a natural-language question and a "
    "CREATE TABLE schema, output exactly one SQL query that answers the question "
    "against the provided schema. Output only the raw SQL query on a single line "
    "with no explanation, no markdown formatting, and no additional text."
)

question = "Which kingdom has Suin as its capital?"
schema = "CREATE TABLE table_name_65 (name_of_kingdom VARCHAR, capital VARCHAR)"

messages = [
    {"role": "system", "content": SYSTEM},
    {"role": "user", "content": f"{question}\n\n```\n{schema}\n```"},
]
prompt = tokenizer.apply_chat_template(
    messages, tokenize=False, add_generation_prompt=True, enable_thinking=False
)
inputs = tokenizer(prompt, return_tensors="pt").to(model.device)

out = model.generate(**inputs, max_new_tokens=128, do_sample=False)
text = tokenizer.decode(out[0][inputs.input_ids.shape[-1]:], skip_special_tokens=True)
print(text.strip().splitlines()[0])
# -> SELECT name_of_kingdom FROM table_name_65 WHERE capital = "suin"
```

The base model is ~59 GB in bfloat16, or ~22 GB per GPU under 4-bit NF4.

> Decoding matters. This model was evaluated with greedy decoding
> (`do_sample=False`, `max_new_tokens=128`). Sampling will not reproduce the
> reported numbers.

**Serving note.** These adapter weights do **not** load as a vLLM LoRA on this
architecture — vLLM's MLA path asserts inside
`DeepSeekV2FusedQkvAProjLinear` because `q_a_proj` and `kv_a_proj_with_mqa` are
fused into one module that a LoRA cannot be attached to. A merged build of these
weights serves under vLLM without complaint. For vLLM deployment, merge first
(`peft.merge_and_unload()`).

## Training details

**Data.** A 4,000-pair subset of
[`b-mc2/sql-create-context`](https://huggingface.co/datasets/b-mc2/sql-create-context)
(78,577 pairs, itself derived from WikiSQL and Spider), split 90/10 by this
project's data-generation stage. Each record is
`{"instruction": <question>, "input": <CREATE TABLE ...>, "output": <SQL>}`.
The subset selection is a generated artifact, not a published split — the
`id`/`instruction`/`input`/`gold` tuples in `predictions.jsonl` are the
authoritative record of what was evaluated.

| | |
|---|---|
| Train samples | 3,600 |
| Validation samples | 400 |
| Prompt format | chat template + system prompt (see Intended use) |

### Method

| | |
|---|---|
| SFT method | `qlora` |
| Base quantisation during training | 4-bit NF4, double quant, bf16 compute |
| Refinement stage | none |
| Hardware | 4x NVIDIA H200 (141 GB), `torchrun --nproc_per_node=4` |

`qlora` is one of three methods considered for this model, alongside 8-bit LoRA
and attention-only 4-bit LoRA. All three were tried; `qlora` scored highest
every time it was compared against the other two on the same data.

No refinement stage ran; the published weights are the SFT adapter.

### Final hyperparameters

| Hyperparameter | Value | Source |
|---|---|---|
| `learning_rate` | 0.0002 | `training_args.bin` |
| `lr_scheduler_type` | cosine | `training_args.bin` |
| `num_train_epochs` | 3 | `training_args.bin` |
| `per_device_train_batch_size` | 1 | `training_args.bin` |
| `gradient_accumulation_steps` | 4 | `training_args.bin` |
| `max_length` | 2048 | `training_args.bin` |
| `warmup_ratio` | 0.05 | `training_args.bin` |
| `weight_decay` | 0.01 | `training_args.bin` |
| `optim` / `max_grad_norm` / `seed` | `adamw_torch` / 1.0 / 42 | `training_args.bin` |
| `bf16` / `gradient_checkpointing` | true / true (`use_reentrant=False`) | `training_args.bin` |
| `neftune_noise_alpha` / `packing` | None / false | `training_args.bin` |
| `lora_r` / `lora_alpha` / `lora_dropout` | 64 / 128 / 0.05 | `adapter_config.json` |
| `target_modules` | the 8 listed in Model details | `adapter_config.json` |

**Effective batch size: 16** (`1 x 4 x 4`). Optimizer steps: 675.

KD parameters are omitted deliberately — this is a `qlora` run, not
`bf16_lora_kd`, so `KD_ALPHA`/`KD_BETA`/`KD_TEMPERATURE` carry inert defaults
that would imply distillation that did not happen.

This configuration is **not a unique optimum**. Other configurations reached the
same `exact_match`; this one was published for being the simplest and cheapest
of them — fewest epochs, shortest training time — and because it scored
marginally higher on BLEU and ROUGE-L. Treat the values as a good working point,
not a tuned maximum.

**Observed training metrics.**

| | |
|---|---|
| Final train loss | 0.4594 |
| Mean train loss | 0.5646523337894016 |
| Train runtime | 6392.6844s |
| Total FLOPs | 2.3441226939026637e+17 |
| Throughput | 1.689 samples/s, 0.106 steps/s |

No eval loss was computed during training; the loop scores on generation, not
perplexity. Loss falls from 2.3882 at step 10 to 1.1077 at step 20 and 0.5940 by
step 170, then improves slowly to ~0.45 by step 660. The task is essentially
learned within the first quarter of epoch 1; epochs 2 and 3 together buy roughly
0.14 of training loss.

## Evaluation

**Protocol.** All 400 validation pairs, no sampling. Predictions generated
greedily (`do_sample=False`, `max_new_tokens=128`) through the same chat template
used in training, against a 4-bit NF4 base to match training numerics. The
predicted query is the **first line** of the generation, stripped. No constrained
decoding and no SQL grammar were applied. Exact match is byte equality against
the reference query; BLEU and ROUGE-L are computed over the same strings.

| Metric | Value |
|---|---|
| Exact match | 0.805000 (322 / 400) |
| BLEU | 0.940082 |
| ROUGE-L | 0.986055 |
| Samples | 400 |

**Baseline for comparison.** **Not measured.** The untuned
`zai-org/GLM-4.7-Flash` was never scored on this split, so these numbers
quantify the fine-tuned model's performance but do not establish how much of it
the fine-tuning is responsible for.

**This is a validation split, not a held-out test set.** Hyperparameters were
selected against it, so expect optimistic bias. A clean estimate needs a third
split that was never used for selection.

## Limitations and bias

**Exact match understates the model; BLEU and ROUGE-L overstate it.** The
0.805 / 0.940 / 0.986 spread is the story of this model. Exact match is byte
equality, so a semantically identical query loses the point on quoting or a
missing `DISTINCT`:

```
question: Find the states where have some college students in tryout and their decisions are yes.
schema:   CREATE TABLE tryout (cName VARCHAR, decision VARCHAR);
          CREATE TABLE college (state VARCHAR, cName VARCHAR)
gold:     SELECT DISTINCT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.decision = 'yes'
pred:     SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.decision = "yes"
```

Relaxing to case- and whitespace-insensitive comparison moves exact match from
0.805 to **0.8125** (325/400) — so only 3 of the 78 misses are pure formatting.
The other 75 are real semantic or structural errors. ROUGE-L at 0.986 mostly
measures that both strings are short SQL over the same table names; it is not
evidence of correctness.

**Output is never degenerate.** All 400 golds and all 400 predictions begin with
`SELECT`; the model never emitted prose, markdown, or an empty string. Format
compliance is not the failure mode.

**Joins are the failure mode.** Miss rate by gold-query feature:

| Gold query contains | n | misses | miss rate |
|---|---|---|---|
| `JOIN` | 13 | 10 | **76.9%** |
| `ORDER BY` | 3 | 1 | 33.3% |
| aggregate (`COUNT`/`SUM`/`AVG`/`MIN`/`MAX`) | 138 | 37 | 26.8% |
| `GROUP BY` | 8 | 2 | 25.0% |
| multi-predicate `WHERE` (`AND`/`OR`) | 123 | 27 | 22.0% |
| single-predicate `SELECT..WHERE` | 181 | 22 | 12.2% |

The model is reliable on the shape it saw constantly (one table, one predicate)
and unreliable on the shape it barely saw. **Do not deploy this on a
multi-table schema.** Note the join, `ORDER BY`, and `GROUP BY` rows rest on
13, 3, and 8 examples respectively — read them as a strong warning, not a precise
rate.

Complexity tracks length: correct predictions have a mean gold length of 10.6
tokens, misses 12.8.

**Dialect is baked in.** The model emits the source corpus's double-quoted
string literals and lower-cases comparison values. On engines where `"x"` is an
identifier rather than a string (PostgreSQL, ANSI mode), output will not run
unmodified.

**No execution or injection safety.** Correctness is measured only as string
similarity to a reference. Nothing here prevents a generated query from being
expensive, wrong, or destructive against a real database.

**Inherits all biases and limitations of the base model.** This adapter changes
0.3945% of the parameters and was not evaluated for social bias, safety, or
fairness.

## Merged-weights equivalence

A merged build of these weights (base + adapter folded into one standalone bf16
model, `W + (alpha/r) * B @ A`) was evaluated on the identical split:

| Metric | Adapter (4-bit base) | Merged (bf16) | Delta |
|---|---|---|---|
| Exact match | 0.805000 | 0.805000 | 0.000000 |
| BLEU | 0.940082 | 0.942079 | +0.001997 |
| ROUGE-L | 0.986055 | 0.987663 | +0.001608 |

**39 of 400 predictions differ** (9.75%) — far more churn than a bf16-trained
adapter would show, because merging into an unquantised base genuinely changes
the numerics the adapter was fitted against. The changes cancel exactly:
**10 predictions flip correct→incorrect and 10 flip incorrect→correct**, leaving
exact match identical and BLEU/ROUGE-L marginally higher.

So a merged distribution is behaviourally equivalent in aggregate but not
prediction-for-prediction. Merging is also the practical route to vLLM serving
(see Serving note). MIT permits distributing derivative works, so publishing a
merged build is allowed.

## Environmental impact

| | |
|---|---|
| Hardware | 4x NVIDIA H200 (141 GB) |
| Training time | 106.5 minutes (6392.6844s) |
| Cloud provider / region | on-premise |

Covers the training of these published weights only. It excludes the wider
hyperparameter search that selected them, which cost substantially more.

## Framework versions

- PEFT 0.18.1
- TRL: 1.0.0
- Transformers: 5.7.0.dev0
- Pytorch: 2.5.1+cu121
- Datasets: 4.8.4
- Tokenizers: 0.22.2
- bitsandbytes: 0.49.2

`transformers` is a git-main build: GLM-4.7-Flash's `Glm4MoeLite` architecture is
not in the stable PyPI release.

## Licence

Adapter weights: **MIT**, inherited from
[`zai-org/GLM-4.7-Flash`](https://huggingface.co/zai-org/GLM-4.7-Flash)
(verified via the Hub API). Training data:
[`b-mc2/sql-create-context`](https://huggingface.co/datasets/b-mc2/sql-create-context),
licensed **CC-BY-4.0** — downstream use should carry that attribution.

## Citation

```bibtex
@misc{glm47flash_sql_create_context_lora_2026,
  title  = {GLM-4.7-Flash Text-to-SQL (LoRA)},
  author = {{SASVA AI Model Cognition Labs (MCL) Team}},
  year   = {2026},
  url    = {https://huggingface.co/SASVAAI/GLM-4.7-Flash-sql-create-context}
}
```