All topics
53 / 66
Snowflake

Stored Procedures & UDFs (JavaScript, SQL, Python)

Encapsulate reusable logic and procedural workflows directly inside Snowflake.

Both put logic inside Snowflake. They are not interchangeable, and the difference is where they can be used.

A UDF is like a calculator function you plug into a spreadsheet formula — cell in, value out; a stored procedure is more like a macro that can open dialogs, make decisions, and touch several sheets in sequence.

Key Concepts

1
    CALL my_procedure();                          -- a procedure: a statement
    SELECT my_function(col) FROM orders;          -- a function: part of a query
2
A UDF computes a value and nothing else. It takes arguments, returns a result, and cannot change data. That restriction is what lets it appear inside a SELECT — the optimiser may call it any number of times, in any order.
SELECT
3
    CREATE FUNCTION tax(amount NUMBER) RETURNS NUMBER
      AS $$ amount * 0.18 $$;
4
A stored procedure does work. Loops, conditionals, several statements, error handling, transactions. It is called as a statement and cannot be used inside a query.
5
    CREATE PROCEDURE refresh_daily() RETURNS STRING
      LANGUAGE SQL AS
    $$ BEGIN
         DELETE FROM summary WHERE day = CURRENT_DATE();
         INSERT INTO summary SELECT ...;
         RETURN 'done';
       END; $$;
6
Languages, and when each is worth it. SQL for straightforward logic. JavaScript for procedural code and awkward JSON handling. Python and Java through Snowpark, which allows real libraries — and is what makes data science work run next to the data rather than after exporting it.
7
A UDTF returns rows, not a value. Where a scalar UDF gives one result per call, a table function gives many:
8
    SELECT * FROM TABLE(split_into_rows('a,b,c'));   -- three rows
9
That is how you turn one string, or one nested structure, into multiple rows inside a query rather than in a separate ETL step.
10
Caller's rights versus owner's rights is the security question. A procedure runs either with the privileges of whoever calls it, or with those of whoever created it:
11
    caller's rights -> the user needs access to everything it touches
    owner's rights  -> the procedure's owner's access is used instead
12
Owner's rights is how you give someone a narrow, controlled operation without the underlying access. An analyst can call a procedure that archives old rows without having DELETE on the table at all.
DELETE
13
It is also how you create a hole, if the procedure takes a string and builds SQL from it. An owner's rights procedure that concatenates user input into a statement runs that input with elevated privileges — SQL injection with consequences. Bind parameters; never concatenate.
14
A practical note. A UDF called in a WHERE clause over a large table runs per row, and in JavaScript or Python that overhead is significant. If the logic can be expressed in SQL, it will be far faster.
WHERE
15
What the interviewer is probing.1. "What is the difference between a procedure and a UDF?" Probing: the distinction. Stalls: "Procedures are bigger." Moves up: a UDF returns a value inside a query and cannot do DML; a procedure is called on its own and can execute statements.
16
2. "When is a UDF the wrong tool?" Probing: the performance point. Stalls: "Never." *Moves up:* a scalar UDF called per row can dominate runtime — a vectorised Python UDF or plain SQL is usually far faster.
17
3. "What does caller versus owner rights mean?" Probing: the security model. Stalls: "Nothing much." Moves up: owner-rights procedures run with the creator's privileges, which is powerful and therefore the thing to review carefully.
18
4. "What is a UDTF for?" Probing: the breadth. Stalls: "Tables." Moves up: returning multiple rows per input row, which covers cases a scalar function cannot.
Use cases:stored-proceduresudfjavascriptpythonsnowpark