Handling Private dbt Packages on a dbt Project
Efficiently managing dbt macros across multiple projects using Jinja logic
When working with dbt, you can use Jinja to simplify the development of your SQL model. That gives you flexibility, since you can add logics that define, during dbt compilation, how your SQL code will be built.
Aside from having Jinja on specific SQL files, you can also develop macros: functions that execute Jinja code and can be called on your SQL models. You can also overwrite some functions that overwrite alias, schema or database of your models.
In this article, we will look at one possible problem when you advance on using Jinja on your project and how you can use Jinja also to solve it.
If you work with multiple dbt projects, and you start to develop your own Jinja macros, you can notice that some of your functions could be used in any of your projects. To avoid repeating code in every project, you can have a separate project, just to store those macros, and use it in your dbt projects as a dbt package. The new repository doesn’t need to have any model in it. Just the macro files and the project.yml file. The project.yml file can be as simple as:
name: 'my_dbt_macros'
version: '0.1.0'
require-dbt-version: [">=1.x.0"]
config-version: 2
target-path: "target"
clean-targets: ["target", "dbt_modules", "dbt_packages"]
macro-paths: ["macros"]
log-path: "logs"It doesn’t even have a profile specified, cause that project doesn’t need to be executed, only imported by other projects.
Having that project saved as one repository, you could simply import that project into all your dbt projects, like you would do with any other git-based dbt package, adding the git link to the packages.yml file:
packages:
- git: "https://github.com/[your-user-or-company-name]/my_dbt_macros.git"
revision: main
warn-unpinned: falseIn the example above, we use the properties revision (instead of version), specifying the branch we want to use, and warn-unpinned as false, to avoid a warning that would remind you that the branch can change and affect your project (since the other project/repo is yours, you should know about those changes, and test them before updating that branch).
Then, when calling a macro in your dbt projects, you have now to reference the project before the function name:
{{ my_dbt_macros.my_function(arguments) }}That solution is simple when we are dealing with a public repo, you can just run dbt deps and have the project downloaded. For a private repo, things can be a little more complex.
If you run your project locally, you can just use the ssh git link in the packages.yml file, instead of the HTTPS. Now, when running a dbt deps command, you would use your user permissions to clone that repo into your packages folder (you may already do that to have access to other private repos).
packages:
- git: "git@github.com:[your-user-or-company-name]/my_dbt_macros.git"
revision: main
warn-unpinned: falseHowever, when we are deploying in production, things can be different. If you have a production environment that you can control and connect a terminal to it, you can do the same strategy. But for more professional production environments, you don’t have that choice.
With DBT Cloud, you cannot generate a SSH key, which you would need to add to your macros repo to allow access to it. If you use Fivetran transformations, one dbt environment has only one SSH key associated, but that key has to be used to the dbt project repo that contains your models and you cannot add the same key to the macros repo.
Another way of connecting to a project repo is to use the HTTPS token method. With that, you can generate a Personal Access Token in your Git account (which will be associated to a user and will have an expiration date) and use that token in the git link of your packages.yml file:
packages:
- git: "https://{{env_var('DBT_ENV_SECRET_GIT_CREDENTIAL')}}@github.com/[your-user-or-company-name]/my_dbt_macros.git"
revision: main
warn-unpinned: falseWith that, the dbt deps command will search for an environment variable called DBT_ENV_SECRET_GIT_CREDENTIAL and use it as the authentication token to connect into your project.
Environment variables can be added to DBT Cloud or Fivetran transformation environments. But what happens when you run dbt deps locally from one of your dbt projects?
“Env var required but not provided: ‘DBT_ENV_SECRET_GIT_CREDENTIAL’” (I am repeating the message in the screenshot, cause that’s the initial problem that led me to the solution — maybe you have the same)
That’s where Jinja comes to hand again. Jinja can be used not only for the development of your models, but also in some of dbt configuration files: dbt_project.yml, profiles.yml and packages.yml.
That means we can use a simple Jinja if/else statement to make a flexible git link in the packages.yml:
packages:
- git: "{% if env_var('DBT_ENV_SECRET_GIT_CREDENTIAL', '') != '' %}https://{{env_var('DBT_ENV_SECRET_GIT_CREDENTIAL', '')}}@github.com/[your-user-or-company-name]/my_dbt_macros.git{% else %}git@github.com:[your-user-or-company-name]/my_dbt_macros.git{% endif %}"
revision: main
warn-unpinned: falseThe env_var function above can have a default value as the second argument, which is blank. That way, the env_var function always returns something, avoiding the error of “env var not provided”.
When running in a production environment that has that environment variable, the jinja if/else will return the HTTPS token link, using the token to authenticate to the repository.
When running locally, since you don’t have that environment variable, the normal SSH link will be returned, without token, using your user permissions.
You can then run dbt deps locally or having the same command being executed from your production environment without any authentication problem.
Conclusion
The possibility of using Jinja not only on dbt models but also in configuration files allows us to avoid inefficient solutions to handle environment settings.
In our case, instead of using Jinja we could have added an environment variable with the same name to the developer computer, containing the git token. But whenever a new developer needs to have access to the project, it would need to receive that token and add it as an environment variable too.
Another option would be to change the git link in the packages file when running locally. It would work, but also it’s not the most elegant way, because you would have to make sure you’re not committing the modified packages file as part of your changes, otherwise your production job could fail, trying to use a normal SSH link.
Knowing the option shown, one single line can do the trick.
