Skip to main content

User Defined Table Functions (SQL UDTFs)

A standard scalar UDF returns exactly one value per row. But what if you need a function that accepts an input and returns multiple rows and columns?

This is where User-Defined Table Functions (UDTFs) come in. A UDTF behaves exactly like a table or a view, but it can accept dynamic parameters.

Topics Covered​

  • UDTF Concepts
  • Creating SQL UDTFs
  • Creating JavaScript UDTFs
  • Using UDTFs in Queries (TABLE constructor)

What is a UDTF?​

Think of a UDTF as a parameterized View. A standard View is static. A UDTF allows you to pass variables into the underlying SQL query, and it returns a dynamic table based on those variables.

Because a UDTF returns a table, you cannot put it in the SELECT clause like a normal function. You must put it in the FROM clause and wrap it in the TABLE() constructor.

Hands-On: Creating a SQL UDTF​

Let's assume an Indian retail bank stores millions of transactions. Analysts frequently need to extract all transactions for a specific customer within a specific date range. Instead of forcing analysts to write complex WHERE clauses every time, we can create a UDTF.

1. Database Setup​

(Run this in your Snowflake worksheet)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_UDF_DB;
USE SCHEMA BEEMS_UDF_DB.PUBLIC;

-- Create Mock Data
CREATE OR REPLACE TABLE TRANSACTIONS (
TXN_ID INT,
CUSTOMER_ID STRING,
AMOUNT NUMBER(10,2),
TXN_DATE DATE
);

INSERT INTO TRANSACTIONS VALUES
(1, 'C-100', 5000.00, '2023-10-01'),
(2, 'C-100', -1200.00, '2023-10-05'),
(3, 'C-101', 300.00, '2023-10-06'),
(4, 'C-100', 8000.00, '2023-10-15');

2. Creating the SQL UDTF​

We create a function that takes cust_id, start_date, and end_date as arguments, and returns a table of matching transactions.

(To verify your identity, run SELECT CURRENT_USER();)

CREATE OR REPLACE FUNCTION GET_CUSTOMER_TXNS(
cust_id STRING,
start_date DATE,
end_date DATE
)
RETURNS TABLE (TXN_ID INT, AMOUNT NUMBER(10,2), TXN_DATE DATE)
AS
$$
SELECT TXN_ID, AMOUNT, TXN_DATE
FROM TRANSACTIONS
WHERE CUSTOMER_ID = cust_id
AND TXN_DATE BETWEEN start_date AND end_date
$$;

3. Querying the UDTF​

To use the UDTF, you place it in the FROM clause wrapped in TABLE().

-- Get all transactions for C-100 in early October
SELECT *
FROM TABLE(GET_CUSTOMER_TXNS('C-100', '2023-10-01'::DATE, '2023-10-10'::DATE));

Creating a JavaScript UDTF​

JavaScript UDTFs are much more powerful. They allow you to write complex procedural logic that emits rows dynamically. This is highly useful for parsing complex JSON, generating mock data, or expanding data (like unpivoting custom structures).

Let's create a JS UDTF that takes a single comma-separated string of Indian cities and "explodes" it into a table with one row per city.

CREATE OR REPLACE FUNCTION EXPLODE_CITIES(city_list STRING)
RETURNS TABLE (CITY_NAME STRING, INDEX NUMBER)
LANGUAGE JAVASCRIPT
AS
$$
{
// The processRow function is called once for every input row
processRow: function (row, rowWriter, context) {

// 1. Split the string by comma
const cities = row.CITY_LIST.split(",");

// 2. Loop through the array
for (let i = 0; i < cities.length; i++) {

// 3. Emit a new row for each element
rowWriter.writeRow({
CITY_NAME: cities[i].trim(),
INDEX: i + 1
});
}
}
}
$$;

Querying the JS UDTF​

-- Explode the string into rows
SELECT *
FROM TABLE(EXPLODE_CITIES('Mumbai, Delhi, Chennai, Bengaluru'));

UDTFs with Lateral Joins​

The most common use case for a UDTF is to pass data from another table into the UDTF row-by-row. You do this using a LATERAL join.

-- Create a table of regions and their cities
CREATE OR REPLACE TABLE REGIONS (REGION_NAME STRING, CITIES STRING);
INSERT INTO REGIONS VALUES
('South', 'Chennai, Bengaluru, Hyderabad'),
('West', 'Mumbai, Pune');

-- Use a LATERAL join to pass the CITIES column into the UDTF
SELECT
r.REGION_NAME,
c.CITY_NAME
FROM REGIONS r,
LATERAL TABLE(EXPLODE_CITIES(r.CITIES)) c;

Note: The comma before LATERAL is shorthand for JOIN LATERAL.

Summary​

  • A UDTF returns a table (multiple rows and columns), whereas a UDF returns a single scalar value.
  • SQL UDTFs act like parameterized views.
  • JavaScript UDTFs use the processRow and rowWriter API to emit custom rows dynamically.
  • Because a UDTF returns a table, it must be placed in the FROM clause using the TABLE() constructor.
  • Use a LATERAL join to pass data from another table into the UDTF.