Change dbt SQL wih macros

The problem I’m having

I have dbt macro with SQ model and need to take var from dbt_project.yml and use it in CLI.
Macro example

{% macro model_with_filter(factor_sql_filter) %}

select  *
    from tableA
        {{ factor_sql_filter }}

{% endmacro %}

factor_sql_filter is var in YML:
"service_type in ('INTEREST_FEE', 'COMMISSION_FEE', 'LATE_FEE') and type != 'CREDIT_MEMO'"

The context of why I’m trying to do this

I don’t want duplicate models with one different row, so I am trying with one macro create multiple models.

What I’ve already tried

dbt run --select model_name --vars '{"factor_sql_filter": "factoring_sql_filter_ci"}'  
dbt run --select model_name --vars '{"factor_sql_filter": var("factoring_sql_filter_ci")}' 

got error: dbt: error: unrecognized arguments on different tries

Any ideas how to solve it?

I didn’t understand what you want to do. Do you want to create several models and each one of them will have this macro with a different filter?

If so, and your model is just a select star with a single filter, I would rather use the SQL code in the models, as it makes them more readable, and you don’t need to worry about vars.

But it is a personal preference, if you want do it your way, you can do it like this:

Your macro is ok.

In your model, you should call the macro this way:

{{ model_with_filter(var('factor_sql_filter')) }}

And you can either put the var in your dbt_project.yml

  factor_sql_filter: "service_type in ('INTEREST_FEE', 'COMMISSION_FEE', 'LATE_FEE') and type != 'CREDIT_MEMO'"

or in the CLI like

dbt run -s my_model --vars "factor_sql_filter: service_type in ('INTEREST_FEE', 'COMMISSION_FEE', 'LATE_FEE') and type != 'CREDIT_MEMO"

When you do something like

dbt run --select model_name --vars '{"factor_sql_filter": "factoring_sql_filter_ci"}'  
dbt run --select model_name --vars '{"factor_sql_filter": var("factoring_sql_filter_ci")}'

You are overwriting your project variables with the value you are passing in the CLI, you can’t call project variables from the CLI.

After some building, decided to build two different models for simplicity instead of creating two models from one with CLI.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.