Databricks DE · Topic 8.5

Unity Catalog Functions

Reusable, governed functions registered in UC by 3-level name — scalar UDFs (a value), table functions/UDTFs (a table), and Python UDFs — shared across queries and controlled by GRANT EXECUTE. This deep dive walks each with verified SQL.

scalar UDFRETURNS TABLE (UDTF)LANGUAGE PYTHONGRANT EXECUTE

The one-liner

UC functions persist business logic in Unity Catalog (catalog.schema.fn): a scalar UDF returns one value (SELECT/WHERE), a UDTF (RETURNS TABLE) returns a table (FROM). Governed by GRANT EXECUTE; they also power column masks & row filters.

📐 Analogy: a saved formula in a shared spreadsheet — define bmi(weight,height) once, everyone reuses it by name, and you control who can run it. A UDTF returns a whole sheet, not one cell.

Scalar vs table function — click each

Scalar UDF (a value)

UDTF (a table)

Returns one value per row → use in SELECT/WHERE.

CREATE FUNCTION prod.util.bmi(w DOUBLE, h DOUBLE)
RETURNS DOUBLE DETERMINISTIC RETURN w/(h*h);
SELECT id, prod.util.bmi(weight, height) AS bmi FROM patients;

Returns a whole table (RETURNS TABLE) → use in FROM like a parameterized view.

CREATE FUNCTION prod.util.top_orders(region STRING, n INT)
RETURNS TABLE (order_id BIGINT, amount DECIMAL(10,2))
RETURN SELECT order_id, amount FROM prod.sales.orders
  WHERE region = top_orders.region ORDER BY amount DESC LIMIT n;
SELECT * FROM prod.util.top_orders('West', 10);

1 · Python UDF (governed, persistent)

For logic SQL can't express; the body is dollar-quoted. Runs per-row in Python → slower than SQL/built-ins, keep off hot paths.

CREATE OR REPLACE FUNCTION prod.util.normalize_phone(p STRING)
RETURNS STRING
LANGUAGE PYTHON
AS $$
  import re
  return re.sub(r'\D', '', p) if p else None      # strip non-digits
$$;

2 · Governance & masks/filters

Calling a function needs USE CATALOG + USE SCHEMA + EXECUTE (same chain as 8.2). The same kind of UDF is what column masks (8.3) & ABAC policies (8.4) attach.

GRANT EXECUTE ON FUNCTION prod.util.bmi TO `analysts`;

-- a mask function consumed by 8.3 column masks / 8.4 ABAC policies
CREATE OR REPLACE FUNCTION prod.util.mask_email(e STRING) RETURNS STRING
RETURN CASE WHEN is_account_group_member('pii_readers') THEN e ELSE '***@***' END;

3 · Can you run it? — toggle the grants

▸ calling prod.util.bmi(...) needs the USE chain + EXECUTE

USE CATALOG prodno

USE SCHEMA prod.utilno

EXECUTE ON FUNCTION prod.util.bmino

🔒 cannot execute function

Scalar vs UDTF vs Python

	Scalar UDF	Table function (UDTF)	Python UDF
Returns	one value	a table (`RETURNS TABLE`)	one value
Used in	SELECT/WHERE	FROM	SELECT/WHERE
Language	SQL or Python	SQL	Python
Perf	fast (SQL)	fast (SQL)	slower (per-row)

Why it matters

Business logic lives in one governed place instead of copy-pasted into every notebook — consistent, permissioned, auditable.
They're the building block for column masks & row filters (8.3/8.4) — those are UC functions.

🧮 Real-world use case: define prod.util.mask_email(email) once; reuse in queries and attach it as a column mask. A UDTF prod.util.top_orders(region, n) lets analysts call SELECT * FROM prod.util.top_orders('West', 10).

Uses, edge cases & limitations

Uses: centralizing business rules; powering masks/filters; parameterized result sets (UDTFs); sharing logic across teams.
Edge cases: Python UDFs are slower than built-ins (per-row Python) — prefer SQL/built-ins on hot paths; mark DETERMINISTIC only if it truly is (don't mark functions using current_*).
Limitation: governed by EXECUTE + USE chain (same as 8.2); heavy UDFs in masks/filters hit the 8.3 perf notes.

Common gotchas

❌ Re-defining logic as a notebook UDF everywhere — register in UC once and GRANT EXECUTE.
❌ Forgetting EXECUTE (and USE CATALOG/SCHEMA) → permission denied calling it.
❌ Heavy Python UDF on a hot path where SQL/built-in would do.
❌ Confusing a scalar UDF (value, in SELECT) with a UDTF (RETURNS TABLE, in FROM).
❌ Marking a non-deterministic function DETERMINISTIC → wrong cached results.