Unity Catalog functions: SQL, Python and Batch Python UDFs

1.Registering SQL and Python functions in Unity Catalog

A session-scoped UDF disappears when its notebook or SparkSession ends. A Unity Catalog function is a governed object that can be shared and called from SQL or PySpark on any supported compute. You create one with CREATE FUNCTION, and the name must take the form catalog.schema.function_name. You can leave off the prefix only if you have already selected the catalog and schema in the SQL Editor. If you register a Python UDF without a catalog or schema, it goes into the current active schema.

The same function can be written in either language. A SQL UDF declares LANGUAGE SQL and returns an expression. A Python UDF declares LANGUAGE PYTHON, puts its body between double dollar signs, and must declare the return type mapping.

CREATE OR REPLACE FUNCTION my_catalog.my_schema.calculate_bmi(weight DOUBLE, height DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
RETURN
SELECT weight / (height * height);

CREATE OR REPLACE FUNCTION my_catalog.my_schema.calculate_bmi(weight_kg DOUBLE, height_m DOUBLE)
RETURNS DOUBLE
LANGUAGE PYTHON
AS $$
return weight_kg / (height_m ** 2)
$$;

Privileges differ by action. To create a function you need USAGE and CREATE on the schema and USAGE on the catalog. To run one you need EXECUTE on the function and USAGE on its schema and catalog. For functions everyone should use, Databricks recommends a dedicated catalog and schema with appropriate access controls. For team functions, use a dedicated schema in the team's catalog. Document each function in its docstring: version number, changelog, purpose, parameters, return value and a usage example. Then callers know what changed when you run CREATE OR REPLACE.

Sources1

2.Inside a Unity Catalog Python UDF: sandbox, handlers, environments, secrets

A Unity Catalog Python UDF runs in a secure, isolated environment and has no access to file systems or internal services. Your code must handle NULLs itself. You can define any number of helper functions, but each must return a scalar. Outbound network traffic is allowed on ports 80, 443 and 53 when you use serverless compute or standard access mode, so a UDF can call an external API with requests. Python UDFs are also a natural fit for tokenization, masking, redaction and encryption, for example a mask_email function used in a dynamic view.

The HANDLER clause names one Python function in the body as the entry point. Code outside the handler runs once, when each Python environment initializes the UDF. That makes it the place for setup work that every call can reuse. The ENVIRONMENT clause pins environment_version.

CREATE OR REPLACE FUNCTION my_catalog.my_schema.greet(name STRING)
RETURNS STRING
LANGUAGE PYTHON
HANDLER 'greet_handler'
ENVIRONMENT (
  environment_version = '6'
)
AS $$
# Runs once when each Python environment initializes the UDF.
greeting_prefix = "Hello"

def greet_handler(name):
    return f"{greeting_prefix}, {name}!"
$$;

That separation is why Databricks recommends setting environment_version explicitly in every Unity Catalog Python UDF. On classic compute, choose a version that meets the UDF's feature requirements and does not go above the recommended maximum for the cluster's runtime.

Secrets follow a declare-then-read pattern. List the Unity Catalog secret's three-part name in the SECRETS clause, set environment_version to 6 or above, and call databricks.secrets.get() in the body. This is a different thing from a workspace-level Databricks secret. Redaction lowers the risk of a secret leaking through errors and logs, but it cannot stop your UDF from returning the secret in query results, so never return secret values.

Sources1

3.Batch Unity Catalog Python UDFs

A row-at-a-time Unity Catalog UDF has the same per-row overhead as a session UDF. The governed answer to that is a Batch Unity Catalog Python UDF. It is generally available and processes batches of data instead of single rows. You create it like a regular Unity Catalog UDF with two additions. PARAMETER STYLE PANDAS makes the UDF process batches with pandas iterators, and HANDLER names the function that receives them. The handler has the same shape as a session-scoped Iterator of multiple Series pandas UDF. On classic compute it needs Databricks Runtime 16.3 or above. It is also supported on serverless compute and on pro and serverless SQL warehouses.

CREATE OR REPLACE FUNCTION my_catalog.my_schema.calculate_bmi_pandas(weight_kg DOUBLE, height_m DOUBLE)
RETURNS DOUBLE
LANGUAGE PYTHON
DETERMINISTIC
PARAMETER STYLE PANDAS
HANDLER 'handler_function'
AS $$
import pandas as pd
from typing import Iterator, Tuple

def handler_function(batch_iter: Iterator[Tuple[pd.Series, pd.Series]]) -> Iterator[pd.Series]:
  for weight_series, height_series in batch_iter:
    yield weight_series / (height_series ** 2)
$$;

Notice the DETERMINISTIC keyword. Databricks treats a Batch Unity Catalog Python UDF as non-deterministic unless you say otherwise. That default suits functions that generate random values, read the current time, or call external APIs. A pure calculation like BMI should declare itself deterministic. Batch UDFs also receive timestamps as pandas.Series values, not the per-row datetime objects a scalar UDF gets.

Sources2

4.Matching UDF features to compute constraints

Exam scenarios usually come with a constraint, such as a particular warehouse type, runtime or feature, and ask whether a UDF will work. The baseline is that Python code in Unity Catalog UDFs needs a serverless or pro SQL warehouse, or a cluster on Databricks Runtime 13.3 LTS or above. One sharp edge is that a view containing a Unity Catalog Python UDF fails on classic SQL warehouses. Newer features carry their own requirements. On serverless compute and pro or serverless SQL warehouses, most of them work only if the UDF explicitly sets environment_version to 6 or above.

Environment version 6 also changes how timestamps arrive. A TIMESTAMP input to a row-at-a-time Python UDF becomes a timezone-naive datetime in UTC. The earlier path, with no ENVIRONMENT clause or a version below 6 on serverless compute, gave a timezone-aware value in the session timezone. Code that read tzinfo or the local clock fields has to restore them explicitly, for example with date.replace(tzinfo=timezone.utc) followed by astimezone.

Sources1

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Upgrading the notebook's serverless environment upgrades the environment of the Unity Catalog Python UDFs it calls.Why is that wrong?

   A Unity Catalog Python UDF runs in the environment_version set in its own ENVIRONMENT clause. The session environment does not change it.

   Covered in Inside a Unity Catalog Python UDF: sandbox, handlers, environments, secrets

2. 2.A Batch Unity Catalog Python UDF is deterministic unless you mark it otherwise.Why is that wrong?

   It is the other way round. Batch Unity Catalog Python UDFs are non-deterministic by default, so add DETERMINISTIC to pure functions.

   Covered in Batch Unity Catalog Python UDFs

3. 3.A view that wraps a Unity Catalog Python UDF works on any SQL warehouse.Why is that wrong?

   Such a view fails on classic SQL warehouses. Python UDF code needs a serverless or pro SQL warehouse, or a cluster on Databricks Runtime 13.3 LTS or above.

   Covered in Matching UDF features to compute constraints

Practise it for real

Register the same BMI calculation as a SQL UDF, a Python UDF and a Batch Python UDF in Unity Catalog, then call it from SQL.

1. 1.In a schema where you have USAGE and CREATE, run the LANGUAGE SQL CREATE OR REPLACE FUNCTION my_catalog.my_schema.calculate_bmi(weight DOUBLE, height DOUBLE) statement from this page.

   Why: It shows the three-part naming and the privileges needed to create a function.

   You should see: The function is created in my_catalog.my_schema.

2. 2.Replace it with the LANGUAGE PYTHON version whose body sits between $$ and returns weight_kg / (height_m ** 2).

   Why: It shows that a Python UDF needs the $$ body and a declared return type.

   You should see: CREATE OR REPLACE succeeds and the function now runs Python.

3. 3.Run SELECT person_id, my_catalog.my_schema.calculate_bmi(weight_kg, height_m) AS bmi FROM person_data; on a serverless or pro SQL warehouse.

   Why: Python Unity Catalog UDFs need a serverless or pro warehouse, or Databricks Runtime 13.3 LTS or above.

   You should see: Each row gets a bmi value.

4. 4.Create calculate_bmi_pandas with DETERMINISTIC, PARAMETER STYLE PANDAS and HANDLER 'handler_function', then call it the same way.

   Why: It turns the row-at-a-time function into a governed batch function and overrides the non-deterministic default.

   You should see: Same results as the scalar version, computed over batches of rows.