dbt, Spark, or just a Python script?

This argument happens at every company and it is usually conducted as a technology preference when it is really a question about constraints. Here is the version that starts from the constraints.

Start with the honest numbers#

Three questions, and the answers determine most of the decision:

How much data, per run? Not "how much data do we have" — how much does one run of one job touch? Most "big data" jobs process a day's partition of a few gigabytes.

How many people will maintain this? One person who also does analysis is a completely different situation from a platform team of four.

What happens if it is broken for a day? This sets how much operational machinery is justified.

A single Python script#

Right when: the data fits in memory on one machine, one or two people own it, and the transformation is genuinely a script rather than a model.

Modern single-machine capability is much larger than people assume. Polars or DuckDB on a 32GB machine handles tens of millions of rows comfortably. A very large share of production data pipelines could run on one laptop and do not, for reasons of habit.

import duckdb

duckdb.sql("""
    copy (
        select date_trunc('day', ordered_at) as day,
               channel,
               sum(revenue_usd) as revenue
        from 'raw/orders/*.parquet'
        group by 1, 2
    ) to 'marts/daily_revenue.parquet' (format parquet)
""")

That is a complete, correct, testable pipeline step. It needs no cluster, no scheduler beyond cron, and no platform team.

Wrong when: more than about three of these exist and they depend on each other. At that point you have a DAG and no tool for expressing it, and you will start encoding dependencies in filenames and sleep statements.

dbt#

Right when: your transformations are SQL, your data is already in a warehouse, and more than one person needs to understand the models.

What it actually gives you, in order of value:

1. A dependency graph derived from your SQL, so ordering is automatic and correct.
2. Tests as a first-class thing, next to the models, running in CI.
3. Documentation and lineage generated rather than written.
4. Environments — the same code runs against dev and prod schemas.

The cost is real and worth naming: a compilation layer between what you write and what runs, a Jinja templating layer that gets abused, and a strong pull toward doing everything in SQL including the things SQL is bad at.

Wrong when: your transformations are genuinely not SQL — complex iterative logic, ML feature engineering, anything needing a loop. People do these in dbt with macros and it is always worse than a Python model would have been.

Spark#

Right when: the data genuinely does not fit on one machine, or you need to process a stream, or you are already running it for other reasons.

Spark is excellent at what it is for. The problem is that it is chosen far more often than it is needed, and its costs are paid continuously:

• A cluster to configure, tune, and pay for.
• Shuffles, partitioning, and skew — a real skill that takes months.
• Debugging that means reading stage graphs and executor logs.
• Startup overhead that makes small jobs slower than a single-machine equivalent.

The threshold I would use: if a day's partition fits in the memory of one large cloud instance, you do not need Spark. That is a few hundred gigabytes now. It rules out most workloads that use it.

Right when it is right: genuinely large data, streaming, or heavy ML preprocessing where the parallelism is the point.

The combination that works for most teams#

Most teams I have seen land somewhere close to:

• Ingestion: a managed connector where one exists, Python where it does not. Land raw, unchanged, with metadata.
• Transformation: dbt in the warehouse. SQL for everything it fits, which is most of it.
• The exceptions: a handful of Python jobs for things SQL is bad at, orchestrated alongside dbt rather than inside it.
• Orchestration: whatever expresses the DAG and reruns a window safely. Airflow, Dagster, Prefect — the choice matters far less than the discipline of parameterising by window.
• Spark: only when a specific job needs it, not as the platform.

The question that actually decides it#

Not "what is the best tool" but "what can this team operate at 3am?"

A Spark cluster nobody understands is worse than a Python script everyone does. A dbt project with 400 models and no tests is worse than 40 models with tests. The constraint is almost never compute; it is the number of things a small team can hold in their heads and fix under pressure.

Pick the least machinery that expresses your dependencies and lets you rerun a window safely. Add more only when something specific breaks.