Migrate Stored Procedures to dbt Without Changing Numbers

Your reports are fed by opaque stored procedures that only two people understand. You want the same numbers, but in dbt so the logic lives in version control, tests run on every change, and refactors don’t risk production. Here’s the plan: inventory dependencies, rewrite mutable steps as declarative models, handle temp tables and loops, pick the right materializations, preserve transaction semantics where they matter, and validate with audit tables and parallel runs. We’ll also call out which procedures you should not force into dbt. This is a concrete playbook you can run in-house; if you want a partner to accelerate the work or steady your cutover, see our dbt Cloud migration and dbt repo performance services.

Inventory the procedural surface area and dependencies

Start by defining the blast radius. A stored procedure is database-resident code that executes imperative SQL/logic, sometimes calling other procedures and functions. Problems with stored procedures: they’re hard to test, often hide DDL and side effects, resist code refactoring, and rarely document data lineage. Stored procedures aren’t evil—just often the wrong tool for analytics.

Build a catalog for each procedure: inputs (tables, files), outputs (tables, views), side effects (GRANTs, DELETEs), schedule, owner, runtime, and downstream dashboards. Expect nested calls; expand the call tree before you change anything. On Snowflake, a quick first pass looks like:

-- Procedures and owners
select catalog_name, schema_name, procedure_name, arguments, created, owner
from information_schema.procedures;

-- Recent queries that invoked procedures (heuristic)
select user_name, query_text, start_time
from snowflake.account_usage.query_history
where query_text ilike '%CALL %';

Extract DDL for each procedure and parse references. Regex is imperfect—use it to seed a manual review. Tag anything that writes outside analytics schemas. Create a migration sheet with a status column and a dbt model target for each output. If you’re migrating from Teradata or Oracle, note proprietary SQL you must translate. This up-front map reduces surprises later.

Decompose mutable steps into declarative dbt models

Stored procedures mix concerns: staging, business logic, and load steps with temp tables and loops. In dbt, split these into modular models wired by refs. Example rewrite:

-- Old (simplified)
BEGIN
  CREATE TEMP TABLE t AS
    SELECT o.*, c.segment FROM raw.orders o
    JOIN raw.customers c ON o.customer_id = c.id;

  DELETE FROM analytics.orders_final WHERE load_date = :run_date;
  INSERT INTO analytics.orders_final
    SELECT *, :run_date as load_date FROM t;
END;

-- models/stg_orders.sql
select o.*, c.segment
from {{ source('raw','orders') }} o
join {{ source('raw','customers') }} c on o.customer_id = c.id

-- models/orders_final.sql
{{
  config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='sync_all_columns'
  )
}}
select *, {{ var('run_date', dateutil.today()) }} as load_date
from {{ ref('stg_orders') }}
{% if is_incremental() %}
  where order_updated_at > (select coalesce(max(order_updated_at), '1900-01-01') from {{ this }})
{% endif %}

-- models/schema.yml
version: 2
models:
  - name: stg_orders
    tests:
      - unique:
          column_name: order_id
      - not_null:
          column_name: order_id
  - name: orders_final
    tests:
      - relationships:
          to: ref('stg_orders')
          field: order_id
sources:
  - name: raw
    tables:
      - name: orders
      - name: customers

Temp tables become models (ephemeral for short-lived CTEs). Loops that run the same query over partitions become one set-based query; generate SQL at compile time with a macro if you need dynamic predicates. Jinja runs at compile time, not in the warehouse—great for SQL generation, not for per-row logic. Use dbt Core or dbt Cloud for orchestration; keep Python or Airflow for orchestration-level branching. See our patterns in dbt macros: Jinja patterns worth stealing.

Choose materializations and preserve the right semantics

Materialization choice carries most of the “don’t change the numbers” risk. Default to views for light transformations, tables for stable aggregates, and incremental for large fact tables. Ephemeral models inline as CTEs for performance. A quick mapping:

Scenario	Materialization	Notes
Small dims, reused widely	table	Fast, stable snapshot of logic
Heavy join, read once downstream	ephemeral	Compile into consumer to save storage
Large facts with upserts	incremental	Use MERGE with unique keys
Exploratory	view	Zero-copy, easy to iterate

Transaction semantics differ. You can’t wrap multiple models in a single database transaction in dbt. To approximate “build then swap” for critical tables, build to a shadow object and swap atomically:

-- As a post-hook on a model (adapter-specific)
ALTER TABLE {{ this }}__new SWAP WITH {{ this }};
DROP TABLE IF EXISTS {{ this }}__old;

Prefer set-based MERGE over delete+insert. Use pre-/post-hooks for grants. Keep DDL-heavy steps procedural. For deeper trade-offs, see our guides on dbt materializations and incremental models. This is standard workflow best practices for a cloud data warehouse; dbt Labs and the dbt community share the same direction.

Validate with audit tables and a strangler-style cutover

Run the old procedure and new dbt path in parallel until numbers match for multiple cycles. Write dbt outputs to a shadow schema and compare to the legacy target with audit models. Create a durable audit table:

create table if not exists analytics_audit.reconciliation (
  entity string, as_of_date date,
  row_count_legacy number, row_count_dbt number,
  sum_amount_legacy number, sum_amount_dbt number,
  diff_count number, sample_mismatch_variant variant,
  checked_at timestamp_ltz default current_timestamp()
);

-- models/audit_orders.sql
select
  'orders' as entity,
  current_date() as as_of_date,
  (select count(*) from legacy.orders_final) as row_count_legacy,
  (select count(*) from {{ ref('orders_final') }}) as row_count_dbt,
  (select sum(amount) from legacy.orders_final) as sum_amount_legacy,
  (select sum(amount) from {{ ref('orders_final') }}) as sum_amount_dbt,
  (select count(*) from (
     select * from legacy.orders_final
     minus select * from {{ ref('orders_final') }}
  )) as diff_count,
  null as sample_mismatch_variant

Gate cutover on zero diffs for agreed windows and stable runtime. For cross-warehouse checks (e.g., Oracle vs BigQuery), mirror both data sets into one warehouse, or run two dbt projects and compare with a small Python job:

# Pseudocode: fetch counts and sums, write to audit
for table in tables:
    l = count_sum(conn_legacy, table)
    n = count_sum(conn_new, table)
    upsert_audit(table, l, n)

Automate in CI so every PR runs sample validations; see dbt CI/CD that catches problems and our data quality testing strategy. This strangler playbook yields successful migrations without a risky big-bang.

What should stay procedural? And fast answers to common questions

Keep these as stored procs (or orchestrated Python) rather than forcing stored procedures to dbt:

• Operations requiring multi-statement transactions across objects (true two-phase semantics).
• Heavy DDL, grants, file I/O, or external API calls.
• Highly dynamic SQL that can’t be resolved at compile time.
• Row-by-row procedural logic with side effects. Use a function or procedure explicitly.

Quick Q&A

• Can I pass params into a dbt model like a function? Use vars: and environment variables; for imperative tasks, use dbt run-operation macros.
• Why not just rewrite into macros? A macro (Jinja) generates SQL at compile time; it doesn’t process data. Use it to template queries, not to replace a transformation engine.
• How does dbt code differ? dbt models are declarative DAG nodes; stored procedures are imperative scripts. The dbt project documents lineage, tests, and owners.
• Snowflake scripting only? You can keep those procedures and call them via hooks, or replace with set-based SQL models when feasible.
• Connect to Oracle and BigQuery at once? Not in one run. Use two profiles or projects and compare outputs externally.
• Procedures that call other procedures? Flatten the call chain into multiple models and orchestrate; or keep them procedural if side effects dominate.
• Why dbt? Version control, testing, documentation, modular design, and a strong dbt community and dbt Developer Hub. It aligns with modern analytics methodology in cloud computing warehouses.

If you’re standing up a new dbt project, start small: a single data model with sources, tests, and docs. When you’re ready to cut over, lean on our dbt migration guide and consider Business Health Reporting to keep the pipeline tied to real KPIs.

Your next action: pick one high-impact procedure, build the inventory entry, sketch its model DAG, and stand up parallel runs with audits. If you want hands-on help, we’ll bring patterns proven in production—start here: contact Vertex.