[BUG] Timestamp to string coercion error when excluding 'beginning_of_all_times' and 'end_of_all_times'

[gjvanvuuren]

Describe the bug

Using the eff_sat_v0 macro, the following code is generated (disable_hwm: true):

WITH
source_data AS (

    SELECT
        `complaint_hk`,
        `load_date_ts`,
        `record_source`
    FROM `01_data_vault_dev`.`stg`.`vw_stg_erp_reklamation` src
    WHERE `load_date_ts` NOT IN ('1900-01-01 00:00:00.000000', '2099-12-31 23:59:59.999999')
),
...

The above query does not exclude the timestamp value for '1900-01-01 00:00:00.000000'

The timestamp related vars in my dbt_project.yml:

  datavault4dbt.beginning_of_all_times: { databricks: "1900-01-01 00:00:00.000000" }
  datavault4dbt.end_of_all_times: { databricks: "2099-12-31 23:59:59.999999" }
  datavault4dbt.timestamp_format: { databricks: "yyyy-MM-dd HH:mm:ss.SSSSSS" }

Environment

• dbt version: dbt-core 1.11.12
• datavault4dbt version: 2.0.3
• Database/Platform: Databricks

Expected behavior

The problem seems to be that the IN statement coerces TIMESTAMP data to STRING data and compares the string values. If they don't exactly match, the condition fails.

It would be better to cast the strings to timestamps and compare the timestamps. For example:

NOT IN (CAST('1900-01-01 00:00:00.000000' AS TIMESTAMP), CAST('2099-12-31 23:59:59.999999' AS TIMESTAMP))