When using dbt-expectations with Clickhouse, it can't convert the timezone when tests require a datetime column

[redecvi]

Describe the bug

When using dbt-expectations with Clickhouse, it can't convert the timezone when tests require a datetime column. It seems that the convert_timezone function of dbt-date is not set up / specified for Clickhouse, so it uses the default command which is not compatible with Clickhouse.

Steps to reproduce

1. Set up dbt with Clickhouse
2. Add catalogica/dbt-expecations with dbt deps
3. Add a test that uses a datetime column, for example: ` tables:
   • name: test_table columns:
     • name: date_col tests:
       • dbt_expectations.expect_row_values_to_have_recent_data: datepart: day interval: 1`
4. Following error will appear: Database Error in test dbt_expectations_source_expect_row_values_to_have_recent_data_dev_test_table_date_col__day__1 (models/gold/schema.yml) HTTPDriver for https://mc4yb4j21e.europe-west4.gcp.clickhouse.cloud:8443 returned response code 404) Code: 46. DB::Exception: Unknown function convert_timezone. Maybe you meant: ['serverTimezone','serverTimeZone']: While processing CAST(date_col, 'timestamp') <= convert_timezone('UTC', 'UTC', CAST(now(), 'DateTime')). (UNKNOWN_FUNCTION) (version 24.6.1.4609 (official build))

Expected behaviour

Test will run and process the timestamp column correctly

Code examples, such as models or profile settings

schema.yml `version: 2

sources:

• name: dev tables:
  • name: test_table columns:
    • name: date_col tests:
      • dbt_expectations.expect_row_values_to_have_recent_data: datepart: day interval: 1`

dbt and/or ClickHouse server logs

10:09:13 Running with dbt=1.8.7 10:09:13 Registered adapter: clickhouse=1.8.4 10:09:13 Found 1 model, 1 test, 1 source, 725 macros 10:09:13
10:09:14 Concurrency: 1 threads (target='dev') 10:09:14
10:09:14 1 of 1 START test dbt_expectations_source_expect_row_values_to_have_recent_data_dev_test_table_date_colday1 [RUN] 10:09:14 1 of 1 ERROR dbt_expectations_source_expect_row_values_to_have_recent_data_dev_test_table_date_colday1 [ERROR in 0.06s] 10:09:14
10:09:14 Finished running 1 test in 0 hours 0 minutes and 1.29 seconds (1.29s). 10:09:14
10:09:14 Completed with 1 error and 0 warnings: 10:09:14
10:09:14 Database Error in test dbt_expectations_source_expect_row_values_to_have_recent_data_dev_test_table_date_colday1 (models/gold/schema.yml) HTTPDriver for https://mc4yb4j21e.europe-west4.gcp.clickhouse.cloud:8443 returned response code 404) Code: 46. DB::Exception: Unknown function convert_timezone. Maybe you meant: ['serverTimezone','serverTimeZone']: While processing CAST(date_col, 'timestamp') <= convert_timezone('UTC', 'UTC', CAST(now(), 'DateTime')). (UNKNOWN_FUNCTION) (version 24.6.1.4609 (official build))

Configuration

Environment

• dbt version: 1.8.7
• dbt-clickhouse version: 1.8.4
• clickhouse-driver version (if using native)
• clickhouse-connect version (if using http): 0.8.3
• Python version: 3.12.7
• Operating system: MacOS 15.1

ClickHouse server

• ClickHouse Server version: 24.6
• ClickHouse Server non-default settings, if any:
• CREATE TABLE statements for tables involved:
• Sample data for these tables, use clickhouse-obfuscator if necessary

[redecvi]

Noticing that a lot of dbt_expectations tests are not directly supported in Clickhouse. Might be a missing feature rather than a bug.