I used to repeat and paste SQL queries that have been nearly an identical, pondering that it was the one solution to work with SQL.
Luckily, I accepted a brand new place as an Analytics Engineer and found dbt, a tremendous software to construct knowledge fashions with dynamic queries and mannequin dependencies.
With dbt we might use macros and never rewrite the identical items of SQL twice. I quickly realized that although we tried to have reusable code inside our tasks, we frequently had an identical items of code in several tasks.
To keep away from code redundancies throughout tasks, I created my first dbt package deal. This package deal is now imported by a number of tasks that share some fashions and macros.
Right here is how one can shortly create your first dbt package deal and reuse code throughout tasks!
A dbt package deal is nothing greater than a daily dbt venture. Let’s create a package deal by calling dbt init. Select the title and configuration you need, it doesn’t actually matter as we aren’t going to make use of the venture alone.
We are going to name that venture the package deal and the venture the place the package deal is imported the primary venture.
Now, contained in the fashions/instance folder, let’s create a file referred to as hello_world.sql, with the next question:
{{config(
materialized = 'desk'
)}}SELECT "Hi there World!" AS discipline
pwd contained in the repository and replica absolutely the path to the dbt venture.
Open your primary venture, and edit packages.yml so as to add the next line:
- native: <absolute_path>
Then in your terminal, run:
dbt deps
dbt run -m instance
Examine the dataset laid out in your profile. You must now see a contemporary desk hello_world that appears like this:
Mission variables
That’s good, however you most likely don’t need the very same question in all your tasks. Excellent news, you need to use venture variables to customize your mannequin.
Let’s edit our question:
{{config(materialized = 'desk')}}SELECT "Hi there {{ var('first_name') }}!" AS discipline
Within the dbt_project.yml file of our primary venture, we are able to add a venture variable like this:
vars: first_name: Marie
Then as soon as your run dbt run -m instance, your question will run with the worth outlined for that variable!
Supply recordsdata
That is only a easy question with no FROM clause; in actual tasks, additionally, you will need to use supply recordsdata to question completely different tables.
Let’s create a mannequin hello_world_2 that makes use of a supply desk.
{{config(materialized = 'desk')}}SELECT *, 2 AS modelFROM {{ supply("hello_source", "hello_world")}}
Right here, we’re telling dbt to search for the desk hello_world contained in the supply hello_source.
However we now have to configure that supply in our venture. Let’s create a config file referred to as hiya.yml contained in the sources folder of our venture.
model: 2sources: - title: hello_sourceventure: "<your_project>" dataset: "<your_dataset>" tables: - title: hello_world
Now after we run our mannequin, here’s what we see:
Import macros
Importing fashions has some main downsides:
- If we’d like variables within the fashions, we now have to outline them within the venture file which may quickly be overloaded
- The kind of venture variables just isn’t preserved, so lists get transformed to strings
- It’s not straightforward to decide on which fashions to incorporate from the package deal
Macros clear up these points, although extra configuration must be achieved.
Contained in the macros folder of our package deal, let’s create a file referred to as hello_everyone.sql.
{% macro hello_everyone(names) -%}{{config(materialized = 'desk')}}SELECT{% for title in names -%}"Hi there {{title}}" AS greeting_{{title}},{%- endfor %}{%- endmacro %}
Now, let’s inform our primary venture to search for that macro by modifying the venture file:
macro-paths: ["macros", "dbt_packages/<package_name>/macros"]
And at last, let’s create a mannequin file inside fashions/instance to name our macro:
{% set names = ["nina", "chloe", "marie"] %}{{ hello_everyone(names) }}
Publish your package deal
To publish your package deal, you simply should push it to a git repository, then it is possible for you to to import it utilizing the next syntax:
packages:
- git: "https://github.com/<git_url>"
revision: <version_tag>
