DBT ref within a for loop-CodePudding

I have a list of subqueries in my dbt project ["base","table_1", "table_2", "table_3"...].

I would like to join them all on a common column, id.

I would like to avoid repetition by doing this using for loop macro. My problem is, I get syntax errors when I try to reference the subquery name {{sub}} within {{ref}}.

Here is the code I was trying.

{% set subs = ["table_1", "table_2", "table_3"] %}


SELECT
{% for sub in subs %}
    {{sub}}.* EXCEPT (id),
{% endfor %}
base.*
FROM {{ref('base')}} as base
{% for sub in subs %}
    LEFT JOIN {{ref({{sub}})}} as {{sub}}
    ON {{sub}}.id = base.id
{% endfor %}

I get a syntax error

expected token ':', got '}'

And if I change to

LEFT JOIN {{ref("'"{{sub}}"'")}} as {{sub}}

I get this error

expected token ',', got '{'

Finally with

LEFT JOIN {{ref("{{sub}}")}} as {{sub}}

I get

Model depends on a node named '{{sub}}' which was not found

Here are some pages I read but couldn't see a solution

Using a string as an argument in a Jinja macro in dbt

https://docs.getdbt.com/reference/dbt-jinja-functions/ref

CodePudding user response：

When you use ref(), you are already inside an expression clause ({{..}}), so you do not need to add another expression clause to refer to your subs.

{% set subs = ["table_1", "table_2", "table_3"] %}


SELECT
{% for sub in subs %}
    {{sub}}.* EXCEPT (id),
{% endfor %}
base.*
FROM {{ref('base')}} as base
{% for sub in subs %}
    LEFT JOIN {{ ref(sub) }} as {{sub}}
    ON {{sub}}.id = base.id
{% endfor %}

However, if your subs are really subqueries with the use of the with sql clause in that same model, you don't need to use ref() because they are not dbt models. You can check the documentation for a better understanding of ref()

{% set subs = ["table_1", "table_2", "table_3"] %}


SELECT
{% for sub in subs %}
    {{sub}}.* EXCEPT (id),
{% endfor %}
base.*
FROM {{ref('base')}} as base
{% for sub in subs %}
    LEFT JOIN {{ sub }}
    ON {{sub}}.id = base.id
{% endfor %}