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!