Deduplication when using ReplicatedMergeTree with delete+insert incremental strategy

[the4thamigo-uk]

Describe the bug

Steps to reproduce

1. build an incremental model using Replicated merge tree and delete+insert strategy
2. populate the model as full refresh
3. re-run as incremental
4. re=run as incremental

Expected behaviour

step 4 should delete the records inserted in step 3 and reinsert them

Actual behaviour

The records are not reinserted in step 4

Explanation

It looks like when you insert exactly the same set of records in the incremental update, the replication deduplication mechanism kicks in and ignores the new block of rows.

I am working around this by adding an alter table... modify setting hook, but it is not ideal. It would be nice if I could specify this as a setting when the table is created, or alternatively that it is set automatically when using the delete+insert strategy, as otherwise it wouldnt be guaranteed to work.

[genzgd]

Just to clarify, the problem is that in Step 4, the lightweight DELETE occurs, but the reinsert does not because of built in ClickHouse deduplication, so those records are just missing?

It seems reasonable to disable deduplication for the destination table, I'll take a look.

[the4thamigo-uk]

Yep thats what seems to happen.

[genzgd]

For the short term it seems like intead of using a hook you should be able to add the insert_deduplicate: 0 into the model config settings, which should be automatically picked up by this code:

 @available
    def get_model_settings(self, model):
        settings = model['config'].get('settings', dict())
        res = []
        for key in settings:
            res.append(f' {key}={settings[key]}')
        return '' if len(res) == 0 else 'SETTINGS ' + ', '.join(res) + '\n'

Is that a fix you can validate quickly?

My current thought is to add insert_deduplicate: 0 automatically into this method as a fix, and have an override configuration value if the user really wants to leave ClickHouse deduplication on.

[the4thamigo-uk]

Ah that looks like a neater solution. Give me 15mins to try it out.

[the4thamigo-uk]

DB::Exception: Unknown setting insert_deduplicate: for storage ReplicatedMergeTree. (UNKNOWN_SETTING) (version 23.10.3.5 (official build)). (UNKNOWN_SETTING) (version 23.10.3.5 (official build))

[genzgd]

Ugh, apparently it doesn't work for a table level setting. I'm curious how your ALTER TABLE statement works in that case.

I'm thinking an alternative solution is just to provide a unique insert_deduplication_token in the macro for each insert.

[the4thamigo-uk]

For reference I added it like this :

{{
  config(
    ...
    settings={
      'insert_deduplicate':0
    }
  )
}}

[the4thamigo-uk]

Oh, these are table level settings... I figured these were settings on the INSERT? If so then replicated_deduplication_window=0 should work

[the4thamigo-uk]

Yeah the above works... thanks... I wasnt aware you could set the table SETTINGS this way.

[the4thamigo-uk]

Probably need the settings option in the readme?

[genzgd]

cool, that seems like a reasonable solution. And yeah, I think that needs to be called out better in the documentation.

[the4thamigo-uk]

Still maybe worth defaulting to this option for this strategy, do you think?

[genzgd]

Yes, I think it's a good default for any of the dbt incremental strategies. The ClickHouse automatic deduplication is sort of an unexpected gotcha in such a high level tool.

[the4thamigo-uk]

Yeah, definitely had me bamboozled earlier... thanks for speedy response. Ill leave this open for you to decide how you want to deal with this. Thanks again.

[genzgd]

You're welcome, thanks for the report. It just so happens I'm in the middle of a lot of dbt-clickhouse work, so this is good timing :)