Search code examples
arraysjinja2dbt

Problems when iterating thru an array of string returned by a Jinja2 macro called in DBT

I encounter a problem when I'm trying to iterate thru an array of String created by a Jinja2 macro. The macro seems to correctly create the array of String (representing dates) and I'm able to see the correct value in DBT. Unfortunately, when I iterate the array returned, it seems that the iteration is done on characters and not strings.

The call of the macro is here:

 {%- set recalculationDate = get_range_of_dates(true) -%}

The "select" in DBT is the following:

 select {{ recalculationDate }} from  {{ source('Test', 'Table_1') }}

The results are the following:

select 
    ['2023-02-07', '2023-02-08', '2023-02-09', '2023-02-10', '2023-02-11', '2023-02-12', '2023-02-13', '2023-02-14', '2023-02-15', '2023-02-16', '2023-02-17', '2023-02-18', '2023-02-19', '2023-02-20'] from Table_1

The problem is when I try to iterate this array with the following code in DBT :

    {%- for currentDate in recalculationDate -%}
        select {{ currentDate }} from  {{ source('Test', 'Table_1') }}
    {%- endfor -%}

I have these results:

  select   from  Table_1  
  select   from  Table_1
  select   from  Table_1
  select   from  Table_1
  select   from  Table_1
  select [ from  Table_1
  select ' from  Table_1
  select 2 from  Table_1
  select 0 from  Table_1
  select 2 from  Table_1
  select 3 from  Table_1
  select - from  Table_1
  select 0 from  Table_1
  select 2 from  Table_1
  select - from  Table_1
  select 0 from  Table_1
  select 7 from  Table_1
  select ' from  Table_1
  select , from  Table_1
  select   from  Table_1
  select ' from  Table_1
and so on...

Any ideas how to solve this problem ? I'm also opened to hear a best coding for this case. Regards.

Edits based on comments:

Final results expected (again, UNION ALL will be added later, for this example, consider that we just want multiple SELECT without UNION ALL clause:

Select "2023-02-07" from Table_1
Union All
Select "2023-02-08" from Table_1
Union All
Select "2023-02-09" from Table_1
...

The iteration will be done in DBT with the "for" described above.

Edit #2: I have exactly the same problem as described in this questions: Jinja2 Array Iteration Problems

Edit #3: To give more information, my problem is why "currentdate" (in the snippet below) return a character instead of a date as I returned an array of dates with my function ? (To be noted that this snippet is used as a test to look at the value of "currentDate" and that the code in final version will have the "currentdate" value in the WHERE clause, in addition to a "UNION ALL" part). When printing the array received, we can see that this is really an array of dates separated by comma.

    {%- for currentDate in recalculationDate -%}
        select {{ currentDate }} from  {{ source('Test', 'Table_1') }}
    {%- endfor -%}
Solution

  • The code from the macro (posted here and pasted below for reference) is important...

    {%- macro get_range_of_dates() -%}
{# Create an empty array to keep all dates #}
{%- set datesArray = [] -%}
{# Create a date representing the starting date of the range from a variable defined in dbt_project.yml  #}
{%- set start = modules.datetime.datetime.strptime(var("currentDate"), "%Y-%m-%d") -%}
{# Create a date reprsenting the ending date of the range (always currentDay) #}
{%- set end = modules.datetime.datetime.now() -%}

{# If start and end variables are not empty...#}
{%- if start and end -%}
    {# Calculate the lenght of the range #}
    {%- set duration = (end - start).days + 1 -%}
    {# Loop the range #}
    {%- for day in range(0, duration) -%}
        {# Add one day at each iteration #}
        {%- set dateIterated = (start + modules.datetime.timedelta(day)) -%}
        {# Convert datetime to string with the desired format #}
        {%- set finalDate = dateIterated.strftime("%Y-%m-%d") -%}
        {# Append the new date to the array of dates #}
        {%- do datesArray.append(finalDate) -%}
    {%- endfor -%}
{%- endif -%}
{# return the array of dates #}
{{ datesArray }}
{%- endmacro -%}

    The issue is that your macro is outputting the datesArray as a string and not returning it as an array so it can be used in the Jinja context. Change {{ datesArray}} at the bottom to {{ return(datesArray) }} and you will get the result you're expecting.