dbt: Transformations on Databricks

Build a tested sales mart with dbt: models, snapshots, incremental loads, tests and contracts, then run it on Databricks with a full retail use case.

1. What dbt Is

dbt (data build tool) handles the T in ELT. Your data is already loaded into the platform, and dbt transforms it there. You write a SELECT statement in a file, and dbt turns it into a table or view, in the right order, with tests and documentation.

dbt does not store data, and it does not process data itself. It compiles your files into SQL and sends that SQL to your platform, such as Databricks, which does the actual work.

The problem it solves

The Data Modelling guide built a sales mart with DDL files and PySpark, and that works well. But every new table meant writing DDL, load code, a MERGE, ordering and checks by hand. dbt covers that repeated work, so an analyst or engineer who knows SQL can build tested tables.

This guide builds the same sales mart for the same retailer, with the same brief: revenue per day, per category and per customer city, and if a customer moves city, old orders must still count under the old city. Comparing the two approaches is the easiest way to see what dbt adds.

2. dbt Products and Versions

The name "dbt" covers several things, so it helps to separate them before you install anything.

dbt v2 changes the engine. dbt Core v2.0 replaces the Python runtime of v1 with a much faster Rust one and has a stricter language spec, so a typo like desciption is now caught. It became the default for new installations in 2026, and v1.12 is the last major v1 release.

Moving a v1 project: upgrade to v1.12 first, run dbt parse --use-v2-parser to see if it parses, and use the dbt-autofix package for the fixes. The project in this guide passed that parse check.

3. How dbt Works

Everything in dbt follows from one idea: a model is a SELECT statement in a .sql file, and the file name becomes the table name. Models connect to each other with the ref function:

-- models/marts/rpt_daily_sales.sql
select f.order_date, sum(f.net_amount) as revenue
from {{ ref('fact_order_line') }} as f
group by f.order_date

That small function does three jobs, and most of dbt's value comes from them.

1. It builds the dependency graph. Because this model calls ref('fact_order_line'), dbt knows that table must be built first. You never write the run order; dbt works it out as a graph called the DAG.

2. It picks the right schema. In dev the reference compiles to your personal schema, and in prod to the shared one. The same code therefore runs safely in both.

3. It gives you lineage. The same graph shows what feeds each table and what breaks if you change it.

What happens on dbt build

dbt build
  └─► Parse: read every .sql and .yml file
  └─► Build the DAG from ref() and source()
  └─► Compile: turn Jinja into plain SQL (see target/compiled)
  └─► Run each node in DAG order, in parallel where possible
  └─► Test each model right after it is built
  └─► If a test fails, skip everything downstream

Our finished project has this DAG. The rest of this guide builds it from left to right.

bronze.customers ─ stg_customers ─ customers_snapshot ─ dim_customer ┐
bronze.products ── stg_products ───────────────────── dim_product ──┤
                   category_groups (seed) ─────────────┘            │
bronze.orders ──── stg_orders ──────┐                               │
bronze.order_lines stg_order_lines ─┴─ int_order_lines ─ fact_order_line
                                                               │
                                     rpt_daily_sales ──────────┘
                                            │
                                     sales_dashboard (exposure)

4. Setting Up the Project on Databricks

dbt needs two things: a connection to Databricks, and a project describing what to build. They are kept separate because each developer has their own connection, while the project is shared in Git.

Step 1: install and connect

python -m venv .venv
.venv\Scripts\activate            # Windows
pip install dbt-databricks

dbt init retail_mart              # creates the project folders
dbt debug                         # tests the connection

The connection lives in profiles.yml. It needs a host and the HTTP path of a SQL warehouse, both shown on the warehouse's Connection details tab in Databricks. Each developer builds into their own schema, so nobody overwrites anyone else's work.

retail_mart:
  target: dev
  outputs:
    dev:
      type: databricks
      catalog: dev_sales
      schema: dbt_asha  # each developer gets their own
      host: your-workspace.cloud.databricks.com
      http_path: /sql/1.0/warehouses/abc123
      auth_type: oauth                 # opens a browser to sign in
      threads: 8
    prod:
      type: databricks
      catalog: prod_sales
      schema: default  # the macro below sets gold and silver
      host: your-workspace.cloud.databricks.com
      http_path: /sql/1.0/warehouses/abc123
      token: "{{ env_var('DBT_ACCESS_TOKEN') }}"  # injected by the dbt task
      threads: 16

Databricks treats personal access tokens as legacy and recommends OAuth. In Unity Catalog, catalog is the top level of the name catalog.schema.table. The login needs USE CATALOG, USE SCHEMA, SELECT, CREATE TABLE, CREATE VIEW and MODIFY, plus CREATE SCHEMA if dbt should create schemas.

Step 2: the project file

Instead of repeating settings in every model, dbt_project.yml sets defaults by folder. Our folders follow the medallion layers: staging models are silver views, marts are gold tables.

dbt_project.yml

name: retail_mart
version: "1.0.0"
profile: retail_mart

model-paths: ["models"]
snapshot-paths: ["snapshots"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
test-paths: ["tests"]

models:
  retail_mart:
    +file_format: delta  # Databricks: enables merge and snapshots
    staging:
      +schema: silver
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +schema: gold
      +materialized: table

snapshots:
  retail_mart:
    +schema: silver
    +file_format: delta

seeds:
  retail_mart:
    +schema: silver
    +file_format: delta

Step 3: the folder layout

retail_mart/
  dbt_project.yml
  packages.yml                  extra packages such as dbt_utils
  models/
    staging/                    one model per source table (silver)
    intermediate/               reusable logic, not exposed to users
    marts/                      facts, dimensions and reports (gold)
  snapshots/                    history of changing tables
  seeds/                        small CSV reference files
  macros/                       reusable SQL functions
  tests/                        custom tests

Step 4: fix the schema names

By default dbt adds the custom name to the target schema, so gold becomes prod_gold. Nearly every team overrides that macro, so that developers get a personal prefix and production gets clean names.

macros/generate_schema_name.sql

{% macro generate_schema_name(custom_schema_name, node) %}
    {% if target.name == 'prod' and custom_schema_name %}
        {{ custom_schema_name | trim }}
    {% elif custom_schema_name %}
        {{ target.schema }}_{{ custom_schema_name | trim }}
    {% else %}
        {{ target.schema }}
    {% endif %}
{% endmacro %}

5. Sources and Staging: The Silver Layer

dbt does not load data, so the first step is to tell it what already exists. Our ingestion jobs land raw tables in bronze, and we declare them as sources. Declaring them means lineage starts at the raw table, and dbt can check whether the data is fresh.

models/staging/_sources.yml

version: 2

sources:
  - name: bronze
    schema: bronze
    tables:
      - name: customers
      - name: products
      - name: orders
        config:
          loaded_at_field: updated_at
          freshness:
            warn_after: {count: 12, period: hour}
            error_after: {count: 24, period: hour}
      - name: order_lines

Each source table then gets one staging model, a thin clean-up layer: rename, cast and deduplicate, with no joins or business logic. Everything downstream reads staging, so if a source column changes, one file needs fixing.

models/staging/stg_customers.sql

with source as (

    select * from {{ source('bronze', 'customers') }}

),

cleaned as (

    select
        customer_id,
        trim(full_name)            as full_name,
        city,
        segment,
        cast(updated_at as timestamp) as updated_at
    from source

),

ranked as (

    select
        *,
        row_number() over (
            partition by customer_id order by updated_at desc
        ) as row_num
    from cleaned

)

select customer_id, full_name, city, segment, updated_at
from ranked
where row_num = 1

source() reads a raw table and ref() reads another model. If a model reads a table by typing its name, it drops out of the lineage and breaks across environments.

Databricks SQL supports QUALIFY, which would shorten the deduplication. We use row_number with a filter so the project also ran on open-source Spark 3.5, which rejected QUALIFY in our test.

Which materialization?

Staging models are views and marts are tables. That choice is called the materialization: how dbt builds a model. The SQL stays the same; only the config changes.

6. Snapshots: Keeping History

Now we reach the hard part of the brief. Our CRM stores only the current city, so when C100 moves to Mumbai the fact that they lived in Pune disappears. A view or table cannot help, because the old value is simply gone. A snapshot solves this: on each run dbt compares the source with what it saw last time and keeps the old version.

snapshots/customers_snapshot.yml

snapshots:
  - name: customers_snapshot
    relation: ref('stg_customers')
    config:
      unique_key: customer_id
      strategy: timestamp
      updated_at: updated_at
      dbt_valid_to_current: "cast('9999-12-31' as timestamp)"
      hard_deletes: invalidate

This one file replaces the two-step MERGE we wrote by hand in the Data Modelling guide. dbt adds the tracking columns itself. After C100 moved, our snapshot held:

customer_id  city    dbt_valid_from  dbt_valid_to
C100         Pune    2025-01-01      2026-03-01
C100         Mumbai  2026-03-01      9999-12-31

Two things to remember about snapshots

• History cannot be rebuilt. The source no longer holds the old values, so the snapshot table is the only copy. In our test dbt build --full-refresh left it untouched, which is what you want. Never drop it casually.
• It only sees what exists at run time. If a customer changes twice between two daily runs, the middle value is missed. If you need every change, capture them with CDC instead.

7. Building the Dimensions

The snapshot has history but it is not yet a clean dimension. The gold model adds the three things our star schema needs: a surrogate key per version, a date range that covers early orders, and an "unknown" row.

models/marts/dim_customer.sql

with versions as (

    select
        customer_id,
        full_name,
        city,
        segment,
        dbt_valid_from,
        dbt_valid_to,
        row_number() over (
            partition by customer_id order by dbt_valid_from
        ) as version_number
    from {{ ref('customers_snapshot') }}

),

keyed as (

    select
        {{ dbt_utils.generate_surrogate_key(
            ['customer_id', 'dbt_valid_from']
        ) }} as customer_sk,
        customer_id,
        full_name,
        city,
        segment,
        -- the first version also covers orders from before our first snapshot
        case when version_number = 1
             then cast('1970-01-01' as timestamp)
             else dbt_valid_from end as valid_from,
        dbt_valid_to as valid_to
    from versions

)

select * from keyed

union all

select
    '-1', 'UNKNOWN', 'Unknown', 'Unknown', 'Unknown',
    cast('1970-01-01' as timestamp), cast('9999-12-31' as timestamp)

Each part of this model solves a specific problem:

The Data Modelling guide used a BIGINT IDENTITY key. dbt projects normally use a hash, because a rebuilt table would otherwise get new numbers and every fact would point to the wrong row.

Packages

generate_surrogate_key comes from dbt_utils, the most used dbt package. Packages are like Python libraries for dbt: list them in a file and run dbt deps.

packages.yml

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.3.0", "<2.0.0"]

Seeds

The product dimension needs a mapping from category to department. It is a small file that the business owns, not a source system, so we keep it in Git as a seed: a CSV that dbt seed loads as a table. Seeds suit small, rarely changing reference data, not raw data loads.

seeds/category_groups.csv

category,department
Home,Household
Kitchen,Household
Mobile,Electronics
Laptop,Electronics

models/marts/dim_product.sql

select
    {{ dbt_utils.generate_surrogate_key(['p.product_id']) }} as product_sk,
    p.product_id,
    p.product_name,
    p.category,
    coalesce(g.department, 'Other') as department
from {{ ref('stg_products') }} as p
left join {{ ref('category_groups') }} as g
  on p.category = g.category

union all

select '-1', 'UNKNOWN', 'Unknown', 'Unknown', 'Unknown'

8. Incremental Models: The Fact Table

dim_customer is small enough to rebuild every run, but a fact table with years of orders is not. An incremental model processes only new or changed rows and merges them in. First, an ephemeral model joins orders to their lines. Ephemeral models become a CTE inside whatever uses them, so no extra table appears in the catalog.

models/intermediate/int_order_lines.sql

select
    l.order_id,
    l.line_number,
    o.customer_id,
    l.product_id,
    o.order_ts,
    o.updated_at,
    l.quantity,
    l.unit_price,
    {{ line_amount('l.quantity', 'l.unit_price') }} as net_amount
from {{ ref('stg_order_lines') }} as l
join {{ ref('stg_orders') }} as o
  on l.order_id = o.order_id

The fact then reads it. Everything from the design is here: the point-in-time join uses the date range, and coalesce sends unmatched rows to the -1 row.

models/marts/fact_order_line.sql

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key=['order_id', 'line_number'],
    liquid_clustered_by=['order_date'],
    on_schema_change='fail'
) }}

with lines as (

    select * from {{ ref('int_order_lines') }}

    {% if is_incremental() %}
    -- rows changed since the last run, plus 3 days for late data
    where updated_at >= (
        select {{ dbt.dateadd('day', -3, 'max(updated_at)') }} from {{ this }}
    )
    {% endif %}

)

select
    l.order_id,
    l.line_number,
    cast(l.order_ts as date)        as order_date,
    coalesce(c.customer_sk, '-1')   as customer_sk,
    coalesce(p.product_sk, '-1')    as product_sk,
    l.quantity,
    l.unit_price,
    l.net_amount,
    l.updated_at
from lines as l
left join {{ ref('dim_customer') }} as c
  on l.customer_id = c.customer_id
 and l.order_ts >= c.valid_from
 and l.order_ts < c.valid_to
left join {{ ref('dim_product') }} as p
  on l.product_id = p.product_id

How the incremental logic works

1. First run. is_incremental() is false because the table does not exist, so the filter disappears and dbt builds the full table.

2. Later runs. is_incremental() is true, so the filter appears and only rows changed in the last 3 days are read. {{ this }} means this model's own table.

3. dbt merges. Because unique_key is set, matching rows are updated and new rows inserted. That is the guarantee Databricks does not give, since primary keys are not enforced.

4. The look-back catches late data. Filtering on exactly max(updated_at) could miss a late row. Three days of overlap costs a little reprocessing, and the merge makes repeats harmless.

Tested: two days of data

Day 2 changed the customer's city, added two orders (one from an unknown customer), and corrected the quantity on order O1.

order_date  city     department   revenue
2026-02-10  Pune     Household     300.00   O1, corrected from 200
2026-02-11  Delhi    Electronics   500.00
2026-03-05  Mumbai   Household      50.00   C100, after moving
2026-03-05  Unknown  Unknown        70.00   C999 is not in the CRM

That is the brief delivered: C100's February order stays under Pune and the March order goes under Mumbai. O1 was updated and not duplicated. A third build changed nothing, with 4 fact rows before and after.

Incremental strategies on Databricks

After changing the logic or the columns, run dbt run --full-refresh --select fact_order_line to rebuild history. on_schema_change: fail stops the run rather than silently changing the table's columns.

9. Testing and Contracts

A model that runs is not necessarily right. dbt has three separate safety nets, and each one catches a different kind of problem at a different moment.

Data tests

Most tests are one line of YAML. dbt turns each into a query that looks for bad rows, and a test passes when the query returns no rows.

models/staging/_staging.yml

version: 2

models:
  - name: stg_customers
    description: One row per customer, latest version from the CRM.
    columns:
      - name: customer_id
        description: Business key from the CRM.
        data_tests: [unique, not_null]
      - name: segment
        data_tests:
          - accepted_values:
              arguments:
                values: [Retail, SMB, Enterprise]

  - name: stg_orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]

unit_tests:
  - name: line_amount_multiplies_and_rounds
    model: int_order_lines
    given:
      - input: ref('stg_orders')
        rows:
          - order_id: O1
            customer_id: C1
            order_ts: 2026-01-01
            updated_at: 2026-01-01
      - input: ref('stg_order_lines')
        rows:
          - order_id: O1
            line_number: 1
            product_id: P1
            quantity: 3
            unit_price: 19.99
    expect:
      rows:
        - {order_id: O1, net_amount: 59.97}

When the built-in ones are not enough, write your own. A custom generic test is reusable across columns; ours is used as positive in the marts YAML below.

tests/generic/positive.sql

{% test positive(model, column_name) %}

select {{ column_name }}
from {{ model }}
where {{ column_name }} <= 0

{% endtest %}

A singular test is a one-off query for a specific rule. Ours confirms the report total equals the fact total, so a bad join cannot quietly lose revenue.

tests/assert_report_matches_fact.sql

-- fails if the report loses or double counts revenue
with fact as (
    select sum(net_amount) as total from {{ ref('fact_order_line') }}
),
report as (
    select sum(revenue) as total from {{ ref('rpt_daily_sales') }}
)
select fact.total as fact_total, report.total as report_total
from fact cross join report
where fact.total <> report.total

Unit tests

The unit_tests block in the staging YAML above checks logic, not data. It gives dbt fake input rows and the expected output: 3 × 19.99 must give 59.97. It runs before the model is built, so it needs no real data.

Contracts

Downstream dashboards depend on the fact table's shape. A contract promises that shape, and dbt checks the model against it while building. This does the job of the assert_contract function we hand-wrote in the Data Modelling guide.

models/marts/_marts.yml

version: 2

models:
  - name: dim_customer
    description: One row per version of a customer (SCD Type 2).
    columns:
      - name: customer_sk
        description: Surrogate key, -1 is the unknown customer.
        data_tests: [unique, not_null]

  - name: dim_product
    columns:
      - name: product_sk
        data_tests: [unique, not_null]

  - name: fact_order_line
    description: "Grain: one row per order line."
    config:
      contract:
        enforced: true
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          arguments:
            combination_of_columns: [order_id, line_number]
    columns:
      - name: order_id
        data_type: string
        constraints:
          - type: not_null
      - name: line_number
        data_type: int
        constraints:
          - type: not_null
      - name: order_date
        data_type: date
        constraints:
          - type: not_null
      - name: customer_sk
        data_type: string
        constraints:
          - type: not_null
        data_tests:
          - relationships:
              arguments:
                to: ref('dim_customer')
                field: customer_sk
      - name: product_sk
        data_type: string
        constraints:
          - type: not_null
      - name: quantity
        data_type: int
        data_tests: [positive]
      - name: unit_price
        data_type: decimal(18,2)
      - name: net_amount
        data_type: decimal(18,2)
        data_tests: [positive]
      - name: updated_at
        data_type: timestamp

exposures:
  - name: sales_dashboard
    type: dashboard
    maturity: high
    owner:
      name: Sales Analytics
      email: sales-analytics@example.com
    depends_on:
      - ref('rpt_daily_sales')
    description: Daily revenue by city and department.

When we changed quantity to a double, the build stopped with this error, before touching the table:

This model has an enforced contract that failed.

| column_name | definition_type | contract_type | mismatch_reason    |
| quantity    | DOUBLE          | INTEGER       | data type mismatch |

As with DDL, Databricks enforces not_null but treats primary and foreign keys as informational only. So we still need the unique_combination_of_columns and relationships tests above.

What a failure looks like

We inserted a duplicate order into bronze and ran dbt build. The uniqueness test on stg_orders failed, and dbt skipped everything downstream, so bad data never reached the fact or the dashboard.

3 of 12 FAIL 1 unique_stg_orders_order_id ..... [FAIL 1]
5 of 12 SKIP relation dbt_asha_gold.fact_order_line
10 of 12 SKIP relation dbt_asha_gold.rpt_daily_sales
11 of 12 SKIP exposure sales_dashboard
Done. PASS=2 WARN=0 ERROR=1 SKIP=9 NO-OP=0 REUSED=0 TOTAL=12

This is why teams prefer dbt build over dbt run then dbt test: with separate commands, the bad data is already in the table by the time the test fails.

10. Macros, Jinja and Documentation

dbt files are SQL with Jinja, a templating language. Anything in {{ }} is replaced with text, and {% %} adds logic such as the is_incremental() block we used. A macro is a reusable function that writes SQL. If a calculation appears in two places, it belongs in one macro.

macros/line_amount.sql

{% macro line_amount(quantity, unit_price) %}
    cast({{ quantity }} * {{ unit_price }} as decimal(18, 2))
{% endmacro %}

The call {{ line_amount('l.quantity', 'l.unit_price') }} compiles to plain SQL, which you can read in target/compiled when debugging:

cast(l.quantity * l.unit_price as decimal(18, 2))

The cross-database macro dbt.dateadd in our fact is the same idea: it writes the correct date function for each platform.

Documentation and exposures

The description fields in the YAML files are not just comments. dbt docs generate turns them, with the lineage graph, into a browsable website, and dbt docs serve opens it locally. An exposure, like sales_dashboard in the marts YAML, adds the dashboard to the lineage. When someone changes a model, they can see which report depends on it and who owns it.

11. dbt Commands

Day to day you use a small set of commands, plus --select to choose what they act on.

Selecting what to run

In our project, dbt ls --select stg_orders+ --resource-type model returned int_order_lines, fact_order_line and rpt_daily_sales: exactly the models affected by a change to orders.

12. Integrating dbt with Databricks

The project is built, so how does it run on Databricks in production? The two systems split the work cleanly: dbt decides what SQL to run and in what order, and Databricks runs it on a SQL warehouse, stores the Delta tables and governs them in Unity Catalog.

            GitHub / Azure DevOps
                     │  project code
                     ▼
          ┌─────────────────────┐
          │  Lakeflow Job       │  schedule, retries, alerts
          │   └─ dbt task       │  runs dbt on small compute
          └──────────┬──────────┘
                     │  generated SQL
                     ▼
          ┌─────────────────────┐
          │  SQL warehouse      │  does the heavy work
          └──────────┬──────────┘
                     ▼
          ┌─────────────────────┐
          │  Unity Catalog      │  Delta tables, lineage,
          │  prod_sales.gold.*  │  permissions, tags
          └─────────────────────┘

Ways to run dbt on Databricks

Running it as a job

A dbt task needs two kinds of compute: a small one that runs dbt itself, and a SQL warehouse that runs the generated SQL. Databricks recommends serverless or a single-node cluster for the first, and the warehouse must be serverless or pro. Here is the job as code, with ingestion running first so dbt always sees fresh bronze data:

# resources/retail_mart.job.yml  (part of an Asset Bundle)
resources:
  jobs:
    retail_mart:
      name: retail_mart
      schedule:
        quartz_cron_expression: "0 0 6 * * ?"
        timezone_id: Asia/Kolkata
      tasks:
        - task_key: ingest_bronze
          notebook_task:
            notebook_path: ../ingest/auto_loader.py
        - task_key: dbt_build
          depends_on: [{ task_key: ingest_bronze }]
          dbt_task:
            project_directory: ./
            profiles_directory: dbt_profiles/
            warehouse_id: ${var.warehouse_id}
            commands:
              - dbt deps
              - dbt build --target ${bundle.target}
          libraries:
            - pypi: { package: "dbt-databricks>=1.12,<2.0" }

Databricks injects DBT_ACCESS_TOKEN for the job's Run As identity, which is why the prod profile reads it. That identity needs CAN USE on the warehouse and the Unity Catalog grants. Pin the dbt-databricks version so an update cannot change a production run. To debug generated SQL, use the warehouse's query history.

Using Delta and Unity Catalog features from dbt

The dbt-databricks adapter exposes Databricks features as config, so they stay in the project rather than in manual ALTER statements.

# dbt_project.yml additions for Databricks
models:
  retail_mart:
    marts:
      +grants:
        select: ["analysts"]             # Unity Catalog permission
      +persist_docs:
        relation: true  # descriptions become comments
        columns: true
      +databricks_tags:
        domain: sales

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key=['order_id', 'line_number'],
    liquid_clustered_by=['order_date'],
    tblproperties={'delta.enableChangeDataFeed': 'true'},
    merge_with_schema_evolution=false
) }}

Masking personal data

dim_customer holds names, and analysts should not see them. Instead of building a separate masked table, we attach a mask to the column:

models:
  - name: dim_customer
    columns:
      - name: full_name
        databricks_tags: { pii: "true" }
        column_mask:
          function: prod_sales.security.mask_name

Streaming tables and materialized views

Models do not have to be batch. A streaming table lets dbt manage continuous ingestion, and a materialized view keeps a report up to date. Both need Unity Catalog and serverless SQL warehouses.

-- models/bronze/orders_stream.sql
{{ config(materialized='streaming_table') }}

select * from stream read_files(
    's3://retail-landing/orders/', format => 'json'
)

-- models/marts/mv_sales_by_city.sql
{{ config(
    materialized='materialized_view',
    schedule={'cron': '0 0 * * * ? *', 'time_zone_value': 'Asia/Kolkata'}
) }}

select city, sum(revenue) as revenue
from {{ ref('rpt_daily_sales') }}
group by city

Python models: when SQL is not enough

Some logic is awkward in SQL, such as applying an ML model or a Python library. On Databricks a model can be a Python file returning a PySpark DataFrame. It joins the same DAG and can be tested like any other model, but use it as the exception, because SQL models are simpler and usually cheaper.

# models/marts/customer_segments.py
from pyspark.sql import functions as F

def model(dbt, session):
    dbt.config(materialized="table",
               submission_method="serverless_cluster")

    sales = dbt.ref("rpt_daily_sales")

    return (sales.groupBy("city")
                 .agg(F.sum("revenue").alias("revenue"))
                 .withColumn("tier",
                     F.when(F.col("revenue") > 1000, "high")
                      .otherwise("standard")))

13. Use Case: A Retail Sales Platform on Databricks

Here is how all of this fits together in a realistic project.

The situation

The retailer has orders in an operational database, customers in a CRM, and products in an ERP. Analysts export to Excel, three teams calculate revenue differently, and a broken pipeline is usually discovered by a manager. The goal: one trusted set of gold tables, refreshed every morning, that fail safely.

The architecture

  Orders DB   CRM    ERP
      │        │      │
      └──── files in cloud storage
                │
     Auto Loader (PySpark)         ingestion: not dbt's job
                │
     prod_sales.bronze.*           raw Delta tables
                │
     dbt: staging views            clean and rename
                │
     dbt: snapshot                 keep customer history
                │
     dbt: dim_* and fact_*         star schema, tested
                │
     dbt: rpt_daily_sales          business metrics
                │
     AI/BI dashboard, Genie        sales team self-service

The daily run

1. 06:00: the Lakeflow Job starts. The ingestion task loads new files into bronze with Auto Loader, in PySpark, because dbt does not ingest data.

2. The dbt task starts when ingestion succeeds. The job dependency guarantees dbt never runs on half-loaded data.

3. dbt build runs the DAG. Staging views refresh, the snapshot captures any customer changes, and dimensions rebuild.

4. Tests run after each step. If a test fails, downstream models are skipped, so yesterday's correct dashboard stays up instead of showing wrong numbers.

5. The fact merges only new rows. The incremental model reads a small window, so the daily cost stays low as history grows.

6. Failures alert the team. The job sends a notification. After fixing the cause, dbt retry restarts from the failure.

7. The dashboard reads gold. Unity Catalog grants, tags and column masks apply to everyone, and its lineage shows how each table was built.

A change request, end to end

The finance team asks for revenue by department. An analyst creates a branch, edits rpt_daily_sales in their own schema, adds a test and opens a pull request. CI builds only the changed models, the team reviews the SQL, and the merge deploys it. No ticket to a platform team, and no code that bypassed the tests.

What the project gained

More good use cases

When is dbt the right tool?

Most real platforms use these together, as in this architecture: PySpark and Auto Loader in, dbt from silver to gold. See the Spark & Databricks guide for the ingestion side.

14. Development and CI/CD

The use case relied on safe pull requests. This is the workflow that makes them safe.

1. Branch and develop. Each developer works on a Git branch and builds into their own schema, such as dbt_asha_gold, so production is untouched.

2. Open a pull request. CI runs dbt against a temporary schema.

3. Build only what changed. Rebuilding everything on every PR is slow and expensive. dbt compares against production's manifest.json, a file describing the deployed project, and picks out what changed.

# CI: build changed models, read unchanged ones from production
dbt deps
dbt build --select state:modified+ \
          --defer --state prod_artifacts/

We tested the selection by renaming one column in rpt_daily_sales. state:modified+ returned only rpt_daily_sales, its test, and the sales_dashboard exposure, and none of the models upstream of it.

4. Merge and deploy. After review, the merge deploys the bundle, and the next job run uses the new code from the main branch.

databricks bundle validate
databricks bundle deploy -t dev
databricks bundle run retail_mart -t dev
databricks bundle deploy -t prod

How this was tested

Nothing here ran on a real Databricks workspace, so the job YAML, streaming tables, materialized views, masks, grants and the Python model come from the dbt and Databricks documentation; try them on dev first. On open-source Delta, the first build also rejected the contract's NOT NULL, which dbt applies with ALTER; Databricks documents not_null as enforced. Rerunning built the rest correctly.

15. Common Mistakes

16. Common Interview Questions