# Using dbt to manage user defined functions

**URL:** <https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18>\
**Category:** Show and Tell\
**Tags:** udf\
**Created:** [April 19, 2018, 2:20am UTC](https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18 "2018-04-19T02:20:22Z")\
**Posts on this page:** 6\
**Page:** 2

<div class="post-metadata">

**Author:** ![hvignolo](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/hvignolo/32/3605_2.png) [@hvignolo](https://discourse.getdbt.com/u/hvignolo)\
**Post date:** [February 2, 2024, 1:59pm UTC](https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18/22 "2024-02-02T13:59:41Z")

</div>

Hey! Hope you’re doing well.

I just wanted to add something that I think might be helpful to this excellent original idea.

The thing is, the `on-run-start` hook can add some seconds to each model/test run to apply all the UDFs. In my case, the overall sum of those seconds was around 10-15% of the entire dbt pipeline runtime, and I considered it was too much time for something that is a static thing.

To tackle this, I decided to do the same idea described here, but instead of executing it with the hook, it’s executed in a step of the CI pipeline, in case some file that matches the glob pattern `macros/udfs/**/*.sql` is modified. You may need to make some adjustments to your infrastructure (your runners), but it worthed a while for my case.

Thanks!

---

<div class="post-metadata">

**Author:** ![brabster](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brabster/32/1021_2.png) [@brabster](https://discourse.getdbt.com/u/brabster)\
**Post date:** [February 19, 2024, 10:38am UTC](https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18/23 "2024-02-19T10:38:52Z")

</div>

Hey, I’ve written an article on udfs as a custom dbt materialization, which allows you to incorporate them into the DAG and so solve most (all?) of the problems I’ve seen mentioned here elegantly.

Writeup here [Materialized UDFs in a dbt World - Tempered Works](https://tempered.works/posts/2024-02-19-udf-dbt-models/)

Example here [pypi\_vulnerabilities/models/published/udfs/matches\_multi\_spec.sql at main · brabster/pypi\_vulnerabilities · GitHub](https://github.com/brabster/pypi_vulnerabilities/blob/main/models/published/udfs/matches_multi_spec.sql)

---

<div class="post-metadata">

**Author:** ![brabster](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brabster/32/1021_2.png) [@brabster](https://discourse.getdbt.com/u/brabster)\
**Post date:** [July 2, 2024, 7:29pm UTC](https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18/24 "2024-07-02T19:29:08Z")

</div>

Note [discussion #10395 on support for UDFs as a materialization](https://github.com/dbt-labs/dbt-core/discussions/10395)

---

<div class="post-metadata">

**Author:** ![dan\_datagoals](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/dan_datagoals/32/4029_2.png) [@dan\_datagoals](https://discourse.getdbt.com/u/dan_datagoals)\
**Post date:** [July 12, 2024, 9:05am UTC](https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18/25 "2024-07-12T09:05:15Z")

</div>

Thanks @claire this was really helpful - I just finished getting it working on databricks.

The only hiccup I came across was that it didn’t want to run multiple create udf statements in one go - So I had to remove the create\_udfs macro and instead put each of the individual create udf macros into the on-run-start command in the dbt\_project.yml file…

> on-run-start:
> 
> - ‘{{create\_udf\_mask\_email\_address()}}’
> - ‘{{create\_udf\_mask\_phone\_number()}}’

---

<div class="post-metadata">

**Author:** ![jch](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@jch](https://discourse.getdbt.com/u/jch)\
**Post date:** [February 4, 2025, 7:31pm UTC](https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18/26 "2025-02-04T19:31:03Z")

</div>

Thank you - this is super useful! I adapted a version from this for Snowflake. One question, I’m curious as why you opted to return an empty list at the end of the custom materialization versus returning the relation of the UDF?

Specifically `{{ return({'relations': []}) }}` versus `{{ return({'relations': [target}) }}`. dbt docs are a little opaque, so I’m not sure how this impacts how dbt tracks state changes, dependencies, etc

---

<div class="post-metadata">

**Author:** ![brabster](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brabster/32/1021_2.png) [@brabster](https://discourse.getdbt.com/u/brabster)\
**Post date:** [February 9, 2025, 5:00pm UTC](https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18/27 "2025-02-09T17:00:31Z")

</div>

Thinking back, it wasn’t clear to me what I should return and why from the docs. I returned the empty array to see what broke and nothing did - so it’s still the empty array. There have been no reports of issues that I’m aware of!

[Previous page](https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18.md?page=1)