Here is a macro I use to dedupe using BigQuery, not sure if its relevant or could help.

{%- macro deduped_dataset(database, table, key="id") -%}
  select deduped.*
    from (
      select
        _inner.*,
        row_number() over (partition by {{ key }} order by updated_at desc ) as rn
      from {{ source(database, table) }} as _inner
    ) as deduped
  where deduped.rn = 1
{%- endmacro -%}

We've had resource issues using row_number() vs. using array_agg. Our dedupe macro is as follows:

{% macro dedupe_source(source, group_by, order_by = None) %}
  select
    event.*
  from (
    select
      array_agg (
        {{ source }}
        {% if order_by != None %}
        order by {{ order_by }}
        {% endif %}
        limit 1
      )[offset(0)] event
    from {{ source }}
    group by {{ group_by }}
  )
{% endmacro %}

It is more performant (can handle more rows, not necessarily faster). You may want to consider going down this path. Adding the LIMIT 1 allows BQ to vastly reduce the dataset it needs to calculate the query.

In addition, rather than passing in a dataset and table, it would be better to pass in a source, so that this can be used as a separate CTE. Of course you could call dedupe( source(db, table) ) if you like, so this would supersede existing functionality.

Also, we use this macro to dedupe based on a configurable column AND order by - so that you can select which row to select.

Hi @switzer and @rwatts3 !

Thank you both for sharing out your dedupe methodologies. I'm working on standing up some client projects right now and hoping to utilize these shortly :)

Hi @blake-enyart , @switzer and @rwatts3 !

I was trying to apply this method but to many columns (4). but I got some errors.

I was wondering if you managed to find a solution for deduping based on many columns.

There is no limit to the number of columns to deduplicate across - just add a comma list in the string. For example, you can call the macro as follows:

with
source as (
  select * from table
),
optimised_source as (

    {{ dedupe_source('source', 'col1, col2, col3, col4', 'col9, col8, col7 desc' ) }}

)

...

Great idea! Using Airbyte in incremental mode without normalization, I could see a great benefit in having such a macro dedicated to Airbyte's raw data. Thanks for sharing your snippet @switzer

👋 Hey team! I'd welcome a PR that implemented this.

It will need to be cross-database compatible by the time we merge it in - I don't know offhand whether all four of the supported adapters (Snowflake, BQ, Redshift, Postgres) have array_agg or not. If they don't, falling back to the row_number based approach seems reasonable - also remember that there are integration tests that can validate that everything works correctly across all warehouses so you don't have to have access to them all individually.

Happy to help out with any questions along the way 🚀

@joellabes I thought I'd take a crack at this. It turns out that:

• Redshift does not have an ARRAY_AGG function.
• Snowflake and Postgres do not have a LIMIT clause available in their ARRAY_AGG implementations.

For these reasons I've ended up implementing the ROW_NUMBER approach as the default and the ARRAY_AGG as a BigQuery specific overload.

It was also necessary for the macro to take a relation as an argument because most of the databases do not have a SELECT * EXCEPT (...) clause and we introduce a new column with ROW_NUMBER. Therefore, we have to use dbt_utils.star to get the select statement from the relation.

Fixed by #512