Example of configuring a User Defined Function through DBT for use in DuckDB

[williamkaper]

Hi, We are evaluating migrating our data storage from PostGreSQL to DuckDB. We need to create a couple UDFs to replace some postgresql functions our models use. I'm trying to find a good simple example on how to build a UDF in python and include it in DBT. I read about how to define a custom plugin, though all the examples didn't seem to relate to UDFs, and inversely, I reviewed how to define a UDF in a python library that interacts with DBT but it didn't seem applicable to DBT.

I just need a simple example of how to build a basic UDF in DBT so I can bridge the gap and get myself unblocked. Thanks in advance for any assistance the community can provide!

[jwills]

hey @williamkaper -- I have a very minimalist example of how to do this using plugins here:

https://github.com/jwills/jaffle_shop_duckdb/tree/duckdb

The important thing to understand is how UDF loading works in the profiles.yml: https://github.com/jwills/jaffle_shop_duckdb/blob/duckdb/profiles.yml

The module_paths arg gives the path where dbt-duckdb needs to look for the plugins that define the custom UDFs (in this case, it's in the lib directory, which since it's a module needs to have an __init__.py file defined.) The plugin that is defined in that my_custom_functions file can do whatever UDF construction you need using the regular DuckDB Python interface.

Hope that helps you get rolling; wanted to call out that the UDFs you create here are only available for the dbt project-- they won't be available if you need to use them later on with the DuckDB database file you may (or may not) be creating after your run completes.