Pack up the stored procedures and move to modernize

It’s time to move your data transformations from stored procedures to a more modern approach with dbt™ and Fivetran
August 17, 2023

We live in a world that’s generating and collecting seemingly infinite amounts of data. That same high-volume of data, in its raw form, requires transformation — or cleansing, modeling and manipulating to prepare it to inform insights. 

Most transformations are performed via SQL, the standard language all analysts use to query and manipulate data — but the method and tools utilized are imperative. 

While stored procedures have provided analysts and engineers with an ability to create and re-use SQL queries, they were never intended to act as the foundation for today’s data transformation needs. 

What is a stored procedure?

Stored procedures consist of one or many SQL statements that are used repeatedly. These statements are then stored on your database so that you can “call” or “execute” them without re-writing the underlying logic each and every time. 

Stored procedures provide great time-saving benefits as you reuse the same, consistent logic to achieve your procedural needs. Additionally, as they are compiled and stored on the database they are often very performant over one-off queries. 

In the eyes of a database administrator (DBA), this is perfectly sufficient for accomplishing tasks like restricting access to sensitive data or increasing database performance. However, there are inefficiencies to using stored procedures in your analytics data pipelines — especially as your team and organization scale. 

Where stored procedures fail

The increase in demand for clear, concise and consistent data has put some serious stress on the functionality of stored procedures. While they might suffice to handle the volume of data, they were never designed for the needs of modern data analytics teams. In fact, relying on stored procedures can put companies in serious risks of vast technical debt.

Documentation and visibility

Stored procedures lack built-in functionality for documentation. Any addition or modification is untracked and applied immediately without accompanying documentation on what was changed or why. 

If your company is lucky, you might find some comments in the code itself explaining the code. If your company is really lucky, you might find a timestamp alongside those comments. Some companies even try to keep tabs on their stored procedures by saving a copy in a shared drive — which is unsurprisingly difficult to maintain.

But, typically, the majority of knowledge of the stored procedure is held by the author of the query. If that person leaves the company, which is occurring at record high rates recently, they take that knowledge with them. This leaves pipelines vulnerable to breakage if there’s not an official knowledge transfer to update and maintain the logic.

Logging and debugging

Similar to their lack of documentation, stored procedures also lack a native logging functionality. Stored procedures - when called or executed - simply respond with whatever was written in the underlying code, meaning that debugging is as easy or hard as the author made it. 

As the complexity of a stored procedure increases, so does the debugging. Thus, the author potentially evolves into a major liability if business critical reports are built on stored procedures that only one or few people built or understand.

Flexibility and scalability

Stored procedures are best used for things that rarely or never change. Have you heard the infamous quote, “that’s the way we have always done it”? This is often the mantra adopted by companies reliant on legacy transformation infrastructure.

It’s inevitable that data and reporting will change over time as businesses evolve and scale. Stored procedures slow growth, especially for fast-moving businesses that need flexible transformations and reporting. Why let your data transformation process limit your data strategy? 

There are many data-driven companies who rely on stored procedures to power their critical business decisions. And there are just as many that want to modernize their transformations technology and move off of their reliance on stored procedures. The barrier to this modernization though is often resourcing and timing. 

While these are legitimate concerns, there are straightforward ways to chip away at the issue at hand.

Better data transformations with dbt™ and Fivetran 

The answer to this problem is actually simple - to address it head on. There is no better time than the present to slash technical debt, especially when it comes to modernizing your transformation pipelines.

Likely the best in-class tool for addressing analytical tech debt is dbt™ by dbt Labs. They’ve built an amazing product that lets teams quickly and collaboratively deploy analytics code following software engineering best practices like modularity, portability, CI/CD and documentation. 

A dbt project provides full documentation, like descriptions of your model including the business use case and value, plus markdown files for more in-depth documentation. These utilities help populate a user-friendly documentation site your company can use to better understand your transformation holistically. 

Once your project is written and documented, logging and debugging become easier as well. Instead of sifting through hundreds of lines of code, a dbt project with digestible, modular steps provides a straightforward approach to debugging as the steps are logged independently. 

But dbt doesn’t operate alone, and is most impactful when paired with modern data movement technology. That’s why we built our native integration with dbt: Fivetran Transformations for dbt Core™*. This integration allows companies to fully automate and scale their ELT process while leveraging best practices along the way. 

You can synchronize your dbt transformations in unison with your data load in your destination, reducing data latency and computational costs — while taking advantage of all of dbt’s features. Fivetran then visualizes your end-to-end pipeline with our data lineage graphs, further increasing your documentation and your ability to debug. And, we provide a full library of pre-built data models that are fully documented and solve your most common data use cases

All of these advantages further decrease your reliance on stored procedures, reduce your tech debt — and ultimately modernize your data processes.

If you still need to use a stored procedure, Fivetran can help

While dbt was not designed to call or execute stored procedures, it’s still possible to do so through dbt hooks or macros. Hooks can be used to execute raw SQL commands in the warehouse (pre- or post-model execution) which includes calling stored procedures (e.g. +post-hook: "call cool_stored_procedure();"). 

You can then utilize Fivetran Transformations for dbt Core’s Integrated Scheduling feature to kick off your stored procedures directly after relevant connectors have finished syncing. The combination of these features allow a seamless and simple approach to migrating from legacy procedures into a modern transformations architecture.

The best time to modernize is now 

Fivetran and dbt offer you the best solution possible to reduce vulnerabilities in your pipeline. 

If you need support to get started, we even offer professional services for integrating dbt Core and Fivetran to completely migrate off stored procedures and into a dbt project where your transformations are documented and productionalized. 

[CTA_MODULE]

Start for free

Join the thousands of companies using Fivetran to centralize and transform their data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Data insights
Data insights

Pack up the stored procedures and move to modernize

Pack up the stored procedures and move to modernize

August 17, 2023
August 17, 2023
Pack up the stored procedures and move to modernize
It’s time to move your data transformations from stored procedures to a more modern approach with dbt™ and Fivetran

We live in a world that’s generating and collecting seemingly infinite amounts of data. That same high-volume of data, in its raw form, requires transformation — or cleansing, modeling and manipulating to prepare it to inform insights. 

Most transformations are performed via SQL, the standard language all analysts use to query and manipulate data — but the method and tools utilized are imperative. 

While stored procedures have provided analysts and engineers with an ability to create and re-use SQL queries, they were never intended to act as the foundation for today’s data transformation needs. 

What is a stored procedure?

Stored procedures consist of one or many SQL statements that are used repeatedly. These statements are then stored on your database so that you can “call” or “execute” them without re-writing the underlying logic each and every time. 

Stored procedures provide great time-saving benefits as you reuse the same, consistent logic to achieve your procedural needs. Additionally, as they are compiled and stored on the database they are often very performant over one-off queries. 

In the eyes of a database administrator (DBA), this is perfectly sufficient for accomplishing tasks like restricting access to sensitive data or increasing database performance. However, there are inefficiencies to using stored procedures in your analytics data pipelines — especially as your team and organization scale. 

Where stored procedures fail

The increase in demand for clear, concise and consistent data has put some serious stress on the functionality of stored procedures. While they might suffice to handle the volume of data, they were never designed for the needs of modern data analytics teams. In fact, relying on stored procedures can put companies in serious risks of vast technical debt.

Documentation and visibility

Stored procedures lack built-in functionality for documentation. Any addition or modification is untracked and applied immediately without accompanying documentation on what was changed or why. 

If your company is lucky, you might find some comments in the code itself explaining the code. If your company is really lucky, you might find a timestamp alongside those comments. Some companies even try to keep tabs on their stored procedures by saving a copy in a shared drive — which is unsurprisingly difficult to maintain.

But, typically, the majority of knowledge of the stored procedure is held by the author of the query. If that person leaves the company, which is occurring at record high rates recently, they take that knowledge with them. This leaves pipelines vulnerable to breakage if there’s not an official knowledge transfer to update and maintain the logic.

Logging and debugging

Similar to their lack of documentation, stored procedures also lack a native logging functionality. Stored procedures - when called or executed - simply respond with whatever was written in the underlying code, meaning that debugging is as easy or hard as the author made it. 

As the complexity of a stored procedure increases, so does the debugging. Thus, the author potentially evolves into a major liability if business critical reports are built on stored procedures that only one or few people built or understand.

Flexibility and scalability

Stored procedures are best used for things that rarely or never change. Have you heard the infamous quote, “that’s the way we have always done it”? This is often the mantra adopted by companies reliant on legacy transformation infrastructure.

It’s inevitable that data and reporting will change over time as businesses evolve and scale. Stored procedures slow growth, especially for fast-moving businesses that need flexible transformations and reporting. Why let your data transformation process limit your data strategy? 

There are many data-driven companies who rely on stored procedures to power their critical business decisions. And there are just as many that want to modernize their transformations technology and move off of their reliance on stored procedures. The barrier to this modernization though is often resourcing and timing. 

While these are legitimate concerns, there are straightforward ways to chip away at the issue at hand.

Better data transformations with dbt™ and Fivetran 

The answer to this problem is actually simple - to address it head on. There is no better time than the present to slash technical debt, especially when it comes to modernizing your transformation pipelines.

Likely the best in-class tool for addressing analytical tech debt is dbt™ by dbt Labs. They’ve built an amazing product that lets teams quickly and collaboratively deploy analytics code following software engineering best practices like modularity, portability, CI/CD and documentation. 

A dbt project provides full documentation, like descriptions of your model including the business use case and value, plus markdown files for more in-depth documentation. These utilities help populate a user-friendly documentation site your company can use to better understand your transformation holistically. 

Once your project is written and documented, logging and debugging become easier as well. Instead of sifting through hundreds of lines of code, a dbt project with digestible, modular steps provides a straightforward approach to debugging as the steps are logged independently. 

But dbt doesn’t operate alone, and is most impactful when paired with modern data movement technology. That’s why we built our native integration with dbt: Fivetran Transformations for dbt Core™*. This integration allows companies to fully automate and scale their ELT process while leveraging best practices along the way. 

You can synchronize your dbt transformations in unison with your data load in your destination, reducing data latency and computational costs — while taking advantage of all of dbt’s features. Fivetran then visualizes your end-to-end pipeline with our data lineage graphs, further increasing your documentation and your ability to debug. And, we provide a full library of pre-built data models that are fully documented and solve your most common data use cases

All of these advantages further decrease your reliance on stored procedures, reduce your tech debt — and ultimately modernize your data processes.

If you still need to use a stored procedure, Fivetran can help

While dbt was not designed to call or execute stored procedures, it’s still possible to do so through dbt hooks or macros. Hooks can be used to execute raw SQL commands in the warehouse (pre- or post-model execution) which includes calling stored procedures (e.g. +post-hook: "call cool_stored_procedure();"). 

You can then utilize Fivetran Transformations for dbt Core’s Integrated Scheduling feature to kick off your stored procedures directly after relevant connectors have finished syncing. The combination of these features allow a seamless and simple approach to migrating from legacy procedures into a modern transformations architecture.

The best time to modernize is now 

Fivetran and dbt offer you the best solution possible to reduce vulnerabilities in your pipeline. 

If you need support to get started, we even offer professional services for integrating dbt Core and Fivetran to completely migrate off stored procedures and into a dbt project where your transformations are documented and productionalized. 

[CTA_MODULE]

Ready to move to modernize?
Get started for free

*dbt Core is a trademark of dbt Labs, Inc. All rights therein are reserved to dbt Labs, Inc. Fivetran Transformations is not a product or service of or endorsed by dbt Labs, Inc.

Related blog posts

How we execute dbt™ runs faster and cheaper
Data insights

How we execute dbt™ runs faster and cheaper

Read post
Wizard for dbt Core™ VSCode extension now available for Snowflake
Product

Wizard for dbt Core™ VSCode extension now available for Snowflake

Read post
How ChatGPT helps us write better dbt™ data models
Data insights

How ChatGPT helps us write better dbt™ data models

Read post
No items found.
Fivetran celebrates triple win with tech partner awards
Blog

Fivetran celebrates triple win with tech partner awards

Read post
Why we were named dbt Labs™  Technology Partner of the Year
Blog

Why we were named dbt Labs™ Technology Partner of the Year

Read post
Laggy insights? Level up with Fivetran at Coalesce 2023
Blog

Laggy insights? Level up with Fivetran at Coalesce 2023

Read post

Start for free

Join the thousands of companies using Fivetran to centralize and transform their data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.