databricks SQL UDF in select statement

[pepco]

In the Unity Catalog we can now create/register SQL UDFs. There are two types - one that returns table and other that returns just a value. If the function that returns value is based on the SQL query and joins it would in standard relational databases represent a correlated query executed for each row - which is usually a code smell in the relational databases. 

A very simple example (I just made it up):

select b.value
from table1 a
join table2 b
   on 1 = 1
   and b.col = a.id
   and b.col = p_parameter1
join table 3 c
   on 1 = 1
   and c.col = p_parameter2
   and ...

The function that returns single value based on simple logic; i.e. based on the input parameters is during execution extrapolated into the query. Basically, it's super helpful for hiding complex case statements or amounts recalculation using the same logic.

Since Databricks is using columnar storage, how does it behave for functions that contain joins? Does it also expand the underlying query into the main query? If yes, does it mean that for every row it adds underlying query?

[balajij8]

@pepco 

SQL UDFs in Unity Catalog are in lined directly by Catalyst during query planning. The function body is substituted into the logical plan before execution. It is fundamentally different from Python or Scala UDFs which operate as black boxes and execute row-by-row outside the optimizer's view. Since Spark can inspect the underlying SQL logic within the UDF, Catalyst applies subquery decorrelation effectively rewriting what would traditionally behave like a correlated scalar subquery into standard join operations.

When you call a scalar SQL UDF containing joins multiple times in the same query particularly with distinct parameters, Catalyst in lines each reference independently. If you inspect the execution plan using EXPLAIN EXTENDED, you will see a separate join (typically a left outer join) appended for each individual call. While Photon and Spark's columnar execution handle vectorized batch processing efficiently, chaining multiple joins will steadily increase query complexity, plan generation overhead and memory pressure.

The traditional RDBMS code smell is largely mitigated because execution is set-based and decorrelated rather than true row-by-row iteration. Encapsulating multi-table lookups or complex CASE expressions inside a SQL UDF provides significant governance and maintainability benefits across Unity Catalog compared to copy pasting join logic across multiple consumers.

The pattern remains practical as long as the lookup tables are relatively small, join keys maintain high cardinality or broadcast thresholds are met. If you are executing these functions on hot paths against massive fact tables however, the chained join overhead can still bottleneck performance. For those heavy-throughput scenarios, pre-materializing the lookup dataset or refactoring to an explicit join or Table-Valued Function (TVF) is generally the better path.

[AbhilashNagilla · Accepted solution]

The earlier reply has the mechanism broadly right, and your two questions have clean answers: yes to the first, no to the second.

A SQL scalar function whose body is a query is planned as a scalar subquery inside the calling statement. Databricks labels the worked example that way, with the comment -- Create a SQL function with a scalar subquery. sitting above it (CREATE FUNCTION). So the body becomes part of the one statement being planned, and nothing issues a second query per row. Columnar storage does not change that, since the correlation is resolved when the statement is planned rather than during row fetching, and the plan below will show you that directly.

Being a scalar subquery carries a constraint worth checking against your example: the query "must return a table that has one column and at most one row" (SQL expression). A body joining three tables with no aggregate can match more than one row for a given set of arguments. That succeeds at CREATE time and then fails at run time with SCALAR_SUBQUERY_TOO_MANY_ROWS, SQLSTATE 21000, "More than one row returned by a subquery used as an expression" (error conditions). An aggregate, or filters that guarantee a unique match, avoids it.

To see the shape for your own function, use its real parameter count and repeat the call:

EXPLAIN EXTENDED
SELECT a.id,
       cat.sch.your_fn(a.id, a.region),
       cat.sch.your_fn(a.id, a.prior_region)
FROM cat.sch.table1 AS a;

Read the optimized logical plan (EXPLAIN). You should see the body folded into the plan as join operators rather than a per-row lookup. One detail I would not assume in advance is the join type, which depends on your runtime and on whether the optimizer can prove the body returns at most one row. Where it cannot, the plan may carry that check in the join itself rather than showing a plain left outer join. For timings rather than structure, run the query and read the operator metrics in the Query Profile.

On the design question, the UDF overview puts built-in functions and SQL UDFs in its most efficient group, so encapsulating this logic is reasonable to keep doing. If a call site ever needs more than one column back, the table-returning form you mentioned, invoked with LATERAL, has a worked example on the same CREATE FUNCTION page.