Migrating Python Udfs

Migrate Python UDFs

Migrate Amazon Redshift Python UDFs (plpythonu) to Lambda UDFs before the June 30, 2026 end-of-support deadline. Discovers UDFs via MCP or CLI, converts Python bodies to Lambda handlers, deploys with safe naming, validates, and renames post-approval.

Reference Documentation

• Discovery queries
• Conversion rules and type mapping
• CLI patterns and Data API rules
• IAM and VPC prerequisites
• Rollback procedures
• Performance and cost optimization

Workflow

Phase 1: Discover Clusters

Ask the user which region to target. Then use the AWS MCP to list all Redshift Serverless workgroups and provisioned clusters in that region. Present the list and ask which one(s) to migrate. Do NOT auto-discover across all regions — require the user to specify the region.

If the user already provided a specific workgroup/cluster name, skip the listing and use the AWS MCP to verify the workgroup or cluster exists in the specified region.

Routing rules based on user input:

• Rule 0 (precedence): If the input matches both Rule 1's conditions AND Rule 2's conditions (e.g., user says both "cluster" and "workgroup" as type keywords, or provides both --cluster-identifier and --workgroup-name), ask the user to clarify which resource type to target. Incidental substrings inside resource names do NOT count as type keywords.
• Rule 1: User says "cluster" as a type keyword (not as a substring within a resource name) or provides --cluster-identifier → use aws redshift describe-clusters directly
• Rule 2: User says "workgroup" as a type keyword (not as a substring within a resource name) or provides --workgroup-name → use aws redshift-serverless get-workgroup directly
• Rule 3: User provides a name with no type keyword (neither "cluster"/"workgroup" as type keywords nor --cluster-identifier/--workgroup-name present) → try Serverless first; if not found, try provisioned. Use whichever succeeds.
• Rule 4: If the user specifies 'all', skip listing and migrate all discovered workgroups/clusters in the specified region without asking for selection.

Phase 2: Validate IAM and VPC Prerequisites

Before any deployment, verify the infrastructure. See iam-vpc-setup.md.

1. IAM role for Lambda execution — must have lambda.amazonaws.com trust 2. IAM role for Redshift namespace — must have:

• Trust policy with BOTH redshift.amazonaws.com AND redshift-serverless.amazonaws.com
• lambda:InvokeFunction permission scoped to the Lambda function name pattern

3. VPC connectivity — if enhanced VPC routing is enabled (or if Lambda UDFs fail with "Empty format"):

• Lambda VPC endpoint (com.amazonaws.<region>.lambda) required
• STS VPC endpoint (com.amazonaws.<region>.sts) required
• Security group must allow inbound TCP 443 from VPC CIDR

4. Namespace default role — verify the namespace has a default IAM role with Lambda invoke permissions 5. Superuser credentials for DDL — Creating EXTERNAL FUNCTIONs requires superuser. The IAM-mapped user (e.g., IAMR:admin) is NOT a superuser by default. Two options:

• (Recommended) Enable managed admin password on the namespace and use --secret-arn with the admin secret.
• (Alternative) Grant superuser to the IAM-mapped user via ALTER USER "IAMR:admin" CREATEUSER; (requires existing superuser access to run this grant).

Present findings to user and fix any gaps BEFORE proceeding to deployment.

Phase 3: Discover Python UDFs

Use the AWS MCP to list databases on the selected workgroup/cluster and ask the user which one contains the Python UDFs (default: dev).

Use the AWS MCP to query for Python UDFs (see discovery-queries.md). Try pg_proc with lanname = 'plpythonu' first, fall back to python_udf_inventory table. Present list and ask which to migrate.

Phase 4: Convert

For each UDF, generate:

• migration-input/<name>/original_udf.sql — original source for traceability
• migration-output/<name>/lambda_function.py — Lambda handler
• migration-output/<name>/<name>.sql — Redshift EXTERNAL FUNCTION DDL

Conversion rules: see conversion-rules.md. Critical points:

• Lambda MUST return json.dumps({"results": [...], "success": True}) — a JSON string, not a Python dict
• Arguments are row-based: event["arguments"][i][j] where i=row index, j=argument position
• Use _lambdaudf suffix for EXTERNAL FUNCTION names to avoid production disruption

Phase 5: Present Migration Plan and Get Approval

STOP and present the full plan to the user before deploying anything. Include:

• Number of UDFs to migrate
• Lambda function names (with _lambda suffix)
• Redshift function names (with _lambdaudf suffix)
• IAM role being used
• Target region
• Any infrastructure changes needed (VPC endpoints, IAM policy updates)

Wait for explicit approval: "Does this plan look good? I will deploy with the _lambdaudf suffix so your existing functions remain untouched."

Do NOT proceed without user confirmation.

Phase 6: Deploy and Validate (One UDF at a Time)

After approval, migrate each UDF sequentially — do NOT batch:

For each UDF: 1. Deploy Lambda — create or update the Lambda function (see cli-patterns.md) 2. Create Redshift EXTERNAL FUNCTION via Data API using admin credentials:

• Serverless: use --secret-arn (managed admin password from Secrets Manager)
• Provisioned cluster: use --db-user with --cluster-identifier (temporary credentials via GetClusterCredentials)
• Use IAM_ROLE default in DDL (references the namespace's default role)

3. Validate — compare PUDF vs LUDF output:

• Run the same input through both the original Python UDF and the new _lambdaudf
• Assert outputs are identical. Use type-safe casting (see cli-patterns.md)
• Example comparison query:

     SELECT <schema>.<name>(args) AS pudf_result,
            <schema>.<name>_lambdaudf(args) AS ludf_result
     FROM <test_input>;

4. On success → report result to user, proceed to next UDF 5. On failure → STOP. Report the failure to the user and ask how to proceed. Do NOT continue to the next UDF.

If validation fails with "Empty format" error, check CloudWatch logs for the Lambda function:

• No Lambda invocation in CloudWatch → VPC/IAM connectivity issue. Check VPC endpoints and IAM trust policy per iam-vpc-setup.md.
• Lambda WAS invoked in CloudWatch → Lambda is returning a Python dict instead of a json.dumps() string. Fix the handler to return json.dumps({"results": [...], "success": True}).

Phase 7: Rename (Post-Validation)

STOP: Wait for explicit customer sign-off: "All UDFs validated successfully. Ready to rename _lambdaudf functions to replace the originals? The old Python UDFs will be renamed with a _pythonudf suffix as a safety net."

Only after confirmation, for each UDF pair in a separate transaction: 1. RENAME the old Python UDF to <original_name>_pythonudf:

   ALTER FUNCTION <schema>.<original_name>(<arg-types>) RENAME TO <original_name>_pythonudf;

2. RENAME the Lambda UDF from <original_name>_lambdaudf to the original name:

   ALTER FUNCTION <schema>.<original_name>_lambdaudf(<arg-types>) RENAME TO <original_name>;

Execute each pair as a separate Data API statement. Do NOT batch all renames into one transaction — isolate failures to individual UDFs.

After all renames succeed, inform the user that _pythonudf functions remain available for rollback until they choose to drop them.

Gotchas

• NEVER deploy without user approval. Always present the plan first.
• Lambda response MUST be json.dumps(...) (string), NOT a Python dict. Returning a dict causes "Empty format" error.
• Arguments are ROW-BASED: args[i][j] (row i, argument j). NOT column-based args[j][i].
• "Empty format" error with no Lambda invocation in CloudWatch = VPC/IAM connectivity issue. Check VPC endpoints and IAM trust policy.
• IAM trust policy for the Redshift namespace role MUST include redshift-serverless.amazonaws.com for Serverless workgroups.
• Creating EXTERNAL FUNCTIONs requires superuser. IAM-mapped users are NOT superusers by default — use --secret-arn with managed admin password, or grant superuser to the IAM-mapped user via ALTER USER.
• All DDL must use the AWS MCP via aws redshift-data execute-statement. Do NOT attempt DDL through read-only query tools.
• Do NOT scan all regions. Ask the user for the region, then list workgroups/clusters in that region for them to pick from.
• Schema comes from discovery query. Do NOT hardcode public.
• Lambda cold starts take 10-30s. Account for this when polling validation results — use retry with sufficient timeout.
• Redshift cannot CAST boolean to VARCHAR. Use CASE WHEN fn(...) THEN 'true' ELSE 'false' END.

CLI Patterns and Data API Rules

Variables

REGION="us-east-1"                          # AWS region
WORKGROUP="my-workgroup"                    # Redshift Serverless workgroup name (human-readable, not ARN)
CLUSTER_ID="my-cluster"                     # Redshift provisioned cluster identifier
DATABASE="dev"                              # Target database name
SECRET_ARN="arn:aws:secretsmanager:us-east-1:123456789012:secret:redshift-admin-xxxxxx"  # Admin secret (xxxxxx = random suffix added by Secrets Manager)
S3_BUCKET="my-lambda-deploy-bucket"         # S3 bucket for Lambda deployment packages
S3_PREFIX="lambda-udfs"                     # S3 key prefix for zip files
ROLE_ARN="arn:aws:iam::123456789012:role/redshift-udf-lambda-role"  # Lambda execution role ARN
DB_USER="admin"                             # Redshift database superuser (provisioned clusters ONLY; do NOT use for Serverless — use SECRET_ARN instead)

Data API Rules

1. Strip whitespace from IDs: STMT_ID=$(aws redshift-data execute-statement ... --query 'Id' --output text | tr -d '[:space:]') 2. Use | delimiter for FUNC_NAME|STMT_ID pairs (safe with bash %%/## operators). 3. Use grep -A10 when extracting DDL from SQL files (multi-arg functions span many lines). 4. Poll with retry loop (never single fixed sleep). Status values: SUBMITTED, PICKED, STARTED, FINISHED, FAILED, ABORTED.

Lambda Deploy (parallel upsert)

Do NOT pass `--vpc-config` to `create-function`. Lambda does NOT need to be in a VPC. Redshift reaches Lambda via VPC endpoints.

for dir in migration-output/*/; do
    FUNC_NAME=$(basename "$dir"); LAMBDA_NAME="${FUNC_NAME}_lambda"
    S3_KEY="${S3_PREFIX}/${FUNC_NAME}/lambda_function.zip"
    { aws lambda update-function-code --function-name "$LAMBDA_NAME" \
        --s3-bucket "$S3_BUCKET" --s3-key "$S3_KEY" --region "$REGION" 2>/dev/null || \
      aws lambda create-function --function-name "$LAMBDA_NAME" \
        --runtime python3.12 --architectures arm64 \
        --handler lambda_function.lambda_handler --role "$ROLE_ARN" \
        --code "S3Bucket=${S3_BUCKET},S3Key=${S3_KEY}" \
        --timeout 60 --memory-size 128 --region "$REGION"
    } &
done
wait

Redshift DDL (sequential per-UDF)

Authentication for DDL execution (mutually exclusive — do NOT mix):

- Serverless: use --workgroup-name + --secret-arn. Do NOT use --db-user with Serverless.

- Provisioned cluster: use --cluster-identifier + --db-user. Do NOT use --secret-arn with provisioned clusters. Deployer needs redshift:GetClusterCredentials. See iam-vpc-setup.md.

Deploy and validate one UDF at a time. For each UDF:

Serverless

FUNC_NAME="<function_name>"
SQL=$(grep -A10 "^CREATE OR REPLACE" "migration-output/${FUNC_NAME}/${FUNC_NAME}.sql" | sed '/^$/d' | sed '/^--/d' | tr '\n' ' ')
STMT_ID=$(aws redshift-data execute-statement --workgroup-name "$WORKGROUP" \
    --database "$DATABASE" --secret-arn "$SECRET_ARN" --sql "$SQL" --region "$REGION" \
    --query 'Id' --output text | tr -d '[:space:]')

Provisioned cluster

FUNC_NAME="<function_name>"
SQL=$(grep -A10 "^CREATE OR REPLACE" "migration-output/${FUNC_NAME}/${FUNC_NAME}.sql" | sed '/^$/d' | sed '/^--/d' | tr '\n' ' ')
STMT_ID=$(aws redshift-data execute-statement --cluster-identifier "$CLUSTER_ID" \
    --database "$DATABASE" --db-user "$DB_USER" --sql "$SQL" --region "$REGION" \
    --query 'Id' --output text | tr -d '[:space:]')

Poll result

for i in 1 2 3 4 5 6 7 8 9 10; do
    sleep 3
    STATUS=$(aws redshift-data describe-statement --id "$STMT_ID" --region "$REGION" --query 'Status' --output text | tr -d '[:space:]')
    if [ "$STATUS" = "FINISHED" ] || [ "$STATUS" = "FAILED" ] || [ "$STATUS" = "ABORTED" ]; then break; fi
done
echo "  $FUNC_NAME: $STATUS"

Validation (PUDF vs LUDF comparison)

After creating each LUDF, compare its output against the original PUDF using the same input:

SELECT <schema>.<name>(args) AS pudf_result,
       <schema>.<name>_lambdaudf(args) AS ludf_result
FROM <test_input>;

Assert both columns return identical values. If they differ, STOP and report to user.

Type-safe casting:

• BOOLEAN: CASE WHEN fn(...) THEN 'true' ELSE 'false' END
• INTEGER/FLOAT: CAST(fn(...) AS VARCHAR)
• VARCHAR: no cast needed

Conversion Rules

Lambda Handler Template

CRITICAL: Return `json.dumps(...)` string, NOT a Python dict. Returning a dict causes "Empty format" error. CRITICAL: Arguments are ROW-BASED. `event["arguments"][i]` is a list of all args for row i.

import json

def lambda_handler(event, context):
    results = []
    for i in range(event['num_records']):
        try:
            row = event['arguments'][i]
            if any(a is None for a in row):
                results.append(None)
            else:
                results.append(_udf_impl(row[0], row[1], ...))
        except Exception:
            results.append(None)
    return json.dumps({"results": results, "success": True})

def _udf_impl(arg1, arg2, ...):
    <original UDF body>

Argument Access

CORRECT (row-based): args[i][0] = row i, first argument WRONG (column-based): args[0][i] — will cause IndexError for multi-arg functions

Response Format

# WRONG — causes "Empty format" error
return {"results": results, "success": True}
# CORRECT
return json.dumps({"results": results, "success": True})

Conversion Steps

1. Extract Python body from between $$...$$ 2. Move imports to module level (always include import json) 3. Wrap body in _udf_impl() preserving nested defs 4. Null-check each arg before calling _udf_impl 5. Apply Python 2-to-3 fixes (see table below) 6. Scan imports for non-stdlib packages; create layers.json if needed 7. Preserve regex backslashes with raw strings r'...'

SQL DDL Template

CREATE OR REPLACE EXTERNAL FUNCTION
    <schema>.<function_name>_lambdaudf(arg1_name arg1_type, arg2_name arg2_type, ...)
    RETURNS <return_type>
    IMMUTABLE
    LAMBDA '<function_name>_lambda'
    IAM_ROLE default;

Type Mapping

Python 2 to 3

External Libraries

Discovery Queries

Primary: pg_proc (live UDFs)

SELECT p.proname AS function_name, p.pronargs AS num_args,
    t.typname AS return_type, n.nspname AS schema_name,
    l.lanname AS language, pg_get_functiondef(p.oid) AS function_definition
FROM pg_proc p, pg_language l, pg_type t, pg_namespace n
WHERE p.prolang = l.oid AND p.prorettype = t.oid
  AND l.lanname = 'plpythonu' AND p.pronamespace = n.oid
  AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY proname;

Fallback: python_udf_inventory (post-Patch 198)

SELECT function_name, num_args, return_type, schema_name, function_definition
FROM public.python_udf_inventory ORDER BY function_name;

Use the fallback when pg_proc returns zero rows (Patch 198 blocks new plpythonu creation and may affect discovery on some clusters).

IAM and VPC Prerequisites

1. Lambda Execution Role

Trust: lambda.amazonaws.com. Attach AWSLambdaBasicExecutionRole managed policy. Naming: redshift-udf-lambda-role

Deployer permissions (on the IAM principal performing deployment, NOT the Lambda role):

lambda:CreateFunction, lambda:UpdateFunctionCode, s3:GetObject, s3:PutObject (on the deploy bucket prefix), iam:PassRole (on Lambda role ARN), redshift-data:ExecuteStatement, redshift-data:DescribeStatement, redshift-data:GetStatementResult, secretsmanager:GetSecretValue (on the admin secret ARN, for Serverless --secret-arn auth).

For provisioned clusters using --db-user: also redshift:GetClusterCredentials.

2. Redshift Namespace Role (invokes Lambda)

Trust policy — MUST include both services:

{"Version": "2012-10-17", "Statement": [{"Effect": "Allow",
  "Principal": {"Service": ["redshift.amazonaws.com", "redshift-serverless.amazonaws.com"]},
  "Action": "sts:AssumeRole"}]}

Inline policy: lambda:InvokeFunction on arn:aws:lambda:<region>:<account-id>:function:*_lambda

<region> = e.g., us-east-1. <account-id> = 12-digit ID (e.g., 123456789012).

Naming: redshift-udf-serverless-role

Common failure: Trust policy only has redshift.amazonaws.com → Serverless cannot assume role → "Empty format" error with no CloudWatch logs. Fix: Add redshift-serverless.amazonaws.com.

3. Namespace Configuration

Namespace name may differ from workgroup. Retrieve via:

aws redshift-serverless get-workgroup --workgroup-name <workgroup-name> --region <region> --query 'workgroup.namespaceName' --output text

Required:

• Attach invoke role: aws redshift-serverless update-namespace --namespace-name <namespace-name> --iam-roles <role-arn> --default-iam-role-arn <role-arn> --region <region>
• Enable managed admin password: aws redshift-serverless update-namespace --namespace-name <namespace-name> --manage-admin-password --region <region>

IMPORTANT: --iam-roles replaces ALL existing roles on the namespace. Include any previously attached role ARNs in the list to avoid removing them.

VPC Endpoints

Required when enhanced VPC routing is enabled OR Lambda UDFs return "Empty format" with no CloudWatch invocation.

# Lambda endpoint
aws ec2 create-vpc-endpoint --vpc-id <vpc-id> \
  --service-name com.amazonaws.<region>.lambda --vpc-endpoint-type Interface \
  --subnet-ids <subnet-id-1> <subnet-id-2> --security-group-ids <sg-id> \
  --private-dns-enabled --region <region>

# STS endpoint
aws ec2 create-vpc-endpoint --vpc-id <vpc-id> \
  --service-name com.amazonaws.<region>.sts --vpc-endpoint-type Interface \
  --subnet-ids <subnet-id-1> <subnet-id-2> --security-group-ids <sg-id> \
  --private-dns-enabled --region <region>

# Security group ingress (TCP 443 from VPC CIDR)
aws ec2 authorize-security-group-ingress --group-id <sg-id> \
  --protocol tcp --port 443 --cidr <vpc-cidr> --region <region>

Placeholder formats: <vpc-id> = vpc-xxxxxxxxxxxxxxxxx, <subnet-id-N> = subnet-xxxxxxxxxxxxxxxxx, <sg-id> = sg-xxxxxxxxxxxxxxxxx, <vpc-cidr> = CIDR notation (e.g., 10.0.0.0/16)

Diagnosing "Empty Format" Error

Superuser Access for DDL

IAM-mapped user (IAMR:admin) is NOT a superuser by default. Two options to create external functions:

Option A (Recommended): Managed admin password 1. Enable managed admin password: aws redshift-serverless update-namespace --namespace-name <namespace-name> --manage-admin-password --region <region> 2. Get secret ARN from response: adminPasswordSecretArn 3. Use in Data API: --secret-arn <arn> on execute-statement

Option B: Grant superuser to IAM-mapped user 1. Connect as an existing superuser (e.g., via managed admin password) 2. Run: ALTER USER "IAMR:admin" CREATEUSER; 3. The IAM-mapped user can now create external functions directly

Performance and Cost Optimization

Language Selection

Start with Python for quick port, optimize to Golang/Rust if needed.

Payload Management

Lambda limits payload to 6 MB (Lambda quotas — Invocation payload). Redshift batches rows to minimize calls.

• Strip unnecessary columns before passing to UDF
• Trim whitespace (CHAR types are padded)
• Use MAX_BATCH_SIZE for large/variable returns (start at 2 MB, tune upward)

Memoization

Cache results for duplicate inputs within a batch:

from functools import lru_cache

@lru_cache(maxsize=10000)
def compute_expensive_result(input_value):
    return result

Concurrency

• Default: 1,000 concurrent executions per Region per account (AWS Lambda quotas)
• Scales with concurrent queries, not row count
• Use reserved concurrency to isolate non-critical UDFs

Cost Evaluation

CloudWatch Insights query (ARM pricing):

parse @message /Duration:\s*(?<@duration_ms>\d+\.\d+)\s*ms\s*Billed\s*Duration:\s*(?<@billed_duration_ms>\d+)\s*ms\s*Memory\s*Size:\s*(?<@memory_size_mb>\d+)\s*MB/
| filter @message like /REPORT RequestId/
| stats sum(@billed_duration_ms * @memory_size_mb * 1.3021e-11 + 2.0e-7) as @cost_dollars_total

Constant 1.3021e-11 = ARM pricing $0.0000133334/GB-s ÷ 1024 MB/GB ÷ 1000 ms/s. For x86, use 1.6279e-11.

Benchmark: 30M rows Levenshtein (Python, ARM, us-east-1) = $0.02329

Rollback Procedures

When to Roll Back

Roll back if ANY occur after the function swap:

• Query results differ between old and new functions
• Lambda invocation errors in CloudWatch
• Unexpected latency increases
• Downstream pipeline failures
• Lambda throttling causing query timeouts

Steps

Before Rename (Phase 7 — validation failed, _lambdaudf still exists alongside original)

1. Drop the Lambda UDF:

   DROP FUNCTION <schema>.<function_name>_lambdaudf(<arg-types>);

<arg-types> = argument types only, e.g., VARCHAR, INTEGER, FLOAT. Do NOT include argument names.

2. Verify the original Python UDF still exists:

   SELECT n.NSPNAME, p.PRONAME, l.LANNAME
   FROM PG_PROC_INFO p JOIN PG_LANGUAGE l ON p.PROLANG = l.OID
   JOIN PG_NAMESPACE n ON p.PRONAMESPACE = n.OID
   WHERE p.PRONAME = '<function_name>' AND n.NSPNAME = '<schema>';

3. Verify downstream queries work with the original function.

After Rename (Phase 8 — swap completed, original Python UDF renamed to _pythonudf)

1. Rename the Lambda external function (now under the original name) back to _lambdaudf:

   ALTER FUNCTION <schema>.<function_name>(<arg-types>) RENAME TO <function_name>_lambdaudf;

2. Rename the Python UDF back to the original name:

   ALTER FUNCTION <schema>.<function_name>_pythonudf(<arg-types>) RENAME TO <function_name>;

3. Verify downstream queries work with the restored Python UDF.

Critical Warning

Rollback is ONLY available until June 30, 2026. After that date:

• Python UDF execution is suspended regardless of function state
• There is NO way to re-enable Python UDFs
• Any queries depending on Python UDFs will fail

Complete all migrations well before the deadline.

Escalation

1. Roll back immediately 2. Document the error (CloudWatch logs, SYS_QUERY_HISTORY) 3. Report to support channel 4. Investigate root cause before re-attempting