dbt UDFs
Published on .
At Howl we strive for reproducibility via dbt, so we needed a way to manage UDFs. For models we use macros, but we do a lot of adhoc SQL too for which UDFs are valuable.
Since we often run single models, the on-run-start solution from “Using dbt to manage user defined functions” was not ideal because it ran the UDFs every time.
We wanted a solution that defined the UDFs within macros that could be run on demand, which after much grumbling I determined requires the use of run_query().
Here is what I came up with, which has been working great for many months now.
{% macro create_udfs() %}
{#
Create some UDFs for to be used outside of dbt
Usage:
dbt run-operation create_udfs
#}
{%set sql %}
{{ my_macro() }}
grant usage
on function {{ target.schema }}.my_macro(varchar)
to role my_role;
{% endset %}
{% do run_query(sql) %}
{% do log("Created UDFs and granted privileges", info=True) %}
{% endmacro %}