If statement when a parameter in the macro is null in dbt

Question

I am new using DBT so maybe you guys can help me create this macro. I have a macro as the one you can see below:

{% macro finding_number(missing_arn, acquirer_id, min_date, max_date) %}

{% set query %}

  select 
       *
  from {{ ref('hola') }}
  where event_date::date between '{{min_date}}' and '{{max_date}}'
    and acquirer_id = {{acquirer_id}} 
    and acquirer_reference_number = '{{missing_arn}}'

{% endset %}

{% set results = run_query(query) %}

{%endmacro%}

What is the problem here? That there might be some cases where I don't have an acquirer_id or a date to populate in the parameters of the macro. Find an example below:

dbt run-operation finding_number --args '{missing_arn: xyz}'

So, if I don't have a value for these parameters and I try to run the macro, it gives me an error as I haven't assigned any values to these parameters ( acquirer_id, min_date and max_date ). I've seen this could be solve by using multiple If statements to "jump" through those conditions in the where statement inside the query if you haven't assigned anything to those parameters, but I don't know how to structure them. For this case, as we only have missing_arn, the if statement would need to "jump" over the condition of acquirer_id, min_date and max_date in the where statement, as we didn't assign any values to these two parameters in order to be able to run the macro.

Thanks!

Answer 1

How I would structure it to solve this quickly…

{% macro finding_number(missing_arn, acquirer_id, min_date, max_date) %}

{% set query %}

  select 
       *
  from {{ ref('hola') }}
  where acquirer_reference_number = '{{missing_arn}}'
    {{%- if min_date is not none and max_date is not none -%}} and event_date::date between '{{min_date}}' and '{{max_date}}' {%- endif -%}
    {{%- if acquirer_id is not none -%}} and acquirer_id = {{acquirer_id}} {%- endif -%}

{% endset %}

{% set results = run_query(query) %}

{%endmacro%}

I did not include an if statement for the missing_arn because you asked about a scenario in which you did have that, HOWEVER, you could apply the same logical pattern as such…

  where 1=1
    {{%- if missing_arn is not none -%}} and acquirer_reference_number = '{{missing_arn}}' {{%- endif -%}}

The where 1=1 allows it to still run if all three are missing or different combination of parameters are left out. Havent tested this, but give it a try.

Optional in case you have a min date but no max date or vice versa:

{{%- if min_date is not none -% }} and event_date::date >= '{{min_date}}' {%- endif -%}
{{%- if max_date is not none -% }} and event_date::date <= '{{max_date}}' {%- endif -%}