[Feature] Provide option to only update specific columns in incremental model

[viplazylmht]

Describe the Feature

When building data models using dbt-oracle, currently for an incremental model, there is no optional way to update only the columns I want. The adapter will update all columns except unique_keys. (I just checked out the code and found it, so if it has been satisfied before, please let me know).

For example, my data model follows the Fact-Dimension model, and here is an example Dimension table:
Table: D_MODEL
Columns: ID, MODEL_NAME, UPDATED_DATE

As we can see, the unique_key ID in the Dimension table is just used to join the Fact table. But, the merge on columns is MODEL_NAME, it is not the primary key of Dimension tables.

So, in this case, I only want to update the column UPDATED_DATE, but there is no config to finalize them. Dbt-oracle update column ID and UPDATE_DATE, and the whole Fact-Dim model seem broken because it can not join again.

Describe alternatives you've considered

Recently, I have worked with dbt-bigquery and they have the option merge_update_columns docs to update only columns they want.

I also read the merge_update_columns source code and found they customized the BaseConfig with BigqueryConfig, then use them on Jinja code too to filter out targeted columns.

So, our problem can be resolved by just creating an additional argument and logic, seem easy!

Who will this benefit?

I thought it is very useful for a lot of users who built special data models with unexpected behavior and make dbt-oracle more fluently.

Anything else?

Maybe if I have time, I will experiment on them. Also, can I create a pull request, or contribute to this project?

[aosingh]

Thank you @viplazylmht for raising this feature request. This is now addressed in dbt-oracle==1.1.1
You can use the config merge_update_columns in incremental models

{{config(materialized='incremental', 
         unique_key='user_id',
         merge_update_columns=['user_name', 'income', 'last_login_date'])}}
SELECT * FROM {{ ref('seed') }}
{% if is_incremental() %}
    WHERE last_login_date > (SELECT max(last_login_date) FROM {{ this }})
{% endif %}

Please test this with dbt-oracle==1.1.1 and let me know if you have any questions.

If you want to check the changes please review the PR #35