A Python Model Is Just a Function

Most tools that support both SQL and Python support one of them properly. Python arrives as an escape hatch: a different execution path, a different set of available features, often a different platform requirement, and a strong implicit suggestion that you should have written SQL instead.

In Interlace a .py model and a .sql model are the same kind of node. Either can depend on the other, in either direction, and the planner does not distinguish between them. This post shows that claim running rather than asserts it.

The graph

The benchmark project in the repository is a ten-model DAG over 25 million synthetic events. It is not a toy: the numbers are generated in-engine so there is nothing to download, and the fan-out does real, repeated work.

The chain that matters here is four models long:

events.sql  →  by_user.sql  →  user_ltv.py  →  top_products.sql

A Python model sits in the middle. Its upstream is SQL. Its downstream is SQL. Nothing about the surrounding models acknowledges that the middle one is written in a different language.

The Python model

"""A Python model in the hot path: 100k user rows stream through Arrow."""

import pyarrow as pa
import pyarrow.compute as pc

from interlace import model


@model(depends_on=["by_user"], strategy="merge", key=["user_id"])
def user_ltv(by_user):
    for batch in by_user.reader():
        score = pc.add(pc.multiply(batch.column("spend"), 0.1), batch.column("events"))
        yield pa.RecordBatch.from_arrays(
            [batch.column("user_id"), batch.column("spend"), pc.round(score, 2)],
            names=["user_id", "spend", "ltv"],
        )

Three things are worth pulling out.

The parameter is the dependency. by_user is not a string to be resolved later; it is the name of another model, and the function signature is the edge. For SQL models the same edge comes from parsing the FROM clause. Both produce identical entries in one graph.

It streams. by_user.reader() yields Arrow RecordBatches, and the function is a generator. Memory stays bounded regardless of how far you scale events.sql — you can raise the row count by an order of magnitude and this model's footprint does not move.

The strategy is the same strategy. merge here is the same keyed upsert a SQL model gets, compiled the same way, running as SQL in the warehouse. Python produced the rows; it did not take over the write path.

The SQL either side

Upstream, plain SQL with no header at all, which means it takes the defaults — materialise: virtual, strategy: replace:

SELECT user_id, count(*) AS events, sum(amount) AS spend
FROM enriched
GROUP BY user_id

Downstream, a model that reads a Python model's output as an ordinary relation:

/* interlace:
  materialise: view
*/
SELECT product_id, sum(revenue) AS revenue
FROM by_product
GROUP BY product_id
ORDER BY revenue DESC
LIMIT 20

That is the edge other tools cannot express freely. A SQL model selecting FROM the output of a Python model is not a bridge, an adapter or a special case. It is a table reference that happens to resolve to a model that happens to be Python.

What makes it work

The Arrow wire format. A model boundary is a RecordBatchReader in both directions, so a Python function and a SQL query are interchangeable at that boundary by construction. There is no conversion step to go wrong and no DataFrame round-trip to blow up memory.

The handle you receive is single-pass and gives you a choice:

Call one of them, once. A handle consumed twice is an error rather than a silent second scan.

You can return a pyarrow.Table, a RecordBatch, a RecordBatchReader, or — as above — yield batches from a generator.

Where the symmetry actually stops

It would be easy to end here, and dishonest. There are three real limits, and they follow from the design rather than from missing work.

Python models are always virtual. They cannot be a view or ephemeral, because both of those require SQL the engine can evaluate directly — a view is a query, and an ephemeral model is inlined as a CTE. There is nothing to inline when the model is a Python function.

Python models cannot deliver to a terminal destination. materialise: table and materialise: file are SQL-only. If you want a Python model's output in an external system, write a one-line SQL model that selects from it:

/* interlace:
  materialise: table
  target: crm.main.user_scores
  strategy: merge
  key: user_id
*/
SELECT user_id, ltv FROM user_ltv

Python models need a key to use incremental. With one, the function's Arrow output is staged and the window's rows are upserted into the target — the same keyed semantics a SQL model gets. Without one it is refused, and that refusal is the honest part: a SQL model has the window predicate pushed into its query, so the engine only ever computes the window, whereas a Python function has already produced everything by the time the window could be applied. An unkeyed windowed rewrite would look incremental while doing the full work every run. Use cursor to bound what the function fetches instead.

The first two raise at definition time, the moment the decorator runs; the third at plan time.

Testing it

Because the decorator registers the model and returns the function unchanged, a Python model is still an ordinary function. Call it with Arrow tables and assert on what comes back.
No fixtures, no warehouse, no separate framework:

import pyarrow as pa


def test_user_ltv():
    by_user = pa.table({"user_id": [1, 2], "spend": [100.0, 50.0], "events": [3, 1]})
    result = pa.Table.from_batches(user_ltv(FakeHandle(by_user)))
    assert result.column("ltv").to_pylist() == [13.0, 6.0]

This is the part that tends to convert people. A pipeline step you can call in a unit test, with no infrastructure, is a different kind of object from a pipeline step you can only observe by running it.

The next post is about the other end of the system, where a promise is much harder to keep: what it takes for an HTTP 200 to actually mean the data is safe.

Read the Python models guide for handles, cursors and the this parameter, or testing for the layered safety net around them.