DBT - for loop issue with number as variable-CodePudding

I am trying to create in DBT tool 'loop for' tables which have number as a variable, the same variable I am using also in macro. Issue is in union part. DBT is trying to create union with table name which is based on number and it is not possible in postgre sql.

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

{%-    set dpids = ["123","1234"]    -%}

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

{{ union_all(dpids) }}

    select * from 123 union all select * from 1234 --fail

So I tried to to change it where I added t_ before number, but in union part, dbt try to get all characters from string.

with 
{% for 't_'~dpid in dpids %}
    {{ dpid }} AS (
        SELECT *
            FROM {{ ref('table_name') }} b
            WHERE b.create_ts > ({{ get_last_load_timestamp('table_name', dpid) }}) 
            AND b.dpid = '{{ dpid }}'
    ){% if not loop.last %},{% endif %}
{% endfor %}
{{ union_all('t_'~dpids) }}

select * from t 
union all 
select * from _ 
union all 
select * from 1
union all 
select * from 2
...and so on

I dont know if its not just possible or am I missing something. If someone has any ideas I would be grateful.

Thanks

CodePudding user response：

Welcome! You've asked a great question for a newcomer!

Essentially, I think you're asking asking

How can I prefix each element of a list of strings in jinja?

How can I do this in a "clean" dbt-eque way?

I'm no postgres expert, but there may be a way to get away with tables named with just integers (casting to string? or quoting them?)

My answer might not be the cleanest, but it should work.

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

{%- set dpids = ["123","1234"]    -%}
{%- set cte_names = [] %}

WITH
{% for dpid in dpids %}
{%- set cte_name = 't_' ~ dpid %}
{% do cte_names.append(cte_name) %}
    {{ cte_name }} AS (
        SELECT *
            FROM {{ ref('table_name') }} b
            WHERE b.create_ts > ({{ get_last_load_timestamp('table_name', dpid) }}) 
            AND b.dpid = '{{ dpid }}'
    ){% if not loop.last %},{% endif %}
{% endfor %}

{{ union_all(cte_names) }}