Stored procedures, macros & UDFs for Teradata → Snowflake
Re-home procedural business logic—Teradata stored procedures, macros, and UDFs—into Snowflake routines with explicit behavior contracts and a replayable harness so retries and backfills don’t change outcomes.
- Input
- Teradata Stored procedure / UDF migration logic
- Output
- Snowflake equivalent (validated)
- Common pitfalls
- Macro side effects ignored: macros often implement control-table logic; losing it breaks restartability.
- 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.
Why this breaks
Teradata estates often embed the real system in procedural assets: stored procedures, macros that generate SQL, and UDFs used pervasively in reporting and ETL. During migration, teams convert tables and queries first—then discover macros and procedures were enforcing business rules, control-table semantics, and operational side effects. 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
- Macro-generated 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 if ported directly
A successful migration extracts the behavior contract and validates it with a replayable harness, not ad-hoc spot checks.
How conversion works
- Inventory & classify Teradata procedural assets: stored procedures, macros, UDFs (scalar/aggregate), and their call sites across ETL and BI.
- 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 Teradata row-by-row logic can be eliminated
- Rewrite dynamic SQL safely using bindings and explicit identifier rules; standardize timestamp intent (NTZ/LTZ/TZ).
- Validate with a harness: golden inputs/outputs, branch and failure-mode tests, and side-effect assertions (audit/control writes).
Supported constructs
Representative Teradata procedural constructs we commonly migrate to Snowflake routines (exact coverage depends on your estate).
| Source | Target | Notes |
|---|---|---|
| Teradata SQL UDFs | Snowflake SQL UDFs | Pure expressions mapped with explicit casts and NULL behavior. |
| Teradata macros | Snowflake stored procedures + parameterized SQL | Macro SQL generation replaced with bound dynamic SQL where needed. |
| Teradata stored procedures | Snowflake stored procedures (SQL/JS) | Control flow rewritten; state and side effects modeled explicitly. |
| Dynamic SQL (macro/proc-generated) | EXECUTE IMMEDIATE with bindings | Normalize identifier rules; reduce drift and injection risk. |
| Control tables for restartability | Applied-window/batch tracking + idempotency markers | Retries/backfills become safe and auditable. |
| Row-by-row procedural transforms | Set-based SQL refactors | Avoid cost and reliability cliffs in Snowflake. |
How workload changes
| Topic | Teradata | Snowflake |
|---|---|---|
| Where logic lives | Macros/procs/UDFs embedded across jobs | Centralized routines with explicit contracts |
| Typing and coercion | Teradata coercion often tolerated | Explicit casts required for stable outputs |
| Operational behavior | Restartability encoded in macros/control tables | Idempotency and side effects must be explicit |
| Performance posture | Procedural loops sometimes tolerated | Set-based refactors usually required for predictable credits |
Examples
Illustrative patterns for moving Teradata procedural logic into Snowflake routines. Adjust schemas, types, and identifiers to match your environment.
-- 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)
$$;Common pitfalls
- Macro side effects ignored: macros often implement control-table logic; losing it breaks restartability.
- 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.
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.
Migration steps
- 01
Inventory procedural assets and build the call graph
Collect macros, stored procedures, and UDFs; map call sites across ETL and BI; identify side effects (audit/control writes) and state assumptions.
- 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/proc/refactor).
- 03
Convert with safety patterns
Rewrite casts and NULL behavior explicitly, migrate macro SQL into bound dynamic SQL, and refactor row-by-row patterns into set-based SQL where feasible.
- 04
Model side effects and restartability
Implement audit/control writes and idempotency markers so reruns/backfills are safe and outcomes are measurable.
- 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.
We inventory your macros/procs/UDFs, migrate a representative subset into Snowflake routines, and deliver a harness that proves parity—including side effects and rerun behavior.
Get a conversion plan, review markers for ambiguous intent, and validation artifacts so procedural logic cutover is gated by evidence and rollback criteria.