create UDFs in the database

[jthandy]

dbt should be able to create UDFs. This would allow users to write UDFs in the same process that they write the rest of their analytics (version control, testing environments, deployment). Because UDFs can be built within a given schema, we should be able to treat them the same as all of the other database objects we are currently managing.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.

[fothergill]

There has been significant discussion of UDFs specifically over in #3391 so I think it is worth keeping this focussed issue for it. I'd be disappointed if it were closed as stale!

For what it's worth, having UDFs in the DAG would make a big difference for us maintaining our BigQuery warehouse. We extract re-useable business logic from our models into UDFs for our warehouse (non-dbt) users. Models then have a logical dependency on the UDF body and there is a chronological dependency on create/update order of the database udf/table/view objects. Now that BigQuery supports referencing tables in UDFs there can also be a chonological dependency of UDF creation on table creation too.

There are concerns expressed in #3391 about scaling of UDFs; in my experince native SQL UDFs in BigQuery scale no differently to inline SQL. They appear to be handled by the query planner in much the same way that inline SQL would be. I do find that javascript UDFs have scaling/performance concerns and generally avoid them.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest; add a comment to notify the maintainers.

[jonahgeorge]

There's still community interest! :D

[lostmygithubaccount]

related discussion for Python considerations: #5741

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.