The Complete Guide to Converting Stored Procedures to dbt Models

Why Migrate from Stored Procedures to dbt?

Stored procedures have been the backbone of enterprise data transformations for decades. But as data teams scale, their limitations become painfully clear: they're stateful, opaque, hard to test, and nearly impossible to version-control. dbt (data build tool) solves these problems by treating SQL transformations as declarative, modular, testable code assembled into a directed acyclic graph (DAG).

Organizations that have completed this migration report dramatic improvements—one team saw uptime go from 65% to 99.9%. But the migration process can seem daunting. This guide provides a comprehensive, step-by-step playbook.

Step 1: Understand the Fundamental Differences

Stored procedures are imperative and stateful—they execute DML operations (INSERT, UPDATE, DELETE, MERGE) line by line, mutating tables in place. dbt is declarative—you write SELECT statements that describe what the final table should look like, and dbt handles the materialization.

Step 2: Map Your Data Flows

Before writing any dbt code, map out what your stored procedure actually does:

1. Identify raw source data — What tables does the procedure read from?

2. Trace the transformations — What joins, filters, aggregations, and business logic are applied?

3. Identify the outputs — What tables are written to?

This mapping will directly inform your dbt model structure.

Step 3: Convert DML Operations to dbt Models

Converting INSERTs

Original stored procedure:

dbt model (returned_orders.sql):

Sequential INSERTs into the same table become UNION ALL:

Converting UPDATEs

Original:

dbt model:

For tables with many columns, use the dbt_utils.star() macro:

Converting DELETEs

Original:

Approach 1 — Invert the logic:

Approach 2 — Soft-delete flag (recommended for auditability):

Converting MERGE / UPSERT Operations

This is the most complex conversion. Use dbt's incremental materialization with a merge strategy.

Original MERGE:

dbt incremental model:

Key concepts:

• is_incremental() returns false on first run (full refresh) and true on subsequent runs

• {{ this }} refers to the current model's existing table

• unique_key defines the primary key for matching records

Replacing Temp Tables

Stored procedures heavily use temporary tables. In dbt:

• Ephemeral models: Materialize as ephemeral — the SQL is injected as a CTE into downstream models without creating a physical table:

• CTEs within a model: For transformation steps that don't need reuse

• Views: For lightweight intermediate steps that multiple models reference

Step 4: Structure Your dbt Project in Layers

The most important architectural decision when migrating from stored procedures is decomposing monolithic logic into dbt's three-layer structure: Staging → Intermediate → Marts.

Staging Layer (models/staging/)

Purpose: One-to-one with source tables. Light transformations only — renaming, type casting, basic computations. This is the only place to use {{ source() }}.

Naming: stg_[source]__[entity]s.sql (double underscore, plural)

Materialization: Views

Example (stg_stripe__payments.sql):

What NOT to do in staging: Joins (except via base models) and aggregations.

Intermediate Layer (models/intermediate/)

Purpose: Purpose-built transformation steps — joining, pivoting, re-graining, filtering. These are the "molecules" built from staging "atoms."

Naming: int_[entity]s_[verb]s.sql (e.g., int_payments_pivoted_to_orders.sql)

Materialization: Ephemeral (default) or views with restricted access

Example (int_payments_pivoted_to_orders.sql):

Marts Layer (models/marts/)

Purpose: Business-defined entities consumed by end users and BI tools. Wide, denormalized, entity-grained.

Naming: Plain business concepts — orders.sql, customers.sql. Grouped by department (finance, marketing).

Materialization: Tables or incremental models

Example (customers.sql):

Step 5: Refactoring Legacy SQL — The CTE Method

When migrating a large stored procedure, follow this systematic process:

1. Migrate 1:1 into dbt

Copy the entire legacy SQL into a .sql file under /models. Make minimal modifications. Run dbt run to verify it works.

2. Replace table references with {{ source() }}

Define sources in a YAML file and replace hard-coded table names:

3. Organize into CTE groupings

Structure the model into four sections:

4. Extract CTEs into separate models

• Import CTEs → staging models

• Logical CTEs → intermediate models (if reusable) or keep as CTEs

• Final CTE → the mart model

Step 6: Add Tests and Documentation

One of dbt's biggest advantages over stored procedures is built-in testing. Define tests in YAML:

Source Freshness

Monitor whether source data is arriving on time:

Run dbt source freshness to check.

Step 7: Validate with audit_helper

The audit_helper package compares your new dbt model output against the original stored procedure output to ensure parity.

Installation

Run dbt deps to install.

Row-Level Comparison

Column-Level Comparison

Tips:

• Start with a few columns (e.g., 5), validate, then add more

• Exclude columns that differ due to environment (timezone differences, etc.)

• Alias columns to match between old and new queries

Step 8: Accelerate with Paradime's DinoAI

For teams looking to speed up the migration process, Paradime offers AI-powered tooling purpose-built for dbt development.

SQL-to-dbt Conversion

DinoAI can automatically convert legacy SQL into dbt models. Right-click any .sql file and select "Convert SQL to dbt model," or use the /sql_to_dbt command. DinoAI rewrites the query using {{ source() }} and {{ ref() }} macros, applies proper CTE structure, and follows your project conventions.

Agent Mode — Full Automation

DinoAI's Agent Mode goes further by directly creating and modifying files in your project:

• Generate staging, intermediate, and mart models from natural language prompts

• Create or update sources.yml with warehouse metadata

• Auto-generate documentation and tests grounded in actual data (it queries your warehouse to discover distinct values, null rates, and distributions)

• Debug failing models — type /fix @model_name and DinoAI identifies and resolves issues

Example prompts:

• Create a staging model for the customers table in my raw_data source

• /model Create a dbt model named int_payments_pivoted that joins payments with orders using order_id. Materialize as a view.

SQL Execution Tool

Available within Agent Mode, DinoAI can run SQL directly against your connected warehouse and reason over the results—profiling datasets, generating tests from real data, validating model output after dbt build, and debugging failing tests, all without leaving the IDE.

.dinorules — Enforce Standards at Scale

Define your team's coding standards in a .dinorules file (naming conventions, SQL style, CTE patterns, testing requirements), and DinoAI automatically enforces them on every operation. Rules are Git-tracked and team-collaborative.

Data Explorer

Paradime's Data Explorer lets you preview model results, view compiled SQL, and test individual CTEs or subqueries before running your entire model—critical for iterative validation during migration. The "Defer to Production" feature lets you preview changes against production-like upstream tables while iterating in development.

Scratchpad

A temporary, Git-ignored environment for drafting and testing SQL, dbt models, and Python snippets without impacting your main codebase—ideal for experimenting during migration.

Common Pitfalls and How to Avoid Them

Summary Checklist

• Map data flows in existing stored procedures

• Define sources in YAML with freshness checks

• Convert INSERT → SELECT, UPDATE → CASE WHEN, DELETE → WHERE filter, MERGE → incremental model

• Replace temp tables with ephemeral models or CTEs

• Structure into staging → intermediate → marts layers

• Add unique, not_null, accepted_values, and relationships tests

• Validate with audit_helper (compare old vs. new output)

• Document models and columns in YAML

• Set up CI/CD and scheduling (e.g., Paradime Bolt)

• Consider using AI tooling (DinoAI) to accelerate repetitive conversion tasks

Resources

• Migrating from Stored Procedures to dbt — dbt Developer Blog

• Migrate from DDL, DML, and Stored Procedures — dbt Developer Hub

• Refactoring Legacy SQL to dbt — Step-by-step walkthrough

• How We Structure Our dbt Projects — Official best practices

• audit_helper Package — Output validation

• dbt_utils Package — Utility macros

• Refactoring SQL for Modularity — Free course

• Paradime DinoAI — AI-powered dbt development

• DinoAI SQL-to-dbt Conversion — Automated conversion