# dbt vs Spark Declarative Pipelines: how they differ and when to use which

dbt and Spark Declarative Pipelines both let you say what each table should contain and work out the order to build them. dbt compiles SQL models with Jinja and runs them inside your warehouse, any of Snowflake, Databricks, BigQuery, Redshift, DuckDB and more, in batches you schedule, with tests and snapshots built in. Spark Declarative Pipelines runs on Apache Spark, in Python or SQL, and handles streaming as well as batch, with streaming tables that read each new row once. On Databricks, Lakeflow pipelines add data quality expectations and AUTO CDC. Choose dbt for SQL transformations in a warehouse, and Spark Declarative Pipelines for streaming or Spark-based ingestion.

- Page: https://tecyfy.com/data-engineering/dbt-vs-spark-declarative-pipelines
- Part of: Data engineering (https://tecyfy.com/data-engineering)

- **dbt** (dbt Labs, https://docs.getdbt.com/docs/introduction): A framework that compiles SQL select statements, with Jinja and YAML, into tables and views in your data platform, in the order their references give. Install: `python -m pip install dbt`
- **Spark Declarative Pipelines** (Apache Spark project; Lakeflow pipelines on Databricks, https://spark.apache.org/docs/latest/declarative-pipelines-programming-guide.html): A Spark framework where you define streaming tables and materialized views in Python or SQL, and the pipeline works out the order and keeps them up to date. Install: `pip install "pyspark[pipelines]"`

Both tools are declarative: you write what each table should hold, not the steps to build it, and the tool reads the references between tables to work out the order. The difference is where the work happens and what kind of data each one expects. dbt sends SQL to a warehouse in batches. Spark Declarative Pipelines runs on Spark and treats data that keeps arriving as the normal case.

## What each one is

**dbt** turns select statements into tables. Each model is a SQL file; `ref()` names the models it reads, which gives dbt the order, the lineage and the selection (`dbt build --select daily_revenue+`). dbt does none of the computing itself: it compiles each model and runs it in your platform, whether that is Snowflake, BigQuery, Databricks, Redshift or DuckDB on a laptop. Tests, [snapshots](https://tecyfy.com/concepts/slowly-changing-dimensions) and documentation live in the same project. The current generation, dbt v2, is a Rust engine that reads your SQL and catches incorrect SQL before it reaches the warehouse.

**Spark Declarative Pipelines** is part of open-source Spark, and Databricks' Lakeflow pipelines, formerly Delta Live Tables, are built on it. You define datasets: a streaming table that grows as new data arrives, a materialized view that holds a query's result, or a temporary view for steps in between. The pipeline reads the definitions, finds the order and runs them, keeping checkpoints so a streaming table never reads the same row twice. On Databricks the same code runs as a Lakeflow pipeline, which adds expectations for data quality, AUTO CDC for change feeds and a continuous mode.

## Side by side

| | dbt | Spark Declarative Pipelines |
| --- | --- | --- |
| **Where it runs** | Inside your data platform. It sends SQL to Snowflake, Databricks, BigQuery, Redshift, DuckDB, Spark or ClickHouse, which does the work. | On Apache Spark, on any cluster spark-submit supports or a Spark Connect server, and on Databricks as Lakeflow pipelines. |
| **Languages** | SQL select statements with Jinja templating and YAML. Python models on Snowflake, BigQuery and Databricks. | Python (decorated functions that return a DataFrame) and SQL (CREATE STREAMING TABLE, CREATE MATERIALIZED VIEW). |
| **Batch and streaming** | Batch. Each run builds the models you select; on Databricks, dbt can also create streaming tables and materialized views. | Both. A streaming table reads each new input row once; a materialized view holds the result of a batch query. |
| **New data only** | An incremental model, with the filter you write inside is_incremental() and a unique_key to update rows. | A streaming table keeps its own checkpoint and reads only what arrived since the last run, with no filter to write. |
| **How the order is found** | From ref() and source() in each model. | From the tables each definition reads, analysed when the pipeline runs. |
| **Checking the data** | Data tests (unique, not_null, accepted_values, relationships and your own) and unit tests on fixed inputs. | Not in open-source Spark. Lakeflow pipelines add expectations that warn, drop the bad rows or fail the update. |
| **History and change data capture** | Snapshots keep type 2 history of a table that is overwritten in place. | Lakeflow's AUTO CDC applies a change feed or snapshots as SCD type 1 or type 2, including changes that arrive out of order. |
| **Rebuild from scratch** | dbt build --full-refresh, for chosen models with --select. | spark-pipelines run --full-refresh for chosen tables, or --full-refresh-all. |
| **Scheduling** | The dbt platform's jobs, or any orchestrator that runs dbt build. | On Databricks, the pipeline's own schedule or a job; elsewhere, an orchestrator that runs spark-pipelines run. |
| **Licence and cost** | Free, on an Apache 2.0 open-source runtime; the dbt platform has a free Developer plan and paid plans. Your warehouse bills the queries. | Apache 2.0, as part of Spark. On Databricks, Lakeflow pipelines run on Databricks compute, which Databricks bills. |

## Which one fits

- **SQL transformations in Snowflake, BigQuery or Redshift:** dbt. dbt runs the SQL in the warehouse you already pay for, and Spark Declarative Pipelines needs a Spark cluster to run.
- **Data that arrives all day from Kafka or files:** Spark Declarative Pipelines. Streaming tables read each new row once and keep their own checkpoint, so ingestion needs no filter and no batch schedule.
- **Analysts who know SQL should own the models:** dbt. A dbt model is a select statement, and the tests, docs and lineage come from the same project in the language they already use.
- **History of a table that is overwritten in place:** dbt or Spark Declarative Pipelines. dbt snapshots keep type 2 history on any platform; on Databricks, Lakeflow's AUTO CDC builds it from a change feed and copes with changes that arrive out of order.
- **A lakehouse on Databricks, from raw files to reports:** dbt or Spark Declarative Pipelines. The 2 work together. Lakeflow pipelines ingest the raw data as it arrives into bronze and silver tables, and dbt models on top build the gold tables people read.
- **Rows that break a rule kept out of the table:** dbt or Spark Declarative Pipelines. A dbt test finds them after the build and can stop the models downstream; a Lakeflow expectation can drop them as they are written.

## The same table in each

The same table in each: read the day's order files, skip orders with no id, and keep daily revenue by country up to date as new files arrive. Each was run on a laptop on 1 file, run again after a second day's file arrived, and gave the same 4 rows: DE 162.00 and FR 80.50 on 14 October, DE 60.00 and IN 15.25 on 15 October.

### dbt: `models/daily_revenue.sql`

```sql
{{ config(materialized='incremental', unique_key=['order_date', 'country']) }}

with orders as (
    select
        order_id,
        country,
        cast(amount as decimal(10, 2)) as amount,
        cast(ordered_at as date) as order_date
    from {{ source('shop', 'orders') }}
    where order_id is not null
    {% if is_incremental() %}
      -- only the days from the newest one already built: that day is rebuilt, later ones added
      and cast(ordered_at as date) >= (select max(order_date) from {{ this }})
    {% endif %}
)

select order_date, country, sum(amount) as revenue, count(*) as orders
from orders
group by order_date, country
```

An incremental model: the first run builds the whole table, later runs read from the newest day already built, and unique_key replaces that day's rows instead of adding them. The source is declared in a YAML file beside it, here reading the CSV files with DuckDB, and a not_null test runs with dbt build. Run with dbt v2 and with dbt v1 (dbt-core 1.12 and dbt-duckdb).

### Spark Declarative Pipelines: `transformations/shop.py`

```python
from pyspark import pipelines as dp
from pyspark.sql import DataFrame, functions as F


@dp.table
def orders() -> DataFrame:
    # A streaming table: each run reads only the files that arrived since the last run.
    return (
        spark.readStream.format("csv")
        .option("header", True)
        .schema("order_id STRING, country STRING, amount DECIMAL(10,2), ordered_at TIMESTAMP")
        .load("../data/orders")
    )


@dp.materialized_view
def daily_revenue() -> DataFrame:
    # A materialized view: the pipeline keeps it equal to this query over the orders table.
    return (
        spark.table("orders")
        .where("order_id IS NOT NULL")
        .groupBy(F.to_date("ordered_at").alias("order_date"), "country")
        .agg(F.sum("amount").alias("revenue"), F.count("*").alias("orders"))
    )
```

2 definitions and no filter for new data: the orders streaming table remembers which files it has read, and on the second run it read only the new one. spark-pipelines init made the project and its spark-pipeline.yml; spark is provided by the pipeline. Run with PySpark 4.2, with a Hive catalog (--conf spark.sql.catalogImplementation=hive) so the tables outlive one run.

The difference that matters most is in the handling of new data. The dbt model has to say what is new: the `is_incremental()` block reads from the newest day already built, and the `unique_key` makes rebuilding that day safe. Get that filter wrong and the model loses or doubles rows, which is why dbt's docs advise a full refresh when an incremental model's logic changes. The Spark file says nothing about new data at all. The streaming table's checkpoint knows which files it has read, and the second run read only the second day's file.

The other difference is what each file assumes is around it. The dbt model runs wherever its profile points, and the same file works on a laptop with DuckDB and on Snowflake. The Spark file needs Spark, and on a laptop a Hive catalog so the tables survive between runs; on Databricks, streaming tables and materialized views are Unity Catalog managed tables.

## Things that trip people up

- **A dbt incremental model is only as right as its filter.** Rows that arrive late, or a filter on the wrong column, are missed for good. See [idempotent pipelines](https://tecyfy.com/concepts/idempotent-pipeline) for why the rebuild of a recent window is the safe pattern.
- **New columns in an incremental dbt model are ignored by default.** `on_schema_change` decides, and no setting fills old rows. Plan a full refresh with any change of columns.
- **Spark pipeline code runs more than once.** The pipeline evaluates the definitions during planning and runs, so a dataset function should only build its DataFrame: no `collect()`, `count()`, `toPandas()` or writes inside it.
- **Expectations are not in open-source Spark.** They, AUTO CDC and continuous mode come with Lakeflow on Databricks. Code that uses them will not run on plain Spark.
- **A full refresh of a streaming table reads everything again.** `--full-refresh` clears its data and checkpoint, so it rereads every file still in the source. Make sure the source still has them.

## Using both

They are not rivals on Databricks so much as layers. Ingest with Lakeflow pipelines into [bronze and silver](https://tecyfy.com/concepts/medallion-architecture) tables as data arrives, then build the gold tables with dbt, where analysts review the SQL and the tests run with every build. Off Databricks, dbt in the warehouse is usually the whole answer, and an [orchestrator](/data-engineering/airflow-vs-dagster-vs-prefect) runs it on a schedule.

## Questions

### What is the difference between dbt and Spark Declarative Pipelines?

dbt compiles SQL models and runs them inside your data platform in batches, with tests, snapshots and docs built in. Spark Declarative Pipelines runs on Apache Spark and handles streaming as well as batch, with streaming tables that read each new row once and materialized views kept up to date. dbt works on many warehouses; Spark Declarative Pipelines needs Spark.

### Is Spark Declarative Pipelines the same as Delta Live Tables?

Not quite. The product formerly known as Delta Live Tables is now Lakeflow pipelines on Databricks, and existing DLT code still works. Lakeflow pipelines are built on Apache Spark Declarative Pipelines, which is part of open-source Spark. Lakeflow adds AUTO CDC, data quality expectations, a queryable event log and continuous mode on top of the open-source framework.

### Can I use dbt and Lakeflow pipelines together?

Yes. A natural split is Lakeflow pipelines for ingestion, where data arrives all day and streaming tables fit, and dbt for the transformations analysts own. The dbt Databricks adapter can also create streaming tables and materialized views itself.

### Does dbt support streaming?

dbt runs in batches you schedule. Incremental models process only new rows on each run, which covers many near-real-time needs when runs are frequent. For data that must be processed as it arrives, use a streaming engine, or on Databricks a streaming table, which dbt's Databricks adapter can create.

### Do Spark Declarative Pipelines have data quality checks?

Expectations are a Lakeflow feature on Databricks, not part of open-source Spark. An expectation is a rule on each row, with 3 actions - warn and keep the row, drop the row, or fail the update. In open-source Spark, filter the rows in the query, as the example on this page does.

### Is dbt free?

Yes. Installing dbt gives you v2, built on an Apache 2.0 open-source runtime, and dbt v1, the original Python version, is still maintained. The dbt platform adds scheduling, CI, hosted docs and alerts, with a free Developer plan and paid plans. Your warehouse bills the queries dbt runs.

## Sources

- [What is dbt?](https://docs.getdbt.com/docs/introduction)
- [Install dbt](https://docs.getdbt.com/docs/local/install-dbt)
- [About data platform connections](https://docs.getdbt.com/docs/core/connect-data-platform/about-core-connections)
- [Python models](https://docs.getdbt.com/docs/build/python-models)
- [Configure incremental models](https://docs.getdbt.com/docs/build/incremental-models)
- [Add data tests to your DAG](https://docs.getdbt.com/docs/build/data-tests)
- [Add snapshots to your DAG](https://docs.getdbt.com/docs/build/snapshots)
- [Databricks configurations](https://docs.getdbt.com/reference/resource-configs/databricks-configs)
- [Spark Declarative Pipelines Programming Guide](https://spark.apache.org/docs/latest/declarative-pipelines-programming-guide.html)
- [Apache Spark Declarative Pipelines](https://docs.databricks.com/aws/en/ldp/concepts/spark-declarative-pipelines)
- [Manage data quality with pipeline expectations](https://docs.databricks.com/aws/en/ldp/expectations)
- [What happened to Delta Live Tables (DLT)?](https://docs.databricks.com/aws/en/ldp/where-is-dlt)
- [The AUTO CDC APIs](https://docs.databricks.com/aws/en/ldp/cdc)
- [Streaming tables](https://docs.databricks.com/aws/en/ldp/streaming-tables)