Stored Procedures, SQL UDFs, Python UDFs and Lambda UDFs in Amazon Redshift: Which One, and When

Most Redshift codebases reach a point where plain SQL statements in an orchestrator are not enough: you need a loop, a retry, a lookup against an external service, or a piece of business logic that five models all need to call the same way. Redshift gives you four places to put that logic — stored procedures, SQL UDFs, Python UDFs and Lambda UDFs — and they have very different performance and operational profiles. Choosing wrong is one of the more common reasons a warehouse that benchmarked fine gets slow in production.

This tutorial covers all four, with working examples, and a decision rule at the end.

1. SQL UDFs: the default, and almost free

A scalar SQL UDF is a macro. Redshift inlines it into the query plan, so it runs on the compute nodes at full speed with no per-row overhead worth measuring.

CREATE OR REPLACE FUNCTION f_net_revenue(gross NUMERIC(18,2), discount NUMERIC(18,2), refund NUMERIC(18,2))
RETURNS NUMERIC(18,2)
IMMUTABLE
AS $$
  SELECT GREATEST($1 - COALESCE($2, 0) - COALESCE($3, 0), 0)
$$ LANGUAGE sql;

SELECT order_id, f_net_revenue(gross_amount, discount_amount, refund_amount) AS net
FROM sales.orders
WHERE order_date >= CURRENT_DATE - 30;

Rules that matter:

• The body is a single SELECT. No joins to your tables, no subqueries against catalog data, no control flow.
• Arguments are positional ($1, $2), which makes long signatures easy to get wrong — keep them short.
• Mark it IMMUTABLE when the output depends only on the inputs. Redshift can then cache and reorder more aggressively.
• Grant it: GRANT EXECUTE ON FUNCTION f_net_revenue(NUMERIC, NUMERIC, NUMERIC) TO ROLE analysts;

If a piece of logic can be expressed as one SQL expression, it belongs here. Roughly 80% of the "we need a UDF" requests we see in code review are this case.

2. Python UDFs: convenient, and the usual performance trap

Python UDFs run a Python interpreter on the compute nodes, once per row.

CREATE OR REPLACE FUNCTION f_url_host(url VARCHAR)
RETURNS VARCHAR
STABLE
AS $$
  from urllib.parse import urlparse
  if url is None:
      return None
  try:
      return urlparse(url).hostname
  except Exception:
      return None
$$ LANGUAGE plpythonu;

They work, they are easy to write, and they are the single most common cause of "this query used to take 40 seconds and now takes 20 minutes". The overhead is per row and it does not parallelise away: a billion-row scan means a billion interpreter calls.

Practical guidance:

• Never call a Python UDF in a WHERE clause on a large table — you have forced a full scan through the interpreter before any filtering.
• If you must use one, apply it after aggregation or on a filtered CTE, so it sees thousands of rows and not billions.
• Check whether the built-in functions already cover it. SPLIT_PART, REGEXP_SUBSTR, REGEXP_REPLACE, JSON_PARSE with SUPER/PartiQL, and the IS_VALID_JSON family remove most of the historical reasons for Python UDFs.
• Materialize expensive derivations once in an ELT step rather than recomputing them in every BI query.

Measure the difference honestly before shipping:

-- Compare the same transformation implemented both ways
SELECT query_id, elapsed_time / 1000000.0 AS seconds, LEFT(query_text, 80)
FROM SYS_QUERY_HISTORY
WHERE start_time > DATEADD(hour, -1, GETDATE())
  AND query_text ILIKE '%f_url_host%'
ORDER BY start_time DESC;

Note also that Python UDFs are not available on Redshift Serverless in the way many teams assume when they move over — verify support for your target configuration before you design around them, and prefer SQL UDFs or Lambda UDFs for portability across provisioned and Serverless.

3. Lambda UDFs: the escape hatch to the rest of AWS

A Lambda UDF hands a batch of rows to an AWS Lambda function and reads the results back. This is how you reach a tokenization service, an external API, a KMS key, or a model endpoint from inside SQL.

Create the external function:

CREATE EXTERNAL FUNCTION f_tokenize(plaintext VARCHAR)
RETURNS VARCHAR
STABLE
LAMBDA 'redshift-tokenizer'
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftLambdaUDFRole';

The Lambda receives a batched payload and must answer in the same order:

def handler(event, context):
    rows = event["arguments"]          # list of [plaintext] lists
    results = [tokenize(r[0]) if r[0] is not None else None for r in rows]
    return {
        "success": True,
        "num_records": len(results),
        "results": results,
    }

What to watch:

• Batching is your throughput knob. Redshift sends rows in batches; a handler that does one network call per row will be ten times slower than one that calls the downstream service in bulk. Write the handler to process the whole batch.
• Response size limits. Keep the payload well under Lambda's 6 MB synchronous limit; MAX_BATCH_ROWS and MAX_BATCH_SIZE on CREATE EXTERNAL FUNCTION let you cap it.
• Concurrency. A big scan can invoke many Lambdas at once. Set reserved concurrency so a single analyst query cannot exhaust the account's Lambda concurrency and take down unrelated services.
• Failure semantics. "success": False with an "error_msg" fails the whole query. Decide deliberately whether a bad row should fail the query or return NULL.
• Cost. You are now paying Lambda invocation and duration per row batch. For a nightly job over 50M rows that can be real money; for masking a few thousand rows in a report it is nothing.

Lambda UDFs are also the sanctioned pattern for external tokenization and de-tokenization alongside Redshift's native dynamic data masking: mask by default with DDM, de-tokenize through a Lambda UDF that checks the caller's role.

4. Stored procedures: control flow and multi-statement work

Stored procedures (PL/pgSQL) are for orchestration inside the database: loops, conditionals, transaction control, dynamic SQL, error handling. They do not return a value per row; they do work.

CREATE OR REPLACE PROCEDURE sp_load_dim_customer(load_date DATE)
AS $$
DECLARE
    rows_merged BIGINT;
BEGIN
    -- Stage
    EXECUTE 'TRUNCATE TABLE staging.customer_stg';
    EXECUTE 'COPY staging.customer_stg FROM ''s3://bucket/customer/' || TO_CHAR(load_date, 'YYYY/MM/DD') ||
            ''' IAM_ROLE ''arn:aws:iam::111111111111:role/RedshiftCopy'' FORMAT AS PARQUET';

    -- Merge into the dimension
    MERGE INTO dw.dim_customer t
    USING staging.customer_stg s ON t.customer_id = s.customer_id
    WHEN MATCHED THEN UPDATE SET name = s.name, segment = s.segment, updated_at = GETDATE()
    WHEN NOT MATCHED THEN INSERT (customer_id, name, segment, updated_at)
                         VALUES (s.customer_id, s.name, s.segment, GETDATE());

    GET DIAGNOSTICS rows_merged := ROW_COUNT;

    INSERT INTO ops.load_audit(job_name, load_date, rows_affected, status, finished_at)
    VALUES ('dim_customer', load_date, rows_merged, 'OK', GETDATE());

EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'dim_customer failed: %', SQLERRM;
    INSERT INTO ops.load_audit(job_name, load_date, rows_affected, status, error_text, finished_at)
    VALUES ('dim_customer', load_date, 0, 'FAILED', SQLERRM, GETDATE());
    RAISE;
END;
$$ LANGUAGE plpgsql;

CALL sp_load_dim_customer(CURRENT_DATE);

Things that bite teams here:

• The exception block is coarse. Redshift supports EXCEPTION WHEN OTHERS, not per-condition handlers, and entering it rolls back the work of the current block. Write the audit row and re-RAISE; don't swallow errors.
• Cursors and row-by-row loops are an anti-pattern. If you find yourself looping over a result set to update rows one at a time, you have written a procedural program on an MPP engine. Rewrite as a set-based MERGE or UPDATE ... FROM.
• Logging. RAISE INFO messages land in SVL_STORED_PROC_MESSAGES; your own audit table is more durable and easier to alert on.
• Security context. SECURITY DEFINER lets the procedure run with the owner's rights, which is how you let an analyst role run a load without granting it write access to the target schema. Use it deliberately and keep those procedures small.
• Calling from outside. The Data API supports CALL, so Step Functions or Airflow can run a procedure asynchronously without holding a connection.

The decision rule

Need	Use
One SQL expression, reused	SQL UDF
Row-level logic SQL can't express, small row counts	Python UDF (measure first)
Call something outside Redshift (API, KMS, tokenizer, model)	Lambda UDF
Loops, transactions, DDL, error handling, multi-statement loads	Stored procedure
Transformation over big tables with lineage and tests	Not a function at all — a dbt/ELT model

The last row is the one worth repeating. A surprising share of UDF and stored-procedure sprawl is transformation logic that should be a materialized model with tests and version control, not something a reviewer has to \df out of the catalog to find.

Inventory what you already have

Before you add another function, see what is there:

-- User-defined functions and their language
SELECT n.nspname AS schema, p.proname AS function, l.lanname AS language
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l ON l.oid = p.prolang
WHERE l.lanname IN ('sql', 'plpythonu', 'plpgsql', 'exfunc')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

-- Stored procedure activity in the last week
SELECT query_text, COUNT(*) AS calls, AVG(elapsed_time) / 1000000.0 AS avg_seconds
FROM SYS_QUERY_HISTORY
WHERE start_time > DATEADD(day, -7, GETDATE())
  AND query_text ILIKE 'call %'
GROUP BY 1 ORDER BY calls DESC LIMIT 25;

Anything that has not been called in ninety days is a candidate for deletion, and anything Python and hot is a candidate for rewriting.

---

If you have inherited a warehouse where business logic is spread across dozens of UDFs and procedures — or you are deciding where new logic should live before it sprawls — our Redshift Performance Optimization and Data Modeling & Architecture engagements cover exactly that review. Get in touch to scope one.