invalidate_hard_deletes option does not work with clickhouse

[andrewkomkov]

Describe the bug

invalidate_hard_deletes option (https://docs.getdbt.com/reference/resource-configs/invalidate_hard_deletes) does not work with clickhouse

Steps to reproduce

1. Create snapshot like this

   {% snapshot snap_imdb_actors %}
   {{
       config(
         target_schema='snapshots',
         strategy='check',
         unique_key='id',
         check_cols='all',
         invalidate_hard_deletes=True
       )
   }}
   with actors as (
   select * from {{ source('imdb', 'actors') }})
   
   select * from actors
   {% endsnapshot %}

2. Run it
3. Then delete a row from table imdb.actors
4. run snapshot again

Expected behaviour

In snapshot table, row with deleted id should have dbt_valid_to, but it does null

Configuration

Environment

• dbt version: 1.7.12
• dbt-clickhouse version: 1.7.6

[andrewkomkov]

I guess i found cause of the problem If somehow pass this setting SET join_use_nulls = 1; It will wokr The question is - how can i pass settings like this to dbt?

[andrewkomkov]

Fixed with this setting

[genzgd]

thanks for the investigation -- my sense is that dbt-clickhouse should make "join_use_nulls" the default perhaps?

[andrewkomkov]

I don't think you should make it enabled by default. Since it is not enabled by default in Clickhouse itself However, this should be included in the module description