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
In a nutshell
  • UDF: one value per row; UDTF: rows per input, called with TABLE(...), handler with process and end_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.

Querycalls a functionscalarUDFruns inside Snowflakeor tableUDTFmany rows outor remoteExternal functionHTTPS service

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;
LanguageNotes
SQLFastest and best optimized; a single expression or query
JavaScriptBuilt in; arguments are uppercase variables inside the code
Python, Java, ScalaHandler 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 methodRole
processCalled once per input row; yields output rows
end_partitionCalled after the last row of a partition; yields summary rows
ConstructorSets 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 (...).

NeedUse
One value per rowUDF
Parse a row into several rows, or per group summariesUDTF
Explode JSON arraysThe 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.

Querycalls external functionJSON POST in batchesProxy serviceAPI gatewayforwardsRemote serviceLambda or other coderesultsSnowflake
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 DEFAULT clause 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

FactorImpact
Network round tripsFar slower than a built in function or internal UDF
BatchingRows go in batches, which improves throughput and cuts load on the service; a retry resends a whole batch
Response sizeEach batch response is limited to 10 MB
Remote service capacityThe service must scale and not throttle; throttling slows or fails queries
OptimizerThe code is opaque, so Snowflake cannot optimize through it
DeterminismIMMUTABLE 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
CostWarehouse time while waiting, plus cloud data transfer and any third party fees
Exam tip

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.

Providerowns secure functionshares inSharesecure objects onlyqueried byConsumercannot see the code
  • Secure functions are required by default when a UDF is added to a share (a share created with SECURE_OBJECTS_ONLY = FALSE relaxes 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.
Watch out

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 with process and end_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.