Confused how to use union relations - can it be used dynamically inline?

Help
1

The problem I’m having

Is it possible to create a table via cte’s, store it as a variable, and then use that as a ref for dbt_utils.union_relations?

The context of why I’m trying to do this

I have a complex table using multiple cte’s in my model which I later down the line need to union with another relation. I could do the manual union, but I’d like to use the cleaner and easier to maintain union_relations approach.

What I’ve already tried

Tons of trial an error here and there, but what I’m trying to accomplish would be something like:

{% set tbl_a %}
  select * from foo  # in reality, this is actually a long cte
{% endset %}

select * from
{{ dbt_utils.union_relations(tbl_a, ref('another_model'))}}
2

I was also confused about the usage of CTEs in union_relations.

By looking at the source code of the macro (available here), I found out that in order to fully protect our union from crashing, dbt requires some metadata on the columns, therefore, it only is able to use queries that are saved as models and not CTEs.

One way to get over that is to materialize your CTEs as views or ephemeral models and then you should be able to use them as union_relations.

I would love if someone corrected me and taught us a way of using CTEs inside union_relations, but I do believe it is not currently possible.

Also, please note that the usage of union_relations should not include a “select * from” before, but rather, just use the union_relations itself:

{{ dbt_utils.union_relations(
    relations=[
                ref('my_first_relation'),
                ref('my_second_relation')
                ]
    ) }}
1 Like
3

ooh, very insightful feedback @shaidi :+1: … and glad to know I’m not the only one with requirement despite the fruitless search for answers.

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

6

Just wanted to make a note here that the above comment was half correct:

One way to get over that is to materialize your CTEs as views or ephemeral models and then you should be able to use them as union_relations

This particular macro requires the references be materialized as views or tables to work, as it relies on the grabbing information from the information schema.