Convert Netezza NZPLSQL to Snowflake Scripting with a mapping table and the six behaviors that change results

CREATE OR REPLACE PROCEDURE
  post_batch(INTEGER)
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
  b ALIAS FOR $1;
BEGIN
  DELETE FROM ledger
    WHERE batch_id = b;
  INSERT INTO ledger
    SELECT * FROM staging
    WHERE batch_id = b;
  RETURN ROW_COUNT;
END;
END_PROC;

CREATE OR REPLACE PROCEDURE
  post_batch(b INTEGER)
RETURNS INTEGER
LANGUAGE SQL AS
$$
BEGIN
  BEGIN TRANSACTION;
  DELETE FROM ledger
    WHERE batch_id = :b;
  INSERT INTO ledger
    SELECT * FROM staging
    WHERE batch_id = :b;
  COMMIT;
  RETURN SQLROWCOUNT;
EXCEPTION WHEN OTHER THEN
  ROLLBACK;
  RAISE;
END;
$$;

How do I convert Netezza stored procedures to Snowflake?

Inventory first, convert second, test third. Pull every procedure's source from the catalog, along with the nzsql shell scripts that call them, and sort them into three lists: procedures that return a REFTABLE, procedures with BEGIN AUTOCOMMIT ON blocks, and procedures that do arithmetic on NUMERIC or INTERVAL values. Those lists are where the manual work sits.

Then decide who does the first draft. SnowConvert will not, because Snowflake's own platform table lists Netezza code conversion as "Tables, views" with no AI code conversion, while the Redshift row beside it includes stored procedures and functions. phData publishes a Netezza to Snowflake approach built on its SQLMorph translator, which flags the nzsql client commands it cannot map, and services partners such as Intricity convert procedures as part of an engagement. If your estate is a few dozen procedures, a careful engineer with the table below is often faster than a procurement cycle. The data half of the project, where the tools are even thinner, is compared on our Netezza to Snowflake migration tools page.

Netezza NZPLSQL to Snowflake Scripting mapping table

Eighteen constructs that turn up in almost every Netezza code base, with the Snowflake Scripting each one becomes and the note a reviewer should check.

Six NZPLSQL behaviors that convert cleanly and return a different answer

Each of these produces valid Snowflake Scripting. None raises an error in testing unless the test data happens to hit it, and they are listed in the order they tend to surface after go-live.

Transaction scope. IBM's NZPLSQL reference says the BEGIN and END keywords "are used only for grouping; they do not start or end a transaction", and that statements inside a BEGIN AUTOCOMMIT ON block each run as a singleton. Everything else runs in the transaction of the call, so a procedure that deletes a batch and fails while reinserting it leaves the ledger as it was. Snowflake commits each DML statement on its own when AUTOCOMMIT is at its default and no transaction is open. Translated line for line, the same procedure deletes the batch, fails, and leaves it deleted. The figure above shows the fix: an explicit transaction, and a handler that rolls back and re-raises.

REFTABLE results. A Netezza procedure declared RETURNS REFTABLE(tbl) fills a temporary table named by REFTABLENAME, and IBM notes that the referenced table "must exist at the time that the stored procedure is created" and that a query calling it cannot add a WHERE clause. Snowflake's version declares RETURNS TABLE(...) and ends with RETURN TABLE(res) over a RESULTSET. The procedure converts cleanly. The callers do not: BI extracts and scripts that read rows from the call need to use the result directly or through RESULT_SCAN, and where the procedure is really a parameterized query, a view or a table function is the better target.

Division scale. IBM's support guidance gives the scale of a division result as the larger of 6 and the dividend's scale plus the divisor's precision plus one. For two NUMERIC(10,2) values that is 13 places. Snowflake's arithmetic reference adds six digits to the dividend's scale up to a ceiling of 12 and rounds rather than truncates, which gives 8. A cost allocation that divides, multiplies and sums in a loop drifts by fractions of a cent per row, and a reconciliation that compares the final figures to Netezza's at full scale reports mismatches by the thousand. Cast the operands to the scale the business rule needs before dividing, and compare results at that scale.

Trailing spaces. IBM documents that CHAR and NCHAR values are padded with spaces and that Netezza ignores trailing spaces on character comparisons. Snowflake's text reference says its CHAR is not space-padded. A procedure that tests IF status_code = 'A' against a value that arrived as 'A ' from an unload file took the branch on Netezza and skips it in Snowflake. Trim on load, or wrap the comparison in RTRIM until the data is clean.

INTERVAL variables. SnowConvert's Netezza type reference maps TIMESPAN, the Netezza interval, to VARCHAR with a conversion warning. Variables declared as intervals and added to timestamps then need DATEADD with an explicit unit. Store durations as a NUMBER of microseconds, which matches Netezza's resolution, and convert at the edges. Snowflake introduced interval data types as a preview in November 2025, so test them before relying on them in production code.

RAISE NOTICE. Netezza prints notices to the nzsql session, and older shops parse that output in shell scripts to decide whether a nightly step succeeded. Snowflake has no notice channel. SYSTEM$LOG writes to an event table that someone has to create and query, so the wrapper script that grepped for "batch complete" now sees nothing and assumes failure, or worse, success. Write status to an audit table the scheduler reads.

Can SnowConvert convert NZPLSQL procedures?

Not according to Snowflake's documentation today. The SnowConvert platform table lists Netezza as generally available for tables and views only, with no source connection, data migration or AI code conversion. Its Netezza references cover data types and CREATE TABLE, where DISTRIBUTE ON is removed and ORGANIZE ON is marked translation pending. Plan the procedures as a rewrite or a separate purchase.

What replaces Netezza stored procedures that run on a schedule?

Usually a Snowflake task that calls the rewritten procedure, which moves the schedule out of cron and into the warehouse. Each task run wakes a warehouse and bills credits, so a job that ran every five minutes on a paid-for appliance now has a running cost, and the first month's bill is often the surprise. Put a real-time alert on the cloud spend behind the new tasks before you raise their frequency, and size the warehouse for the job rather than reusing the one the analysts query.

How do I test a Netezza to Snowflake procedure conversion?

Run the same inputs through both and compare outputs, not row counts. Capture real calls on Netezza, replay them in Snowflake against the same data, and diff the rows returned and the rows changed. Include a call that fails halfway, so the transaction rewrite is exercised, a padded CHAR value, and a division whose result has more than eight decimal places.

That replay only works if Snowflake holds the same data as Netezza on the day you test, and it has to keep holding it for the months the rewrite takes. That is a data sync rather than a code conversion, and it is what our plans do: declare the columns once with CHAR trimmed and INTERVAL as a number, then sync incrementally on a timestamp or key with per-record logs, from $49 a month. The closest relatives of this guide are converting Informix SPL to PostgreSQL and converting Oracle PL/SQL to PostgreSQL, and the Snowflake side of the cutover, including merge cadence and what it costs in credits, is in Db2 to Snowflake replication tools and CDC cost.