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
In a nutshell
  • 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 CALLER changes 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.

SQL querycallsUDFreturns a value per rowused inSELECT or WHERETask or notebookCALLStored procedureSnowflake ScriptingrunsCOPY, MERGE, DDL

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 kindReturnsUse it for
ScalarOne value per input rowCleaning or deriving a column
Tabular (UDTF)A set of rows per input rowSplitting, parsing or exploding data, used with TABLE(...)
Aggregate (UDAF, Python)One value per groupCustom aggregation logic
Vectorized PythonOne value per row, processed in batchesFaster work on large row counts with pandas style batches
External functionA value from a remote service called through an API integrationCalling 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.

Exam tip

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

UDFStored procedure
Called withInside an expression (SELECT f(col))CALL proc() as its own statement
Return value usable in a queryYesNo, it is the statement's result
Runs DDL and DMLNoYes
RightsRuns with the owner's rightsEXECUTE 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, REPEAT and LOOP.
  • Variables: declared in DECLARE or created with LET; 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: EXCEPTION handlers can use SQLCODE, SQLERRM and SQLSTATE, and RAISE rethrows or raises a named exception.
  • Dynamic SQL: EXECUTE IMMEDIATE runs 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.

NotebookSQL and Python cellscell 1COPY INTOraw tablecell 2CALL proceduretransformscheduled byTaskrun notebook

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.

Watch out

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 SELECT list. It is called with CALL.
  • Forgetting that EXECUTE AS OWNER is the default for procedures, so the caller's privileges do not apply unless EXECUTE AS CALLER is 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 CALLER changes that.
  • Snowflake Scripting gives variables, loops, cursors, EXCEPTION handling and EXECUTE 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 INTO and MERGE, which UDFs cannot do.

  • Which clause lets a procedure use the privileges of the user who calls it?

    EXECUTE AS CALLER; the default is EXECUTE 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.