DBT for循环使用数字作为变量时PostgreSQL union报错问题问询

你遇到的两个报错分别来自Jinja语法使用不当和传参类型不匹配：

• 循环声明{% for 't_'~dpid in dpids %}属于非法Jinja语法，不能在循环变量定义段做字符串拼接操作
• union_all('t_'~dpids)的入参是拼接后的字符串，而dbt的union_all宏要求入参为列表类型，宏会自动遍历入参的每个元素生成union语句，你传入字符串后宏就会遍历每个字符，才会出现逐字符拆分的错误SQL。

正确的实现逻辑是先预处理生成带t_前缀的CTE名称列表，再分别用于CTE定义和union操作，示例代码如下：

{{ config(materialized='table') }}

{%- set dpids = ["123","1234"] -%}
{# 构造带t_前缀的CTE名称列表 #}
{%- set cte_names = [] %}
{% for dpid in dpids %}
    {%- do cte_names.append('t_' ~ dpid) %}
{% endfor %}

WITH
{% for idx in range(dpids | length) %}
    {{ cte_names[idx] }} AS (
        SELECT *
            FROM {{ ref('table_name') }} b
            WHERE b.create_ts > ({{ get_last_load_timestamp('table_name', dpids[idx]) }}) 
            AND b.dpid = '{{ dpids[idx] }}'
    ){% if not loop.last %},{% endif %}
{% endfor %}

{# 传入处理好的CTE列表生成union语句 #}
{{ union_all(cte_names) }}

如果你的dbt版本支持prefix过滤器，可以简化CTE列表的构造逻辑：

{%- set cte_names = dpids | map('prefix', 't_') | list -%}

最终生成的SQL会自动给CTE加前缀，完全符合PostgreSQL语法要求：

WITH
t_123 AS (
    ... WHERE dpid = '123'
),
t_1234 AS (
    ... WHERE dpid = '1234'
)
select * from t_123 union all select * from t_1234

内容的提问来源于stack exchange，提问作者Boris B