User-Defined Functions & UDTFs

Snowflake · Stored Procedures & UDFs

Theory

A user-defined function (UDF) extends SQL with your own logic. A scalar UDF returns one value per input row and is used inline like a built-in function, for example SELECT amount, calc_tax(amount, state) FROM products. A user-defined table function (UDTF) returns a set of rows for each call and is used in the FROM clause with TABLE(...), for example generating one row per day in a date range.

UDFs can be written in SQL, JavaScript, Python, Java or Scala. Unlike procedures, functions cannot run DML or DDL; they only compute results. Secure UDFs hide their definition, and external functions call code hosted outside Snowflake, such as an HTTPS endpoint behind an API gateway, through an API integration.

Why we use it

Reusable business rules, such as tax calculation, scoring or parsing, should be written once rather than copied into many queries. UDFs make that logic callable everywhere and keep it consistent.

When to use it

Use a scalar UDF for per-row calculations, a UDTF to turn one input into many rows, and external functions only when logic must run in an outside service. Prefer built-in functions or plain SQL expressions when they are enough, since they are usually faster.

Scalar UDFs

Written in SQL, JavaScript, Python, or Java, a scalar UDF takes input values and returns one output value — usable directly inside a SELECT list exactly like UPPER() or ROUND().

UDTFs: Returning a Table

A UDTF can return multiple rows and columns per call, similar in spirit to FLATTEN — useful for logic like splitting one row into several, or running a repeatable multi-row calculation.

External Functions

An external function calls out to an API hosted outside Snowflake (via API Gateway), letting SQL invoke arbitrary external logic — the escape hatch when logic can't practically live inside Snowflake.

How it works

  1. Need reusable logic in a query
  2. Does it return one value per row? Yes: Create a scalar UDF and call it in SELECT. No: Consider a UDTF.
  3. Does it return several rows per input? Yes: Create a UDTF and use TABLE(...) in FROM. No: Use a stored procedure for multi-step work.
  4. Grant USAGE on the function to roles that need it
  5. Call it like a built-in function

Example

CREATE OR REPLACE FUNCTION calc_tax(amount FLOAT, state VARCHAR)
RETURNS FLOAT AS
$$
  amount * CASE state WHEN 'Maharashtra' THEN 0.18 WHEN 'Delhi' THEN 0.12 ELSE 0.10 END
$$;

SELECT amount, calc_tax(amount, 'Maharashtra') AS tax FROM products;

CREATE OR REPLACE FUNCTION date_range(start_date DATE, end_date DATE)
RETURNS TABLE (dt DATE) LANGUAGE SQL AS
$$
  SELECT DATEADD(DAY, SEQ4(), start_date) FROM TABLE(GENERATOR(ROWCOUNT => DATEDIFF(DAY,start_date,end_date)+1))
$$;

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic