Support for UDFs as a materialization

[brabster]

Kicking off a discussion after conversation with @dataders on Slack, with an original mention back in Feb in #i-made-this

Straightforward UDF as a materialization implementation

I experimented with UDFs as a materialization in BigQuery a few months ago and it went pretty well - UDF as materialization seem to play quite nicely with the dependency graph and tests (writeup). I created a public repo as part of that work and I've seen a bit of traffic there and been contacted about it a couple of times.

My implementation is deliberately minimal, involving only a new materialization macro that could perhaps be implemented by adapters without any changes elsewhere in the dbt ecosystem. Improving the param list from a string felt like a good improvement but as it is, it actually solves the problem quite neatly in practice for my usecases. Those include testing, ref-ing within models and UDFs ref-ing one another. I've undertaken a significant refactor from hooks to this materialization, and some subsequent changes to the UDFs themselves without issues.

Usage examples

Examples come from https://github.com/brabster/pypi_vulnerabilities

Simple UDF

{{ config(
    materialized='udf',
    parameter_list='spec STRING',
    returns='STRING',
    description='Extracts semver operator from specification string, eg. "<" in "<1.1.1"'
) }}

REGEXP_EXTRACT(spec, r'^([<=>]+)')

UDF Reference another UDF

{{ config(
    materialized='udf',
    parameter_list='spec STRING',
    returns='STRING',
    description='Convert semver spec to a sortable string value. Supports up to 4 components, eg. 1.2.3.4'
) }}

{{ ref('semver_index_to_comparable') }}(spec, 0)
  || '.' || {{ ref('semver_index_to_comparable') }}(spec, 1)
  || '.' || {{ ref('semver_index_to_comparable') }}(spec, 2)
  || '.' || {{ ref('semver_index_to_comparable') }}(spec, 3)

Model reference UDF

SELECT
    download.download_date,
    download.package,
    download.package_version,
    download.installer,
    download.download_count,
    vuln.cve,
    vuln.commit_date,
    {{ ref('matches_multi_spec') }}(vuln.specs, download.package_version) was_known_vulnerable_when_downloaded
FROM {{ ref('daily_package_downloads') }} download

UDFs deploying as part of DAG

08:28:58  Found 33 models, 73 data tests, 3 sources, 594 macros
08:28:58  
08:28:58  Concurrency: 8 threads (target='current')
08:28:58  
08:28:58  1 of 102 START sql view model published_us_billing.daily_billing ............... [RUN]
08:28:58  2 of 102 START sql udf model published_us_internal.extract_op .................. [RUN]
08:28:58  3 of 102 START sql udf model published_us_internal.extract_prerelease .......... [RUN]
08:28:58  4 of 102 START sql udf model published_us_internal.extract_prerelease_prefix ... [RUN]
08:28:58  5 of 102 START sql udf model published_us_internal.extract_prerelease_suffix ... [RUN]
08:28:58  6 of 102 START sql udf model published_us_internal.extract_release ............. [RUN]
08:28:58  7 of 102 START sql udf model published_us_internal.extract_version ............. [RUN]
08:28:58  8 of 102 START sql view model published_us_internal.file_downloads ............. [RUN]
08:28:59  5 of 102 OK created sql udf model published_us_internal.extract_prerelease_suffix  [None (0 processed) in 0.75s]
08:28:59  9 of 102 START sql view model published_us_billing.looker_studio_billing ....... [RUN]
...
08:29:00  17 of 102 START test test_udf_extract_prerelease ............................... [RUN]
08:29:00  18 of 102 START test test_udf_extract_prerelease_prefix ........................ [RUN]
...

Evidence that UDF support is a persistent demand for dbt

I wrote about testing and deploying UDFs via a hooks a couple of years ago and according to Google Search Console that post attracts thousands of impressions and over a hundred clicks a month, consistently, for the past couple of years - 2,500 clicks in the last 12 months - for queries like "dbt udf". I conclude that UDF handling in dbt is a long-running problem affecting lots of practitioners. I can see demand back to 2018. There are at least a couple of issues on the topic across adapters and core

• Persistent UDF Materialization dbt-bigquery#454
• Manage data warehouse objects (other than tables and views) #5099

UDFs vs. Macros

Why not just use dbt macros? Amazon Athena does not support SQL macros for some reason. I was forced to replicate what I'd achieved with UDFs on more capable platforms with macros, and I wrote up some advice for others in the same position... so I can speak to why I had a much less pleasant experience. In no particular order:

• cannot use UDFs outside the dbt ecosystem, so ad-hoc queries and other tooling are impacted
• generated SQL is more complex and harder to understand
• no identity or documentation for functions in the DWH, leading me to try and work around that with comments in the SQL to explain the mess of SQL that should have just been a named UDF
• macros that refer to other macros generate really awful SQL, and there’s nothing to control the scope of your macro. That means if you’re not careful, operator precedence in the surrounding query can alter the result. A macro add_one with content x + 1 will work fine in most contexts, but when used like this: {{ add_one(100) }} * 2, you get 102 instead of the expected 202
• UDF can declare the types of parameters and results that are checked at runtime – so if you say it takes an INT64 and someone tries to run it on a string, boom – nice clear error. You can’t do that with a macro

In the examples I gave above, there are maybe eight or more small functions that assemble into a pure SQL semver matching function. I would not have fancied trying to do that as macros, and the SQL that would have been generated would, I suspect, be the stuff of nightmares. UDFs + dbt encouraged active decomposition, TDD and ended in a well-tested result I could confidently build on.

Non-SQL UDFs

I can only really speak to my experience on BigQuery - I think JS UDFs might be able participate directly with the implementation I've given. If it were me, I would focus on getting consistent support for SQL UDFs, as I think they should be supported across most platforms, rather than trying to come up with a solution for all possible variants of UDF functionality.

Current Situation

Example repo in https://github.com/brabster/dbt_materialized_udf

It looks like the materialization approach works quite well, solves a real problem, and doesn't require any changes or direct support in dbt itself. I don''t want to create a central repo that will attract the UDF materializations for multiple adapters and become a support problem like dbt-external-tables. It seems sensible to add this materialization to adapters where the communities, expertise and quality controls are already in place, which is why I'm asking here. What do you think is a good way forward? For example, should I raise a PR in the BigQuery adapter and PostgreSQL adapter repos to adopt the materializations that are in that repo (PostgreSQL support is proposed in an issue)

[Isarien]

Very usefull feature.
I use it for udf and stored procedure. It works fine.

[moseleyi]

Having version-controlled UDFs that do not slow the whole pipeline (hooks) and better management of grants would be a perfect addition!

[zoraida]

Hi! that is very nice! I am new on DBT and I am already facing a problem that I cannot do with SQL, I was wondering if i could build a python model that gets materialised as udf and then use this model as an aggregation function since this is what i need. Any suggestion? Thanks!

[brabster]

Which platform are you on? Snowflake? I think if the data warehouse itself provides a way to define a Python model as a UDF/UDAF in SQL, then you should be able to template out the SQL and use the approach I've outlined. I've just used the same approach to add BigQuery ML AI models into the dbt graph because they're defined using SQL, for example. If you can share which platform you're on and maybe an example of the kind of Python model you want to use as a UDAF then someone might be able to help?

[zoraida]

Hi! Thanks for the answer! We use Databricks so yes, it does support to implement Python UDFs that can be later used from SQL. I would like to be able to implement UDFs when needed. For the specific use case, i need to use the UDF into a group by group.

[brabster]

OK cool. I don't know much about Databricks, but it looks like you should be able to use this approach.

https://www.databricks.com/blog/2022/07/22/power-to-the-sql-people-introducing-python-udfs-in-databricks-sql.html

I think it's a minor adjustment to the existing materialization macro, something like this after adjusting the input parameters:

{%- set create_sql -%}
CREATE OR REPLACE FUNCTION {{ target }}({{ parameter_list }})
RETURNS {{ ret }}
LANGUAGE PYTHON
AS $$
  {{ python_body }}
$$;
{%- endset -%}

Could probably make the language configurable if you need to support SQL and Python UDFs:

{{ config(
    materialized='python_udf',
    parameter_list='a STRING',
    returns='STRING',
    description='not sure if Databricks supports UDF descriptions?'
) }}
import json
keys = ["email", "phone"]
obj = json.loads(a)
for k in obj:
  if k in keys:
    obj[k] = "REDACTED"
return json.dumps(obj)

I'm not sure how you'd set up a UDAF, but from what you've said that might not be necessary.

i need to use the UDF into a group by group

sounds like you can just use the UDF and group by the resulting value, something like:

SELECT
  my_udf(my_col) new_val
FROM ...
GROUP BY new_val

Hopefully there's enough there to help you out - maybe shout on the dbt Slack if you need dbt+Databricks specific help?

[zoraida]

Hi! thank you Paul! i will play with it and let you know how it goes. And yes, i do need a UDF not an UDAF.

[Kukunin]

Custom materialization is a great way to support UDFs. thanks for the idea. We use postgres, and this is what we end up for udfs:

{% materialization udf, adapter="postgres" %}
  {%- set target = adapter.quote(this.database) ~ '.' ~ adapter.quote(this.schema) ~ '.' ~ adapter.quote(this.identifier) -%}

  {%- set parameter_list=config.get('parameter_list') -%}
  {%- set ret=config.get('returns') -%}

  {%- set create_sql -%}
  CREATE OR REPLACE FUNCTION {{ target }}({{ parameter_list }})
  RETURNS {{ ret }}
  LANGUAGE plpgsql
  IMMUTABLE
  AS $$
    {{ sql }};
  $$
  {%- endset -%}

  {% call statement('main', fetch_result=False, auto_begin=False) -%}
    {{ create_sql }};
    COMMIT
  {%- endcall %}

  {{ return({'relations': []}) }}

{% endmaterialization %}

One weird thing I couldn't explain is that it doesn't work without COMMIT. From logs I saw actually it reverts the transaction. auto_begin = False doesn't help neither, so the hack was to add an explicit COMMIT there. I'm not a big dbt guru, hope you can advice something.

This is an example of a udf defined:

{{ config(
  materialized='udf',
  parameter_list='val TEXT, formats TEXT[]',
  returns='DATE'
) }}

DECLARE
  result DATE;
  fmt TEXT;

BEGIN
  FOREACH fmt IN ARRAY formats LOOP
    BEGIN
      result := to_date(val, fmt);
      RETURN result;
    EXCEPTION WHEN OTHERS THEN
      CONTINUE;
    END;
  END LOOP;

  -- If no format succeeded, return NULL
  RETURN NULL;
END

There is an issue to support this in unit tests, because it's impossible (afaik) to return an inline function as CTE, so we worked around it this a custom macro:

{% macro ref_udf(id) %}
  {{ ref(id) }}
{% endmacro %}

and overrides for unit tests:

unit_tests:
  - name: ...
    model: ...
    given:
      #...

    overrides:
      macros:
        ref_udf: 'public.generate_address_id'

    expect:
      rows:
      #...

UDF dependency is still tracked via the macro decorator, which is great. There are still an issue that we depend on production version of the function and it must exist before running unit tests, but at least it works

[brabster]

Awesome. No idea why you'd need to explicitly commit, sorry you had a couple of extra hoops to jump through but at least it's working!

[james-johnston-thumbtack]

@Kukunin Regarding the unit tests, what if a data model calls more than one UDF? How would you specify the overrides? Have you found any solution, other than creating separate parameterless macros to reference each UDF? For example:

Model:

SELECT {{ ref_udf('first_udf') }}(), {{ ref_udf('second_udf') }}()

Unit test YAML:

    overrides:
      macros:
        # This is obviously not correct, but what would you put here?
        ref_udf: 'public.first_udf'
        ref_udf: 'public.second_udf'

The dbt documentation only provides examples for macros that don't have any input parameters. Most unfortunate.

Normally, in other programming languages with test mocking frameworks, we would declare a list of expected function calls with expected inputs that the mock would assert against... and different mock outputs that vary depending on the mock inputs...

I suppose one could work around this, using entirely separate macros (ugly!):

SELECT {{ ref_first_udf() }}(), {{ ref_second_udf() }}()

[Kukunin]

yep, I guess with multiple udfs we should have an ugly multiple macros solution. Don't know more elegant solution.

[moseleyi]

Thank you @brabster for the UDF materialization for BigQuery.. it will come handy! I wish dbt developed more support for other objects in the warehouse like functions!

[graciegoheen]

@brabster Would LOVE to get your thoughts on a new discussion I started, where UDFs would be a new node type in dbt -> #11851

[brabster]

Sorry for the delay, have been taking a holiday! I have written a related post I had planned today to flush my mental buffers https://tempered.works/posts/2025/08/04/udafs-stored-procedures-and-more-in-dbt/ and I'll share thoughts on the specifics on the other issue tomorrow.

[james-johnston-thumbtack]

I like the approach that @brabster came up with of making UDF a custom materialization - very nice! I ported it to RisingWave, which is a stream processing database. I shared my results on a discussion there, at https://github.com/orgs/risingwavelabs/discussions/22768

I took a few artistic liberties compared to @brabster's custom materialization. Perhaps it might be a useful design for an enhanced BigQuery UDF materialization, or other database engine UDF materializations.

• The parameter list is now a Jinja list, e.g. ["first BIGINT", "second BIGINT"]
• A new language config is now accepted. You can specify sql, javascript, python, remote (for remote functions), etc. so that the UDFs don't have to be in SQL.
• A new function_type config is now accepted. It can be:
  • scalar: function accepts scalar inputs and returns a scalar output.
  • aggregated: function aggregates multiple input rows into a scalar output. In BigQuery it would be a user-defined aggregate function
  • table: function is a table-valued function that returns multiple rows and columns. In BigQuery it would be a table function.
• A new options config is now accepted. It's a generic key/value dictionary that can be passed as options when creating the UDF, e.g. RisingWave's WITH clause, or BigQuery's OPTIONS clause.
• RisingWave doesn't yet support function comments/descriptions. But if it did, I would look into making sure the persist_docs config was obeyed properly. See for example this block in the BigQuery adapter used for other materialization types: https://github.com/dbt-labs/dbt-adapters/blob/91cbc2f76e204de6aa03f656bc02ad589d06e2fb/dbt-bigquery/src/dbt/adapters/bigquery/impl.py#L773-L775

Some other changes of note:

• The custom materialization now runs hooks.
• Models of other materialization types can be replaced. I.e. it will call load_relation(this) and drop any relation found using adapter before trying to create the UDF.

The approach I took to write this custom materialization was to start with the adapter's materialization for a view, and then I adapted it to create a UDF instead of a view, but otherwise keeping a roughly similar structure. I think that would be a good approach to follow for other database engines, just to make sure important functionality and patterns within the adapter are still followed.

[brabster]

Awesome! I really wanted to keep my implementation super-simplistic, this looks like a great starting point for generalising! 🙏