Stored Procedures, UDFs, and Snowflake Scripting: A Practical Guide

Imagine you're a data engineer at a fast-growing e-commerce company. Every night, you need to clean raw sales data, calculate customer lifetime value, and load it into a reporting table. Without stored procedures, you'd manually run a dozen SQL statements in sequence, hoping you don't miss a step. With Snowflake stored procedures and UDFs, you can automate this entire pipeline, handle errors gracefully, and reuse logic across multiple workflows.

Snowflake's support for stored procedures and user-defined functions (UDFs) brings the power of procedural programming to your data warehouse. Whether you're migrating from traditional databases like PostgreSQL or building new data pipelines, understanding these features is essential for writing maintainable, efficient code. Snowflake Scripting, introduced in 2021, provides a native SQL-based language for writing stored procedures, reducing the need for JavaScript or Python.

In this tutorial, you'll learn how to create and use stored procedures and UDFs in Snowflake, with a focus on real-world scenarios. We'll cover Snowflake Scripting syntax, debugging techniques, and common pitfalls. By the end, you'll be able to automate complex data transformations, improve code reusability, and troubleshoot production issues like a pro.

1. Introduction to Snowflake Stored Procedures and UDFs

Snowflake stored procedures allow you to write procedural code that executes multiple SQL statements, supports conditional logic (IF/ELSE), loops (FOR, WHILE), and exception handling. They can be written in JavaScript (using Snowflake's JavaScript API) or Snowflake Scripting (a SQL-based procedural language). UDFs, on the other hand, are functions that return a single scalar value or a table. They are ideal for encapsulating reusable calculations or transformations that can be used in SELECT statements.

When should you use each? Use stored procedures for tasks that require multiple steps, such as ETL pipelines, data validation, or administrative operations. Use UDFs for row-level computations, like calculating discounted prices or formatting strings. Both support parameters and can be called from SQL.

Snowflake Scripting is the recommended approach for new development because it integrates seamlessly with SQL and is easier to read and maintain. However, JavaScript procedures offer more flexibility for complex logic and integration with external APIs.

In this section, we'll create a simple stored procedure and a UDF to get started.

-- Create a stored procedure using Snowflake Scripting
CREATE OR REPLACE PROCEDURE greet_user(user_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
  RETURN 'Hello, ' || user_name || '!';
END;
$$;

-- Call the procedure
CALL greet_user('Alice');

-- Create a scalar UDF
CREATE OR REPLACE FUNCTION add_tax(price NUMBER)
RETURNS NUMBER
AS
$$
  price * 1.08
$$;

-- Use the UDF in a query
SELECT add_tax(100) AS price_with_tax;

2. Snowflake Scripting: Variables, Cursors, and Control Flow

Snowflake Scripting introduces variables, cursors, and control flow constructs that make procedural programming possible. Variables are declared using DECLARE or LET, and can be of any Snowflake data type. Cursors allow you to iterate over query results row by row. Control flow includes IF/ELSE, FOR, WHILE, and REPEAT loops.

Let's build a procedure that processes orders and updates inventory. This example demonstrates variables, a cursor, and a loop.

CREATE OR REPLACE PROCEDURE process_orders()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  order_id INT;
  order_total NUMBER;
  cur CURSOR FOR SELECT id, total FROM orders WHERE status = 'PENDING';
  processed_count INT DEFAULT 0;
BEGIN
  OPEN cur;
  FOR rec IN cur DO
    order_id := rec.id;
    order_total := rec.total;
    -- Update inventory logic (simplified)
    UPDATE inventory SET quantity = quantity - 1 WHERE product_id IN (SELECT product_id FROM order_items WHERE order_id = order_id);
    UPDATE orders SET status = 'PROCESSED' WHERE id = order_id;
    processed_count := processed_count + 1;
  END FOR;
  CLOSE cur;
  RETURN 'Processed ' || processed_count || ' orders.';
END;
$$;

CALL process_orders();

3. Creating and Using User-Defined Functions (UDFs)

UDFs in Snowflake can be scalar (return a single value) or tabular (return a table). They can be written in SQL, JavaScript, Python, or Java. SQL UDFs are the simplest and most performant for simple calculations. JavaScript UDFs allow more complex logic, such as string manipulation or date arithmetic.

Here's an example of a SQL UDF that calculates the number of business days between two dates, excluding weekends.

CREATE OR REPLACE FUNCTION business_days(start_date DATE, end_date DATE)
RETURNS INT
AS
$$
  SELECT COUNT(*)::INT
  FROM TABLE(GENERATOR(ROWCOUNT => DATEDIFF('day', start_date, end_date) + 1))
  WHERE DATEADD('day', SEQ4(), start_date) BETWEEN start_date AND end_date
    AND DAYOFWEEK(DATEADD('day', SEQ4(), start_date)) NOT IN (0, 6)
$$;

-- Usage
SELECT business_days('2024-01-01', '2024-01-10') AS business_days;

4. Stored Procedures for ETL: A Real-World Example

ETL (Extract, Transform, Load) pipelines are a common use case for stored procedures. Let's build a procedure that extracts raw sales data from a staging table, transforms it by calculating commissions, and loads it into a reporting table. This example uses Snowflake Scripting with error handling and logging.

CREATE OR REPLACE PROCEDURE etl_sales_pipeline()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  start_time TIMESTAMP;
  end_time TIMESTAMP;
  rows_affected INT;
BEGIN
  start_time := CURRENT_TIMESTAMP;
  
  -- Step 1: Extract and transform
  CREATE OR REPLACE TEMP TABLE transformed_sales AS
  SELECT 
    sale_id,
    product_id,
    quantity,
    price,
    quantity * price AS revenue,
    CASE 
      WHEN quantity > 10 THEN revenue * 0.1
      ELSE revenue * 0.05
    END AS commission
  FROM raw_sales
  WHERE processed_flag = FALSE;
  
  rows_affected := (SELECT COUNT(*) FROM transformed_sales);
  
  -- Step 2: Load into reporting table
  INSERT INTO sales_report (sale_id, product_id, revenue, commission, processed_date)
  SELECT sale_id, product_id, revenue, commission, CURRENT_TIMESTAMP
  FROM transformed_sales;
  
  -- Step 3: Mark raw data as processed
  UPDATE raw_sales SET processed_flag = TRUE
  WHERE sale_id IN (SELECT sale_id FROM transformed_sales);
  
  end_time := CURRENT_TIMESTAMP;
  
  -- Log the execution
  INSERT INTO etl_log (procedure_name, start_time, end_time, rows_processed, status)
  VALUES ('etl_sales_pipeline', start_time, end_time, rows_affected, 'SUCCESS');
  
  RETURN 'Processed ' || rows_affected || ' rows.';
EXCEPTION
  WHEN OTHER THEN
    INSERT INTO etl_log (procedure_name, start_time, end_time, rows_processed, status, error_message)
    VALUES ('etl_sales_pipeline', start_time, CURRENT_TIMESTAMP, 0, 'FAILED', SQLERRM);
    RETURN 'Error: ' || SQLERRM;
END;
$$;

5. JavaScript Stored Procedures: When and How to Use Them

JavaScript stored procedures offer more flexibility than Snowflake Scripting, especially for complex logic, external API calls, or when you need to use JavaScript libraries. However, they require understanding Snowflake's JavaScript API, which includes methods like snowflake.execute(), snowflake.createStatement(), and resultSet.

Here's an example of a JavaScript procedure that calls an external API (simulated) to enrich customer data.

CREATE OR REPLACE PROCEDURE enrich_customer_data(customer_id INT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
  var result = '';
  try {
    // Simulate external API call (replace with actual HTTP request)
    var apiResponse = '{"name": "John Doe", "email": "john@example.com"}';
    var customer = JSON.parse(apiResponse);
    
    // Update customer table
    var updateStmt = snowflake.createStatement({
      sqlText: 'UPDATE customers SET name = ?, email = ? WHERE id = ?',
      binds: [customer.name, customer.email, customer_id]
    });
    updateStmt.execute();
    
    result = 'Customer ' + customer_id + ' enriched successfully.';
  } catch (err) {
    result = 'Error: ' + err.message;
  }
  return result;
$$;

CALL enrich_customer_data(123);

6. Debugging and Monitoring Stored Procedures and UDFs

Debugging stored procedures in Snowflake can be challenging because you cannot step through code interactively. However, you can use several techniques: - Use SYSTEM$LOG to insert log messages into a table. - Query the QUERY_HISTORY view to see the SQL statements executed by the procedure. - Use the GET_DDL function to review the procedure definition. - For JavaScript procedures, use console.log() (output appears in the query profile).

Here's an example of logging within a Snowflake Scripting procedure.

-- Create a log table
CREATE OR REPLACE TABLE proc_log (
  log_time TIMESTAMP,
  message VARCHAR
);

-- Procedure with logging
CREATE OR REPLACE PROCEDURE debug_example()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
  INSERT INTO proc_log VALUES (CURRENT_TIMESTAMP, 'Starting procedure');
  
  -- Some logic
  LET x INT := 10;
  INSERT INTO proc_log VALUES (CURRENT_TIMESTAMP, 'Variable x = ' || x);
  
  -- Simulate work
  CREATE OR REPLACE TEMP TABLE temp AS SELECT * FROM orders LIMIT 100;
  INSERT INTO proc_log VALUES (CURRENT_TIMESTAMP, 'Temp table created');
  
  RETURN 'Done';
END;
$$;

CALL debug_example();
SELECT * FROM proc_log ORDER BY log_time;

7. Best Practices and Performance Considerations

When writing stored procedures and UDFs, follow these best practices: - Use set-based operations instead of cursors whenever possible. - Limit the number of DDL statements inside procedures; they consume more resources. - Use temporary tables for intermediate results to avoid locking. - Mark UDFs as IMMUTABLE or STABLE to enable caching. - Avoid using UDFs in WHERE clauses on large tables. - Use exception handling to catch errors and rollback if necessary. - Monitor procedure execution with Snowflake's QUERY_HISTORY and ACCOUNT_USAGE views.

-- Example: Set-based update instead of cursor
-- Instead of looping, use a single UPDATE with JOIN
UPDATE inventory i
SET quantity = i.quantity - oi.quantity
FROM order_items oi
WHERE i.product_id = oi.product_id
  AND oi.order_id IN (SELECT id FROM orders WHERE status = 'PENDING');

-- Mark UDF as IMMUTABLE for caching
CREATE OR REPLACE FUNCTION add_tax(price NUMBER)
RETURNS NUMBER
IMMUTABLE
AS
$$
  price * 1.08
$$;