Skip to main content

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

In the future, we're considering adding support for table type. Refer to this issue to track the progress and provide any feedback.

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:

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

Adapterscalar SQLscalar Pythonaggregate SQLaggregate Python
dbt-bigquery✅✅✅❌
dbt-snowflake✅✅❌✅
dbt-databricks✅❌❌❌
dbt-postgres✅❌❌❌
dbt-redshift✅❌❌❌
Loading table...

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 — for example, they sum values or calculate an average for a group. Queries use these functions in GROUP BY operations.

Aggregate functions are currently supported only for:

  • Python functions on Snowflake
  • SQL functions on BigQuery

Example use cases:

  • Calculating totals or averages for groups of data (for example, total sales per customer)
  • Aggregating data over time (for example, daily, monthly, or yearly totals)
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

Was this page helpful?

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

0
Loading