Sqlmorpher Eval

---
name: sqlmorpher-eval
description: Evaluates an LLM's ability to generate correct SQL queries for transforming building energy data schemas. It measures how well different prompt strategies and iterative optimization handle complex schema mappings, pivoting, and aggregation in real-world smart building datasets. Use when the user wants to benchmark on Building Energy Data Transformation Benchmark, or asks about evaluating this task. Reports Execution Accuracy.
metadata:
  skill_kind: dataset_eval
  source_arxiv: 2309.01957
  bibtex_key: sharma2023sqlmorpher
  confidence: high
---

# sqlmorpher-eval

> Automatic Data Transformation Using Large Language Model: An Experimental Study on Building Energy Data — Sharma et al. (2023) (arXiv:2309.01957, 2023)

## What this evaluates

Evaluates an LLM's ability to generate correct SQL queries for transforming building energy data schemas. It measures how well different prompt strategies and iterative optimization handle complex schema mappings, pivoting, and aggregation in real-world smart building datasets.

## Datasets

- **Building Energy Data Transformation Benchmark** — total 105; splits: test (105)

## Metrics

- `Execution Accuracy` **(primary)** — range: [0, 1]
  - Ratio of correctly transformed cases to the total number of transformation cases. A case is counted as correct if the generated SQL passes experimental validation tests within 5 iterations.
- `Column Similarity` — range: [0, 1]
  - Average similarity score across all target attributes in a case, computed by comparing each column in the transformed dataset to its ground truth counterpart. Set to 0 if output generation fails.
- `Number of Iterations to Success` — range: other
  - Average number of prompt-response iterations required to achieve a column similarity score of 1.0, capped at 5 iterations per case.

## Input / output format

**Input**: Source schema, target schema, domain-specific attribute explanations, schema change hints, and optionally one demonstration example, formatted as a natural language prompt.

**Output**: A SQL query string that transforms the source table into the target schema.

## Scoring recipe

```python
def evaluate(predictions, golds):
    correct = 0
    sims = []
    iters = []
    for pred, gold in zip(predictions, golds):
        if passes_sandbox_validation(pred, gold, max_iter=5):
            correct += 1
            sims.append(compute_column_similarity(pred.df, gold.df))
            iters.append(iterations_used)
    exec_acc = correct / len(predictions)
    col_sim = sum(sims) / len(sims) if sims else 0.0
    iter_succ = sum(iters) / len(iters) if iters else 0.0
    return exec_acc, col_sim, iter_succ
```

## Common pitfalls

- Execution accuracy requires implementing an iterative retry loop with a hard cap of 5 iterations; omitting this loop will drastically underestimate performance.
- Column similarity is explicitly set to 0 for cases that fail to generate output data, heavily penalizing complete generation failures rather than partial schema matches.
- The benchmark uses real-world data from 21 companies with highly variable column names and formats, requiring careful prompt engineering to avoid LLM hallucination on domain-specific attributes.

## Evidence (verbatim from paper)

> We report the following metrics in the experimental study: Execution Accuracy: This metric is defined as the ratio of the number of correctly transformed cases to the total number of transformation cases. For each case, if the LLM can return the correct transformation query that passes the experimental validation tests as described in Sec.[III-C] within 5 iterations, it is considered a correctly transformed case. Column Similarity: We compute the similarity score for each column in the transformed dataset and its corresponding column in the ground truth target dataset (defined in Sec.[III-C]). As detailed in Sec.[III-C], we compute a similarity score for each column. We further define the column similarity per case as the average similarity scores of all target attributes in the case, the column similarity per group as the average similarity scores of all cases in the group, and the overall column similarity as the average similarity scores of all cases in all groups. The similarity score is set to zero for cases that fail to generate output data for similarity comparison.

## Citation

```bibtex
@misc{sharma2023sqlmorpher,
  title={Automatic Data Transformation Using Large Language Model: An Experimental Study on Building Energy Data},
  author={Sharma et al. (2023)},
  year={2023},
  note={arXiv:2309.01957}
}
```

- arXiv: 2309.01957