Search code examples
sqljinja2amazon-redshiftdbt

dbt + jinja Cannot cast column value to int

I need Dynamic loop in dbt based on a column of the row

select id,loop_count,
{% set row_loop_cnt  %}
loop_count
{% endset %}
{% for i in range(loop_count) %}
    //creating a list 
{% endfor %}
created_list as column_name
from table_name

I am getting 'str object cannot be interpreted as an integer' error

I tried multiple way of casting like

loop_count::int 'redshift'

loop_count | int 'Jinja'

But no luck could you please help me here

Solution

  • Macros are compiled (templated) before the query is run. That means that the data in your database doesn't run through the jinja templater. When you {% set row_loop_cnt = "loop_count"%} you're just passing a string with the value loop_count into jinja, not the data from the field with that name.

    From your query, I assume that the table_name table contains a field called loop_count, and that field's data includes an integer that you would like to use to repeat a value in another column.

    In most databases, you can do this with SQL, and not involve jinja at all. It's possible to use the run_query macro to pull data into the jinja context, but this is slow and error-prone, and not really applicable in a situation where each row of your data wants to reference a different value.

    Assuming the simplest possible implementation of // creating a list, I would write this query as:

    select id, loop_count, repeat(value_col || ',', loop_count) as created_list
from table_name