A stored procedure is a named Snowflake program that encapsulates data transformation or control logic and runs through a single procedure call. It can replace repeatedly issued SQL, while adding branching, looping, exception handling, and logging where the pipeline needs them. section 4.3 section 4.6
Use a procedure when a pipeline must merge staged data into a target table whenever new data arrives. A task or another orchestration mechanism can invoke that procedure, keeping the load operation centralized and repeatable. section 4.3 section 4.3.1
A basic Snowflake Scripting procedure defines:
- A CREATE PROCEDURE declaration with a name and optional parameters. section 4.3.1
- A RETURNS clause for the result type and a LANGUAGE clause; SQL denotes Snowflake Scripting. section 4.3.1
- A body enclosed in $$, with BEGIN and END around the statements to execute. section 4.3.1
- An optional RETURN statement that reports execution information, such as rows affected through SQLROWCOUNT. section 4.3.2
Loading combined customer orders
LOAD_CUSTOMER_ORDERS executes a MERGE from combined staging data into CUSTOMER_ORDERS_COMBINED. Calling it loads the target table; adding RETURN 'Load completed.' with SQLROWCOUNT reports the affected-row count after the merge. section 4.3.1 section 4.3.2
Procedures may be written in Snowflake Scripting, JavaScript, Java, Python, or Scala. For work requiring procedural retrieval of model output, transformation, and saving results, a Python procedure can accept an input parameter, use a handler function, and return a table. section 4.3 section 7.4.1
For resilient pipelines, add exception handling to catch errors or unexpected outcomes and perform remediation or notification. Built-in logging can capture procedure messages during execution in an event table, enabling periodic pipeline-status review. section 4.6 section 4.6
