Nat Taylor — Blog, Product Management & Tinkering

dbt UDFs

Published on Nov 05, 2023. Updated 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 %}