Feature Request: Support for Snowflake UDTFs (Table-Returning Functions) in dbt Core

[a51001k]

Problem

dbt Core 1.11 introduced support for user-defined functions via the functions: resource. However, current support is limited to scalar and aggregate functions.

Snowflake supports UDTFs (User-Defined Table Functions) using:

CREATE OR REPLACE FUNCTION my_function(arg STRING)
RETURNS TABLE (col1 STRING, col2 NUMBER)
LANGUAGE SQL
AS
$$
    SELECT ...
$$;

There is currently no way to define and manage such table-returning functions declaratively using dbt’s functions: configuration.

As a result, users must:

• Manage UDTFs via macros + run-operation
• Use on-run-start hooks
• Or manage DDL entirely outside of dbt

This breaks consistency with how scalar and aggregate UDFs are managed.

Proposal

Extend the functions: resource to support a new type (e.g., table) and structured return schema definition.

Example YAML:

functions:
  - name: get_customers_from_group_list
    config:
      schema: udf_schema
      database: udf_db
      type: table
    arguments:
      - name: customer_group_list
        data_type: string
    returns:
      columns:
        - name: customer_id 
          data_type: string
        - name: customer_name
          data_type: string

This would compile to:

CREATE OR REPLACE FUNCTION udf_db.udf_schema.get_customers_from_group_list(customer_group_list STRING)
RETURNS TABLE (
    customer_id STRING,
    customer_name STRING
)
LANGUAGE SQL
AS
$$
    SELECT ...
$$;

Benefits

• Full parity with Snowflake UDF capabilities
• Declarative management of all function types
• Improved governance and version control
• Eliminates need for macro-based DDL workarounds
• Aligns with dbt’s goal of managing analytics-layer SQL artifacts

Use Case

In financial analytics environments, UDTFs are frequently used to:

• Expand group-based inputs into filtered entity lists
• Encapsulate reusable filtering logic
• Standardize complex parameterized queries
• Enable clean lateral joins (SELECT * FROM TABLE(function(...)))
• Without native support, these must be managed outside dbt’s artifact system.

Summary

Adding first-class support for table-returning UDTFs would complete dbt’s UDF functionality for Snowflake and improve consistency, governance, and maintainability for advanced SQL use cases.

[a51001k]

I posted the same post here: #12492