Support for delete+insert incremental strategy

[canbekley]

Describe the feature

Adapter support for an incremental strategy that is able to handle non-unique matching keys when replacing incremental data (as described in the specs for the built-in delete+insert strategy). A potential solution could be based around Databricks' REPLACE ON syntax, that replaces rows based on a boolean expression:

If table_name is a Delta Lake table, REPLACE ON atomically deletes any rows in the table that match the query rows based on the specified boolean_expression, then inserts the query rows. Unlike REPLACE WHERE’s boolean_expression, REPLACE ON’s boolean_expression can reference columns from both the table and the query. If the query is empty, no deletions occur because there are no query rows to match.

The compilation code could be similar to replace_where strategy, compiling the following model:

{{ config(
    materialized='incremental',
    file_format='delta',
    incremental_strategy='delete+insert',
    unique_key=('date_day')
) }}

with new_events as (

    select * from {{ ref('events') }}

    {% if is_incremental() %}
    where date_day >= date_add(current_date, -1)
    {% endif %}

)

select
    date_day,
    count(*) as users

from new_events
group by 1

into the following sql run code:

create temporary view databricks_incremental__dbt_tmp as

    with new_events as (

        select * from analytics.events


        where date_day >= date_add(current_date, -1)


    )

    select
        date_day,
        count(*) as users

    from events
    group by 1

;

insert into table analytics.databricks_incremental as t
replace on (t.date_day = s.date_day)
select `date_day`, `users` from databricks_incremental__dbt_tmp as s

Describe alternatives you've considered

The alternative incremental strategies all have certain downsides for this use-case. insert_overwrite does seem to require partitioned tables, and also partitioning granularity that matches the added increment. merge strategy doesn't work with non-unique unique_keys. And replace_where would require us to be explicit about our increment ranges via incremental_predicates (which seems not easy to work with for this case).

Who will this benefit?

Anyone that would like to build incremental models based on a non-unique "matching key" (or unique_key in the case of delete+insert) for non-partitioned tables (e.g. liquid clustered tables).

Are you interested in contributing this feature?

I could also contribute this feature

[canbekley]

Hi @benc-db, may I kindly ask for your feedback on this request? as mentioned, I could also contribute this feature. but before jumping into action, I would like to understand if I have overseen anything or if there is any other context about why delete+insert hadn't been added before.

[benc-db]

we were actually just discussing adding this recently. I would happily review a PR on this if you wanted to contribute, I think replace on is an appropriate implementation mechanism.