[Feature] Adding WHERE codintion during MERGE update

[philiphagglund]

Hi,

It would be nice to be able to controll the incremental merge strategy by adding a WHERE clause to the WHEN MATCHED THEN part of the merge statement. As i see on dbt-oracle github it seems the adapter doesn't support it.

ON (src.id = tgt.id AND src.date = tgt.date)
WHEN MATCHED THEN
    UPDATE SET
        tgt.end_date = src.end_date
    WHERE
        src.end_date <> tgt.end_date
WHEN NOT MATCHED THEN
    INSERT (tgt.id, tgt.date, tgt.end_date)
    VALUES (src.id, src.date, src.end_date)

I got recommended by #db-oracle on dbt slack to raise a feature request so it gets tracked.

Describe alternatives you've considered

This is the repons i got from #db-oracle slack channel from @aosingh:

dbt-oracle implements the functionality conforming to the standard defined by dbt.
However, I see that you need support for an arbitrary where clause condition during MERGE update. We will need to think about the interface to implement this. One way could be to introduce a dbt-oracle specific parameter. The challenge is to make this a generic config which can work for any where condition. The where condition can be on the target data or source data or both.
You can also raise a feature request here - https://github.com/oracle/dbt-oracle/issues so that this gets tracked

Who will this benefit?

No response

Anything else?

No response

[aosingh]

@philiphagglund

We are working on dbt-oracle 1.4 release which will support incremental predicates

We will support incremental predicates in dbt-oracle conforming to rules defined by dbt Labs.

I think, this will solve your feature request. Please read about it and I will let you know when the first cut is released on PyPI.

Let me know if you have any questions

[aosingh]

@philiphagglund

Did you try incremental predicates ? Does it solve your usecase.

[philiphagglund]

Hi @aosingh

Unfortunately is this not going to solve the usecase i have. I have now tried the new incremental predicates functionality and it works fine, except is doesn't accept a column in the ON statement to be updated:

[0m07:00:02.502344 [info ] [Thread-1 (]: oracle adapter: Oracle error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "DBT_INTERNAL_DEST"."END_DATE"

Below is the query generated by DBT using incremental predicates.

yaml:

version: 2

models:
  - name: tst
    config:
      materialized: incremental
      tags: cas
      unique_key: ['id', 'date_key']
      incremental_strategy: merge
      incremental_predicates: ["DBT_INTERNAL_SOURCE.end_date<> DBT_INTERNAL_DEST.end_date"]

Sql:

ON (src.id = tgt.id AND src.date = tgt.date AND DBT_INTERNAL_SOURCE.end_date <> DBT_INTERNAL_DEST.end_date )
WHEN MATCHED THEN
    UPDATE SET
        tgt.end_date = src.end_date
WHEN NOT MATCHED THEN
    INSERT (tgt.id, tgt.date, tgt.end_date)
    VALUES (src.id, src.date, src.end_date)

The result I was expecting would be:

ON (src.id = tgt.id AND src.date = tgt.date)
WHEN MATCHED THEN
    UPDATE SET
        tgt.end_date = src.end_date
    **WHERE**
        **DBT_INTERNAL_SOURCE.end_date <> DBT_INTERNAL_DEST.end_date**
WHEN NOT MATCHED THEN
    INSERT (tgt.id, tgt.date, tgt.end_date)
    VALUES (src.id, src.date, src.end_date)

[aosingh]

@philiphagglund understood the problem. I guess i need to think about how to support this conditional update clause during MERGE. Probably, a parameter which can be supplied by the user during incremental materialization config.