Why moving from stored procedures to dbt drives trust, talent, and AI-readiness

This guest blog post is from Ryan Bennett, lead data engineer at phData.

As a strategic data leader, you focus on driving business value, building productive teams, instilling company-wide trust in data, and setting your organization’s technological direction (just to name a few). You focus on the “what” and the “why”, asking and answering broad questions like:

• “What are we trying to achieve? And why does it matter?”
• "Is our strategy aligned with the strategic goals of the organization?"
• "Are we driving actionable insights, or just generating reports?"
• "How do we quantify and measure the ROI of our work?"

Maybe your customer transaction data holds untapped signals that could unlock new growth opportunities. Or, perhaps your organization spends large amounts of time verifying untrusted data. The specifics on how to achieve your vision rest within the domain of your team. The foundation they build upon, though, can either accelerate your mission or tether you to the past.

One critical, but often overlooked, foundation is data transformation.

For decades, many organizations have relied on stored procedures: SQL code embedded in the database or even loose SQL scripts sitting on desktops to manage data transformations. While these approaches may work in the short term, they create hidden risks: siloed logic that only a few people understand, fragile pipelines that break under scale, and limited visibility for leaders who need to trust the data.

In this blog, we’ll explore the hidden ways that stored procedures hinder your strategic goals, and how migrating to a modern platform like dbt can unlock greater strategic value.

Building organizational trust with modern data engineering

Trust is critical in the world of data, and stored procedures make building organizational trust difficult. Often existing as complex database objects, these black boxes are understood only by the few technical staff who build and maintain them. Stored procedures often lack documentation, tests, or a full view of the data lineage.

This opacity makes debugging inefficient, and teams may spend days or weeks unravelling an issue. Beyond eroding trust with data consumers, these delays can have significant business costs. Additionally, without a clear understanding of downstream impacts or tests to give confidence, changes to stored procedures can introduce risk.

With built-in tools for documentation, testing, and version control, dbt empowers organizations to create trustworthy data products. Understanding the data lineage from a raw source to a curated dataset is transparent in dbt.

Setting technological direction

As a strategic data leader, you vet and choose platforms and tools for your organization’s future, and that future is increasingly linked with AI. Legacy platforms are often slow to adopt new capabilities or neglect them altogether. Stored procedures are not the language of the modern AI ecosystem.

Projects powered by dbt, with their rich metadata and structure, are fertile grounds for AI-assisted development. dbt Copilot allows developers to generate code, documentation, and tests accelerating your organization’s delivery of insights.

The dbt Semantic Layer can provide context to other AI tools, enabling capabilities like “Talk to Your Data”. Ever-growing training data from public dbt projects, coupled with the aforementioned metadata and structure, gives dbt an advantage in the modern AI landscape. This advantage will only increase as time passes.

Attracting and retaining talent with modern tools

The technologies and platforms you choose directly impact your organization’s ability to attract and retain talent. Data professionals desire modern tools and systems that align with software development best practices. Technology centered around stored procedures can be a red flag for potential candidates and a source of frustration for your existing team.

Additionally, the development of stored procedures can resemble the wild west, with differing code styles and a lack of consistency between developers. This can hinder collaboration and slow down the onboarding of new hires.

As an opinionated platform, dbt offers consistent styling and structure between projects. Once familiar with dbt, developers can jump into most projects and provide value quickly. With its standardized workflows, dbt reduces onboarding time for new hires by 30%, lowering ramp-up costs. As your team matures, upskilling SQL-savvy analysts to dbt is an easier proposition than training them on stored procedure development.

Driving business value with dbt

Ultimately, every strategic decision needs to drive business value. The inefficiencies of stored procedures are a tax on your organization’s ability to move quickly, generate insights, and build trust. From complex debugging, manual testing, and risky deployments, stored procedures slow your organization’s development cycle. This negatively impacts your ability to make data-driven decisions.

dbt provides value by streamlining the development process. With integrated tooling for documentation, tests, and AI-assisted development, dbt increases development efficiency and decreases time to actionable insights. Data products can be built, tested, and deployed with more confidence and speed. Teams have reported spending significantly less time debugging data issues after adopting dbt, thanks to its clear error messages and modular structure. With fewer outages and rework, dbt frees up to 20% more development capacity. This is a competitive advantage in today’s fast-paced world.

Choosing between legacy tools like stored procedures and modern platforms like dbt is a choice between the past and the future.

FAQ

What are the risks of using stored procedures for data transformation?

Stored procedures are difficult to maintain and scale. They are oftentimes black boxes filled with hidden, complex logic.

How does dbt support AI-driven data projects?

dbt empowers teams to build, test, and deploy data products with the same rigor as software engineering. This leads to cleaner, higher-quality data that produces better results for AI applications.