Snowflake Stored Procedures & UDFs — Expert Interview Questions

Q: Stored procedure vs UDF vs UDTF: when to use each?

Stored Procedure: executes a program with multiple SQL statements, DDL, DML, transaction control. Called with CALL. Use for: ETL orchestration, admin automation, multi-step workflows. Can modify data. UDF (User-Defined Function): takes input, returns single value per row. Called in SELECT. Use for: row-level transformations, calculations. Cannot modify data. UDTF (User-Defined Table Function): takes input, returns multiple rows. Called with TABLE(). Use for: exploding data, ML inference per group, custom aggregations returning rows. Key: procedures are imperative (do things), functions are declarative (compute things).

Q: Caller rights vs owner rights: what is the security difference?

Owner rights (default): procedure runs with the privileges of the role that OWNS the procedure, regardless of who calls it. Use for: controlled data access (grant procedure execution without granting table access). Caller rights (EXECUTE AS CALLER): procedure runs with the privileges of the calling role. Use for: utility procedures that should respect the caller permissions, admin scripts that need the caller context. Security implication: owner rights procedures can be security risks if they access sensitive data and the owner role is highly privileged. Always audit owner-rights procedures that access PII. Specify during creation: CREATE PROCEDURE ... EXECUTE AS OWNER|CALLER.

Q: How do you handle transactions in stored procedures?

Snowflake stored procedures support explicit transaction control: BEGIN, COMMIT, ROLLBACK. Key rules: (1) If a procedure is called within an existing transaction, it joins that transaction (no nested transactions). (2) If called outside a transaction, each SQL statement is auto-committed unless you explicitly BEGIN. (3) On unhandled exception: if autocommit is off, changes are rolled back. (4) CALL inside a task graph: all tasks share the graph transaction. Best practice: always wrap multi-statement DML in BEGIN/COMMIT with TRY/CATCH for ROLLBACK on error. Use SQLSTATE for error handling.

Q: JavaScript vs Python vs SQL for stored procedures?

SQL: simplest, best for pure SQL orchestration (MERGE, INSERT, DDL sequences). No external libraries. Python: best for ML, complex logic, pandas/sklearn. Runs in Snowpark sandbox. Access to Anaconda packages. JavaScript: legacy, still supported. Best for JSON manipulation. No package ecosystem compared to Python. Scala/Java: available via Snowpark. Best for teams with JVM expertise. Choose based on: team skills, library needs, and complexity. For pure SQL workflows, SQL procedures are fastest. For anything requiring non-SQL logic, Python is the modern choice.

Q: How do you dynamically generate and execute SQL in a stored procedure?

In SQL procedures: use EXECUTE IMMEDIATE with string concatenation. Example: LET stmt := 'SELECT * FROM ' || :table_name; EXECUTE IMMEDIATE :stmt. In Python: session.sql(f"SELECT * FROM {table_name}").collect(). Security warning: dynamic SQL is vulnerable to SQL injection. Always validate inputs, use IDENTIFIER() for object names (SELECT * FROM IDENTIFIER(:table_name)), and never concatenate user input directly into SQL strings.

Q: How do you return results from a stored procedure?

SQL procedures: RETURN value (single scalar). For tabular results: use RESULTSET and TABLE(RESULT_SCAN(LAST_QUERY_ID())). Python procedures: return a string, or write results to a table and return the table name. JavaScript: return JSON string or scalar. Limitation: procedures cannot directly return a result set to the caller like a function. Workaround: write to a temporary table, return the table name, caller queries the temp table. Or use a UDTF instead if you need tabular output in SELECT.

Can stored procedures be called from tasks?

Yes. A task SQL can be CALL my_procedure(args). Common pattern: task triggers on stream, calls procedure that implements complex ETL logic. The procedure runs within the task execution context.

Maximum execution time?

Stored procedures have the same timeout as regular queries: dependent on warehouse. For long-running procedures, use a larger warehouse and consider breaking into multiple procedures called sequentially.