Data projects are about more than just the end product or results. From start to finish, every step in the data lifecycle must be curated with efficiency, quality, scalability, and maintainability in mind. This is especially true for the middle stages, where data is prepared for reporting or analysis. Transformation processes that are slow, inefficient, or difficult to understand will be hard to maintain and lead to time-consuming data refreshes.
In data transformation, we often find ourselves writing the same code or logic over and over. This not only wastes time but also makes projects cluttered and less efficient. This is where the DRY principle - Don’t Repeat Yourself - comes in. By centralising repeated logic, we can streamline our work and make our projects cleaner and more manageable.
In Python, you use functions. In SQL, you use CTEs. And in dbt, you use macros to apply the DRY principle. This series, #MacroManage, is a collection of dbt speed tips designed to help you simplify your project by leveraging both custom macros and community-created packages. This first post will cover the basics: what macros are, how and when to create your own, and how to find and install community packages.
So, what exactly are macros? Think of them like functions in Python. Just as you can create a reusable block of code that you can call repeatedly with different inputs, a dbt macro is a block of Jinja* code you can reference throughout your project. Macros are defined in .sql files and typically stored in your macros’ directory, allowing you to centralise and reuse complex or repetitive logic in your models.
*if you’re unfamiliar with Jinja, read more about it in the dbt docs
Now that we know what macros are and how they can benefit our project, let's explore how to build our own macro from scratch using a common data transformation scenario.
Build your own macro
Step 1: Think through the logic
Imagine you have bank account data stored in cents, but your reports need to show dollar amounts. You find yourself repeatedly writing the same code - dividing the amount by 100 and rounding to two decimal places - across multiple SQL queries.
Step 2: Define the Macro
This is a perfect example of a problem a macro can solve. Instead of repeating this logic, you can define a macro called convert_cents_to_dollars. You'll first create a new file in your macros directory (e.g., macros/cents_to_dollars.sql) and define the macro. Think of this as creating a new function.
Step 3: Use the Macro in Your Models
Now, in any of your SQL models, instead of rewriting the conversion logic, you simply call the macro. By using the macro, your code is now more readable, and if you ever need to change the conversion logic (for example, to add more decimal places), you only have to update the macro file, and the change will be applied everywhere.
While creating your own custom macros gives you ultimate control, sometimes it's more efficient to leverage the work of others. Before you spend time building a macro from scratch, it's always a good idea to check if a dbt package with a pre-built solution already exists.
Install a dbt package
Just as Python developers can install pre-built libraries from PyPI, you can use dbt packages to leverage reusable code from the dbt community. A package is essentially a collection of macros, models, and other resources that solve a common data problem, such as generating surrogate keys or standardising data from a marketing source.
Instead of writing complex logic from scratch, you can browse the dbt Hub to find a package that fits your needs. Many packages are maintained by dbt Labs or the community, and they often come with documentation and tests, saving you significant development time.
Step 1: Add it to the yaml
Add a packages.yml file: In the root directory of your dbt project, create a file named packages.yml. This file lists all the packages your project depends on. You simply copy the package details (name and version) from the dbt Hub into this file.
Step 2: Install using dbt deps
Run dbt deps: After saving the packages.yml file, run the command dbt deps in your terminal. This command reads the file, downloads the packages from their source (typically GitHub), and installs them into your project's dbt_packages directory. Once installed, all the macros and models from the package are available for you to use in your project.
This first post has introduced the foundation of dbt macros - a powerful tool for keeping your code DRY. We've seen how to create simple macros to avoid repeating logic and how to leverage the vast library of community-created packages. In the next instalments of #MacroManage, we'll dive deeper into some of the most useful macros and packages to supercharge your dbt workflow. Stay tuned and happy macromanaging!