Parameterize union_relations macro to support deduplication with UNION

[will-hou]

Describe the feature

By default, the union_relations macro performs a UNION ALL. However, I think the macro should also support deduplication of rows using UNION

dbt-utils/macros/sql/union.sql

Line 1 in a17d66f

{%- macro union_relations(relations, column_override=none, include=[], exclude=[], source_column_name='_dbt_source_relation', where=none) -%}

Describe alternatives you've considered

Users could perform a SELECT DISTINCT * after the UNION ALL in their model which would achieve the same functionality. However, I think it more ideal to include the option as a parameter since it is supported by SQL.

Additional context

N/A

Who will this benefit?

While it is better practice to dedupe in staging models, teams may want to use a quick UNION for prototyping non-productionized code.

Are you interested in contributing this feature?

Yes! I would be happy to make a PR with the change and associated tests.

Tasks

Beta Give feedback

No tasks being tracked yet.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[katieclaiborne-duet]

I'm interested in this feature as well! Anything I can do to help move it along?

[dbeatty10]

@katieclaiborne-duet

What do you think about the ergonomics of each of the options below?

Option 1: new boolean parameter named distinct with a default of false?

Option 2: a new string parameter named union? And its two possible values were all (default) and distinct)?

Option 3 (like in #819): a new boolean parameter named deduplicate (default false)?

Examples

Option 1:

{{ dbt_utils.union_relations(
    relations=[ref('my_model'), source('my_source', 'my_table')],
    distinct=false
) }}

Option 2:

{{ dbt_utils.union_relations(
    relations=[ref('my_model'), source('my_source', 'my_table')],
    union='distinct'
) }}

Option 3:

{{ dbt_utils.union_relations(
    relations=[ref('my_model'), source('my_source', 'my_table')],
    deduplicate=true
) }}

[katieclaiborne-duet]

Option 2 is closest to my initial thought!

I had imagined a new string parameter named union_type, with values of all (default) and distinct.