Python Model Execution

Relevant source files

The following files were used as context for generating this wiki page:

• dbt-adapters/src/dbt/include/global_project/macros/python_model/python.sql
• dbt-spark/src/dbt/include/spark/macros/materializations/table.sql
• dbt-spark/src/dbt/include/spark/macros/materializations/incremental/incremental.sql
• dbt-tests-adapter/src/dbt/tests/adapter/python_model/test_python_model.py
• dbt-spark/tests/functional/adapter/test_python_model.py
• dbt-snowflake/tests/functional/adapter/test_python_model.py

Python Model Execution in dbt allows users to define data transformations using Python code rather than standard SQL. This feature enables the use of data science libraries and complex procedural logic that is difficult to express in SQL. The system relies on a combination of global Jinja macros to wrap user code and adapter-specific materializations to execute it on platforms like Spark, Snowflake, or BigQuery.

Sources: dbt-adapters/src/dbt/include/global_project/macros/python_model/python.sql, dbt-spark/src/dbt/include/spark/macros/materializations/table.sql:1-5

Architecture and Core Components

The execution flow involves wrapping a user-defined model(dbt, session) function with dbt-specific boilerplate code. This boilerplate provides the dbt object, which facilitates interaction with the dbt environment, such as referencing other models (dbt.ref) or accessing configuration (dbt.config).

The dbtObj and Environment Mocking

To allow Python models to behave like SQL models, dbt injects a set of helper classes and functions into the execution context. This includes:

• dbtObj: The primary interface passed to the user's model function. It contains methods for ref, source, config, and tracking incremental state.
• this: Represents the current model's database, schema, and identifier.
• config: A class providing access to model configurations via get() and meta_get() methods.

Sources: dbt-adapters/src/dbt/include/global_project/macros/python_model/python.sql:68-106

Component Relationship Diagram

This diagram illustrates how the dbt-generated boilerplate interacts with the user's provided Python code.

Execution Workflow

The execution process follows a standard dbt materialization pattern but swaps the SQL execution for a Python script submission.

1. Macro Resolution: dbt identifies the model as language='python'.
2. Code Compilation: The user's Python code is combined with a "postfix" generated by dbt macros. This postfix includes the ref and source resolution logic.
3. Submission: The combined script is sent to the target adapter (e.g., Spark) for execution.
4. Materialization: The target environment runs the script, which results in a DataFrame. This DataFrame is then written to a table or appended to an existing one.

Sequence of Code Generation

The following sequence diagram shows how dbt constructs the final Python script sent to the processing engine.

Materialization Strategies

Python models support standard dbt materializations, primarily table and incremental.

Table Materialization

For Spark, the py_write_table macro handles the conversion of various DataFrame types (pandas, Koalas, PySpark) into a Spark DataFrame before writing it to the destination using a specified file format (e.g., Delta or Parquet).

Sources: dbt-spark/src/dbt/include/spark/macros/materializations/table.sql:50-100

Incremental Materialization

Incremental execution relies on the dbt.is_incremental flag. Users can write conditional logic within their Python model to filter data based on the existing state of the target table.

def model(dbt, session):
    dbt.config(materialized="incremental", unique_key='id')
    df = dbt.ref("upstream_model")
    if dbt.is_incremental:
        # filter to new records only
        df = df.filter(df.id > dbt.ref("this_model").max('id'))
    return df

Sources: dbt-tests-adapter/src/dbt/tests/adapter/python_model/test_python_model.py:126-133, dbt-spark/src/dbt/include/spark/macros/materializations/incremental/incremental.sql:1-15

Configuration Options

Python models support specific configurations that control how the code is executed and how it interacts with the environment.

Configuration Key	Purpose	Derived From
packages	Lists external Python libraries to install in the execution environment.	dbt-spark/tests/functional/adapter/test_python_model.py:100
submission_method	Determines how the script is sent to the cluster (e.g., job_cluster for Spark).	dbt-spark/tests/functional/adapter/test_python_model.py:88
materialized	Defines the persistence strategy (table or incremental).	dbt-tests-adapter/src/dbt/tests/adapter/python_model/test_python_model.py:19
meta	Allows custom metadata to be retrieved via dbt.config.meta_get().	dbt-tests-adapter/src/dbt/tests/adapter/python_model/test_python_model.py:175-180

Metadata Retrieval

The meta_get method allows users to access values defined in the meta config block of either the Python file or the schema.yml.

# Accessing meta in Python
owner = dbt.config.meta_get('owner')
priority = dbt.config.meta_get('priority', 'low')

Sources: dbt-adapters/src/dbt/include/global_project/macros/python_model/python.sql:55-66, dbt-tests-adapter/src/dbt/tests/adapter/python_model/test_python_model.py:176-177

Platform Specifics (Spark Example)

In Spark-based adapters, the system intelligently handles DataFrame conversions. It checks for the availability of pyspark.pandas or databricks.koalas before falling back to standard pyspark.sql.DataFrame.

• DataFrame Conversion: If a user returns a pandas.DataFrame, dbt attempts to convert it to a Spark DataFrame using the most efficient available method (preferring pandas-on-Spark or Koalas to minimize overhead).
• Write Logic: Data is written using df.write.mode("overwrite").saveAsTable("...").

Sources: dbt-spark/src/dbt/include/spark/macros/materializations/table.sql:71-94

Summary

Python Model Execution bridges the gap between traditional SQL warehousing and modern data science workflows. By utilizing a common boilerplate structure across adapters, dbt ensures that core features like ref, source, and config work seamlessly within Python scripts while delegating the actual execution and DataFrame management to specialized adapter macros.