dbt with Python

dbt manages warehouse transformations as versioned models - mostly SQL, with Python models where adapters allow pandas/polars-style logic in the warehouse runtime.

Recipe

Quick-reference recipe card - copy-paste ready.

# models/staging/stg_orders.sql
select
  order_id,
  cast(revenue as double) as revenue,
  region,
  ordered_at
from {{ source('raw', 'orders') }}
where order_id is not null

# models/marts/revenue_by_region.py (dbt Python model)
def model(dbt, session):
    df = dbt.ref("stg_orders").to_pandas()
    return df.groupby("region", observed=True)["revenue"].sum().reset_index()

When to reach for this:

• Analytics transformations owned by data teams in SQL
• Need tests, docs, and lineage on warehouse tables
• Python only for stats/ML features SQL cannot express cleanly
• CI runs dbt build on every PR

Working Example

-- models/staging/stg_orders.sql
with source as (
    select * from {{ source('ecommerce', 'orders') }}
),
cleaned as (
    select
        order_id,
        upper(trim(region)) as region,
        cast(revenue as numeric(18, 2)) as revenue,
        ordered_at::timestamp_tz as ordered_at
    from source
    where revenue >= 0
)
select * from cleaned

# models/staging/schema.yml
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: revenue
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0

-- models/marts/fct_revenue_daily.sql
select
    date_trunc('day', ordered_at) as revenue_date,
    region,
    sum(revenue) as total_revenue,
    count(*) as order_count
from {{ ref('stg_orders') }}
group by 1, 2

What this demonstrates:

• source and ref dependency graph
• Staging cleanup in SQL close to warehouse engine
• YAML tests on keys and revenue bounds
• Mart aggregation as downstream model

Deep Dive

How It Works

• dbt compiles Jinja SQL, runs materializations (view, table, incremental).
• DAG from ref() - dbt build runs tests after model builds.
• Python models delegate to adapter runtime (Snowflake Snowpark, BigQuery, etc.).
• Artifacts: manifest.json, catalog.json for lineage tools.

Layering

Python Notes

# Keep Python models thin - heavy lifting still belongs in SQL when possible
def model(dbt, session):
    import pandas as pd
    orders = dbt.ref("stg_orders")
    pdf = orders.to_pandas() if hasattr(orders, "to_pandas") else orders
    # feature engineering ...
    return pdf

Gotchas

• Python model on wrong adapter - not all warehouses support Python models. Fix: check adapter docs; fall back to SQL UDF.
• Logic duplicated in BI tools - metrics diverge from dbt marts. Fix: expose grains in marts; BI only visualizes.
• Skipping tests on staging - bad keys propagate. Fix: unique/not_null on primary keys minimum.
• Full-refresh incremental by mistake - expensive rebuild. Fix: document incremental_strategy and unique keys.
• Hard-coded database names - breaks across envs. Fix: target profiles dev/prod via env vars.

Alternatives

FAQs

dbt debug && dbt build --select stg_orders+

tests:
  - relationships:
      to: ref('dim_customers')
      field: customer_id

Related

• Data Validation & Quality - expectations tests
• File Formats - external tables on Parquet
• Data Engineering Basics - ETL lifecycle
• PySpark - cluster-scale alternative

Stack versions: This page was written for Python 3.14.0 (stable 3.14, maintenance 3.13), FastAPI 0.115+, Django 5.2, Flask 3.1, Pydantic 2, PyTorch 2.6+, pandas 2.2+, Polars 1.x, ruff 0.9+, and uv 0.6+.