SQL or Python for Data Transformations? Start With the Engine
In 2026 I think the useful answer is a routing rule. It comes down to where the data lives, what shape the computation has and which engine can do the work without moving the problem somewhere worse.
The SQL-versus-Python argument usually gets framed as a matter of taste, but I think it’s really about placement. SQL is a declarative interface to engines that already know how to scan, join, group, sort and window large tables. Python is a general-purpose language that shines when the work is messy, stateful, API-shaped or just not naturally a table operation. So the question I care about is where the computation should happen, and what the team can safely maintain after the clever version has shipped.
SQL won the transformation layer, and Python won the machinery around it.
— working rulePick the engine first, and the language follows.
The original Start Data Engineering framework gets the important part right, which is that choosing SQL or Python before you choose the execution engine is backwards. CPython, DuckDB, Polars, Spark, Snowflake, BigQuery and Databricks aren’t interchangeable runtime details, because they decide the cost, the parallelism, the failure mode and how much there is to maintain.
Three engines, three jobs
Most bad SQL-versus-Python decisions are really bad engine decisions, and the code is only what you see on the surface.
Language runtime
CPython, JVM, BEAM
Best when logic is custom, stateful, API-heavy, or needs normal software-engineering tools.
Single-node analytics
DuckDB, Polars
Best when the data is moderate, local or Parquet-shaped, and a cluster would spend more time coordinating than computing.
MPP warehouse
Snowflake, BigQuery, Spark, Databricks
Best when the data already lives there, scale is real, and the transformation is set-based.
The actual rule
Move code to data, not data to code
If the data is already in an optimizer, pulling it out to Python for a join usually means paying more to get less.
In practice, teams choose layers.
The 2026 surveys paint a pretty consistent picture. Python is still the broad language of data engineering, and SQL is still the default for the transformation layer. What practitioners actually complain about is speed pressure, weak ownership, legacy systems and trust, much more than syntax.
| Signal | What the research says | What it means for SQL vs Python |
|---|---|---|
| Joe Reis survey | 1,101 practitioners, 82% use AI tools daily or more, and only 11% say data modeling is going well. | The bottleneck is the design discipline around the code, more than writing the code itself. |
| dbt Labs survey | 72% prioritize AI-assisted coding, and 71% worry about hallucinated or incorrect data reaching stakeholders. | Reviewability and governance matter more as code generation gets cheaper. |
| DataTalks.Club | dbt dominates transformations, pandas and Spark are still common, and Polars is growing from a small base. | The stack is layered, with SQL for modeled transformations and Python for ingestion and systems code. |
| Field consensus | SQL is the common language of warehouse transformations, and Python is the common language of the pipeline machinery. | The best teams use both, but they don’t let both into every layer by default. |
For normal transformation work, SQL is usually faster and easier to inspect.
The benchmark pattern is pretty clear. For set-based work like joins, aggregates, filters and windows, a warehouse or analytical SQL engine has optimizers, statistics, vectorization and parallel execution that hand-written Python rarely matches. But the same evidence also shows that “use Spark for everything” has become lazy advice.
| Workload | Observed result | Winner | Read it carefully |
|---|---|---|---|
| 100K rows | SQL 0.8s vs pandas 2.1s | SQL 2.6× faster | Small data still benefits from the optimizer. |
| 10M rows | SQL 18.2s vs pandas 78.3s | SQL 4.3× faster | The gap widens as data grows. |
| 50M rows | SQL 94.1s vs pandas 421.7s, and Python needed 41 GB RAM | Python memory pressure | The failure mode is local memory as well as time. |
| 10 GB TPC-DS | DuckDB and Polars beat Spark on small machines | Single-node | Clusters aren’t free, because coordination has a cost. |
| 100 GB+ | Spark scales better as cores rise | Spark | The crossover is real, and so is the cost of getting there. |
DuckDB and Polars changed the old trade-off.
The old map was simple, SQL meant the warehouse and Python meant local, but that map doesn’t hold anymore. DuckDB puts an analytical SQL engine inside a process, Polars gives Python a fast Rust-backed DataFrame engine and dbt gives SQL a layer of discipline. So in practice the stack is now built from parts that fit together.
Four tools, four positions
The question has moved on from “SQL or Python?” to which tool owns which boundary.
dbt
The discipline layer for SQL
Models, dependencies, documentation, lineage, and reviews for transformations that belong in the warehouse.
DuckDB
Portable SQL without a server
A local analytical engine that makes SQL useful on Parquet, CSV, and moderate workloads without spinning up a cluster.
Polars
Fast Python for DataFrame work
A serious alternative to pandas when logic is code-shaped but the data still wants vectorized execution.
Spark
The scale engine
Still the right answer when the workload is truly distributed, and not just inconvenient.
Choose by data location, logic shape, and maintenance.
A useful decision framework starts with something most teams ignore, which is that the best transformation is usually the one that avoids moving data. From there, ask whether the logic is set-based, whether the team can review it and whether the failure mode is one you want to own.
| If this is true | Default to | Reason |
|---|---|---|
| Data already lives in Snowflake, BigQuery, Databricks, or Postgres | SQL-first, ideally with dbt or a disciplined SQL workflow | The optimizer is already next to the data, and moving rows out to Python adds cost and takes that advantage away. |
| The transform is joins, aggregates, windows, filters, slowly changing dimensions | SQL-first | This is what relational engines were built to do. Review is easier, and the engine has statistics. |
| The work calls APIs, masks PII before landing, parses weird payloads, or branches heavily | Python-first | The work is shaped like a program rather than a relation, so tests, packages, retries and types matter. |
| Data is under ~100 GB and file-based | DuckDB or Polars | A single-node engine may beat a cluster by avoiding coordination cost. |
| The workload is truly distributed or already platform-bound | Spark or the warehouse-native engine | Scale wins eventually, so don’t pretend things are simple when the data is actually huge. |
AI made code cheaper, which made review more important.
When an LLM can draft both the SQL model and the Python function, writing the code gets really cheap, but verifying it doesn’t. That changes the language argument. I think the best interface is the one your team can inspect, test and govern without turning every pull request into archaeology.
Optimize for reviewability over typing speed
The 2026 surveys show the split pretty clearly, with teams speeding up code generation faster than they speed up validation.
SQL advantage
Readable intent
A good SQL model says what relation should exist. Analysts and analytics engineers can review it together.
Python advantage
Testing machinery
pytest, typing, packaging, mocks, and contracts are mature. That matters when AI wrote the first draft.
Governance gap
Creation outruns validation
71% worry about incorrect AI-generated data reaching stakeholders, but only 24% prioritize AI-assisted pipeline governance.
Better question
Who signs the output?
The language is secondary to whether a real maintainer can explain, test, and own the result.
The debate ends when the routing is clear.
I’d default to SQL when the data is already in an analytical engine and the transformation is set-based. I’d default to Python when the work is ingestion, orchestration, API-shaped logic, cleaning before the warehouse, ML-adjacent systems work or anything that needs normal software tests more than a query optimizer. DuckDB and Polars fit when the job is too small for a platform team and too important for pandas improvisation, and Spark or the warehouse fit when the scale is real.
So the useful rule has nothing to do with “SQL is better” or “Python is better.” Run the transformation where the data already lives, express it in the interface the engine can optimize, and leave it in a form the next maintainer can review. That rule will pick SQL most of the time for the transformation layer and Python for much of the system around it. And I think it will pick both, cleanly separated, more often than either camp wants to admit.
The original SQL-versus-Python frame was useful because it put the criteria out in the open, like performance, cost, data size, data location, code style and maintainability. The 2026 version keeps those criteria but shifts the center of gravity. Overall, I’d pick the execution engine first and the interface second, and keep governance in the picture the whole time.
— end —