Databricks user-defined functions (UDFs) let you apply custom logic to data in SQL or Spark when built-in functions do not meet the need. The main choices are SQL UDFs for reusable SQL expressions, Python UDFs for custom Python logic, and vectorized Pandas UDFs for some batch-oriented Python workloads; the right option depends on compatibility, performance, and how you need to share the function.
What a Databricks UDF is
A UDF is a function you define and then call as part of a query or DataFrame transformation. It can take input values, apply your logic, and return a value of a declared or inferred type. For example, a data team might define a function that normalizes a product code, applies a domain-specific score, or converts a specialized measurement.
UDFs are useful because the logic can be named and reused instead of copied into every query. They do not replace Databricks or Apache Spark built-in functions, however. Built-ins such as string, date, and mathematical functions are generally easier for Spark to understand and optimize. A custom function is most valuable when it encapsulates logic that is not already available or would otherwise be repeated and difficult to maintain.
In Databricks, a function may be created for one session or stored in a metastore so authorized users can call it later. The creation method, language, and availability depend on the compute environment and catalog setup. A function in a notebook is not automatically a permanent, organization-wide object.
Main types of user-defined functions
SQL UDFs are defined using SQL and are a natural fit for logic that can be expressed with SQL operators and built-in functions. A simple Databricks SQL example is:
CREATE FUNCTION circumference(radius DOUBLE)
RETURNS DOUBLE
RETURN 2 * PI() * radius;
The function can then be called in a query, for example with SELECT circumference(3.0). A SQL UDF is often the simplest choice when the rule is a compact expression and SQL users need to share it. Depending on how it is created and the catalog in use, it can be a temporary function or a persistent catalog function.
Scalar Python UDFs run custom Python logic on individual input rows or values. In PySpark, a function can be wrapped with udf and assigned a return type:
from pyspark.sql.functions import udf
from pyspark.sql.types import StringType
@udf(returnType=StringType())
def normalize_code(value):
return value.strip().upper() if value else None
result = df.withColumn('normalized_code', normalize_code('code'))
This example preserves null or empty input as None and normalizes a nonempty code. Specifying the return type makes the expected output explicit. The Python function is applied as part of Spark’s distributed work; it is not simply a local loop over the complete DataFrame on the driver.
Pandas UDFs, also called vectorized UDFs, use Apache Arrow to transfer batches of data between Spark and Python. Depending on the supported form and workload, the user function can operate on Pandas Series or batches rather than handling one value at a time. This can reduce per-row overhead, but it requires compatible dependencies and does not guarantee a speedup for every job.
Scala UDFs are another possibility in Spark environments where Scala is used. Support and the appropriate API depend on the Databricks runtime and compute type. Before choosing a language-specific UDF, check that the target environment supports it and that its dependencies can be made available there.
How UDF execution works
Databricks uses Spark to divide distributed data processing across the compute resources running a job or query. A UDF called in a DataFrame transformation or SQL statement becomes part of that computation. With a Python UDF, Spark must pass data to Python execution and receive the result; with a Pandas UDF, Arrow can move batches between the Spark and Python sides. The exact execution details depend on the function type and runtime.
This boundary matters because Spark can reason about built-in expressions more directly than arbitrary Python code. A Python UDF may be opaque to parts of query optimization: Spark generally cannot inspect the function body and rewrite its internal logic as it can with a sequence of native SQL expressions. The query planner may still optimize other parts of the query, but the UDF can limit some opportunities.
UDFs should also be written as if Spark may evaluate them on different workers and in parallel. Avoid relying on local state, execution order, or side effects such as writing a file once per row. A distributed query can retry work, process partitions in different orders, or run more than one task at a time. Functions that depend on mutable global state or external effects can therefore behave unexpectedly.
How to choose the right approach
Start by asking whether a built-in function or a combination of native SQL expressions can express the rule. If so, use that first: it is usually clearer to Spark’s optimizer and easier for other users to read. If not, select the narrowest custom option that meets the requirement.
- Choose a SQL UDF for concise logic expressed in SQL, particularly when SQL users need a reusable function.
- Choose a scalar Python UDF when the required logic is naturally written in Python and no suitable native expression exists.
- Consider a Pandas UDF when the operation can be applied efficiently to batches and you can support the required Python and Arrow environment.
- Consider a Scala UDF when your team already uses Scala and the target Databricks compute supports the required API.
- Keep the logic in a transformation or view if naming and reusing a function does not provide enough benefit to justify a separate UDF.
Also consider who will call the function. A notebook-local function is convenient for experimentation, while a catalog function is more suitable when multiple users, jobs, or SQL sessions need a governed, reusable object. Check dependencies, permissions, and runtime compatibility before moving a function from development into production.
Performance, testing, and common problems
UDF performance depends on the function body, data volume, type conversions, compute configuration, and the surrounding query. A Python UDF that performs little work per row can spend a substantial share of its time on serialization and language-boundary overhead. A Pandas UDF can reduce that overhead by processing batches, but expensive Python operations or large intermediate objects can still make it slow or memory-intensive.
Test with representative data rather than assuming that a vectorized implementation is faster. Compare results as well as elapsed time, and include nulls, empty strings, unexpected values, and boundary cases. A function that works for a small sample may expose type, memory, or data-quality issues at production scale. For Python UDFs, make sure the return value matches the declared Spark type; incompatible values can cause runtime errors or conversion problems.
Common issues include missing Python packages on the cluster, a return type that does not match the actual result, null handling that was not defined, and attempting to use a function on a compute type that does not support its language or feature. When a function is stored in a catalog, an incorrect catalog or schema reference can also make it appear unavailable. Check the job or SQL error details, the active catalog and schema, and the function’s declared signature before changing the query.
For troubleshooting, reduce the function to a small reproducible example, validate its behavior on representative inputs, and then test it within the intended Databricks compute environment. If a native expression can replace part of the UDF, doing so may improve both performance and diagnosability.
Sharing functions with Unity Catalog
Unity Catalog can provide a governed place to store functions so they can be managed as data objects rather than recreated in individual notebooks. A persistent function can be referenced by its catalog and schema, which helps make its location explicit. This is useful for shared business rules, such as standardized parsing or scoring logic, when multiple workflows need consistent behavior.
Creating and calling a catalog function are separate permission questions. Users generally need the relevant usage privileges on the catalog and schema, as well as permission to execute the function; creating one requires the appropriate create privilege on the schema. Administrators should grant access according to the function’s audience and the data it can expose. Exact feature availability and supported languages can vary by Databricks product, runtime, and compute type, so verify those constraints for the intended deployment.
Document the function’s purpose, input and return types, null behavior, and any dependencies. Treat changes to a shared function as changes to a shared interface: callers may rely on its current behavior even if they are maintained by different teams. Test changes against known cases and coordinate deployments where downstream jobs depend on the result.
Costs and operational considerations
A UDF is not usually a separate per-call service with its own independent price. Its work runs on the compute used by the query or job, so resource consumption and cost are tied to the workload and the Databricks pricing model for that compute. An inefficient UDF can increase runtime and resource use; a function that simplifies repeated logic may make development and maintenance easier, but it does not automatically reduce compute costs.
Operationally, account for the full lifecycle: development, dependency installation, testing, permissions, monitoring, and upgrades. External Python packages may need to be installed or otherwise made available on the relevant compute, and a function that works in one notebook environment may fail in a separately configured job or SQL environment. Keep reusable dependencies controlled and use the same supported environment in validation and production.
UDFs compared with alternatives
A UDF is not the only way to package reusable logic. Built-in Spark SQL functions and DataFrame expressions are usually the first alternative to examine. Views can provide reusable query logic without introducing a custom function, while notebooks, libraries, and shared modules can organize Python code that is not naturally a scalar expression. The right boundary depends on whether users need to call the logic inside SQL, a DataFrame transformation, or a broader application.
A user-defined table function (UDTF) is also different from a scalar UDF. A scalar UDF returns a value for an input invocation; a table function can return rows and is used in a table-producing context where supported. Do not choose a scalar UDF when the actual task is to expand each input into multiple output rows. Confirm which function forms are available in the Databricks environment before designing around them.
A useful rule of thumb is to keep computation in native Spark expressions when possible, use a UDF when custom logic genuinely calls for one, and make shared functions explicit, tested, and permission-controlled. This balances flexibility with optimization and operational simplicity.
Frequently Asked Questions
Are Databricks UDFs the same as Spark UDFs?
Databricks runs on Spark technologies, and PySpark UDFs use Spark’s user-defined-function model. Databricks also provides catalog and SQL capabilities that affect how functions are created, shared, and governed. Supported features can depend on the runtime and compute type.
Should I use a Python UDF or a SQL UDF?
Use a SQL UDF when SQL can express the rule and SQL users need to reuse it. Use a Python UDF when the logic requires Python and cannot be expressed adequately with native SQL or Spark functions. Prefer native expressions when they meet the need.
Are Pandas UDFs always faster than regular Python UDFs?
No. Pandas UDFs can reduce overhead by processing batches, but results depend on the operation, data volume, memory use, and environment. Benchmark both correctness and performance with representative data.
Can I use a UDF in multiple Databricks notebooks?
Yes, if it is made available to those notebooks. A function defined only in a session or notebook is not automatically shared permanently. A persistent catalog function or a shared code library can serve broader reuse, subject to the compute type and permissions.
Why does my UDF work in a notebook but fail in a job?
The job may use different compute, runtime settings, installed packages, catalog context, or permissions. Check that the job environment supports the UDF type, has its dependencies, uses the correct catalog and schema, and has access to execute the function.
Related
- Split String Into an Array in Excel
- Normal Balance in Accounting: How Debits and Credits Work
- Balance of Payments Mind Map: Accounts and Flows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- PowerShell Excel Alignment: Center, Left, and Right TextYes. In PowerShell on Windows, use Excel’s COM automation to set a cell range’s HorizontalAlignment and, if needed,…
- Monthly Loan Payment Formula in ExcelTo calculate a fixed monthly loan payment in Excel, use PMT. With the loan principal in B1, the…
- OFFSET in Excel: Syntax, Examples, and UsesIn Excel, OFFSET returns a reference to a cell or range located a specified number of rows and…
- Concatenate in Excel: Formulas, Delimiters, and TipsTo concatenate in Excel means to join text or values into one text string. In Microsoft Excel for…
- Excel Timesheet: Calculate Hours Worked With FormulasTo calculate a shift in an Excel timesheet, put the start time in C2, end time in D2,…
- Excel FILTER Formula: Remove DuplicatesTo return filtered results without duplicates in Excel, combine FILTER with UNIQUE: FILTER selects the rows you want,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.