dbt snapshot - check strategy - stopped working properly

[vonschultz666]

Snapshot check strategy odd behavior. While timestamp strategy works fine, snapshot check strategy works quite odd and works fine only if dbt_updated_at parameter in .yml config is used (similar to timestamp strategy).
Main issue is with inconsistent results - historic version rows are not closed (they remain valid to 2100-01-01), new version rows are automatically closed (they are not valid to 2100-01-01), etc.

Steps to reproduce

1. Create table
2. Run initial snapshot - everything works
3. Update some column, for example "company_name"
4. Run snapshot again - as stated above - old version rows are not closed, new version rows are automatically closed, etc.

Expected behavior

dbt_valid_to = some date/time - ie. actual time of snapshot run - historic version
dbt_valid_to = 2100-01-01 00:00:00 for opened current versions

Configuration

Environment

• dbt version: 1.9.4
• dbt-clickhouse version: 1.9.1

ClickHouse server

• ClickHouse Server version: 25.6.3.116

Snapshot model

{% snapshot snp_test_table %}

{{ config(
    query_settings = {'join_use_nulls': 1},
    target_schema=generate_schema_name('snapshots')
    )
}}

SELECT
    {{ dbt_utils.star(
      from = ref('stg_test_table')
    ) }}
FROM {{ ref('stg_test_table') }}

{% endsnapshot %}

Snapshot yml

version: 2
snapshots:
  - name: snp_test_table
    description: 'Test table'
    latest_version: 1
    versions:
      - v: 1
        config:
          alias: snp_test_table
    config:
      unique_key: vat_number
      strategy: check
      dbt_valid_to_current: "{{ var('future_proof_date') }}"
      hard_deletes: 'invalidate'
      check_cols:
        - company_name
        - post_code
        - company_city
      group: TGDA
      tags:
        - test_table

var('future_proof_date') is defined in the dbt_project.yml file as:
vars: future_proof_date: "toDateTime('2100-01-01 00:00:00')"

DDL's

CREATE TABLE services.test_table
(

    `vat_number` String,

    `company_name` String,

    `post_code` String,

    `company_city` String
)
ENGINE = MergeTree
ORDER BY vat_number
SETTINGS index_granularity = 8192;

[BentsiLeviav]

@vonschultz666 could you please share more information related to your CH setup? Is it a cluster/single instance?

[vonschultz666]

Hi @BentsiLeviav , sorry for the late reply, I've just returned from my vacation.

CH is single instance, please let me know if you need any further info, I'll reply promptly since I've settled in and available 24/7.

My team is switching to Airbyte v2 connector so we need to adjust our dbt snapshots accordingly. Maybe we will use _airbyte_extracted_at for "dbt_updated_at" if this error can't be resolved.

Nevertheless, this is quite tricky, as column check strategy snapshots worked before just perfectly, ie. without any "dbt_updated_at" parameter.

[BentsiLeviav]

@vonschultz666 is the issue reproducible? (with the setup you provided above, specifically)

[vonschultz666]

@BentsiLeviav in our environment yes, we tried column check strategy with numerous tables, every time outcome is the same.

I have doubts regarding the combination of our ClickHouse and dbt version, but i might be wrong.

[dlabrousse]

We encountered another issue, but it seems to be the same problem.

When a line is created, the dbt_valid_to is set with now, and the previous record is updated as well. This results in two records with the same dbt_valid_to.

dbt-clickhouse : 1.9.2
clickhouse : 25.7.4.11

[vonschultz666]

Can confirm that the issue is still present in the following environment:
ClickHouse: v25.7.5.34
dbt-core: v1.9.9
dbt-clickhouse adapter: v.1.9.2

Where we can't use timstamp strategy, we will use column check strategy with updated_at parameter equal to "airbyte_extracted_at".

[BentsiLeviav]

@vonschultz666, we haven't yet released the version with this fix. Hopefully, we'll do it in the next few days.