Unusual UndefinedMacroException error

Help
, ,
1

I am trying to create histogram bins in dbt using jinja. This is the code I am using.

{% set sql_statement %}
    select  min(eir) as min_eir, floor((max(eir) - min(eir))/10) + 1 as bin_size from {{ ref('interest_rate_table') }}
{% endset %}

{% set query_result = dbt_utils.get_query_results_as_dict(sql_statement) %}

{% set min_eir = query_result['min_eir'][0] %}
{% set bin_size = query_result['bin_size'][0] %}

{% set eir_bucket = [] %}

{% for i in range(10) %}
    {% set eir_bucket = eir_bucket.append(min_eir + i*bin_size) %}
{% endfor %}

{{ log(eir_bucket, info=True) }}

select 1 as num

The above code returns dbt.exceptions.UndefinedMacroException.
Below is the error log.

dbt.exceptions.UndefinedMacroException: Compilation Error in model terms_dist (/my/file/dir)
  'bin_size' is undefined. This can happen when calling a macro that does not exist. Check for typos and/or install package dependencies with "dbt deps".

Now, I haven’t written the SQL yet. I want to build an array containing the historical bins, that I can use in my code. (edited)

2

Hi datachef,

Have you tried to output or log bin_size immediately after you set it (and temporarily remove subsequent code just to see the outcome)?

Cheers,
Tomaz