The short version
To convert a stored procedure to dbt, split it at every DML statement and turn each one into a SELECT. An INSERT ... SELECT becomes a model, an UPDATE becomes a column expression, a DELETE becomes a WHERE that keeps the other rows, and a MERGE becomes an incremental model with a unique_key. Hard deletes become a soft-delete flag filtered out downstream. Then run EXCEPT both ways between the old table and the new model: zero rows means parity.
Below, a PL/pgSQL procedure on PostgreSQL 16, rebuilt with dbt-core 1.11.7 and dbt-postgres 1.10.0. Every output is pasted from the run.
The procedure I started with
A nightly job. A loader appends order changes to raw_orders; the procedure takes everything loaded since the last watermark, keeps the newest version of each order, cleans it, drops test orders, and upserts the rest into orders, removing cancelled ones.
CREATE OR REPLACE PROCEDURE refresh_orders()
LANGUAGE plpgsql
AS $$
DECLARE
wm timestamptz;
n int;
BEGIN
SELECT last_loaded_at INTO wm FROM etl_watermark;
-- 1. INSERT: newest version of each order changed since the last run
TRUNCATE stg_orders;
INSERT INTO stg_orders (order_id, customer_id, status, amount, updated_at, loaded_at)
SELECT DISTINCT ON (order_id)
order_id, customer_id, status, amount, updated_at, loaded_at
FROM raw_orders
WHERE loaded_at > wm
ORDER BY order_id, updated_at DESC;
GET DIAGNOSTICS n = ROW_COUNT;
RAISE NOTICE 'staged % rows', n;
-- 2. UPDATE: clean the status, look up the email
UPDATE stg_orders s
SET status = lower(trim(s.status)),
customer_email = lower(c.email)
FROM raw_customers c
WHERE c.customer_id = s.customer_id;
-- 3. DELETE: test orders never reach the target
DELETE FROM stg_orders WHERE status = 'test';
GET DIAGNOSTICS n = ROW_COUNT;
RAISE NOTICE 'deleted % test rows', n;
-- 4. MERGE: upsert into the target, cancelled orders are removed
MERGE INTO orders o
USING stg_orders s ON o.order_id = s.order_id
WHEN MATCHED AND s.status = 'cancelled' THEN
DELETE
WHEN MATCHED THEN
UPDATE SET customer_id = s.customer_id, customer_email = s.customer_email,
status = s.status, amount = s.amount,
updated_at = s.updated_at, loaded_at = s.loaded_at
WHEN NOT MATCHED AND s.status <> 'cancelled' THEN
INSERT VALUES (s.order_id, s.customer_id, s.customer_email,
s.status, s.amount, s.updated_at, s.loaded_at);
GET DIAGNOSTICS n = ROW_COUNT;
RAISE NOTICE 'merged % rows', n;
UPDATE etl_watermark
SET last_loaded_at = (SELECT coalesce(max(loaded_at), wm) FROM raw_orders);
END;
$$;
PostgreSQL's MERGE can conditionally insert, update or delete in one statement, and a WHEN MATCHED condition can refer to both source and target columns, which is how the cancelled branch picks its rows.
I loaded two batches into a scratch schema. Batch one: six raw rows, two versions of order 101, a TEST order, mixed-case statuses. Batch two pays 102, cancels 104, adds 106, and adds 107 already cancelled. The Docker CLI hung that day, so instead of psql I sent the SQL through a short psycopg2 script; the NOTICE lines are what Postgres raised.
The first CALL staged 5 rows, deleted 1 test row and merged 4. The second:
NOTICE: staged 4 rows
NOTICE: deleted 0 test rows
NOTICE: merged 3 rows
CALL
order_id | customer_id | customer_email | status | amount | updated_at | loaded_at
101 | 1 | ann@example.com | paid | 120.00 | 2026-10-01 11:00:00+00:00 | 2026-10-01 12:00:00+00:00
102 | 2 | bob@example.com | paid | 35.50 | 2026-10-02 09:00:00+00:00 | 2026-10-02 12:00:00+00:00
105 | 2 | bob@example.com | new | 15.00 | 2026-10-01 11:20:00+00:00 | 2026-10-01 12:00:00+00:00
106 | 1 | ann@example.com | new | 42.00 | 2026-10-02 10:00:00+00:00 | 2026-10-02 12:00:00+00:00
(4 rows)
How to convert a stored procedure to dbt, statement by statement
dbt's own guide to migrating from stored procedures gives the mapping, and it held up line for line:
| In the procedure | In dbt |
|---|---|
INSERT INTO t SELECT ... |
a model whose body is the SELECT |
UPDATE t SET col = expr |
the same SELECT, with expr as the column |
DELETE FROM t WHERE x |
a WHERE not x, or a soft-delete flag |
MERGE |
an incremental model with a unique_key |
| watermark table | is_incremental() and {{ this }} |
For an UPDATE the guide says to keep every column and use the computation on the right side of the equals sign as the value. For a DELETE it is blunt: dbt will never destroy data, so use soft deletes. Hard-coded table names in FROM it calls one of the most serious mistakes new users make, because ref and source are how dbt orders the work.
Steps 1 to 3 of the procedure collapse into one view, one CTE or clause per statement:
-- models/stg_orders.sql: steps 1 to 3 of the procedure
{{ config(materialized='view') }}
with latest as (
-- the INSERT: newest version of each order
select distinct on (order_id)
order_id, customer_id, status, amount, updated_at, loaded_at
from {{ source('raw', 'raw_orders') }}
order by order_id, updated_at desc
),
cleaned as (
-- the UPDATE: the right-hand side of each SET becomes a column expression
select l.order_id,
l.customer_id,
lower(c.email) as customer_email,
lower(trim(l.status)) as status,
l.amount,
l.updated_at,
l.loaded_at
from latest l
left join {{ source('raw', 'raw_customers') }} c using (customer_id)
)
-- the DELETE: a WHERE that keeps the rows the procedure did not delete
select * from cleaned
where status <> 'test'
A trap: in the UPDATE ... FROM, an order with no matching customer is not updated and keeps a null email. A left join keeps the order; an inner join would drop it. Such an order also keeps its raw, uncleaned status in the procedure, which my model does not copy. My test data had no such order, so the parity check below could not catch it: put one in yours.
The MERGE becomes an incremental model
The guide's advice for MERGE is to handle it as you would an INSERT or an UPDATE first, then convert to the incremental materialization. It also says dbt has no native DELETE as a match action, so WHEN MATCHED THEN DELETE turns into a soft-delete flag:
-- models/orders.sql: step 4 of the procedure, the MERGE
{{ config(
materialized='incremental',
unique_key='order_id'
) }}
select order_id,
customer_id,
customer_email,
status,
amount,
updated_at,
loaded_at,
status = 'cancelled' as is_deleted -- WHEN MATCHED ... THEN DELETE, as a soft delete
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where loaded_at > (select max(loaded_at) from {{ this }})
{% endif %}
A third model, orders_current, is a view over {{ ref('orders') }} with where not is_deleted and the procedure's seven columns: the follow-on filter the guide asks for.
The watermark table is gone. is_incremental() is true only when the table exists, the model is incremental and --full-refresh was not passed; {{ this }} reads the model's own latest loaded_at; and with a unique_key a new row for an existing key replaces the old one.
On Postgres that replacement is not a MERGE by default. The dbt-postgres configs page lists delete+insert as the default when unique_key is defined. The compiled SQL in target/run/ confirmed it:
delete from "postgres"."convert_a_stored_procedure_to_dbt_models_dbt"."orders" as DBT_INTERNAL_DEST
where (order_id) in (
select distinct order_id
from "orders__dbt_tmp160040549812" as DBT_INTERNAL_SOURCE
);
insert into "postgres"."convert_a_stored_procedure_to_dbt_models_dbt"."orders" ...
Set incremental_strategy='merge' for a real MERGE; the trade-offs are in dbt incremental: merge vs delete+insert.
The mistake I made first: same schema, same table names
My first dbt run used the procedure's schema. dbt replaced the procedure's stg_orders table with its view, then took the procedure's orders table for an existing incremental model and inserted into it:
2 of 3 OK created sql incremental model convert_a_stored_procedure_to_dbt_models.orders [INSERT 0 0 in 0.09s]
The next CALL failed:
psycopg2.errors.WrongObjectType: "stg_orders" is not a table
CONTEXT: SQL statement "TRUNCATE stg_orders"
PL/pgSQL function refresh_orders() line 9 at SQL statement
Nothing was lost only because that insert selected zero rows. Keep dbt in its own schema while the procedure still runs. dbt appends a custom schema to the target schema as <target_schema>_<custom_schema>, so this in dbt_project.yml is enough:
models:
sp2dbt:
+schema: dbt
The runs
I dropped the schema, rebuilt it, and replayed the batches with dbt run after each CALL. Batch one built the table (SELECT 4). After batch two it took the incremental path (INSERT 0 4): the four changed orders, the two cancelled ones as soft deletes.
order_id | status | amount | is_deleted
101 | paid | 120.00 | False
102 | paid | 35.50 | False
104 | cancelled | 980.10 | True
105 | new | 15.00 | False
106 | new | 42.00 | False
107 | cancelled | 10.00 | True
(6 rows)
Order 107 never reached the procedure's table, because its MERGE skipped cancelled orders on insert. In dbt it sits there flagged, and orders_current hides it.
Then dbt test, with unique and not_null on both models and accepted_values on the status. dbt data tests are select queries that return failing rows, so a pass means zero rows came back:
20:00:43 Done. PASS=5 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=5
Did the dbt models match the stored procedure?
Tests check rules; parity needs a comparison. EXCEPT returns the rows in the first query that are not in the second, so run it both ways:
SELECT 'only in procedure' AS side, * FROM (
SELECT * FROM convert_a_stored_procedure_to_dbt_models.orders
EXCEPT
SELECT * FROM convert_a_stored_procedure_to_dbt_models_dbt.orders_current
) a
UNION ALL
SELECT 'only in dbt', * FROM (
SELECT * FROM convert_a_stored_procedure_to_dbt_models_dbt.orders_current
EXCEPT
SELECT * FROM convert_a_stored_procedure_to_dbt_models.orders
) b;
side | order_id | customer_id | customer_email | status | amount | updated_at | loaded_at
(0 rows)
I ran it again after dbt run --full-refresh, which drops the table and rebuilds it for all time (SELECT 6), and got (0 rows) again: the incremental path and the full rebuild both match the procedure. EXCEPT removes duplicates, so on a table without a primary key use EXCEPT ALL or compare count(*) too. On a real migration I run this check after every load while both pipelines run side by side, and switch the procedure off only after a run of clean days; proving the new pipeline matches the old one goes further.
What does not map one to one
- Staging and temp tables become CTEs or views, as
stg_ordersdid here. Procedures with many of them: temp tables in stored procedures, the dbt way. - The watermark row becomes
max(loaded_at)from{{ this }}. If late rows can arrive with an olderloaded_at, use a lookback window instead of a strict>. - The hard delete is now a flag, so the dbt table only grows. Downstream reads
orders_current, neverorders. - Loops and cursors need a different approach: T-SQL cursors and loops, rewritten for dbt.