Data Movement · Topic 9 of 17
UDFs, Snowflake Scripting and Notebooks for Pipelines
Pick between UDFs, stored procedures and Snowflake Scripting, and learn how Notebooks run procedure pipelines for data ingestion on a schedule.
- 5 min
- UDF languages: SQL, JavaScript, Python, Java, Scala. Kinds: scalar, tabular, aggregate, vectorized, external function.
- UDFs return values inside queries and cannot run DDL or DML. Procedures are
CALLed and can. - Procedures run as owner by default.
EXECUTE AS CALLERchanges that.
What it is
A user-defined function (UDF) is code you write that returns a value inside a SQL statement. A stored procedure is code you run with CALL that can issue DDL and DML. Snowflake Scripting is the SQL based procedural language (variables, loops, branches, cursors, exceptions) used to write procedures and anonymous blocks.
Notebooks are the Snowsight workspace where SQL, Python and Markdown cells run in order. A data engineer uses them to call procedures and COPY statements as a pipeline and to schedule the whole thing.
How it works
UDFs
A UDF can be written in SQL, JavaScript, Python, Java or Scala. Python, Java and Scala handlers can be inline in the statement or loaded from a stage with IMPORTS.
CREATE FUNCTION clean_phone(p STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'run'
AS $$
import re
def run(p):
return re.sub(r'[^0-9]', '', p) if p else None
$$;
| UDF kind | Returns | Use it for |
|---|---|---|
| Scalar | One value per input row | Cleaning or deriving a column |
| Tabular (UDTF) | A set of rows per input row | Splitting, parsing or exploding data, used with TABLE(...) |
| Aggregate (UDAF, Python) | One value per group | Custom aggregation logic |
| Vectorized Python | One value per row, processed in batches | Faster work on large row counts with pandas style batches |
| External function | A value from a remote service called through an API integration | Calling code that runs outside Snowflake |
A secure UDF hides its definition from users who are not the owner. Functions can be overloaded: the same name with different argument types is allowed.
A UDF is for computing a value inside a query. It is not the tool for loading data or changing tables. If the scenario needs DDL, DML or COPY INTO, the answer is a stored procedure.
UDF versus stored procedure
| UDF | Stored procedure | |
|---|---|---|
| Called with | Inside an expression (SELECT f(col)) | CALL proc() as its own statement |
| Return value usable in a query | Yes | No, it is the statement's result |
| Runs DDL and DML | No | Yes |
| Rights | Runs with the owner's rights | EXECUTE AS OWNER (default) or EXECUTE AS CALLER |
Snowflake Scripting
A script is a block of DECLARE, BEGIN, EXCEPTION and END sections.
CREATE PROCEDURE load_and_merge()
RETURNS STRING
LANGUAGE SQL
AS
DECLARE
rows_loaded INTEGER DEFAULT 0;
BEGIN
COPY INTO raw_orders FROM @orders_stage FILE_FORMAT = (TYPE = CSV);
MERGE INTO orders t USING raw_orders s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount);
RETURN 'done';
EXCEPTION
WHEN OTHER THEN
RETURN 'failed: ' || SQLERRM;
END;
- Control flow:
IF,CASE,FOR,WHILE,REPEATandLOOP. - Variables: declared in
DECLAREor created withLET; used inside SQL with a colon prefix such as:rows_loaded. - Cursors and result sets: loop over query results, for example to process each staged table.
- Errors:
EXCEPTIONhandlers can useSQLCODE,SQLERRMandSQLSTATE, andRAISErethrows or raises a named exception. - Dynamic SQL:
EXECUTE IMMEDIATEruns a SQL string, such as a statement that depends on a table name variable. - Anonymous blocks: run a script without creating a procedure by wrapping it in
EXECUTE IMMEDIATE $$ ... $$.
A task body can be a single SQL statement, a CALL to a procedure, or a Snowflake Scripting block, so scripting is the usual way to put several steps behind one task.
Notebooks as pipeline runners
A notebook holds SQL, Python and Markdown cells. A SQL cell can run COPY INTO, and a Python cell can call a stored procedure through Snowpark, for example session.call('load_and_merge'). The result of a SQL cell can be used from a Python cell and the reverse.
Notebooks run on a warehouse runtime (the kernel runs on the notebook warehouse) or a container runtime (the kernel runs on a compute pool node). In both, SQL and Snowpark queries execute on a warehouse. The scheduler in the notebook toolbar runs the notebook as a task, and you can sync notebooks with a Git repository. Check the current preview status of scheduling and Git for your runtime in the documentation, because it differs by runtime.
Snowflake renamed the original Notebooks to Legacy Notebooks and describes Notebooks in Workspaces as the next generation. Know that both names exist, and that the pipeline idea is the same.
Ownership of the notebook is required to edit it. Collaboration is governed by role based access control, so grant the owner role only the privileges the pipeline needs.
Common mistakes
- Using a UDF when the work needs DML or DDL. UDFs cannot change tables.
- Expecting a stored procedure's result to be used inside a
SELECTlist. It is called withCALL. - Forgetting that
EXECUTE AS OWNERis the default for procedures, so the caller's privileges do not apply unlessEXECUTE AS CALLERis declared. - Writing a notebook pipeline whose steps are not idempotent. A scheduled rerun then loads or merges the same data twice.
- Using a scalar UDF per row when a built in function or a set based SQL statement would do the work faster.
What to remember for the exam
- UDF languages: SQL, JavaScript, Python, Java, Scala. Kinds: scalar, tabular, aggregate, vectorized, external function.
- UDFs return values inside queries and cannot run DDL or DML. Procedures are
CALLed and can. - Procedures run as owner by default.
EXECUTE AS CALLERchanges that. - Snowflake Scripting gives variables, loops, cursors,
EXCEPTIONhandling andEXECUTE IMMEDIATE. - A task can run a scripting block or a
CALL, so one task can run a multi step pipeline. - Notebooks mix SQL, Python and Markdown cells, run on a warehouse or container runtime, and can be scheduled.
Check yourself
A pipeline must load staged files and then merge them into a target. Should this be a UDF or a stored procedure?
A stored procedure, because it runs
COPY INTOandMERGE, which UDFs cannot do.Which clause lets a procedure use the privileges of the user who calls it?
EXECUTE AS CALLER; the default isEXECUTE AS OWNER.How does a data engineer run a notebook pipeline nightly?
Schedule the notebook with the toolbar scheduler, which runs it as a task, with cells that call COPY statements and stored procedures.
Topic 9 of 17 in Data Movement
Test yourself on Data Movement
The full Snowflake DEA-C02 question bank has 709 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.