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.