Writing a model in Python instead of SQL

dbt supports Python models as an alternative to SQL for transformations that are easier to express in Python, such as complex statistical logic or use of ML libraries. Python models are defined in .py files and must return a DataFrame, but they run on the data platform's compute (not dbt's), so they only work with adapters that support this feature (Snowflake, Databricks, BigQuery).

1 · Learn the must-know

• 1A Python model must define a function named model(dbt, session) that returns a DataFrame (pandas, Spark, or Snowpark, depending on platform).
• 2Use dbt.ref() and dbt.source() inside the Python model function to reference other models and sources, just as you would with Jinja in SQL models.
• 3Python models support the same node configs as SQL models (materialized, tags, schema, etc.), typically set via dbt.config() calls inside the model function or in a .yml/config block.
• 4Only table and incremental materializations are supported for Python models; view and ephemeral materializations are not supported.
• 5Python models are only supported on Snowflake, Databricks, and BigQuery (via Dataproc); other adapters like Postgres/Redshift do not support them.
• 6Because Python models execute as separate jobs on the warehouse's compute (e.g., Snowpark, PySpark), they can be slower to run and iterate on than SQL models, so SQL should still be preferred for simple transformations.

2 · Check your understanding