Snowflake Stored Procedures and User-Defined Functions

Snowflake lets you write your own reusable code blocks that run directly inside the database. Two powerful tools make this possible: Stored Procedures and User-Defined Functions (UDFs). Understanding both helps you automate complex tasks, enforce business logic, and keep your SQL clean and organized.

What Are Stored Procedures?

A stored procedure is a named block of code stored inside Snowflake. You write the logic once, give it a name, and call it whenever you need it. Think of it like a recipe saved in a cookbook. Instead of writing out all the steps every time you cook a dish, you just say "make pasta" and follow the saved recipe.

Stored procedures in Snowflake can perform actions that go beyond simple queries. They can create tables, insert data, delete rows, run loops, handle errors, and execute multiple SQL statements in sequence.

Real-World Diagram: The Assembly Line

Imagine a car factory assembly line. Raw parts come in, get processed through multiple stations, and a finished car comes out. A stored procedure works the same way:

• Input: Parameters you pass in (like a start date and end date)
• Processing: SQL statements that run in sequence (clean data, transform it, load it)
• Output: A result or confirmation that the job is done

Without stored procedures, you would write all those steps manually every time. With a stored procedure, you call one name and the entire assembly line runs automatically.

Creating Your First Stored Procedure

Snowflake supports stored procedures written in JavaScript, Python, Scala, Java, and SQL scripting. The most beginner-friendly option is JavaScript or SQL scripting.

SQL Scripting Example

CREATE OR REPLACE PROCEDURE update_sales_summary()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  DELETE FROM sales_summary WHERE summary_date = CURRENT_DATE;
  
  INSERT INTO sales_summary (summary_date, total_sales, total_orders)
  SELECT 
    CURRENT_DATE,
    SUM(amount),
    COUNT(order_id)
  FROM orders
  WHERE order_date = CURRENT_DATE;
  
  RETURN 'Sales summary updated successfully';
END;
$$;

To run this procedure, you write:

CALL update_sales_summary();

Every time you call it, Snowflake runs the delete and insert steps in order, then returns a confirmation message.

JavaScript Stored Procedure Example

CREATE OR REPLACE PROCEDURE archive_old_records(days_old NUMBER)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
  var archive_sql = `
    INSERT INTO archived_orders
    SELECT * FROM orders
    WHERE order_date < DATEADD(day, -${DAYS_OLD}, CURRENT_DATE)
  `;
  
  var delete_sql = `
    DELETE FROM orders
    WHERE order_date < DATEADD(day, -${DAYS_OLD}, CURRENT_DATE)
  `;
  
  snowflake.execute({sqlText: archive_sql});
  snowflake.execute({sqlText: delete_sql});
  
  return "Archival complete for records older than " + DAYS_OLD + " days";
$$;

CALL archive_old_records(90);

Here, you pass 90 as a parameter and the procedure archives and removes all orders older than 90 days.

Error Handling in Stored Procedures

Stored procedures can catch errors and respond gracefully instead of crashing. This matters when your procedure runs unattended on a schedule.

CREATE OR REPLACE PROCEDURE safe_data_load()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  err_msg STRING;
BEGIN
  INSERT INTO target_table SELECT * FROM staging_table;
  RETURN 'Load successful';
EXCEPTION
  WHEN OTHER THEN
    err_msg := SQLERRM;
    INSERT INTO error_log (error_time, error_message)
    VALUES (CURRENT_TIMESTAMP, err_msg);
    RETURN 'Load failed: ' || err_msg;
END;
$$;

If the insert fails for any reason, the procedure catches the error, logs it to an error table, and returns a message instead of stopping everything.

What Are User-Defined Functions (UDFs)?

A User-Defined Function, or UDF, is a custom function you create to extend Snowflake's built-in capabilities. Snowflake comes with hundreds of built-in functions like SUM(), UPPER(), and DATEDIFF(). When a built-in function does not exist for what you need, you write a UDF.

Real-World Diagram: The Custom Stamp

Picture a post office. It has standard stamps for common destinations. But if you ship to a special location with a custom rate, you need a custom stamp made just for that purpose. A UDF is that custom stamp. You define it once and use it anywhere in your queries, just like a built-in function.

Types of UDFs in Snowflake

Scalar UDFs

A scalar UDF takes one or more inputs and returns a single value for each row. It works exactly like a built-in function inside a SELECT statement.

CREATE OR REPLACE FUNCTION calculate_tax(price FLOAT, tax_rate FLOAT)
RETURNS FLOAT
LANGUAGE SQL
AS
$$
  price * tax_rate / 100
$$;

-- Use it like any built-in function
SELECT 
  product_name,
  price,
  calculate_tax(price, 8.5) AS tax_amount,
  price + calculate_tax(price, 8.5) AS total_price
FROM products;

You defined the formula once. Now every query that needs tax calculation uses this function.

Table UDFs (UDTFs)

A Table UDF returns multiple rows instead of a single value. This is useful when you want to generate or transform data into a table shape.

CREATE OR REPLACE FUNCTION generate_date_range(start_date DATE, end_date DATE)
RETURNS TABLE (calendar_date DATE)
LANGUAGE SQL
AS
$$
  SELECT DATEADD(day, seq4(), start_date)
  FROM TABLE(GENERATOR(ROWCOUNT => DATEDIFF(day, start_date, end_date) + 1))
$$;

-- Use it in a FROM clause
SELECT calendar_date
FROM TABLE(generate_date_range('2024-01-01', '2024-01-31'));

This UDTF generates every date between two given dates, which is very handy for building date dimension tables or filling gaps in time-series data.

Python UDFs

Python UDFs let you bring Python libraries into your SQL queries. This opens the door to complex string operations, machine learning scoring, statistical calculations, and much more.

CREATE OR REPLACE FUNCTION clean_phone_number(raw_phone STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'clean_phone'
AS
$$
import re

def clean_phone(raw_phone):
    if raw_phone is None:
        return None
    digits = re.sub(r'\D', '', raw_phone)
    if len(digits) == 10:
        return f"({digits[:3]}) {digits[3:6]}-{digits[6:]}"
    elif len(digits) == 11 and digits[0] == '1':
        return f"({digits[1:4]}) {digits[4:7]}-{digits[7:]}"
    else:
        return "Invalid"
$$;

SELECT 
  customer_name,
  phone_raw,
  clean_phone_number(phone_raw) AS phone_formatted
FROM customers;

This Python UDF standardizes messy phone numbers into a consistent format, something that would take many lines of SQL to replicate.

Key Differences: Stored Procedures vs UDFs

Diagram: The Chef vs The Recipe Card

Think of a UDF as a recipe card. You read it, follow the steps, and it tells you exactly what one ingredient becomes. It gives you a result. A stored procedure is the full chef. The chef reads multiple recipe cards, uses different techniques, manages the kitchen, handles problems when ingredients run out, and delivers the full meal.

Secure UDFs

Snowflake lets you mark a UDF as SECURE. This hides the internal logic from users who call the function. They can use it but they cannot see how it works. This matters when your function contains business rules, proprietary formulas, or sensitive logic.

CREATE OR REPLACE SECURE FUNCTION calculate_commission(sales_amount FLOAT)
RETURNS FLOAT
LANGUAGE SQL
AS
$$
  CASE
    WHEN sales_amount < 10000 THEN sales_amount * 0.05
    WHEN sales_amount < 50000 THEN sales_amount * 0.08
    ELSE sales_amount * 0.12
  END
$$;

A regular user calling SELECT calculate_commission(25000) gets the result but cannot inspect the commission tiers.

Calling Procedures with Parameters

Parameters make stored procedures flexible. Instead of hardcoding values, you pass them at call time.

CREATE OR REPLACE PROCEDURE refresh_region_data(region_name STRING, load_date DATE)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  DELETE FROM regional_sales
  WHERE region = region_name AND sale_date = load_date;
  
  INSERT INTO regional_sales
  SELECT * FROM raw_sales
  WHERE region = region_name AND sale_date = load_date;
  
  RETURN 'Refreshed ' || region_name || ' for ' || load_date::STRING;
END;
$$;

CALL refresh_region_data('North America', '2024-06-01');
CALL refresh_region_data('Europe', '2024-06-01');

The same procedure handles any region and any date. You avoid writing duplicate code for each combination.

Using Snowflake Scripting Variables and Loops

Snowflake's SQL scripting language supports variables, loops, and conditionals. These make procedures as powerful as traditional programming languages for database tasks.

CREATE OR REPLACE PROCEDURE backfill_monthly_summaries(start_year INT)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  current_month INT := 1;
  current_year INT := start_year;
  rows_inserted INT := 0;
BEGIN
  WHILE current_month <= 12 DO
    INSERT INTO monthly_summary (year, month, total_revenue)
    SELECT 
      current_year,
      current_month,
      COALESCE(SUM(revenue), 0)
    FROM daily_sales
    WHERE YEAR(sale_date) = current_year
      AND MONTH(sale_date) = current_month;
    
    rows_inserted := rows_inserted + 1;
    current_month := current_month + 1;
  END WHILE;
  
  RETURN 'Inserted ' || rows_inserted || ' monthly summary rows for ' || start_year;
END;
$$;

CALL backfill_monthly_summaries(2023);

This procedure loops through all 12 months for a given year and builds summary data for each one automatically.

Granting Access to Procedures and UDFs

Like all Snowflake objects, procedures and functions follow the role-based access control model. You grant USAGE on a procedure or function to allow a role to call it.

-- Grant usage to a role
GRANT USAGE ON PROCEDURE update_sales_summary() TO ROLE analyst_role;
GRANT USAGE ON FUNCTION calculate_tax(FLOAT, FLOAT) TO ROLE analyst_role;

-- Revoke access when no longer needed
REVOKE USAGE ON PROCEDURE update_sales_summary() FROM ROLE analyst_role;

The analyst role can now call the procedure and function without knowing what is inside them.

Viewing and Managing Stored Procedures and UDFs

-- List all stored procedures in the current schema
SHOW PROCEDURES;

-- List all UDFs
SHOW FUNCTIONS;

-- View the definition of a procedure
SELECT GET_DDL('PROCEDURE', 'update_sales_summary()');

-- View the definition of a function
SELECT GET_DDL('FUNCTION', 'calculate_tax(FLOAT, FLOAT)');

-- Drop a procedure
DROP PROCEDURE IF EXISTS archive_old_records(NUMBER);

-- Drop a function
DROP FUNCTION IF EXISTS calculate_tax(FLOAT, FLOAT);

Key Points Summary

• Stored Procedures run a sequence of SQL statements, can modify data, and are called with CALL.
• UDFs compute and return values, work inside SELECT queries, and cannot modify data.
• Both support multiple languages: SQL, JavaScript, Python, Java, and Scala.
• Scalar UDFs return one value per row; Table UDFs return multiple rows.
• Mark a UDF as SECURE to hide business logic from end users.
• Use parameters to make procedures flexible and reusable.
• Snowflake scripting supports variables, loops, and error handling inside procedures.
• Grant access with GRANT USAGE so only authorized roles can run your code.