Data Engineering · Topic 16 of 16
Functions - UDFs, UDTFs, External Functions and Secure Functions
Choose between UDFs, UDTFs, external functions and secure functions: languages, calling syntax, remote service limits, performance and sharing rules.
- 5 min
- UDF: one value per row; UDTF: rows per input, called with
TABLE(...), handler withprocessandend_partition. - UDF languages: SQL, JavaScript, Python, Java, Scala; SQL is best optimized.
- External function: scalar only, needs API integration and proxy service, not shareable, slow, batched, 10 MB response per batch.
What it is
Functions extend SQL with custom logic. A UDF returns one value for each input row. A UDTF (user defined table function) returns zero or more rows for each input row. An external function is a UDF whose code runs outside Snowflake, behind a remote HTTPS service. A secure function hides its definition and is used when functions are shared or must not leak data.
The decision is where the code should run and what shape of result is needed.
How it works
UDFs
CREATE OR REPLACE FUNCTION net_price(price NUMBER, tax FLOAT)
RETURNS NUMBER
LANGUAGE SQL
AS 'price * (1 + tax)';
SELECT net_price(amount, 0.2) FROM orders;
| Language | Notes |
|---|---|
| SQL | Fastest and best optimized; a single expression or query |
| JavaScript | Built in; arguments are uppercase variables inside the code |
| Python, Java, Scala | Handler based; can import packages and staged files; run in a secure sandbox |
Useful properties: RETURNS NULL ON NULL INPUT skips the call for NULL arguments, IMMUTABLE tells Snowflake the same input always gives the same output, and VOLATILE (the default) does not. Python UDFs can also be written to process batches of rows at once (vectorized), which is faster for numeric and dataframe work.
A UDF is for calculation. It cannot run DDL or DML, which is the job of a stored procedure.
UDTFs
A UDTF is called in the FROM clause with the TABLE keyword. It can read several input rows per partition, and returns a set of rows with a declared column list.
SELECT t.word, t.cnt
FROM docs d,
TABLE(word_count(d.body) OVER (PARTITION BY d.doc_id)) t;
| Handler method | Role |
|---|---|
process | Called once per input row; yields output rows |
end_partition | Called after the last row of a partition; yields summary rows |
| Constructor | Sets up state for a partition |
With OVER (PARTITION BY ...), Snowflake processes partitions in parallel, so partitioning gives both grouping and speed. SQL UDTFs return the result of a query with RETURNS TABLE (...).
| Need | Use |
|---|---|
| One value per row | UDF |
| Parse a row into several rows, or per group summaries | UDTF |
| Explode JSON arrays | The built in FLATTEN, not a custom UDTF |
External functions
An external function calls a remote service through a proxy service such as Amazon API Gateway or Azure API Management. An API integration object holds the credentials and the allowed endpoints.
CREATE EXTERNAL FUNCTION score_text(txt STRING)
RETURNS VARIANT
API_INTEGRATION = my_api_integration
AS 'https://example-gateway.execute-api.us-east-1.amazonaws.com/prod/score';
Key facts:
- External functions must be scalar: one output row per input row.
- They are functions, not procedures, and they cannot be shared with consumers through Secure Data Sharing.
- They cannot appear in a shared view, a
DEFAULTclause or a COPY transformation. - Setup needs a cloud administrator to build the proxy service first.
- Data sent to a third party service may be kept by that service, which matters for compliance.
External function performance impacts
| Factor | Impact |
|---|---|
| Network round trips | Far slower than a built in function or internal UDF |
| Batching | Rows go in batches, which improves throughput and cuts load on the service; a retry resends a whole batch |
| Response size | Each batch response is limited to 10 MB |
| Remote service capacity | The service must scale and not throttle; throttling slows or fails queries |
| Optimizer | The code is opaque, so Snowflake cannot optimize through it |
| Determinism | IMMUTABLE states that the same input always gives the same output; VOLATILE is the default. Queries that call external functions are not served from the result cache |
| Cost | Warehouse time while waiting, plus cloud data transfer and any third party fees |
To reduce external function cost, filter and deduplicate rows before calling the function, call it on only the needed column, and write results to a table instead of recalculating them in every query.
Prefer an internal Python or Java UDF when the logic and packages can run inside Snowflake, and use an external function only when the code must stay outside, such as an existing ML endpoint.
Secure functions
Add SECURE to a UDF or external function with CREATE SECURE FUNCTION. Then the definition is visible only to the owner, and not through SHOW FUNCTIONS or INFORMATION_SCHEMA output for other roles.
- Secure functions are required by default when a UDF is added to a share (a share created with
SECURE_OBJECTS_ONLY = FALSErelaxes this). - Snowflake restricts some optimizations, so a secure function can be slower than a regular one.
- Use them to protect business logic or to stop clever predicates from exposing hidden rows.
Secure does not mean fast. Use it where hiding the code or protecting data matters, not on every function.
Common mistakes
Trying to insert data from a UDF. UDFs calculate; use a procedure for DML.
Writing a UDTF to explode JSON. FLATTEN already does it and is optimized.
Assuming external functions can be shared. They cannot be shared through Secure Data Sharing.
Ignoring the remote service limits. Throttling and slow responses become Snowflake query failures.
What to remember for the exam
- UDF: one value per row; UDTF: rows per input, called with
TABLE(...), handler withprocessandend_partition. - UDF languages: SQL, JavaScript, Python, Java, Scala; SQL is best optimized.
- External function: scalar only, needs API integration and proxy service, not shareable, slow, batched, 10 MB response per batch.
- Secure functions hide the definition and are needed for sharing; they can reduce performance.
- Procedures do DML and DDL; functions do not.
Check yourself
A function must call an existing ML scoring API. Which type and what must exist first?
An external function, with an API integration and a proxy service in front of the remote service.
How do you run a UDTF in parallel across groups?
Call it with
OVER (PARTITION BY ...), so each partition is processed separately.A provider wants to share a UDF in a share without exposing its code. What is needed?
Create it as a secure function; secure objects are required for sharing by default and hide the definition.
Topic 16 of 16 in Data Engineering
Test yourself on Data Engineering
The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.