dbt-clickhouse: Incremental strategy insert_delete is not valid. use_lw_deletes Being ignored

Hi There,

It seems that since version 1.3.0 the incremental strategy reference has changed but this isn’t documented. Any model using incremental_strategy='insert+delete' throws: The incremental strategy 'insert_delete' is not valid for Clickhouse. This is on version 1.3.2/1.3.3

Changing the strategy to incremental_strategy='delete_insert' resolves this error however it wrongfully throws this message after: dbt_clickhouse adapter: Lightweight deletes are not available, using legacy ClickHouse strategy

Lightweight deletes are enabled in our instance. Screenshot 2023-02-01 at 19 49 11

About this issue

Original URL
State: closed
Created a year ago
Comments: 16 (9 by maintainers)

Most upvoted comments

That xml should go either either into a specific user element or profile element. For my local test server I put it in the “default” profile element in my users.xml:

 <profiles>
        <default>
           
            <!--<http_max_field_value_size>50</http_max_field_value_size>-->
            <allow_experimental_object_type>1</allow_experimental_object_type>
            <enable_http_compression>1</enable_http_compression>
            <cast_ipv4_ipv6_default_on_conversion_error>1</cast_ipv4_ipv6_default_on_conversion_error>
            <flatten_nested>0</flatten_nested>
            <input_format_skip_unknown_fields>0</input_format_skip_unknown_fields>
            <allow_experimental_lightweight_delete>1</allow_experimental_lightweight_delete>
            <query_profiler_real_time_period_ns>1000000000</query_profiler_real_time_period_ns>
            <query_profiler_cpu_time_period_ns>1000000000</query_profiler_cpu_time_period_ns>
        </default>
        <geoff>
            <http_max_field_value_size>350</http_max_field_value_size>
        </geoff>
    </profiles>

genzgd on Feb 1, 2023

We’ve actually set that parameter in the profile which is why this was weird. Looking at this though I don’t think it’s actually being used but I’m not very familiar with the structure of the project.

Screenshot 2023-02-01 at 19 46 43

apostoltego on Feb 1, 2023

Btw there is a PR for the updated documentation. https://github.com/dbt-labs/docs.getdbt.com/pull/2626/files

genzgd on Feb 1, 2023