Sqlmorpher Eval

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

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

@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