---
license: bsd-3-clause
base_model: Salesforce/codet5p-220m
library_name: transformers
pipeline_tag: text-generation
language:
- en
datasets:
- gretelai/synthetic_text_to_sql
tags:
- sql
- sql-to-text
- code
- codet5p
- text2text-generation
model-index:
- name: codet5p-sql2text
  results:
  - task:
      type: text2text-generation
      name: SQL-to-Text
    dataset:
      name: gretelai/synthetic_text_to_sql
      type: gretelai/synthetic_text_to_sql
      split: test
    metrics:
    - type: bleu
      name: BLEU
      value: 33.1003
    - type: rouge1
      name: ROUGE-1
      value: 66.8589
    - type: rouge2
      name: ROUGE-2
      value: 45.1124
    - type: rougel
      name: ROUGE-L
      value: 57.0609
---

# SQL-to-Text (Salesforce/codet5p-220m)

`Salesforce/codet5p-220m` fine-tuned to explain a SQL query in plain English.

The direction is **SQL -> natural language**: the model takes a query (and,
optionally, the DDL of the tables it touches) and returns a sentence describing
what that query does. It does *not* generate SQL from a question.

## Prompt format

Inputs follow one fixed template; training, evaluation and inference all build
it with the same function, so they cannot drift apart. The schema block is
dropped when no DDL is supplied, and when it is supplied only `CREATE TABLE ...` statements are kept.

```
Explain the following SQL query.

Schema:
<CREATE TABLE statements>

SQL:
<the query>
```

## Usage

```python
from transformers import AutoModelForSeq2SeqLM, AutoTokenizer

model_id = "thealper2/codet5p-sql2text"
tokenizer = AutoTokenizer.from_pretrained(model_id)
model = AutoModelForSeq2SeqLM.from_pretrained(model_id)

schema = "CREATE TABLE employees (id INT, name TEXT, salary INT, dept_id INT);"
sql = "SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;"
prompt = f"Explain the following SQL query.\n\nSchema:\n{schema}\n\nSQL:\n{sql}"

inputs = tokenizer(
    prompt,
    return_tensors="pt",
    truncation=True,
    max_length=256,
)
outputs = model.generate(
    **inputs,
    num_beams=4,
    max_new_tokens=128,
    min_new_tokens=5,
    early_stopping=True,
)
print(tokenizer.decode(outputs[0], skip_special_tokens=True))
```

## Training data

[`gretelai/synthetic_text_to_sql`](https://huggingface.co/datasets/gretelai/synthetic_text_to_sql), mapping
`sql` + `sql_context`
to `sql_explanation`.

Preprocessing drops rows that are too short to be a real explanation, removes
exact duplicates and duplicate *inputs*, and removes any training row whose
input also appears in the official test split, so the reported test scores are
not inflated by leakage. The validation split is
3% of the cleaned train
split (seed 42).

Sequence lengths were chosen from the measured token-length distribution:
source 256 tokens, target
128 tokens.


## Training procedure

| Hyper-parameter | Value |
| --- | --- |
| Epochs | 3.00 |
| Learning rate | 0.0001 |
| LR schedule | linear |
| Warmup ratio | 0.0500 |
| Weight decay | 0.0100 |
| Optimiser | adamw_torch |
| Per-device train batch size | 16 |
| Gradient accumulation | 4 |
| Max gradient norm | 1.00 |
| Model selection | eval_rougeL |
| Seed | 42 |
| Effective batch size | 64 |

Trained on a single NVIDIA GeForce RTX 5060 Ti (15.9 GB) with torch 2.11.0+cu128, bf16 mixed precision.

Wall-clock training time: 84 minutes.

## Evaluation

Scored by `evaluate.py` on the full splits with beam search (num_beams=4).

### Generation quality

| Metric | Validation | Test | _meta |
| --- | --- | --- | --- |
| Examples | 2989 | 5850 | - |
| BLEU | 33.65 | 33.10 | - |
| ROUGE-1 | 67.15 | 66.86 | - |
| ROUGE-2 | 45.76 | 45.11 | - |
| ROUGE-L | 57.58 | 57.06 | - |
| Mean generated length | 36.37 | 35.69 | - |
| Loss | 0.5829 | 0.5923 | - |

### SQL-aware faithfulness

Recall metrics ask whether the explanation mentions what the query actually does; the *rate* metrics are error rates, where lower is better -- they measure claims the query does not support.

| Metric | Validation | Test |
| --- | --- | --- |
| Examples | 2989 | 5850 |
| Operation recall | 98.53 | 98.40 |
| Aggregation recall | 98.72 | 98.57 |
| Join mention recall | 98.21 | 98.74 |
| Join table coverage | 98.34 | 98.56 |
| Condition column coverage | 82.98 | 81.99 |
| Condition value coverage | 86.18 | 87.24 |
| Operation over-claim rate | 5.47 | 5.29 |
| Unsupported number rate | 3.98 | 3.18 |
| Unsupported quoted-string rate | 3.20 | 3.18 |
| Unsupported entity rate | 1.38 | 1.72 |

## Limitations

* Trained on synthetic queries and synthetic explanations, so the phrasing
  reflects that generator's style rather than how a particular team documents
  its own queries.
* Explanations are grounded in the query text, not in the data: the model
  cannot know what a column means beyond its name.
* Condition coverage is the weakest area -- long `WHERE` clauses lose some
  columns and literals -- so an explanation may describe a filter less
  precisely than the query applies it. Do not rely on it as an audit of what a
  query returns.
* Inputs are truncated past the configured source length, so very large schemas
  are only partially visible to the model.
* English only.

## Reproduction

```bash
make preprocess
make train
make evaluate
```

Base model: [`Salesforce/codet5p-220m`](https://huggingface.co/Salesforce/codet5p-220m).