Nat TaylorBlog, Product Management & Tinkering

dbt UDFs

Published on . 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

    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 %}

Popular Posts

Post Navigation