{%- macro deduplicate(relation, partition_by, order_by) -%}

{{ return(adapter.dispatch('deduplicate', 'dbt_utils')(relation, partition_by, order_by)) }}

{% endmacro %}

{%- macro default__deduplicate(relation, partition_by, order_by) -%}

with row_numbered as (

select

_inner.*,

row_number() over (

partition by {{ partition_by }}

order by {{ order_by }}

) as rn

from {{ relation }} as _inner

)

select

distinct data.*

from {{ relation }} as data

{#

-- Not all DBs will support natural joins but the ones that do include:

-- Oracle, MySQL, SQLite, Redshift, Teradata, Materialize, Databricks

-- Apache Spark, SingleStore, Vertica

-- Those that do not appear to support natural joins include:

-- SQLServer, Trino, Presto, Rockset, Athena

#}

natural join row_numbered

where row_numbered.rn = 1

{%- endmacro -%}

{# Redshift should use default instead of Postgres #}

{% macro redshift__deduplicate(relation, partition_by, order_by) -%}

{{ return(dbt_utils.default__deduplicate(relation, partition_by, order_by=order_by)) }}

{% endmacro %}

{#

{%- macro postgres__deduplicate(relation, partition_by, order_by) -%}

select

distinct on ({{ partition_by }}) *

from {{ relation }}

order by {{ partition_by }}{{ ',' ~ order_by }}

{%- endmacro -%}

{#

{%- macro snowflake__deduplicate(relation, partition_by, order_by) -%}

select *

from {{ relation }}

qualify

row_number() over (

partition by {{ partition_by }}

order by {{ order_by }}

) = 1

{%- endmacro -%}

{#

{%- macro databricks__deduplicate(relation, partition_by, order_by) -%}

select *

from {{ relation }}

qualify

row_number() over (

partition by {{ partition_by }}

order by {{ order_by }}

) = 1

{%- endmacro -%}

{#

{%- macro bigquery__deduplicate(relation, partition_by, order_by) -%}

select unique.*

from (

select

array_agg (

original

order by {{ order_by }}

limit 1

)[offset(0)] unique

from {{ relation }} original

group by {{ partition_by }}

)

{%- endmacro -%}