Using dbt to manage user defined functions

Show and Tell
13

Hey @dabit – I got some more info internally on this, and my previous reply is not a good one! Running a commit; isn’t a great idea because it might mess with other queries that dbt runs, where dbt is expecting a transaction to already be open.

Instead, you could run this statement as an operation (docs). So, before you dbt run, execute the following:

dbt run-operation create_udfs

You’ll have to adjust your create_udfs macro to actually run each query – the run_query macro will not begin a transaction automatically.

{% macro create_udfs() %}

{% do run_query('create or replace library my_library language plpythonu') %}

{% do run_query('create schema if not exists {{ target.schema }}') %};

{% do run_query(create_f_list_custom_keys()) %};

{% do run_query(create_f_count_custom_keys()) %};

{% do run_query(create_f_future_date() %}

{% endmacro %}

^ I haven’t tested this code but it should be close to the thing that you want!

2 Likes