Functions

Functions are SQL or Python definitions under functions/ that SQLBuild compiles and deploys to the warehouse alongside your models. They participate in the DAG: if a function definition changes, SQLBuild redeploys it, and every model that calls it directly treats the change as a change to its own query.

Scalar UDFs

Scalar UDFs return a single value per row. They can be written in SQL or Python.

SQL UDFs

Place SQL function files under functions/sql/. Each file has a FUNCTION() header declaring arguments and return type, followed by a SQL expression body. The header accepts description, arguments, returns, database, schema, and tags; any other key fails compilation with D002, naming the key and its file and line. description is required: a function without one fails compilation with P010 (see Descriptions):

-- functions/sql/udf__is_completed_order.sql
FUNCTION (
  description "Whether an order status counts as completed",
  arguments (order_status STRING),
  returns BOOLEAN,
);

order_status = 'completed'

Python UDFs

Place Python function files under functions/python/. Each file has exactly one function decorated with @udf(...):

functions/python/is_completed_order_py.py

from sqlbuild.functions import udf

@udf(
    arguments={"order_status": "STRING"},
    returns="BOOLEAN",
    runtime_version="3.11",
)
def main(order_status: str | None) -> bool:
    """Whether an order status counts as completed."""
    return order_status == "completed"

The function docstring is its description; @udf(description="...") overrides it. One of them is required, or compilation fails with P010. The docstring is deployed with the function body, so editing it redeploys the function; a description= value does not.

Python UDFs are deployed as warehouse-native functions (Snowflake Python UDFs, BigQuery remote functions, etc.). The @udf decorator is used for static discovery only - SQLBuild parses the AST without importing your code.

Using scalar UDFs

Reference scalar UDFs in models with __udf("name"):

SELECT
  order_id,
  __udf("udf__is_completed_order")(status) AS is_completed
FROM __ref("stg_orders")

The function name is the file stem (filename without extension). __udf() resolves to the adapter-native function call at compile time.

Table functions

Table functions return multiple rows and columns. They are written in SQL under functions/sql/ with a returns table(...) declaration:

-- functions/sql/table_fn__customer_orders.sql
FUNCTION (
  description "Customer orders",
  arguments (p_customer_id INTEGER),
  returns table (
    order_id INTEGER,
    ordered_at TIMESTAMP,
    waffle_name VARCHAR,
    line_total_cents INTEGER,
    order_status VARCHAR,
    is_completed_order BOOLEAN
  )
);

SELECT
  order_id,
  ordered_at,
  waffle_name,
  line_total_cents,
  order_status,
  is_completed_order
FROM __ref("fact_orders")
WHERE customer_id = p_customer_id

Why table functions exist

Table functions are designed as an alternative to final-layer views for cases where views don’t push predicates efficiently. A view over fact_orders with a WHERE customer_id = ? filter may scan the entire table if the engine doesn’t push the predicate down. A table function accepts the filter as an argument and guarantees the predicate is applied at execution time.

Managed table function dependencies

Models call managed table functions with __table_fn("name")(arguments...):

SELECT customer_id, order_id
FROM __table_fn("table_fn__customer_orders")(42)

The name must be double quoted and the second argument list is required, including for a zero-argument function. SQLBuild validates that the target exists, is a table function, and receives the declared number of arguments.

The reference creates a typed dependency in the project DAG. Selecting a consuming model also selects the required function, function changes propagate to consumers, and cycles through models and functions are rejected. SQLBuild treats the call as an opaque relation for SQL analysis and uses the function’s declared returns table(...) columns for star expansion and terminal lineage.

Incremental state still belongs to the consuming model. A table function does not own a cursor interval or retain execution state.

Using table functions

Applications and analysts can call the deployed warehouse function directly:

SELECT * FROM table_fn__customer_orders(42)

References inside functions

SQL functions can reference the same resources as models:

Reference	Syntax
Model	__ref("model_name")
Seed	__seed("seed_name")
Source	__source("source_name")
Scalar UDF	__udf("function_name")
Table function	__table_fn("function_name")(arguments...)

These references are resolved at compile time and create DAG edges. If a referenced model changes, the function is redeployed.

A SQL function uses macros, constants, and enums available from its file under functions/sql/. See How Visibility Works to limit declarations to one function folder or to that folder and its children.

Change propagation

Functions participate in fingerprint-based change detection. Their identity includes dependencies and declared return contracts in addition to the function body and runtime metadata. If a function changes, SQLBuild redeploys it.

A model that calls a changed function directly plans as Function changed, with cause: function <name> changed. The function change counts as a change to that model’s own query, so the model’s own replay_on_change decides how much history it reprocesses (forward-only by default). Models further downstream are not affected by the function change and continue forward. Functions have no replay setting of their own; to reprocess history when a function changes, set replay_on_change on the models that call it. See Replay decisions.

Project layout

functions/
  sql/
    udf__is_completed_order.sql        # scalar SQL UDF
    table_fn__customer_orders.sql     # table function (returns table)
  python/
    is_completed_order_py.py      # scalar Python UDF

Python UDF options

The @udf decorator accepts these keyword arguments:

Argument	Required	Description
description	Yes, unless the function has a docstring	What the function returns; overrides the docstring
arguments	Yes	Dict mapping argument names to SQL types
returns	Yes	SQL return type string
runtime_version	No	Python runtime version (e.g. "3.11")
entry_point	No	Function name to use as the entry point (defaults to the decorated function name)
packages	No	List of Python packages required at runtime

database, schema, and tags are also accepted. Any other keyword fails compilation with D002, naming the keyword and its file and line.

Adapter support

All four supported adapters implement SQL UDFs, Python UDFs, and table functions:

Feature	DuckDB	Snowflake	BigQuery	Databricks
SQL UDFs	Yes	Yes	Yes	Yes
Python UDFs	Yes	Yes	Yes	Yes
Table functions	Yes	Yes	Yes	Yes

Future adapters may not support all function types. SQLBuild raises a clear error if a function type is unsupported by the configured adapter.