Benchmarking LLMs on ETL Logic Synthesis: Can AI Truly Replace Data Pipeline Scripting?

A developer built the ETL-to-Python Code Synthesis Benchmark, a Kaggle submission that tests whether large language models can translate legacy visual ETL node graphs from tools like Knime, Alteryx and SSIS into vectorized pandas/polars code. Under deterministic zero-shot settings (temperature = 0.0), Google DeepMind's gemini-3.8-flash and gemini-2.5-pro both passed all four tasks (100% accuracy, avg score 1.00), with flash averaging 14.89 s API latency versus 33.78 s for pro, while both models avoided iterative df.iterrows() loops in favor of vectorized groupby cumsum and rank operations.

This is a submission for the Kaggle Benchmarking Challenge https://dev.to/challenges/kaggle-2026-09-23 In enterprise data engineering, migrating visual ETL pipelines from tools like Knime , Alteryx , or SSIS or complex business pseudocode into performant, vectorized Python pandas / polars is one of the most critical and recurring challenges. While standard benchmarks evaluate generic programming puzzles or synthetic LeetCode algorithms, real-world data pipelines break due to subtle edge cases. I built the ETL-to-Python Code Synthesis Benchmark to evaluate whether LLMs can synthesize clean, idiomatic, and robust Python code from visual workflow specifications. php flowchart TD Start "🚨 Input: Legacy Visual ETL Node Graph" -- T1 "Task 01: Left Join & Imputation<br/ • Coerce nulls<br/ • Calculate is vip flag" Start -- T2 "Task 02: Regex Extraction<br/ • Parse key-value logs<br/ • Retain corrupted rows" Start -- T3 "Task 03: Cumulative Windows<br/ • Running total cumsum <br/ • Intra-department rank" Start -- T4 "Task 04: Matrix Reshaping<br/ • Melt wide quarters<br/ • Flatten MultiIndex headers" T1 -- Sandbox "🧪 Sandboxed PyTest Execution Engine" T2 -- Sandbox T3 -- Sandbox T4 -- Sandbox Sandbox -- Leaderboard "🏆 Sub-millisecond DataFrame Assertion Leaderboard" style Start fill: 1e1e2e,stroke: 89b4fa,color: cdd6f4 style Sandbox fill: 313244,stroke: f9e2af,color: cdd6f4 style Leaderboard fill: 14532d,stroke: 22c55e,color: f0fdf4 etl 01 Joiner & Missing Value Imputation with Type Coercion : is vip . etl 02 Regex Extractor & Multi-Column Sanitizer : etl 03 GroupLoop to Vectorized Cumulative Windows : .cumsum , target achievement ratios, rolling 3-month averages, and intra-department dense rankings. etl 04 Unpivoting, Pivoting & Multi-Level Column Flattening : Each task runs inside an automated Python sandbox that tests DataFrame structural integrity, exact type fidelity, and output values under sub-millisecond execution times. I evaluated modern state-of-the-art models from Google DeepMind under deterministic zero-shot settings temperature = 0.0 : gemini-3.8-flash gemini-2.5-pro | Model | Accuracy Passed / Total | Avg Score | Avg API Latency | Sandbox Assertion Speed | |---|---|---|---|---| | 🥇 gemini-3.8-flash | 100.0% 4/4 | 1.00 | 14.89 s | ~13.4 ms | | 🥈 gemini-2.5-pro | 100.0% 4/4 | 1.00 | 33.78 s | ~16.0 ms | | Task ID | Description | gemini-3.8-flash | gemini-2.5-pro | |---|---|---|---| | etl 01 | Left Join, Missing Values & Type Coercion | ✅ PASS | ✅ PASS | | etl 02 | Regex Extraction & Edge-Case Sanitization | ✅ PASS | ✅ PASS | | etl 03 | Cumulative Windows & Department Ranks | ✅ PASS | ✅ PASS | | etl 04 | Matrix Reshaping & MultiIndex Flattening | ✅ PASS | ✅ PASS | "CORRUPTED LINE WITHOUT DELIMITERS" , models often default to chaining aggressive dropna operations that delete the entire corrupted line. .fillna "anonymous" fallbacks to avoid silent audit data loss. 🕹️ Mini-Quiz: Why is df.iterrows the enemy of production ETL pipelines? Click to reveal The Cost: Iterating over DataFrame rows with for index, row in df.iterrows converts each row into a pandas Series, creating massive Python overhead and slowing execution by up to 100x–500x compared to vectorized C-level operations like df.groupby .cumsum or .rolling . Native Loop Vectorization is Solved: In Task 3 translating Knime's iterative GroupLoop node , both models entirely avoided for row in df.iterrows or iterative Python loops. Both synthesized clean, vectorized df.groupby 'employee id' 'revenue' .cumsum and df.groupby 'department' 'revenue' .rank ascending=False, method='min' , demonstrating strong intrinsic understanding of pandas performance optimization. Flash Delivers 2.27x Higher Throughput: gemini-3.8-flash achieved a perfect 100% score in an average of 14.89 seconds per task , compared to 33.78 seconds for gemini-2.5-pro . For real-time IDE extensions and automated transpilers, Flash is clearly the most cost-effective choice. You can inspect, fork, and run this benchmark directly on Kaggle and GitHub: etl knime to python code synthesis @kbench.task : python import kbench import re, pandas as pd, numpy as np @kbench.task name="etl knime to python code synthesis", version="1.0.0", description="Evaluates LLM capability in converting visual ETL pipeline logic into idiomatic, vectorized Python pandas code." def evaluate etl benchmark model output: str, task id: str = "etl 01" - float: code match = re.search r" ?:python ?\s . ? \s ", model output, re.DOTALL clean code = code match.group 1 .strip if code match else model output.strip local scope = {"pd": pd, "np": np, "re": re} try: exec clean code, local scope, local scope if "transform etl" not in local scope or not callable local scope "transform etl" : return 0.0 Rigorous assertions on DataFrames return 1.0 except Exception: return 0.0 All dataset fixtures, automated test suites, and runners are open-sourced at github.com/jun-matsui/kaggle-etl-benchmark https://github.com/jun-matsui/kaggle-etl-benchmark .