Incremental query in DBT based on current month with Jinja

I am trying to implement a incremental query in DBT using Jinja.

Considering there are tables getting created every month in warehouse with year and month suffix and I need to write a logic to union the new table which gets created every month to execute the DBT model. Below is the code which I have started with

#initialize the months in a list
{% set months= ['03','04','05','06','07','08','09','10','11','12','01','02'] %}

#first select query for Feb month of 2022
SELECT *, '2022-02-01' AS ref_month
FROM source_table_2022_02

#initilalize year variable to 2022
{% set year= namespace(items=2022) %}

#loop through the months to generate dynamic query for upcoming months
{% for month in months %}

   #if month is Jan increment the year
   {% if month == '01' %}
    {% set year.items = year.items + 1 %}
   {% endif %}

    UNION ALL 
    
    SELECT *, '{{ year.items }}-{{ month }}-01' AS ref_month
    FROM source_table_{{ year.items }}_{{ month }}

{% endfor %}

output of above logic is as below

SELECT *, '2022-02-01' AS ref_month
FROM source_table_2022_02

UNION ALL

SELECT *, '2022-03-01' AS ref_month
FROM source_table_2022_03

UNION ALL

SELECT *, '2022-04-01' AS ref_month
FROM source_table_2022_04

.
.
.

UNION ALL

SELECT *, '2023-02-01' AS ref_month
FROM source_table_2023_02

I need help in stopping the for loop when we reach the current month i.e Dec(because there is no current_month method in Jinja and I need to implement this logic in DBT models.sql file and not a python file), instead of looping through the upcoming months.

Note: as mentioned earlier the source table gets created every month with year and month suffix

I also want to continue the loop after 2023 Feb in the upcoming months. Current logic stops immediately after the list iteration ends i.e 2023 Feb

Solution

Would you consider installing the commonly used dbt-utils package?

If so, they have a macro called get_relations_by_pattern and another called union_relations.

These could be used to solve your problem as follows:

{% set monthly_relations = dbt_utils.get_relations_by_pattern('my_schema', 'source_table_%') %}

    SELECT *
      FROM {{ dbt_utils.union_relations(relations = monthly_relations) }}

Note that a new field _dbt_source_relation will be added, listing the original table name. You'll be able to parse the month and year from this.