dbt vs Stored Procedures (3 key differences)

You can absolutely write your transformations as stored procedures instead of adopting a tool like dbt. The difference is not whether the SQL runs, it is everything around the SQL. A stored procedure is code saved and executed inside the database, so the running object and the source of truth are the same thing, which makes version control, testing and documentation manual chores. dbt is a separate project that lives outside the database, usually in a git repository, and compiles SQL before sending it over a connection to be run. That structure is what gives you branches, pull requests, environments, Jinja templating, built in tests and generated documentation. The three differences worth deciding on are core functionality, maintainability, and governance.

Difference 1: Core functionality

Start with what each one actually is, because the rest follows from that.

What a stored procedure is

A stored procedure is exactly what the name says: a stored sequence of commands. You write a script that inserts data, creates tables, updates tables or deletes records, and you save it in the database in a repeatable format.

At one of the first companies I worked for, we had a pile of procedures that ran every morning around 6 a.m. and refreshed every table so the data was ready for the day.

CREATE PROCEDURE dbo.load_daily_sales
AS
BEGIN
    TRUNCATE TABLE dbo.daily_sales;

    INSERT INTO dbo.daily_sales (order_date, customer_id, amount)
    SELECT order_date, customer_id, SUM(amount)
    FROM   dbo.orders
    GROUP BY order_date, customer_id;

    EXEC dbo.load_daily_sales_summary;
END

Procedures also nest. One procedure calls another, as in the last line above, and you schedule the top of the chain with SQL Agent jobs or an external tool like SSIS, Informatica or Airflow.

What a code based tool is

dbt is the one I use most, though others do similar work. The defining trait is that the project lives outside the database as a codebase of files and folders, usually version controlled on GitHub or GitLab.

The clearest way I can put it: a procedure has code in the database, dbt is an advanced compiler pointed at the database. dbt is not a database and it does not store your data.

It connects to your warehouse, generates a batch of SQL scripts from your project, and sends them over to be executed there.

-- models/marts/daily_sales.sql
{{ config(materialized='table') }}

select
    order_date,
    customer_id,
    sum(amount) as amount
from {{ ref('stg_orders') }}
group by 1, 2

Why the compile step matters

Because dbt compiles before it runs, it can be dynamic and modular in ways a saved script cannot. Set a configuration in one place and it applies to many objects at once.

That is the whole point of the tool. Things you would otherwise write out many times, and later update in many places, get managed centrally.

Jinja is the templating layer that makes this work. If you have written a lot of procedures you know how much repetition there is, and how awkward it gets generating complicated SQL by hand.

The nearest database equivalent is a user defined function, but that comparison undersells it. Jinja runs at compile time, so it can assemble SQL for edge cases rather than just execute reusable logic.

Running it from anywhere

dbt is a Python project you invoke from the command line, so it runs as a bash command anywhere you can type commands.

That gives you a lot of automation flexibility. You do not need a fully configured database environment just to execute the code.

Difference 2: Maintainability

Data and code get messy. The team grows, more people touch the logic, the query count climbs, and eventually it is hard to keep straight.

How you handle that is where code based tools pull ahead, because developer workflow is one of the main reasons dbt was built in the first place.

Change history inside a procedure

With a procedure, you create and run everything in the database. You are saving directly into the working code, and the working code often is production.

The usual substitute for version control is a comment block at the top of the file. Dates, names, and a short description of what changed, maintained by hand.

/* ---------------------------------------------
   2024-11-02  JS  Added refund exclusion
   2024-12-18  MK  Fixed duplicate customer rows
   2025-01-09  JS  Changed grain to order line
--------------------------------------------- */

It depends entirely on whether people remember to write it. And even when they do, it tells you something changed, not which lines changed.

Over a few years of edits and quick fixes, things get missed. Tracking down when a number started being wrong turns into archaeology.

What a version controlled project gives you instead

dbt was designed around the gap above. Instead of recording changes in a comment, you use the version control platform:

• Branches. Work on a change without touching what is running.
• Local testing and separate environments. Run your version before anyone else sees it.
• Pull requests. A reviewed, documented path for code to reach production, with the exact diff attached.

You get real visibility into who changed what, which a comment in the code never provides.

SQL files and YAML files

A dbt project splits into two kinds of files. SQL files hold the logic: select statements and the transformations themselves. YAML files hold configuration, structure and metadata.

Each file focuses on one thing, and you can impose a folder structure that makes the project navigable for whoever comes next.

Because it is a standalone Python based project, you can also add tooling around it: linters, automated code review, and project checks that run before anything ships.

Difference 3: Governance and quality

The last difference is how you keep quality from drifting, and it comes down to whether the controls are built in or left to individual discipline.

Testing a stored procedure

Procedures are objects in the database, so testing them is usually manual. You either check by hand or write another procedure or function to do the checking for you, and then you have to know how to run and organize those.

Maybe you keep a set of SQL scripts that confirm there are no duplicates or that a column is never empty. That works, as long as someone runs them.

Database constraints, with a caveat

The other option is leaning on the database itself: a not null constraint, or a unique constraint on a primary key.

Be careful here, because several modern cloud databases do not enforce all of these. Snowflake and BigQuery, for example, accept some constraint definitions without actually enforcing them.

If you are going the procedure route, confirm what your database really does rather than what the syntax suggests.

Documentation and tests in the project

In dbt both of these live in the project. Documentation is written alongside the models, so you are not switching between tools, and dbt can publish it as a browsable website.

That matters for transparency. In the past this information was buried in the code or sitting in a wiki somewhere nobody opens.

Tests are a few lines of YAML. Uniqueness, not null, relationships and accepted values come built in, and you can write your own and manage them in the same project.

version: 2

models:
  - name: daily_sales
    description: One row per order date and customer.
    columns:
      - name: customer_id
        description: Foreign key to dim_customers.
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id

None of this is new work. It is the same quality checking teams did manually with procedures, collected into a tool built for the job.

The tool is not the strategy

Worth saying plainly: dbt will not solve your problems just because you installed it.

Weak logic, poor naming conventions, missing structure and a haphazard approach survive the move. In some cases a code based tool makes the mess more confusing, because now it is spread across a project instead of sitting in one script.

Use it with intent or the benefits above stay theoretical. Be strategic about how it fits into your architecture, and it can meaningfully improve how your team works.

Related reading

• SQL vs dbt Models (and the value of CTEs)
• Start Using Jinja in dbt Macros (3 Examples)
• Why Data Teams Need Version Control
• Why Data Migrations Go Wrong (3 reasons)

Final takeaway

Most of the teams I work with are moving off procedures not because the SQL was bad, but because nobody could safely change it. The reason to pick a code based tool is the workflow around the code: branches, reviews, tests and documentation that hold up as the team grows.