README.md

---
license: apache-2.0
base_model: Snowflake/Arctic-Text2SQL-R1-7B
library_name: peft
tags:
- text-generation
- transformers
- peft
- lora
- text2sql
- sql
- postgres
- pgvector
- bf16
- qwen2
- arctic
language:
- en
pipeline_tag: text-generation
model-index:
- name: text2sql-7b-v4-16
  results:
  - task:
      type: text-generation
      name: PostgreSQL Text2SQL
    dataset:
      name: MongoDB eval_data_FT.eval_data_F_v4
      type: private
      split: validation
    metrics:
    - type: exact_match
      value: 1.0
      name: Exact match
    - type: accuracy
      value: 1.0
      name: PostgreSQL syntax accuracy
    - type: accuracy
      value: 1.0
      name: pgvector usage accuracy
---

# text2sql-7b-v4-16


## v4 Update Notes

This repository is the v4 follow-up to the earlier `ihebaker10/text2sql-7b-v3-16` line. The v4 run uses the `eval_data_FT.eval_data_F_v4` collection and removes the old multi-stage/GRPO workflow.

Key v4 changes:

- One-stage supervised fine-tuning only
- No GRPO stage
- No schema parsing or generation patching
- Raw schema text kept in the prompt
- Balanced loaded sample: 100 SQL-only and 100 pgvector/semantic records
- Validation split: 20 held-out records
- Answer-only loss with prompt-side truncation so SQL labels are preserved
- Published artifact is a PEFT LoRA adapter for `Snowflake/Arctic-Text2SQL-R1-7B`

## Model Details

`text2sql-7b-v4-16` is a PEFT LoRA adapter fine-tuned from `Snowflake/Arctic-Text2SQL-R1-7B` for PostgreSQL text-to-SQL generation on the v4 HSE/retex schema dataset.

- Model type: causal language model adapter, text generation
- Base model: `Snowflake/Arctic-Text2SQL-R1-7B`
- Fine-tuning method: PEFT LoRA adapter
- Backend: `transformers_peft_lora`
- Dataset version: `0.1.4`
- Output format: one fenced `sql` block
- Precision: BF16
- Adapter tensor format: Safetensors
- Published artifact: LoRA adapter plus tokenizer/config/metrics, not a merged full base-model checkpoint

## Intended Technical Use

Use this adapter to generate PostgreSQL SQL from a natural-language question plus schema context. The training/evaluation data covers two modes:

- `exact_sql`: deterministic PostgreSQL without vector search
- `semantic_pgvector`: PostgreSQL using `embed_query(...)` and pgvector distance operators when semantic retrieval is required

Generated SQL should still be parsed, reviewed, and executed only in a controlled database environment.

## How to Use

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

base_model = "Snowflake/Arctic-Text2SQL-R1-7B"
adapter_id = "ihebaker10/text2sql-7b-v4-16"

tokenizer = AutoTokenizer.from_pretrained(base_model, trust_remote_code=True)
if tokenizer.pad_token is None:
    tokenizer.pad_token = tokenizer.eos_token

base = AutoModelForCausalLM.from_pretrained(
    base_model,
    torch_dtype=torch.bfloat16,
    device_map="auto",
    trust_remote_code=True,
)
model = PeftModel.from_pretrained(base, adapter_id)
model.eval()

prompt = """System:
You are an expert PostgreSQL and pgvector text-to-SQL model.
Return exactly one fenced sql block.

User:
Task: Generate one PostgreSQL query.
Mode: exact_sql

[Schema DDL]
CREATE TABLE example_table (id bigint PRIMARY KEY, status int);

[Question]
How many rows are active?

Assistant:
"""

inputs = tokenizer(prompt, return_tensors="pt").to(model.device)
with torch.no_grad():
    output_ids = model.generate(
        **inputs,
        max_new_tokens=384,
        do_sample=False,
        pad_token_id=tokenizer.pad_token_id,
        eos_token_id=tokenizer.eos_token_id,
    )
print(tokenizer.decode(output_ids[0, inputs["input_ids"].shape[1]:], skip_special_tokens=True))
```

## Training Data

The v4 notebook loaded data from a private MongoDB collection `eval_data_FT.eval_data_F_v4`. The uploaded repository does not include the private raw dataset or database credentials.

Split / Type | Examples | Share
--- | ---: | ---:
Loaded SQL-only (`is_sql_only=true`) | 100 | 50.0%
Loaded pgvector/semantic (`is_sql_only=false`) | 100 | 50.0%
Training | 180 | 90.0%
Validation | 20 | 10.0%

Data handling:

- One-stage SFT only
- No GRPO
- No schema parsing
- No SQL generation patching
- Dataset kept as-is after loading
- Loaded set was already balanced 50/50 by `is_sql_only`
- Prompt uses raw schema text and answer-only loss

## Training Procedure

Training used one PEFT LoRA supervised fine-tuning stage.

Parameter | Value
--- | ---
Base model | `Snowflake/Arctic-Text2SQL-R1-7B`
Backend | `transformers_peft_lora`
Epochs | `3`
LoRA rank | `64`
LoRA alpha | `128`
LoRA dropout | `0.05`
Target modules | `q_proj, k_proj, v_proj, o_proj, gate_proj, up_proj, down_proj`
Learning rate | `0.0001`
Weight decay | `0.01`
Warmup ratio | `0.05`
Micro batch size | `1`
Gradient accumulation | `16`
Effective batch size | `16`
Max sequence length | `2048`
Precision | `bf16=True`, `fp16=False`
Gradient checkpointing | `True`
Validation every steps | `50`
Checkpoint every steps | `50`

Parallelism in this run:

Parameter | Value
--- | ---
World size | `1`
Tensor parallel size | `1`
Pipeline parallel size | `1`
Context parallel size | `1`
Data parallel size | `1`

This v4 run used the local single-GPU Transformers/PEFT backend. TP/PP values are recorded for NeMo compatibility, but TP/PP were not active because `world_size=1`.

## Validation Metrics

Validation was run on 20 held-out examples: 10 SQL-only and 10 pgvector/semantic examples.

Label | n | SQL output acc | PostgreSQL syntax acc | Dialect acc | ILIKE policy acc | pgvector usage acc | Exact match
--- | ---: | ---: | ---: | ---: | ---: | ---: | ---:
SQL-only | 10 | 1.000 | 1.000 | 1.000 | 1.000 | 1.000 | 1.000
pgvector | 10 | 1.000 | 1.000 | 1.000 | 1.000 | 1.000 | 1.000
Combined | 20 | 1.000 | 1.000 | 1.000 | 1.000 | 1.000 | 1.000

Confusion matrix for pgvector mode detection on combined validation:

Metric | Value
--- | ---:
True positives | 10
False positives | 0
False negatives | 0
True negatives | 10
pgvector precision | 1.000
pgvector recall | 1.000
SQL-only specificity | 1.000

Evaluation gates:

Metric | Required | Actual | Status
--- | ---: | ---: | ---
SQL output accuracy | 0.95 | 1.000 | pass
PostgreSQL syntax accuracy | 0.98 | 1.000 | pass
PostgreSQL dialect accuracy | 1.00 | 1.000 | pass
ILIKE policy accuracy | 1.00 | 1.000 | pass
pgvector usage accuracy | 0.95 | 1.000 | pass

## Target SQL Verification

Before training, target SQL verification checked 7862 target rows:

Metric | Value
--- | ---:
PostgreSQL syntax accuracy | 1.000
MySQL-free accuracy | 1.000
ILIKE policy accuracy | 1.000
pgvector usage accuracy | 1.000
Failed rows | 0

## Repository Contents

- `adapter_model.safetensors`: LoRA adapter weights
- `adapter_config.json`: PEFT configuration
- tokenizer files from the final training run
- `metrics/`: validation CSV and gate JSON files
- `tools/train_text2sql_peft.py`: training source used for this run
- `notebooks/arctic_text2sql_finetune_nvidia.sanitized.ipynb`: sanitized notebook copy with credentials removed
- training/split/config metadata JSON files

## Limitations

- Metrics are from a small held-out validation set of 20 examples.
- The raw training dataset is private and not included.
- The artifact is a LoRA adapter, not a standalone merged full model.
- Generated SQL can be syntactically valid but still unsuitable for a live database without review.
- The model is specialized for the schema/prompt style used in the v4 notebook.

## Safety and Privacy

The notebook copy is sanitized before upload. MongoDB credentials and private connection strings are not included in this repository.