dbt Vs. Stored Procedures! Should You Migrate Your SQL to dbt?

It's 2am and a nightly stored procedure has stopped at step 14 of 20. Steps 1 to 13 already committed. Is it safe to just run it again? This is dbt vs stored procedures on the same data, run for real on Postgres. A stored procedure is a list of steps that change tables; a dbt model is a SELECT that describes what a table should contain, ordered by ref() and source(). We convert a 20-step procedure into six dbt models statement by statement, following dbt's migration guide: INSERT becomes a model, UPDATE a CASE, DELETE a filter, and MERGE an incremental model with a unique_key. Then a head-to-head on testing, lineage, recovery and code review, including what happens when you rerun the failed night (the procedure doubles a day's revenue, dbt retry doesn't). Finally, the two kinds of procedure dbt shouldn't replace, dbt's escape hatches, and the verdict. ⏱️ Chapters 0:00 - Step 14 of 20 failed at 2am 1:00 - What a stored procedure really is 2:10 - How a dbt model works: SELECT plus ref() 3:12 - Steps vs state: the real difference 4:23 - Converting INSERT, UPDATE, DELETE and MERGE 6:17 - Head-to-head: testing and lineage 7:11 - Recovery: rerunning the failed night 8:03 - Code review and CI 8:34 - When stored procedures still win 9:52 - dbt's escape hatches: hooks and run-operation 10:21 - The verdict: migrate or keep? 11:27 - Recap 12:00 - Honest limits 🔗 Links dbt: https://www.getdbt.com dbt guide, migrating from stored procedures: https://docs.getdbt.com/guides/migrate-from-stored-procedures dbt audit_helper: https://hub.getdbt.com/dbt-labs/audit_helper/latest/ tSQLt: https://tsqlt.org PostgreSQL: https://www.postgresql.org SQL Server SET XACT_ABORT: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql #dbt #dbtvsstoredprocedures #storedprocedures #SQL #SQLServer #Postgres #TSQL #datatransformation #ETL #ELT #incrementalmodels #dataengineering #analyticsengineering #datapipeline #dbtmigration