仅当var不是空列表时执行dbt模型

Question

我有一个dbt增量模型，看起来像这样:

-- 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 %}

但是它失败了，因为它在数据库中运行了下面的语句:

create or replace  view model__dbt_tmp

as (
-- depends_on: stg_table
);

那么问题是，如果日期列表为空，我如何跳过模型创建?谢谢:)

Answer 1

您需要一个具有正确列但返回零行的有效查询。这应该可以工作:

{% 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

另外，我将对您的代码进行其他简化。Jinja在for循环中有一个loop变量，loop.first和loop.last标志只在可迭代对象的第一个和最后一个元素上为真。所以for循环可以变成:

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

但是我真的不认为你需要用ctes和union来做所有这些工作。您的RDBMS可能支持带有日期的in操作符，并且/或者这可能只是一个连接。