To SQL or to Python; there is another problem

A recent r/dataengineering discussion described a familiar disagreement. A team was moving millions of daily records into Databricks. Python already handled ingestion, but one engineer wanted the silver-layer transformations in Python as well. Other team members preferred SQL because more people could read and maintain it.

The discussion quickly became a vote for a language. SQL is easier to read. Python is easier to test. SQL is declarative. Python is proper engineering. One will scale; the other will not.

Most of those claims are too broad to guide a platform decision.

For structured Spark transformations, SQL and the DataFrame API often describe the same work. The more useful decision is about ownership: who must understand the transformation, what kind of change it receives, how it will be tested, and where its operational boundary belongs.

I would not standardize an entire data platform on one language. I would standardize the rules for choosing.

The execution engine removes one easy argument

Apache Spark's SQL and DataFrames guide makes an important point: SQL and the Dataset or DataFrame APIs use the same execution engine. Spark receives more information about structured data and the computation, then uses that information for optimization.

That does not guarantee identical performance for every pair of implementations. A Python user-defined function can block optimizations that a built-in SQL expression would preserve. A poorly written SQL query can create unnecessary shuffles. Different APIs can expose different features.

It does mean that "Python is faster" and "SQL scales better" are weak starting positions. If two implementations resolve to the same logical and physical plan, the syntax is not the main performance decision.

The team should compare evidence:

representative input
        ↓
SQL implementation + Python implementation
        ↓
logical and physical plans
        ↓
runtime, shuffle, spill, skew, cost
        ↓
maintenance and test review

The benchmark should use production-shaped data. A transformation that looks fine on ten thousand evenly distributed rows can behave very differently when one customer owns a third of the records.

Performance matters. It just has to be measured after the two options perform the same work.

Choose the language from the shape of the transformation

Databricks now documents this choice directly. Its Lakeflow guidance for SQL and Python says that both interfaces produce the same underlying dataflow graph and provide equivalent functionality for most processing. It recommends SQL for logic that can be expressed clearly in SQL, and Python when the pipeline needs programmatic control, an external library, or a Python-only feature.

That is a much better boundary than bronze, silver, and gold.

A layer name tells us how refined the data is supposed to be. It does not tell us whether the implementation needs loops, conditional generation, an external API, a custom library, or a readable set of joins and aggregations.

I would use a decision table like this:

Transformation shape	Default	Reason
Joins, filters, windows, aggregations	SQL	Direct expression of relational logic
Business-facing dimensional models	SQL	Wider reviewer and maintainer pool
Reusable DataFrame algorithm	Python	Functions and modules provide a clean boundary
Dynamic pipeline generation	Python	The logic needs programmatic control
External library or non-tabular processing	Python	SQL is no longer the natural interface
A pipeline containing both shapes	Both	Keep each part in the clearest interface

The last row is easy to overlook. Databricks supports mixing SQL and Python sources in one pipeline. A platform standard does not need to turn a mixed workload into an ideological contest.

The standard can say that relational transformations default to SQL, while Python is available when the transformation crosses a documented threshold. That gives the team consistency without making SQL imitate a programming language or making Python wrap every select, join, and groupBy.

A silver layer is not one kind of work

"Put the silver layer in Python" sounds precise because the layer has a name. In practice, silver often contains several different responsibilities:

• parsing source records and handling malformed values
• deduplicating events
• applying CDC semantics
• standardizing identifiers and units
• joining reference data
• enforcing quality expectations
• preparing reusable domain entities

Some of that work is straightforward relational logic. Some of it needs stateful streaming behavior. Some of it depends on a library. Some of it encodes a business definition that an analyst or domain engineer must review.

One language across the layer can make the diagram tidy while making ownership worse.

Suppose the finance team must approve revenue adjustments. A SQL model that exposes the joins and conditions may be easier for that review, even if a data engineer could write the same transformation in PySpark. Now suppose the platform must create fifty similar ingestion flows from metadata. Python can generate those definitions without copying fifty SQL files.

The language changes because the work changes.

I would split the layer by stable responsibilities, then choose the interface for each responsibility:

source-specific parsing       -> Python when libraries or custom parsing are needed
generic row validation        -> SQL expectations or built-in expressions
relational conformance        -> SQL
shared algorithm              -> unit-tested Python module
business model                -> SQL owned with the domain

This also gives code review a useful question. The reviewer is no longer deciding whether they like SQL or Python. They are checking whether the implementation matches the kind of change the component is expected to receive.

Readability belongs to a named reader

Teams often say that one language is more readable without naming the reader.

Readable to whom?

The engineer maintaining ingestion may prefer Python because the surrounding framework, tests, and deployment code are already Python. The analytics engineer validating a customer metric may prefer SQL because the transformation is close to the query they use for reconciliation. The on-call engineer may prefer whichever version exposes input tables, output tables, and failure conditions without three layers of abstraction.

Readability is part of the operating model. I would identify at least four readers for an important transformation:

1. The author making the first change.
2. The reviewer checking correctness.
3. The on-call engineer diagnosing a failed run.
4. The next owner changing it six months later.

If only the first reader benefits from the chosen language, the team is optimizing for writing rather than ownership.

This is where clever frameworks become expensive. A metadata-driven Python abstraction can remove repeated code, but it can also hide the generated query, input dependencies, and quality rules. The abstraction earns its place when the repeated behavior is stable, the generated plan is observable, and the team can test one definition without running the entire framework.

If every transformation needs an exception in the metadata, the framework has stopped reducing complexity. It has merely moved the complexity into configuration.

Testing is different, but neither language gets a free pass

Python has a familiar unit-testing story. Databricks recommends separating transformation logic into plain PySpark functions so they can be tested locally with pytest; its local pipeline development guide also describes validation and workspace execution as complementary layers.

That is useful, especially for functions with branching logic. It does not make a mocked DataFrame test proof that the production pipeline is correct.

SQL can also be tested with small input relations, expected outputs, schema checks, and integration runs. The syntax is different, but the required evidence is similar:

• representative happy-path rows
• nulls and malformed values
• duplicates and late records
• corrections and deletions
• schema changes
• row-count and value reconciliation
• a production execution plan

The team should define these checks once and require them from either interface.

I would add one more rule: test the published table contract separately from the implementation. If a Python transformation is replaced by SQL, consumers should not discover the change through missing columns or altered null behavior. The same applies in the other direction.

Write a small standard, not a language constitution

A workable standard can fit on one page:

Default to SQL for declarative relational transformations.

Use Python for programmatic control, shared algorithms,
external libraries, or features unavailable in SQL.

Mix them when one pipeline contains both kinds of work.

Require the same contract tests, reconciliation, lineage,
and operational metadata from both.

Benchmark performance claims with representative data
and inspect the execution plan.

Then record exceptions with a short decision note. State the transformation shape, intended maintainers, test approach, runtime evidence, and the condition that would trigger reconsideration.

This avoids two bad outcomes. The first is a Python-only platform where ordinary relational logic is hidden behind builders and helper functions. The second is a SQL-only platform where procedural logic leaks into generated statements and fragile templating.

For the Databricks migration in the Reddit discussion, I would keep ingestion in the Python framework that already works. I would default ordinary silver transformations to SQL when they are relational and need broad review. I would keep Python for custom parsing, reusable algorithms, dynamic definitions, and features that SQL does not cover. Both paths would publish the same ownership, test, lineage, and recovery information.

The language decision matters. The team identity attached to it does not.

Pick the interface that makes the transformation easiest to understand, verify, and operate by the people who own it. If that produces a mixed pipeline, that is a reasonable design, not a failure to standardize.