SQL or Python for Data Transformations? Start With the Engine
The useful answer in 2026 is not a language preference. It is a routing rule: where does the data live, what shape is the computation, and which engine can do the work without moving the problem somewhere worse?
The SQL-versus-Python argument is usually framed as taste. It is not taste. It is 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 not naturally a table operation. The question is not which language is better. The question is where the computation should happen — and what the team can safely maintain after the clever version has shipped.
SQL won the transformation layer. Python won the machinery around it.
— working ruleThe language is the interface. The engine is the decision.
The original Start Data Engineering framework gets the important part right: choosing SQL or Python before choosing the execution engine is backwards. CPython, DuckDB, Polars, Spark, Snowflake, BigQuery, and Databricks are not interchangeable runtime details. They define the cost, the parallelism, the failure mode, and the maintenance surface.
Three engines, three jobs
Most bad SQL-versus-Python decisions are really bad engine decisions. The code is only the surface area.
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, extracting it to Python for a join is usually paying to lose.
Practitioners are not choosing one language. They are choosing layers.
The 2026 survey picture is consistent. Python remains the broad language of data engineering. SQL remains the transformation layer's default. The pain reported by practitioners is not syntax. It is speed pressure, weak ownership, legacy systems, and trust.
| 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; only 11% say data modeling is going well. | The bottleneck is not writing code. It is the design discipline around the code. |
| dbt Labs survey | 72% prioritize AI-assisted coding; 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 remains common; Spark remains common; Polars is growing from a small base. | The stack is layered: SQL for modeled transformations, Python for ingestion and systems code. |
| Field consensus | SQL is the common language of warehouse transformations; Python is the common language of pipeline machinery. | The winning team uses both, but it does not let both into every layer by default. |
At normal transformation work, SQL is usually faster and easier to inspect.
The benchmark pattern is not subtle. For set-based work — joins, aggregates, filters, 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” is now 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; Python needed 41 GB RAM | Python memory pressure | The failure mode is not just time. It is local memory. |
| 10 GB TPC-DS | DuckDB and Polars beat Spark on small machines | Single-node | Clusters are not free. Coordination has a cost. |
| 100 GB+ | Spark scales better as cores rise | Spark | The crossover is real. So is the cost of reaching it. |
DuckDB and Polars changed the old trade-off.
The old map was simple: SQL meant warehouse, Python meant local. That map is obsolete. DuckDB puts an analytical SQL engine inside a process. Polars gives Python a fast Rust-backed DataFrame engine. dbt gives SQL a discipline layer. The practical stack is now compositional.
Four tools, four positions
The question is no longer “SQL or Python?” It is 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 correct answer when the workload is truly distributed, not merely inconvenient.
Choose by data location, logic shape, and maintenance.
A useful decision framework starts with the fact most teams ignore: 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. Moving rows out to Python adds cost and removes leverage. |
| 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 program-shaped, not relation-shaped. 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. Do not cosplay simplicity when the data is actually huge. |
AI made code cheaper. It made review more important.
When an LLM can draft both the SQL model and the Python function, the writing cost collapses. The verification cost does not. That changes the language argument. The winning interface is the one your team can inspect, test, and govern without turning every pull request into archaeology.
Optimize for reviewability, not typing speed
The 2026 surveys show the split clearly: teams are accelerating code generation faster than they are accelerating 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; 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.
Default to SQL when the data is already in an analytical engine and the transformation is set-based. Default to Python when the work is ingestion, orchestration, API-shaped logic, pre-warehouse cleaning, ML-adjacent systems work, or anything that needs normal software tests more than a query optimizer. Use DuckDB and Polars when the job is too small for a platform team and too important for pandas improvisation. Use Spark or the warehouse when the scale is real.
The useful sentence is not “SQL is better” or “Python is better.” It is: 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 sentence will pick SQL most of the time for the transformation layer. It will pick Python for much of the system around it. It will pick both, cleanly separated, more often than either camp wants to admit.
The original SQL-versus-Python frame was productive because it forced the criteria into the open: performance, cost, data size, data location, code style, and maintainability. The 2026 version keeps those criteria but changes the center of gravity. Execution engine first. Interface second. Governance always.
— end —