User-defined functions Beta

User-defined functions (UDFs) enable you to define and register custom functions in your warehouse. Like macros, UDFs promote code reuse, but they are objects in the warehouse so you can reuse the same logic in tools outside dbt, such as BI tools, data science notebooks, and more.

UDFs are particularly valuable for sharing logic across multiple tools, standardizing complex business calculations, improving performance for compute-intensive operations (since they're compiled and optimized by your warehouse's query engine), and version controlling custom logic within your dbt project.

dbt creates, updates, and renames UDFs as part of DAG execution. The UDF is built in the warehouse before the model that references it.

Prerequisites

• Make sure you're using dbt platform's Latest Fusion or Latest release track or dbt Core v1.11.

• Use one of the following adapters:

  dbt Core

  • BigQuery
  • Snowflake
  • Redshift
  • Postgres
  • Databricks

  dbt Fusion engine

  • BigQuery
  • Snowflake
  • Redshift
  • Databricks

Defining UDFs in dbt

To define UDFs in dbt, refer to the following steps:

1. Create a SQL file under the functions directory. For example:

   functions/is_positive_int.sql

   
   REGEXP_CONTAINS(a_string, r'^[0-9]+$')

   Note: You can specify configs in the SQL file or in the corresponding YAML file in Step 2.

2. Specify the function name and define the config, properties, return type, and optional arguments in a corresponding YAML file. For example:

   functions/schema.yml

   functions:
     - name: is_positive_int # required
       description: My UDF that determines if a string represents a positive (+) integer # required
       config:
         schema: udf_schema
         database: udf_db
       arguments: # optional
         - name: a_string # required if arguments is specified
           data_type: string # required if arguments is specified
           description: The string that I want to check if it's representing a positive integer (like "10") 
       returns: # required
         data_type: boolean # required

   The rendered CREATE UDF statement depends on which adapter you’re using. For example:

   Snowflake

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string STRING)
   RETURNS BOOLEAN
   LANGUAGE SQL
   AS $$
     REGEXP_CONTAINS(a_string, r'^[0-9]+$')
   $$;

   Redshift

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(VARCHAR)
   RETURNS BOOLEAN
   VOLATILE # Technically this function could be set as STABLE, but we don't support setting volatility yet
   AS $$
     REGEXP_CONTAINS($1, r'^[0-9]+$')
   $$ LANGUAGE SQL;

   BigQuery

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string STRING)
   RETURNS BOOLEAN
   AS (
     REGEXP_CONTAINS(a_string, r'^[0-9]+$')
   );

   Databricks

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string STRING)
   RETURNS BOOLEAN
   LANGUAGE SQL
   RETURN
     REGEXP_CONTAINS(a_string, r'^[0-9]+$');

   Postgres

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string STRING)
   RETURNS BOOLEAN
   AS $$
     REGEXP_CONTAINS(a_string, r'^[0-9]+$')
   $$ LANGUAGE SQL;

3. Reference the UDF in a model using the {{ function(...) }} macro. For example:

   models/my_model.sql

   select
       maybe_positive_int_column,
       {{ function('is_positive_int') }}(maybe_positive_int_column)
   from {{ ref('a_model_i_like') }}

4. Run dbt compile. In the following example, the {{ function('is_positive_int') }} is replaced by the UDF name udf_db.udf_schema.is_positive_int.

   models/my_model.sql

   select
       maybe_positive_int_column,
       udf_db.udf_schema.is_positive_int(maybe_positive_int_column) as is_positive
   from analytics.dbt_schema.a_model_i_like

   In your DAG, a UDF node is created from the SQL and YAML definitions, and there will be a dependency between is_positive_int → my_model.

   The DAG for the UDF node

After defining a UDF, if you update the SQL file that contains its function body (is_positive_int.sql in this example) or its configurations, your changes will be applied to the UDF in the warehouse next time you build.

Using UDFs in unit tests

You can use unit tests to validate models that reference UDFs. Before running unit tests, make sure the function exists in your warehouse. To ensure that the function exists for a unit test, run:

dbt build --select "+my_model_to_test" --empty

Following the example in Defining UDFs in dbt, here's an example of a unit test that validates a model that calls a UDF:

tests/test_is_positive_int.yml

unit_tests:
  - name: test_is_positive_int 
    description: "Check my is_positive_int logic captures edge cases"
    model: my_model
    given:
      - input: ref('a_model_i_like')
        rows:
          - { maybe_positive_int_column: 10 }
          - { maybe_positive_int_column: -4 }
          - { maybe_positive_int_column: +8 }
          - { maybe_positive_int_column: 1.0 }
    expect:
      rows:
        - { maybe_positive_int_column: 10,  is_positive: true }
        - { maybe_positive_int_column: -4,  is_positive: false }
        - { maybe_positive_int_column: +8,  is_positive: true }
        - { maybe_positive_int_column: 1.0, is_positive: true }

Listing and selecting UDFs

To list UDFs in your project, run dbt list --select "resource_type:function" or dbt list --resource-type function.

To select UDFs when building a project, run dbt build --select "resource_type:function".

For more information about selecting UDFs, see the examples in Node selector methods.

Limitations

• Creating UDFs in other languages (for example, Python, Java, or Scala) is not yet supported.
• Only scalar functions are currently supported.

Related FAQs

When should I use a UDF instead of a macro?

Both user-defined functions (UDFs) and macros let you reuse logic across your dbt project, but they work in fundamentally different ways. Here's when to use each:

Use UDFs when:

 You need logic accessible outside dbt

UDFs are created in your warehouse and can be used by BI tools, data science notebooks, SQL clients, or any other tool that connects to your warehouse. Macros only work within dbt.

 You want to standardize warehouse-native functions

UDFs let you create reusable warehouse functions for data validation, custom formatting, or business-specific calculations that need to be consistent across all your data tools. Once created, they become part of your warehouse's function catalog.

 You want dbt to manage the function lifecycle

dbt manages UDFs as part of your DAG execution, ensuring they're created before models that reference them. You can version control UDF definitions alongside your models, test changes in development environments, and deploy them together through CI/CD pipelines.

 Jinja compiles at creation time, not on each function call

You can use Jinja (loops, conditionals, macros, ref, source, var) inside a UDF configuration. dbt resolves that Jinja when the UDF is created, and the resulting SQL body is what gets stored in your warehouse.

Jinja influences the function when it’s created, whereas arguments influence it when it runs in the warehouse:

• ✅ Allowed: Jinja that depends on project or build-time state — for example, var(“can_do_things”), static ref(‘orders’), or environment-specific logic. These are all evaluated once at creation time.
• ❌ Not allowed: Jinja that depends on function arguments passed at runtime. The compiler can’t see those, so dynamic ref(ref_name) or conditional Jinja based on argument values won’t work.

Use macros when:

 You need to generate SQL at compile time

Macros generate SQL dynamically before it's sent to the warehouse (at compile time). This is essential for:

• Building different SQL for different warehouses
• Generating repetitive SQL patterns (like creating dozens of similar columns)
• Creating entire model definitions or DDL statements
• Dynamically referencing models based on project structure

UDFs execute at query runtime in the warehouse. While they can use Jinja templating in their definitions, they don't generate new SQL queries—they're pre-defined functions that get called by your SQL.

Expanding UDFs

Currently, only SQL UDFs are supported. Python, Java, and Scala UDFs are planned for future releases. Once Python UDFs are available, they'll support conditionals and looping within the function logic itself (using Python syntax), but they'll still execute at runtime, not at compile time like macros.

 You want to generate DDL or DML statements

Macros can create entire model definitions, tests, or any SQL statement. UDFs are limited to returning values or tables.

 You need to adapt SQL across different warehouses

Macros can use Jinja conditional logic to generate warehouse-specific SQL (see cross-database macros), making your dbt project portable across platforms.

UDFs are warehouse-specific objects. Even though UDFs can include Jinja templating in their definitions, each warehouse has different syntax for creating functions, different supported data types, and different SQL dialects. You would need to define separate UDF files for each warehouse you support.

 Your logic needs access to dbt context

Both macros and UDFs can use Jinja, which means they can access dbt context variables like {{ ref() }}, {{ source() }}, environment variables, and project configurations. You can even call a macro from within a UDF (and vice versa) to combine dynamic SQL generation with runtime execution.

However, the difference between the two is when the logic runs:

• Macros run at compile time, generating SQL before it’s sent to the warehouse.
• UDFs run inside the warehouse at query time.

 You want to avoid creating warehouse objects

Macros don't create anything in your warehouse; they just generate SQL at compile time. UDFs create actual function objects in your warehouse that need to be managed.

Can I use both together?

Yes! You can use a macro to call a UDF or call a macro from within a UDF, combining the benefits of both. So the following example shows how to use a macro to define default values for arguments alongside your logic, for your UDF

{% macro cents_to_dollars(column_name, scale=2) %}
  {{ function('cents_to_dollars') }}({{ column_name }}, {{scale}})
{% endmacro %}

Related documentation

• User-defined functions
• Jinja macros

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.