thinQmaster Virtual testing partner Adaptive practice exams that find your weak spots and drill them. Every answer explained. From $69
Enroll

thinQmaster

Find your exam

Five questions free, before you decide.

Step 1 · choose a vendor

In one of our camps? Your practice exams come with the tuition — they are on your study page already.

Data & Analytics · Intermediate

User-Defined Function (UDF)

A custom function created in the database that returns a scalar value or a table and can be called inside SQL statements.

Definition

UDFs extend SQL with reusable logic. Scalar UDFs return one value per row, while table functions (UDTFs) return rows. Snowflake and Databricks support UDFs in SQL, Python, Java and Scala; vectorized Python UDFs process batches of rows for speed.

Example

CREATE FUNCTION net_price(p NUMBER, d NUMBER) RETURNS NUMBER AS 'p * (1 - d)';

Exam tip

In Snowflake, a UDF is called in a SELECT; a stored procedure is invoked with CALL and can run DDL and DML.

Related terms