Vol. 01 · No. 05
— Field Notes —
June 2026
A Field Note on Data Transformations

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 rule
01 · The reframing

Pick 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.

The routing problem

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.

Figure 1 Start with the engine. The engine decides data movement, parallelism and cost, and SQL and Python are just the interfaces into that decision.
02 · What the field does

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.
Figure 2 The field data points to layering. SQL and Python are both winning, but in different parts of the system.
03 · The performance boundary

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.
Figure 3 The boundary moved up. Spark still matters, just not for the 10 GB job that a laptop-class engine can finish without a platform team.
04 · The tooling shift

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.

The 2026 toolbox

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.

Figure 4 The modern stack isn’t binary. dbt, DuckDB, Polars and Spark each cover a different pressure point.
05 · The routing rule

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.
Figure 5 The practical routing table. Start with where the data lives, then the shape of the logic, and then what the team can actually maintain.
06 · The AI wrinkle

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.

The post-AI rule

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.

Figure 6 The AI shift. Once writing code is cheap, the scarce thing is review you can trust.
Closing observation

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 —

SQL or Python for Data Transformations? Start With the Engine · A Field Note Set in Shadows Into Light Two & Cause

← Back to all posts