Workload

Stored procedures & UDFs for Redshift → Snowflake

Re-home reusable logic—Redshift UDFs, procedure-like utilities, and operational macros—into Snowflake routines with explicit behavior contracts and a replayable harness so retries and backfills don’t change outcomes.

Book assessment Explore workloads

Quick answer

Re-home reusable logic—Redshift UDFs, procedure-like utilities, and operational macros—into Snowflake routines with explicit behavior contracts and a replayable harness so retries and backfills don’t change outcomes.

Back to pair page

Context

Why this breaks

Redshift estates often embed business correctness in reusable logic: UDFs used in reporting and ETL, procedure-like utilities that manage windows and control tables, and macros that generate SQL. During migration, teams convert tables and queries first—then discover these utilities were the real system. Snowflake can implement equivalent outcomes, but only if procedural behavior is turned into explicit routines with testable semantics for state, errors, and idempotency. Common symptoms after migration:

  • Outputs drift due to type coercion and NULL handling differences - Dynamic SQL behaves differently (quoting, binding, identifier resolution) - Error handling changes; pipelines fail differently or silently continue - Side effects (audit/control writes) disappear unless recreated - Row-by-row procedural patterns become expensive and fragile if ported directly A successful migration extracts the behavior contract and validates it with a replayable harness, not ad-hoc spot checks.

Approach

How conversion works

  • Inventory & classify procedural assets: Redshift UDFs, stored procedures/utilities, and macro scripts. Build a call graph to ETL jobs and BI queries. - Extract the behavior contract: inputs/outputs, typing/NULL intent, side effects, error semantics, state assumptions, and performance constraints. - Choose Snowflake target form per asset:
  • Snowflake SQL UDF for pure expressions - Snowflake JavaScript UDF for complex string/regex/object handling - Snowflake stored procedure (SQL/JS) for multi-statement control flow and dynamic SQL - Set-based refactor where procedural loops can be eliminated - Rewrite dynamic SQL safely using bindings and explicit identifier rules. - Validate with a harness: golden inputs/outputs, branch and failure-mode tests, and side-effect assertions (audit/control writes).

Coverage

Supported constructs

Representative Redshift procedural constructs we commonly migrate to Snowflake routines (exact coverage depends on your estate).

SourceTargetNotes
Redshift scalar UDFsSnowflake SQL UDFsPure expressions mapped with explicit casts and NULL behavior.
Procedure-like ETL utilitiesSnowflake stored procedures (SQL/JS)Control flow rewritten; state and side effects modeled explicitly.
Dynamic SQL generationEXECUTE IMMEDIATE with bindingsNormalize identifier rules; reduce drift and injection risk.
Control tables for restartabilityApplied-window/batch tracking + idempotency markersRetries/backfills become safe and auditable.
Timestamp/timezone helpersExplicit timestamp intent (NTZ/LTZ/TZ) and conversionsPrevents boundary-day drift in reporting and SCD logic.
Row-by-row procedural transformsSet-based SQL refactorsAvoid cost and reliability cliffs in Snowflake.

Compare

How workload changes

TopicRedshiftSnowflakeNotes
Where logic livesUDFs + ETL utilities embedded across jobsCentralized routines (UDFs/procedures) with explicit contractsMigration consolidates and stabilizes reusable logic.
Typing and coercionImplicit casts often toleratedExplicit casts required for stable outputsValidation focuses on mixed-type branches and join keys.
Operational behaviorReruns/retries often encoded in ETL conventionsIdempotency and side effects must be explicitHarness proves behavior under retries/backfills.
Cost postureCluster utilization and WLMWarehouse credits + bounded scansRefactors reduce long-running procedural work and credit spikes.

Examples

Examples

Illustrative patterns for moving Redshift procedural logic into Snowflake routines. Adjust schemas, types, and identifiers to match your environment.

01_sql_udf_snowflake.sql
-- Snowflake SQL UDF example
CREATE OR REPLACE FUNCTION UTIL.SAFE_DIV(n NUMBER(38,6), d NUMBER(38,6))
RETURNS NUMBER(38,6)
AS
$$
  IFF(d IS NULL OR d = 0, NULL, n / d)
$$;
02_js_udf_snowflake.sql
-- Snowflake JavaScript UDF example
CREATE OR REPLACE FUNCTION UTIL.NORMALIZE_CODE(x STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
  if (x === null) return null;
  return x.trim().toUpperCase();
$$;
03_proc_dynamic_sql_snowflake.sql
-- Snowflake stored procedure (SQL) with dynamic SQL + bindings
CREATE OR REPLACE PROCEDURE UTIL.REFRESH_WINDOW(start_d DATE, end_d DATE)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  sql_cmd STRING;
BEGIN
  sql_cmd := 'DELETE FROM MART.FACT_ORDERS WHERE EVENT_DATE BETWEEN ? AND ?';
  EXECUTE IMMEDIATE :sql_cmd USING (start_d, end_d);
  RETURN 'OK';
END;
$$;
04_harness_example.sql
-- Minimal harness pattern: compare routine outputs to golden expectations
WITH golden AS (
  SELECT 10::NUMBER AS n, 2::NUMBER AS d, 5::NUMBER AS expected UNION ALL
  SELECT 10, 0, NULL
)
SELECT
  n, d,
  UTIL.SAFE_DIV(n, d) AS got,
  expected,
  IFF(got IS NULL AND expected IS NULL, 'PASS', IFF(got = expected, 'PASS', 'FAIL')) AS verdict
FROM golden;

Workload Assessment

Migrate Redshift logic with a test harness

We inventory your UDFs and procedural utilities, migrate a representative subset into Snowflake routines, and deliver a harness that proves parity—including side effects and rerun behavior.

Book assessment

Avoid

Common pitfalls

  • Procedural side effects ignored: audit/control writes disappear and reruns become unsafe.
  • Mixed-type branches: CASE/IF returns mixed types; Snowflake needs explicit casts.
  • NULL semantics drift: null-safe equality and type coercion differ; match logic changes.
  • Dynamic SQL injection risk: concatenation without bindings/escaping causes drift and risk.
  • Row-by-row loops: procedural loops should be refactored into set-based SQL to avoid cost cliffs.
  • No harness: without replayable tests, parity becomes a debate at cutover.

Proof

Validation approach

  • Compile + interface checks: each routine deploys; signatures match the contract (args/return types).
  • Golden tests: curated input sets validate outputs, including NULL-heavy and boundary cases.
  • Branch + failure-mode coverage: expected failures (invalid inputs, missing rows) are tested.
  • Side-effect verification: assert expected writes to audit/control tables and idempotency under retries/backfills.
  • Integration replay: run routines within representative pipelines and compare downstream KPIs/aggregates.
  • Performance gate: confirm no hidden row-by-row scans; set-based refactors validated with credit/runtime baselines.

Execution

Migration steps

A sequence that keeps behavior explicit, testable, and safe to cut over.

  1. 01

    Inventory procedural assets and build the call graph

    Collect UDFs and procedural utilities, map call sites across ETL and BI, and identify side effects (audit/control writes) and state assumptions.

  2. 02

    Define the behavior contract

    For each asset, specify inputs/outputs, typing/NULL intent, expected errors, side effects, restart semantics, and performance expectations. Choose the target form (UDF/procedure/refactor).

  3. 03

    Convert logic with safety patterns

    Rewrite casts and NULL behavior explicitly, migrate dynamic SQL using bindings, and refactor row-by-row patterns into set-based SQL where feasible.

  4. 04

    Model side effects and restartability

    Implement audit/control writes and idempotency markers so reruns/backfills are safe and outcomes are measurable.

  5. 05

    Build a validation harness and cut over

    Create golden inputs, edge cohorts, and failure-mode tests. Validate outputs and side effects deterministically, then cut over behind gates with rollback-ready criteria.

FAQ

Frequently asked questions

Do we have to rewrite everything as Snowflake stored procedures? +

Not necessarily. Many UDFs can become SQL UDFs, and some procedural utilities should be refactored into set-based SQL. We choose the target form per asset to minimize risk and cost.

What happens to side effects and control tables? +

They must be recreated explicitly. We model audit/control writes as explicit steps and prove idempotency under retries/backfills with simulations.

How do you prove parity for procedures and functions? +

We build a replayable harness with golden inputs/outputs, branch and failure-mode coverage, and side-effect assertions. Integration replay validates downstream KPIs before cutover.

Will performance change after migration? +

It can. Row-by-row procedural loops are the biggest risk. We refactor to set-based SQL where possible and gate performance with credit/runtime baselines.

Migration Acceleration

Cut over routines with proof-backed sign-off

Get a conversion plan, review markers for ambiguous intent, and validation artifacts so procedural logic cutover is gated by evidence and rollback criteria.

Book assessment
Book assessment