Execute dbt model only if var is not empty list

Question

I have a dbt incremental model that looks pretty like this:

-- depends_on: {{ref('stg_table')}}
{% set dates_query %}
SELECT DISTINCT date FROM dates_table
{% if is_incremental() %}
WHERE date NOT IN (SELECT DISTINCT date FROM {{this}})
{% endif %}
{% endset %}

{% set dates_res = run_query(dates_query) %}

{% if execute %}
{# Return the first column #}
{% set dates_list = dates_res.columns[0].values() %}
{% else %}
{% set dates_list = [] %}
{% endif %}

{% if dates_list %}
with
{% for date in dates_list %}
prel_{{date | replace('-', '_')}} as (
  SELECT smth FROM {{ref('stg_table')}}
  WHERE some_date = cast('{{date}}' as date)
),
{% endfor %}
prel AS ( 
select * from prel_{{dates_list[0] | replace('-', '_')}}
{% for date in dates_list[1:] %}
union all
select * from prel_{{date | replace('-', '_')}}
{% endfor %}
)
SELECT some_transformations FROM prel

{% endif %}

But it fails with error, because it runs following statement in database:

create or replace  view model__dbt_tmp
  
   as (
    -- depends_on: stg_table
);

So the question is how can I skip the model creation if dates list is empty? Thanks :)

Answer 1

You need a valid query that has the right columns but returns zero rows. This should work:

{% if dates_list %}
with
{% for date in dates_list %}
prel_{{date | replace('-', '_')}} as (
  SELECT smth FROM {{ref('stg_table')}}
  WHERE some_date = cast('{{date}}' as date)
),
{% endfor %}
prel AS ( 
select * from prel_{{dates_list[0] | replace('-', '_')}}
{% for date in dates_list[1:] %}
union all
select * from prel_{{date | replace('-', '_')}}
{% endfor %}
)
{% else %}
prel AS ( 
SELECT smth FROM {{ref('stg_table')}}
  WHERE 1=0
)
{% endif %}

SELECT some_transformations FROM prel

Separately, I would make other simplifications to your code. Jinja has a loop variable inside for loops, and flags called loop.first and loop.last that are only true on the first and last elements of an iterable. So your for loop can become:

prel AS ( 
{% for date in dates_list %}
{% if not loop.first %}union all{% endif %}
select * from prel_{{date | replace('-', '_')}}
{% endfor %}
)

But really I don't think you need to do all of this work with ctes and unioning. Your RDBMS probably supports the in operator with dates, and/or this could just be a join.