Add ability to define UDFs so that models that use them can be compiled

[joellabes]

Once UDFs' inputs and outputs are defined (which will likely be similar to how macros' types can now be defined), it'll be possible for the dbt Fusion engine to check the validity of UDFs. Right now, it thinks that you're using a function that doesn't exist and raises an error.

In the meantime, you can disable static analysis of models that use the UDF: https://docs.getdbt.com/docs/fusion/new-concepts#the-static_analysis-config

Update, 2025-07-18: Snowflake users can fetch existing UDFs automatically by setting the env var DBT_ENGINE_EXPERIMENTAL_LIST_UDFS=1

Update, 2025-07-23 Check out this discussion to get into the details of UDF definition as a part of the dbt language: dbt-labs/dbt-core#11851

[eliasdefaria]

Updating this ticket - I think we should implement this (at least for Snowflake) by fetching the UDFs remotely pre-compile (shoutout Trenton for this idea).

Spec

At compile time, in the same way we fetch remote schemas, we fetch UDF function signatures for functions we don't have in our registry.

This is great because:

• It significantly decreases the friction of first time onboarding, since the user can just run compile without pulling down and adding all their UDFs (potentially hundreds) into the dbt project.
• If we happen to be missing a new Snowflake function in the compiler (like the new Snowflake cortex functions), compile will just pull the fn signatures for these and work! We can then improve the compiler and perf later by adding the fn to our registry.
• It seems lower lift than building UDF support with compilation, scheduling UDF creation on run and build, etc.

Snowflake natively supports SHOW FUNCTION and even SHOW USER FUNCTIONS to fetch only the user defined ones... Also DESCRIBE FUNCTION can be used to to fetch the function body.

I'd propose we start by trying this for Snowflake and seeing where we land

[eliasdefaria]

Confirming initial support for this has been merged! For anyone interested, try setting DBT_ENGINE_EXPERIMENTAL_LIST_UDFS=1 as an environmental variable and attempt a compile with a UDF. We'll roll out as the default behavior after we get some initial tests out of the way with live projects

[pempey]

Confirming initial support for this has been merged!

@eliasdefaria Do we know what beta version this will be present in?

[joellabes]

I think it's been merged but not yet released - my guess is it'll be in beta 44?

Although I think we'll also be renaming the env var to DBT_ENGINE_EXPERIMENTAL_LIST_UDFS in the spirit of dbt-labs/dbt-core#11795

[joellabes]

@pempey beta 44 is out now - set DBT_ENGINE_EXPERIMENTAL_LIST_UDFS=1 and give it a whirl!

[eliasdefaria]

Let us know how it goes @pempey :D

[eliasdefaria]

Okay we've done some testing, things generally work well! One thing we found was that many users store their UDFs in a different database and/or schema from their dev one. When we run SHOW USER FUNCTIONS, we use the default database & schema set in the profile to determine this.

As such, we need to make two modifications (one immediate, one for later).

1. (Now) Before fetching the UDFs, we should find all public search_paths set for the default database and schema. We can do this using show parameters like 'search_path';. We'll then scan the result of this for all UDFs present.
2. (Later) This doesn't handle the scenario where the user hardcodes the fully qualified name of the UDF in their SQL (example: my_db.my_schema.my_function()). For this, we'll need to (at SQL parse time), deduce the fqn of the function and fetch that UDF directly. We can deduce this is a UDF from the fully qualified name usage, as snowflake system functions in our registry are available in the default namespace.

More coming soon. Shoutout @xuliangs for the great work thus far!

[sophieml]

wow, Christmas in July ❄️

two questions:

• A silly one: for this specific Snowflake implementation, how are we supposed to be calling the UDFs? I was only able to successfully compile by specifying the fully qualified name, e.g. {{ config.get('database') }}.{{ config.get('schema') }}.<udf_name>. Although, I suppose you need a fully qualified name if there are duplicate function signatures across databases/schemas?
• Not sure if this belongs in the dbt-core discussion, here, or Slack: will Fusion still be able to compile UDFs defined outside of dbt's planned UDF management system? For example, if we need to commit crimes with Javascript and that language is not supported by the dbt UDF ecosystem.

[eliasdefaria]

wow, Christmas in July ❄️

two questions:

* A silly one: for this specific Snowflake implementation, how are we supposed to be calling the UDFs? I was only able to successfully compile by specifying the fully qualified name, e.g. `{{ config.get('database') }}.{{ config.get('schema') }}.<udf_name>`. Although, I suppose you need a fully qualified name if there are duplicate function signatures across databases/schemas?

* Not sure if this belongs in the dbt-core discussion, here, or Slack: will Fusion still be able to compile UDFs defined outside of dbt's planned UDF management system? For example, if we need to commit crimes with Javascript and that language is not supported by the dbt UDF ecosystem.

Okay to the first question, by default we search for the UDF in the default database and schema set in the profiles.yml. You are probably having to fully qualify your function call b/c the function exists remotely in a different database and/or schema from what you have set in your profiles.yml. Snowflake has its own logic for managing conflicting names and resolving object identifiers, and the search_path (see my comment above) plays a big part of that. We'll use that to resolve the correct function identifier locally, so you'll be able to resolve UDFs called without fqns.

To the second question, Fusion will still be able to compile UDFs defined outside of dbt's planned UDF management system, but the recommended way to manage your UDFs will be within the dbt framework. There will be a bunch of benefits to this!

[sophieml]

@eliasdefaria thanks! I did see your comment; hopefully this will be moot with the search paths, but the UDF does actually exist in my default database/schema as listed in the dbt Cloud connection, and I also stripped down our dbt project and created the UDF in the model schemas for good measure. In the logs I do actually see some SHOW USER FUNCTION calls that follow our database/schema overrides but maybe that was a red herring:

04:33:23 -- created_at: 2025-07-23T04:33:23.369106404+00:00
-- dialect: snowflake
-- node_id: not available
-- desc: List user functions
SHOW USER FUNCTIONS IN SCHEMA <db1>.<schema1>;
04:33:23 -- created_at: 2025-07-23T04:33:23.741763564+00:00
-- dialect: snowflake
-- node_id: not available
-- desc: List user functions
SHOW USER FUNCTIONS IN SCHEMA <db1>.<schema2>;

[eliasdefaria]

@eliasdefaria thanks! I did see your comment; hopefully this will be moot with the search paths, but the UDF does actually exist in my default database/schema as listed in the dbt Cloud connection, and I also stripped down our dbt project and created the UDF in the model schemas for good measure. In the logs I do actually see some SHOW USER FUNCTION calls that follow our database/schema overrides but maybe that was a red herring:

04:33:23 -- created_at: 2025-07-23T04:33:23.369106404+00:00
-- dialect: snowflake
-- node_id: not available
-- desc: List user functions
SHOW USER FUNCTIONS IN SCHEMA <db1>.<schema1>;
04:33:23 -- created_at: 2025-07-23T04:33:23.741763564+00:00
-- dialect: snowflake
-- node_id: not available
-- desc: List user functions
SHOW USER FUNCTIONS IN SCHEMA <db1>.<schema2>;

Oh holy smokes looks like the propagated db & schema config is being used for the request! This makes a ton of sense. Thanks for flagging, we'll have it fixed in the next release.

[pempey]

I think that what I am experiencing is expected but I would to lay it out here for clarity.

Project Set Up

• dbt Fusion version 2.0.0-beta.52
• Our UDFs are created with a macro for each UDF and an on-run-start macro call the calles all of the UDF macros
• In development we use the database attribute of the profiles.yml as a prefix for database names, PROD -> PEMPEY_PROD
• We have an override for the generate_database_name macro
• We use environment variables to define the names of the database we use SNOWFLAKE_PROD_DATABASE=PROD
• We build the UDFs in multiple database PREP and PROD
• When creating the functions in development we include the prefixing into the UDF macro "{{ target.database}}_{{ env_var_db }}".{{target.schema}}
• When we call the UDF we explicitly call the generate_database_name macro "{{generate_database_name(env_var('SNOWFLAKE_PREP_DATABASE'))}}"

Test Set Up

• The target.database is set to PEMPEY
• The target.schema is set to PREPARATION
• The model google_analytics_4_events_source has a call to the UDF parse_bigquery_object_array
• The command executed is dbtf compile -s google_analytics_4_events_source
• The static_analysis for the model is set to on
• The env var DBT_ENGINE_EXPERIMENTAL_LIST_UDFS is set to 1

Result

• I can see that the UDF is created CREATE OR REPLACE FUNCTION \"PEMPEY_PREP\".preparation.parse_bigquery_object_array
• I get an error on the model:

error: dbt1000: ADBC error: [Snowflake] 002003 (02000): SQL compilation error:
Database 'PEMPEY' does not exist or not authorized. (SQLSTATE: 02000, Vendor code: 2003)

• The search for functions are not looking in the correct schema and database:

-- created_at: 2025-07-29T13:05:22.263716+00:00
-- dialect: snowflake
-- node_id: not available
-- desc: List user functions
SHOW USER FUNCTIONS IN SCHEMA PEMPEY.dbt_test__audit;
-- created_at: 2025-07-29T13:05:22.618418+00:00
-- dialect: snowflake
-- node_id: not available
-- desc: List user functions
SHOW USER FUNCTIONS IN SCHEMA PEMPEY_PREP.google_analytics_4;
-- created_at: 2025-07-29T13:05:23.364435+00:00
-- dialect: snowflake
-- node_id: not available
-- desc: List user functions
SHOW USER FUNCTIONS IN SCHEMA RAW.google_analytics_4_bigquery;