type

💡Did you know...

Available from dbt v1.11 or with the dbt "Latest" release track.

functions/<filename>.yml

functions:
  - name: <function name>
    config:
      type: scalar | aggregate | table  # scalar & aggregate partially supported, table coming soon

Definition

The type config specifies the type of user-defined function (UDF) you're creating. This config is optional and defaults to scalar if not specified.

Supported function types

The following function types are supported:

• scalar (default)
• aggregate
• table

Support for type differs based on the warehouse and language (SQL or Python) you're using:

Adapter	scalar SQL	scalar Python	aggregate SQL	aggregate Python	table SQL	table Python
dbt-bigquery	✅	✅	✅	❌	❌	❌
dbt-snowflake	✅	✅	❌	✅	❌	❌
dbt-databricks	✅	❌	❌	❌	❌	❌
dbt-postgres	✅	❌	❌	❌	❌	❌
dbt-redshift	✅	❌	❌	❌	❌	❌

scalar (default)

A scalar function returns a single value for each row of input. This is the most common type of UDF.

Example use cases:

• Data validation (checking if a string matches a pattern)
• Data transformation (converting formats, cleaning strings)
• Custom calculations (complex mathematical operations)

functions/schema.yml

functions:
  - name: is_positive_int
    description: Determines if a string represents a positive integer
    config:
      type: scalar
    arguments:
      - name: input_string
        data_type: STRING
    returns:
      data_type: BOOLEAN

aggregate

Aggregate functions operate on multiple rows and return a single value. These functions are used in GROUP BY operations.

Aggregate functions are supported only for:

• Python functions on Snowflake
• SQL functions on BigQuery

More warehouse and language support is planned for a future release.

Example:

functions/schema.yml

functions:
  - name: double_total
    description: Sums values and doubles the result
    config:
      type: aggregate
    arguments:
      - name: values
        data_type: FLOAT
        description: A sequence of numbers to aggregate
    returns:
      data_type: FLOAT

table

Table functions return a table (multiple rows and columns) rather than a single value.

Coming soon

Support for table functions is planned for a future release.

Related documentation

• User-defined functions
• Function properties
• Function configurations
• Volatility
• Arguments
• Returns

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.