Python models in dbt: the model() function, configuration and materializations

1.When a Python model beats a SQL model

A Python model is for the work SQL cannot express cleanly. Training or deploying a data science model, running a statistics routine, or parsing awkward dates with a library such as dateutil used to need separate infrastructure and its own scheduler. A Python model turns that step into a node in the dbt project instead, with tests, documentation and lineage like any other model.

From the outside, a Python model looks like a SQL model. It takes almost all of the same configuration options: you can test and document it, add tags and meta, and grant other users access to its results. Selection works the same way too: by name, file path, configuration, position upstream or downstream of another model, or modification compared with a previous project state. The properties YAML for a Python model is the same YAML you already write for SQL.

models:
  - name: my_python_model

    # Document within the same codebase
    description: My transformation written in Python

    # Configure in ways that feel intuitive and familiar
    config:
      materialized: table
      tags: ['python']

    # Test the results of my Python transformation
    columns:
      - name: id
        # Standard validation for 'grain' of Python results
        data_tests:
          - unique
          - not_null

The biggest difference is where the code runs. dbt never runs a Python model locally. It sends the code to the data platform, which runs it in its own Python runtime. That is why only some adapters support Python models: the platform needs a fully featured Python runtime. Snowflake, BigQuery and Databricks are the supported platforms. In practice, a library installed on your laptop does nothing for the model. The library has to be available where the platform runs the code.

Use Python only for the step that needs it. SQL models can ref() a Python model, and a Python model can read SQL models. So a typical project stages and joins data in SQL, hands one prepared table to a Python model for the feature engineering or scoring, and goes back to SQL downstream.

Sources12

2.The model(dbt, session) function and its return value

Every Python model is a .py file in the models/ folder that defines one function, model(), with two parameters. **dbt is a class that dbt compiles separately for each model. It is how the code reaches the project and the DAG. session** is the platform's connection to its Python backend. The model uses it to read tables as DataFrames and to write DataFrames back. PySpark normally exposes its session as a global called spark, but dbt always passes it in explicitly as session so the signature is the same on every platform.

import ...

def model(dbt, session):

    my_sql_model_df = dbt.ref("my_sql_model")

    final_df = ...  # stuff you can't write in SQL!

    return final_df

The returned DataFrame does the job of a SQL model's final select. The function must return exactly one DataFrame, and when the model runs, dbt saves that DataFrame's full result as a table in the warehouse. DataFrame operations are evaluated lazily, so in development you can look at intermediate results with .show() or .head() before deciding what to return. When you run dbt run --select python_model, dbt builds both arguments and passes them in. Your job is only to write the function.

dbt does not use Jinja to render Python models. A Python model has no {{ }} blocks and no access to the full global Jinja context that SQL models enjoy. Everything it knows about the project arrives through the dbt argument, and that limit shapes how you configure Python models, covered further down.

Sources1

3.Reading upstream data with dbt.ref() and dbt.source()

A Python model joins the DAG through the same two functions SQL models use, called as methods on the dbt object. dbt.ref() reads another model, whether SQL or Python, and dbt.source() reads a raw source table. Each edge they create carries the same meaning as its SQL counterpart, described in [ref versus source dependencies](lesson:1.1). Here, though, the calls return DataFrames pointing at the upstream source, model, seed or snapshot, not a table name dropped into SQL text.

The returned object is the platform's native DataFrame, so a model that wants pandas has to convert it. The Snowflake tutorial's models call .to_pandas() on the result of dbt.ref("int_lap_times_years") before using pandas groupby and rolling methods. Lineage runs the other way as well: a downstream SQL model can select * from {{ ref('my_python_model') }} as it would any table.

There is one hole in that symmetry. A Python model cannot reference an ephemeral model. An ephemeral model is never built as a database object, so it has nothing a DataFrame could point at. If a Python model needs logic that currently lives in an ephemeral model, give that upstream model a materialization that produces a relation.

def model(dbt, session):

    # DataFrame representing an upstream model
    upstream_model = dbt.ref("upstream_model_name")

    # DataFrame representing an upstream source
    upstream_source = dbt. ? ("upstream_source_name", "table_name")

    ...

Sources13

4.Configuring Python models and passing in vars, env vars and target

You can configure a Python model in the same three places as a SQL model, and the usual [configuration precedence](lesson:1.6) applies:

- dbt_project.yml, for many models at once - a properties .yml file in the models/ directory - the model's .py file, by calling dbt.config(), the Python equivalent of the {{ config() }} macro

dbt.config() is deliberately limited. It accepts literal strings, booleans and numbers, plus dynamic configuration, and nothing else: no function calls and no nested data structures. The reason is parsing. dbt analyzes those arguments statically while it parses the project, before any Python runs. Anything more complex belongs in the config property of a properties YAML file.

That makes YAML the bridge for runtime context. To branch on var, env_var or target, set each one as a config value in a properties file, where Jinja is allowed. Then read it back inside the function with dbt.config.get(). The example below limits fct_orders to 500 rows whenever the target is dev.

models:
  - name: my_python_model
    config:
      materialized: table
      target_name: "{{ target.name }}"
      specific_var: "{{ var('SPECIFIC_VAR') }}"
      specific_env_var: "{{ env_var('SPECIFIC_ENV_VAR') }}"

def model(dbt, session):
    target_name = dbt.config.get("target_name")
    specific_var = dbt.config.get("specific_var")
    specific_env_var = dbt.config.get("specific_env_var")

    orders_df = dbt.ref("fct_orders")

    # limit data in dev
    if target_name == "dev":
        orders_df = orders_df.limit(500)

Custom values stored under meta have their own accessor. dbt.config.meta_get("custom_value") returns a single key. dbt.config.get("meta") returns the whole dictionary, so write dbt.config.get("meta", {}).get("custom_value") for the case where no meta is configured. Since dbt 1.8, config values also resolve inside Python f-strings. Before that, dbt.config.get() inside an f-string printed None.

Sources1

5.Supported materializations: table and incremental

Python models support only two of the [core materializations](lesson:1.2): table, the default, and incremental. A view has no place to store a DataFrame computed in a Python runtime, and the ephemeral pattern, which inlines SQL into a downstream query, cannot carry Python code either. If a model's job fits in a view, it belongs in SQL.

For incremental models, the dbt object provides the two things the logic depends on. dbt.is_incremental reports whether the current run is incremental, and dbt.this gives the location of the model's own table, broken down as dbt.this.database, dbt.this.schema and dbt.this.identifier. These are the Python counterparts of the is_incremental() macro and {{ this }}. The usual pattern is to set materialized="incremental", take the upstream DataFrame, and, when dbt.is_incremental is true, filter it against what is already stored in dbt.this, so the run processes only new rows. How those rows are then merged or appended is a question of [incremental strategy](lesson:1.11).

Sources1

6.Running Python models on BigQuery: submission methods and packages

On BigQuery, the platform-specific choice is how the code is submitted. The adapter reads data from BigQuery, computes the result either natively with BigQuery DataFrames or on Dataproc, and writes it back. You choose with submission_method, set either in the profile or on a single model.

The packages setting is where teams get caught. It lists Python packages to pip install before the model runs, but it only takes effect with submission_method: bigframes. Dataproc, serverless or cluster, ignores it without any error or warning, so a model moved from BigFrames to Dataproc keeps a packages list that installs nothing. On Dataproc, packages have to come from a custom container image or from cluster initialization actions. submission_method can also be left out when dataproc_cluster_name is declared.

Sources4

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.dbt runs a Python model's code on the machine where dbt is invoked and uploads the result.Why is that wrong?

   The data platform runs all of the code remotely. Libraries and compute must exist on the platform.

   Covered in When a Python model beats a SQL model

2. 2.dbt.config() inside a .py file can take var(), env_var() or other function calls, just like the SQL config() macro.Why is that wrong?

   dbt.config() accepts only literals (and dynamic configuration), because dbt parses it statically. Runtime values go in properties YAML and are read back with dbt.config.get().

   Covered in Configuring Python models and passing in vars, env vars and target

3. 3.A Python model can dbt.ref() any upstream model, including an ephemeral one.Why is that wrong?

   Ephemeral models cannot be referenced from Python models. The upstream model needs a materialization that builds a relation.

   Covered in Reading upstream data with dbt.ref() and dbt.source()

4. 4.Listing libraries in the packages config installs them for any BigQuery Python model.Why is that wrong?

   packages only works with the bigframes submission method. Dataproc ignores it silently and needs a custom image or initialization actions.

   Covered in Running Python models on BigQuery: submission methods and packages