DBT macro for repetitive task

Question

I am a beginner in DBT. I have a requirement where I have created an Incremental model like below. I need to execute the same Incremental model logic statements for different systems.

There are 3 variables or parameters that I need to pass. i.e. for each run the ATTRIBUTE_NAME, VIEW_NAME, SYSTEM_NAME will need to be passed. For the next run, all the 3 parameters would be different.

However, for a particular SYSTEM_NAME, the VIEW_NAME and ATTRIBUTE_NAME are fixed.

Please help me to execute the dbt run using a macro for this requirement and pass the different system names and their corresponding view names and attribute names. Objective is to use single dbt run statement and execute this model for all ATTRIBUTE_NAME, VIEW_NAME, SYSTEM_NAME.

For now, I have defined variable and execute each run separately for each systems like below in CLI

e.g.

dbt run --vars '{"VIEW_NAME": CCC, "SYSTEM_NAME": BBBB, "ATTRIBUTE_NAME": AAAA}' -m incremental_modelname

dbt run --vars '{"VIEW_NAME": DDD, "SYSTEM_NAME": FFF, "ATTRIBUTE_NAME": HHH}' -m incremental_modelname

dbt run --vars '{"VIEW_NAME": EEE, "SYSTEM_NAME": GGG, "ATTRIBUTE_NAME": III}' -m incremental_modelname

Re-usuable Incremental model:

{{ 
    config(
        materialized='incremental', 
        transient=false, 
        unique_key='composite_key',  
        post_hook="insert into table (col1, col2, col3)
                        select 
                            '{{ var('ATTRIBUTE_NAME') }}',
                            col2, 
                            col3 
                        from {{ this }}  a
                        join table b on a=b
                        where b.SYSTEM_NAME='{{ var('SYSTEM_NAME') }}';
                         commit;"
    ) 
}}


with name1 AS (
    select 
        *
    from {{ var('VIEW_NAME') }}
),

select 
    *
from name1

{% if is_incremental() %}

where (select timestamp_column from {{ var('VIEW_NAME') }}) > 
    (select max(timestamp_column) from {{ this }} where SYSTEM_NAME='{{ var("SYSTEM_NAME") }}')
                             
{% endif %}

user_6556438 · Answer

The easiest way would be to:

Create a model(or even a seed) that holds the system name, view name and attribute name.

Within your code, add a for loop

 {% set query %}
 select system_name, view_name, attribute_name from model_name
 {% endset %}

 {% set results = run_query(query) %}

 {% for result in results %}

     /*
     Put your query here but reference the columns needed 
     results.columns[0].values()  = system_name
     results.columns[1].values()  = view_name
     results.columns[2].values()  = attribute_name
     */

DBT macro for repetitive task

1 Answers