Skip to content
For the complete documentation index, see llms.txt. This page is also available as Markdown.

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.

Dependency type
Where it goes
How it's picked up

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_on explicitly 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 columns right before returning.

  • Tag prediction and confidence columns distinctly from dimension columns.

  • Include a model_version column in every prediction or forecast table.

Troubleshooting

Symptom
Likely cause
Fix

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

References

  • Python models - the @model decorator, execution context, and DataFrame examples.

  • Model kinds - supported kind values, including which kinds Python models can't use.

Last updated

Was this helpful?