Build a Python model for AI/ML workflows
Write a Vulcan Python model that returns a DataFrame instead of SQL, for machine learning, forecasting, or logic that doesn't fit cleanly in SQL.
SQL models in Vulcan are transpiled and optimized across many engines, but SQL can't train a machine learning model or call scikit-learn. When a transformation needs real ML (training or scoring a model, statistical forecasting, or logic that's painful to express as a query), a Python model can run that logic directly and still plug into the same plan/run lifecycle as a SQL model.
Use this recipe when you need a model to return a DataFrame built with pandas, PySpark, or another Python library, instead of a SELECT statement. A Vulcan Python model is a function decorated with @model. Vulcan calls the function at plan/run time and writes whatever it returns or yields as the model's output rows.
Steps
1. Declare the model with @model
Import model and ModelKindName from vulcan, and decorate a function with the model name, an explicit columns schema, kind, and any depends_on, tags, or grains.
import typing as t
from datetime import datetime
import pandas as pd
from vulcan import ExecutionContext, ModelKindName, model
@model(
"warehouse.ml.refund_severity_monitor",
columns={
"ds": "date",
"city": "string",
"refund_severity_band": "string",
"refund_events": "int",
"total_refund_amount": "double",
},
kind=dict(name=ModelKindName.FULL),
grains=["ds", "city", "refund_severity_band"],
tags=["python", "ml", "monitoring"],
depends_on=["warehouse.silver.refund_enriched"],
)
def execute(
context: ExecutionContext,
start: datetime,
end: datetime,
execution_time: datetime,
**kwargs: t.Any,
) -> pd.DataFrame:
...columns is required for any Python model that isn't SQL-generating. Without it, Vulcan raises ConfigError: Python model must define column schema.. It's also the contract Vulcan uses to validate output and generate the target table. Declare depends_on explicitly rather than relying on Vulcan to infer it from the function body. This matters for ML models, since training or scoring logic often builds queries dynamically in ways static parsing can miss.
Run vulcan plan to confirm the model appears in the DAG with the correct upstream dependency.
2. Pull data with context.fetchdf
Inside the function, query upstream tables through the execution context instead of a raw connection.
context.fetchdf runs through the engine adapter and returns a pandas DataFrame, so the rest of the function can use normal pandas code. On Spark engines, use context.spark instead (for example, context.spark.table(table) or context.spark.sql(...)) to keep the data in a PySpark DataFrame. context.resolve_table(...) resolves a model name to its physical table name, so the raw SQL string always points at the right table.
Always guard for an empty result. Yielding an empty DataFrame with the wrong dtypes can break the write step downstream, so use yield from (); return instead.
3. Do the ML work
This is where a Python model earns its keep: feature engineering, training, or scoring. Two common patterns:
Statistical forecasting with pandas and numpy:
A full MLlib pipeline on Spark:
Defer pyspark.ml imports to inside the function body rather than the module's top level. This lets Vulcan parse the file for dependency inference even in environments where PySpark isn't installed. For the Spark-and-fallback version of this pattern, see Cluster data with a Spark ML Python model.
4. Cast columns and return the result
On Spark, return the Spark DataFrame itself rather than converting it with .toPandas(), which pulls every row onto the driver and can run it out of memory. Cast every column to the exact type declared in columns, and yield the result.
Vulcan writes exactly what the function returns against the columns schema. A mismatched dtype (int32 where int was declared, for example) is the most common cause of write-time errors for Python models. Confirm the physical table's schema matches by running vulcan run and inspecting the table (DESCRIBE <physical_table> on your engine, or the Vulcan catalog).
5. Add metadata for discoverability
Use tags, column_descriptions, and column_tags to mark which columns are ML outputs versus dimensions, and include a model_version column in every prediction table.
Downstream consumers need to know which columns are dimensions (grains, categories) and which are model outputs (predictions, confidence bounds). Tagging this explicitly makes the model self-documenting in the catalog. A model_version column costs one line and makes it easy to trace which model version produced which row after a retrain.
6. Ship external dependencies
AI/ML models often need a package that isn't preinstalled, or a JVM library for Spark. Vulcan projects support both through dedicated folders at the project root.
Python packages (.whl)
dependencies/python/ (nested folders supported)
Build the wheel, drop it in the folder, and import it normally in model code
Java/JVM libraries (.jar)
dependencies/java/ (flat, no nested folders)
The Spark stack auto-loads JARs from this folder for both driver and executors
Vulcan runs Python models locally, wherever Vulcan itself runs, so Python dependencies must be importable in that same process. That's why wheels have their own folder. Spark JARs, by contrast, need to be on the classpath of both the driver and every executor, which is why they're loaded from a separate, JVM-specific folder.
Confirm vulcan plan succeeds with no missing-dependency errors. For a JAR, context.spark.sparkContext.getConf().get("spark.jars", "") confirms it's registered before you rely on it.
Best practices
Declare
depends_onexplicitly for ML models rather than relying on parsed inference.Guard every DataFrame-returning function against empty upstream data with
yield from (); return.Cast every output column to the exact dtype declared in
columnsright before returning.Tag prediction and confidence columns distinctly from dimension columns.
Include a
model_versioncolumn in every prediction or forecast table.
Troubleshooting
ConfigError: Python model must define column schema.
columns is missing from the @model decorator
Add an explicit columns dict
Dependency not detected during planning
The upstream reference is built dynamically (for example, an f-string)
Declare depends_on explicitly instead of relying on parsing
Import errors during planning
An ML library is imported at the top of the file
Move the import inside the function body, deferred until execution
Write fails with a type mismatch
The returned DataFrame's dtypes don't match columns
Cast columns explicitly (for example, .astype("float64")) before returning
ImportError for a library at model runtime
The package isn't shipped as a wheel
Build it and place it under dependencies/python/
Missing JVM class for a Spark dependency
The JAR isn't on the classpath
Place it under dependencies/java/ (flat, no nested folders) and confirm via spark.jars
Related recipes
Cluster data with a Spark ML Python model - a complete Spark MLlib model with a scikit-learn fallback.
Wait for data to be ready with signals - gate a model's run on data actually being present, not just the clock.
References
Python models - the
@modeldecorator, execution context, and DataFrame examples.Model kinds - supported
kindvalues, including which kinds Python models can't use.
Last updated
Was this helpful?