DRY principles: How to write efficient SQL
It’s important to find the balance between reusable coding blocks that you refer to and including the full code.
Notes from this dbt blog post as part of the “Study for the dbt Analytics Engineering Certification with me” series. Access to the full Study Guide.
Why write DRY code?
The DRY principle represents:
A move from:
Repetitive patterns
Duplicate code
Duplicate logic
To:
Modular and referenceable code
It’s important to find the balance between reusable coding blocks that you refer to and including the full code. Too many references can make your code and logic hard to understand. While too many code lines can make it equally incomprehensible.
A good way to gauge when it’s time to make a piece of code reusable is when you find that you have to write it for the third time: The Rule of Three.
You also need to make sure you have good documentation to aid users in understanding your code and logic.
Reusable codes can be useful for the calculation of KPIs that will be used throughout the business. This ensures everyone is coming from the same definition and looking at the same value.
Useful tools to make your code DRY
Common Table Expressions (CTEs)
Break up complex coding with CTEs that can be referenced throughout the code. For example, when you need to reaggregate data so it’s joined to the table. Adding the reaggregation as a CTE makes it easier to debug and understand your code.
View materialisations
Let’s say this reaggregation extrapolates a single model and needs to be used across the project. Then, a good idea is to add it to a View and refer to it when needed.
dbt macros and packages
These not only help you to create re-usable blocks of code, but often they also save you the hassle of writing the code itself, with the added ability to customise tests.