Python Models in dbt
Historically, dbt was strictly a SQL-based framework. If you wanted to build a table in your data warehouse, you had to write a .sql file.
However, SQL is not the best language for everything. Advanced statistical forecasting, machine learning inference, and complex string manipulations are much easier to write in Python using libraries like pandas and scikit-learn.
To solve this, dbt introduced Python Models. You can now write a .py file, and dbt will execute it directly inside your Data Warehouse using technologies like Snowpark (for Snowflake) or Dataproc (for BigQuery).
Topics Covered
- When to use Python Models
- Anatomy of a dbt Python Model
- The
dbt.ref()function in Python - Example: Anomaly Detection
1. When to use Python Models
You should not use Python models for basic joins, filters, or aggregations. SQL is significantly faster and cheaper for standard data engineering tasks.
Use Python Models when:
- You need to perform Machine Learning inference (e.g., scoring a customer's fraud risk).
- You need to apply complex statistical formulas (e.g., ARIMA forecasting).
- You need to use advanced NLP libraries to parse unstructured text (e.g., extracting sentiment from customer reviews).
2. Anatomy of a dbt Python Model
A Python model in dbt is simply a .py file saved in your models/ directory.
Every dbt Python model must follow one strict rule: It must define a function called model(dbt, session).
This function must return a DataFrame (either a Pandas DataFrame or a Snowpark DataFrame).
# models/marts/fct_customer_fraud_scores.py
def model(dbt, session):
# This configuration is identical to the {{ config() }} block in SQL!
dbt.config(
materialized="table",
packages=["pandas", "scikit-learn"]
)
# ... logic goes here ...
return final_dataframe
3. The dbt.ref() Function in Python
Just like in SQL, you cannot hardcode table names in Python. You must use the DAG!
In Python, the Jinja {{ ref('model_name') }} is replaced by the Python method dbt.ref('model_name'). This ensures your Python model appears correctly in the lineage graph alongside your SQL models.
4. Example: Fraud Detection using Snowpark
Let's assume our Indian fintech app has a SQL staging model (stg_transactions). The Data Science team has given us a simple Python function to calculate a "Fraud Score" based on the transaction amount.
We want to read the SQL table, apply the Python logic, and save the result as a new physical table in Snowflake.
# models/marts/fct_fraud_detection.py
import pandas as pd
def model(dbt, session):
# 1. Configure the model
dbt.config(
materialized="table",
packages=["pandas"]
)
# 2. Read the upstream SQL model into a Snowpark DataFrame
# Notice we use dbt.ref() to maintain the DAG!
df_transactions = dbt.ref("stg_transactions")
# 3. Convert to a Pandas DataFrame (if the dataset is small enough)
# Note: For massive datasets, you should keep it as a Snowpark DataFrame to avoid memory limits.
pdf = df_transactions.to_pandas()
# 4. Apply our complex Python logic
# (In real life, this would be a scikit-learn model.predict() call)
def calculate_fraud_score(amount):
if amount > 100000:
return 0.95 # High risk
elif amount > 50000:
return 0.50 # Medium risk
else:
return 0.05 # Low risk
pdf['fraud_score'] = pdf['AMOUNT_INR'].apply(calculate_fraud_score)
# 5. Return the final DataFrame. dbt will automatically write this back to Snowflake as a table!
return pdf
What happens when you run this?
When you run dbt run --select fct_fraud_detection, dbt takes this Python script and sends it directly to Snowflake. Snowflake spins up a temporary Python Sandbox (using Snowpark), downloads the pandas package, executes the code securely, and writes the output into BEEMS_PROD.MARTS.FCT_FRAUD_DETECTION.
The data never leaves the Snowflake environment!
Summary
- Python Models allow you to write
.pyfiles inside your dbt project. - They are executed entirely within the Data Warehouse using technologies like Snowflake Snowpark.
- Every Python model must define a
model(dbt, session)function and return a DataFrame. - Use
dbt.ref('model_name')instead of Jinja{{ ref() }}. - Reserve Python models for ML inference, statistics, and NLP; use SQL for standard data transformations.