# Confused how to use union relations - can it be used dynamically inline?
**URL:** https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226
**Category:** Help
**Created:** [July 20, 2023, 3:14pm UTC](https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226 "2023-07-20T15:14:53Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@maxim.kupfer](https://discourse.getdbt.com/u/maxim.kupfer)
#### Post date: [July 20, 2023, 3:14pm UTC](https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226/1 "2023-07-20T15:14:53Z")
## 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:
```python
{% 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'))}}
```
---
### Author:  [@shaidi](https://discourse.getdbt.com/u/shaidi)
#### Post date: [July 27, 2023, 7:51am UTC](https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226/2 "2023-07-27T07:51:25Z")
I was also confused about the usage of CTEs in union\_relations.
By looking at the source code of the macro (available [here](https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/union.sql)), 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:
```auto
{{ dbt_utils.union_relations(
relations=[
ref('my_first_relation'),
ref('my_second_relation')
]
) }}
```
---
### Author:  [@maxim.kupfer](https://discourse.getdbt.com/u/maxim.kupfer)
#### Post date: [July 27, 2023, 6:16pm UTC](https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226/3 "2023-07-27T18:16:24Z")
ooh, very insightful feedback @shaidi 👍 … and glad to know I’m not the only one with requirement despite the fruitless search for answers.
---
### Author:  [@system](https://discourse.getdbt.com/u/system)
#### Post date: [August 3, 2023, 6:17pm UTC](https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226/4 "2023-08-03T18:17:04Z")
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
---
### Author:  [@joellabes](https://discourse.getdbt.com/u/joellabes)
#### Post date: [February 16, 2024, 12:49am UTC](https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226/5 "2024-02-16T00:49:20Z")
---
### Author:  [@patkearns10](https://discourse.getdbt.com/u/patkearns10)
#### Post date: [February 16, 2024, 1:39am UTC](https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226/6 "2024-02-16T01:39:47Z")
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.
---
### Author:  [@Abeeya](https://discourse.getdbt.com/u/Abeeya)
#### Post date: [May 31, 2024, 7:41am UTC](https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226/7 "2024-05-31T07:41:44Z")
Hello @shaidi if we have duplicates after UNIONing both views/tables, How to remove it?
select DISTINCT \* from {{ dbt\_utils.union\_relations…}} removes duplicates, but sqlfluff validation fails with parsing errors, Is there any other way?
---
### Author:  [@shaidi](https://discourse.getdbt.com/u/shaidi)
#### Post date: [October 8, 2024, 11:20am UTC](https://discourse.getdbt.com/t/confused-how-to-use-union-relations-can-it-be-used-dynamically-inline/9226/8 "2024-10-08T11:20:27Z")
Hey, late replying, hopefully this is still helpful for you / for future readers:
The dbt\_utils.union\_relations macro automatically performs a “UNION ALL” operation between each relation, therefore, does not allow distinct selection / removal of duplicates.
You can try to find a different method of using the select DISTINCT operator that sqlfluff will be okay with, or, you can create a custom macro - union\_relations\_distinct!
This is the source code for the union\_relations macro - [dbt-utils/macros/sql/union.sql at main · dbt-labs/dbt-utils · GitHub](https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/union.sql)
You can copy the entire macro and replace the part of `union all` with `union distinct` and there you have it.