Change dbt SQL wih macros

[Vytlksn]

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
    where 
        {{ 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?

[brunoszdl]

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

vars:
  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"

[brunoszdl]

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.

[Vytlksn]

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