Python models

What a Python model is

A .py file in models/ that defines one function, model(dbt, session), and returns a DataFrame. Where a SQL model ends in a final SELECT, a Python model ends in a final DataFrame — dbt saves whatever it returns as a table, exactly as it would the result of a SELECT.

def model(dbt, session):

    # read upstream models, same DAG as SQL
    obs = dbt.ref("int_blood_pressure_all")

    final_df = ...   # the part SQL couldn't do

    return final_df

The two arguments dbt passes in:

• dbt — the project context. dbt.ref() and dbt.source() return DataFrames pointing at upstream models and sources, so the Python model takes its place in the DAG like any other. dbt.config() sets configuration; dbt.is_incremental supports the incremental pattern.
• session— the connection to the platform's Python runtime. On Snowflake this is a Snowpark session: the code executes inside Snowflake, next to the data. Nothing runs on your laptop, and the data never leaves the warehouse.

Downstream models do not care that it was Python: {{ ref('my_python_model') }} works from any SQL model, and the YAML file beside it carries the same description, owner and data_tests as everything else. Tests on a Python model's grain are exactly as valuable as on a SQL model's — arguably more, because reviewers are less able to eyeball the logic.

Configuration and packages

Configure inside the file with dbt.config(), or in YAML as usual. Third-party packages are declared so the platform can provide them:

def model(dbt, session):
    dbt.config(
        materialized="table",
        packages=["scikit-learn", "numpy==1.23.1"]
    )

On Snowflake, packages come from the Anaconda channel available inside Snowpark — most of the scientific Python stack is there. Two materialisations are supported: table (default) and incremental. Python models cannot be views or ephemeral — they always write a table.

When Python is the right call

dbt's own guidance is the right default: if you can write it equally well in SQL, write it in SQL — more colleagues can read it, and it scales better. Python earns its place when SQL genuinely cannot express the work, or expresses it terribly:

• Statistical and ML methods — risk-score models, clustering, survival analysis, anything that wants scikit-learn or scipy rather than a tower of CASE statements.
• Algorithms that fight SQL — fuzzy matching, complex record-linkage scoring, graph traversal.
• Reusing an existing Python method — when a validated implementation already exists as Python, porting it to SQL adds risk rather than value.

Know the trade-offs before reaching for it:

• Slower and costlier than the equivalent SQL — general Python compute, and pandas-style code runs single-node unless written against the Snowpark DataFrame API.
• Harder to review — fewer team members read Python fluently, which raises the bar on comments, tests and the PR description.
• No print() debugging — the code runs remotely; debugging means platform logs or writing diagnostics into a column.