Custom Macro vs Installed Package
As you advance in your Data Engineering journey, you will encounter complex problems that standard SQL cannot solve.
When you hit one of these roadblocks, you have two choices:
- Write a Custom Jinja Macro yourself in your
macros/folder. - Install a dbt Package from the open-source community that solves the problem.
How do you decide which approach to take?
Topics Covered
- The Case for Installed Packages
- The Case for Custom Macros
- Internal Company Packages
- Decision Framework
1. The Case for Installed Packages
If you are trying to do something that every data engineer in the world has to do, you should use a package.
For example, generating a surrogate key (hashing multiple columns together to create a unique ID) is a universally required task. Writing the MD5() hash logic yourself across multiple SQL dialects is annoying and prone to errors.
Instead, you should just use the dbt_utils.generate_surrogate_key macro from the dbt-utils package.
Pros of Packages:
- Zero Maintenance: The open-source community updates the code for you. If a new version of Snowflake is released and breaks the macro, dbt Labs will fix the package; you don't have to touch your code.
- Battle-Tested: Packages like
dbt-expectationshave been tested on thousands of databases worldwide. Your custom macro has only been tested by you. - Speed: Typing
dbt depstakes 5 seconds. Writing a custom macro takes 5 hours.
2. The Case for Custom Macros
If you are trying to do something that is unique to your specific company, you must write a custom macro.
For example, imagine you work for an Indian ride-sharing company. The pricing team has an incredibly complex, proprietary formula for calculating "Surge Pricing" based on traffic in Bengaluru, weather conditions, and time of day.
No open-source package will ever contain a macro called calculate_bengaluru_surge_pricing.
Pros of Custom Macros:
- Complete Control: You own the business logic. If the CEO decides to change the surge formula tomorrow, you can instantly update your macro without waiting for an open-source maintainer to approve a Pull Request.
- Security: You do not have to rely on third-party code executing inside your secure Snowflake environment.
3. The Hybrid: Internal Company Packages
There is a third option that enterprise companies use: Internal Packages.
If your Indian ride-sharing company has 5 different dbt projects (one for Marketing, one for Finance, one for Operations), you do not want to copy-paste your custom calculate_bengaluru_surge_pricing macro into all 5 projects. If the formula changes, you would have to manually update 5 different codebases!
Instead, you create a private GitHub repository called beems-dbt-macros. You put your custom macro in there.
Then, in your 5 dbt projects, you import it via packages.yml using a Git URL!
# packages.yml
packages:
# Import an internal, private package directly from GitHub!
- git: "https://github.com/beems-techne/beems-dbt-macros.git"
revision: main
4. Decision Framework
When faced with a complex logic problem, ask yourself these questions:
- Is this a generic SQL utility? (e.g., Pivoting data, generating keys, getting a date dimension).
- Action: Search the dbt Hub. Install an open-source package.
- Is this highly specific business logic for my team? (e.g., Calculating proprietary profit margins).
- Action: Write a Custom Macro in your project's
macros/folder.
- Action: Write a Custom Macro in your project's
- Is this highly specific business logic, but multiple teams need it?
- Action: Write the macro, but publish it as an Internal Git Package so other teams can import it via
dbt deps.
- Action: Write the macro, but publish it as an Internal Git Package so other teams can import it via
Summary
- Don't reinvent the wheel. Use Installed Packages (like
dbt-utilsordbt-expectations) for universal data engineering tasks. - Use Custom Macros for proprietary business logic.
- You can create your own Internal Packages by hosting your macros in a private Git repository and importing them across multiple dbt projects.