search

calogica / dbt-expectations

Port(ish) of Great Expectations to dbt test macros
https://calogica.github.io/dbt-expectations/
Apache License 2.0
989 stars 120 forks source link

[BUG] expect_table_aggregation_to_equal_other_table doesn't work with dates #305

Open rafael-italiano opened 2 months ago

rafael-italiano commented 2 months ago

Is this a new bug in dbt-expectations?

Current Behavior

Using the expect_table_aggregation_to_equal_other_table test on a DATETIME, TIMESTAMP or DATE column returns the following error. I think this might be specific to BigQuery, because its COALESCE() function doesn't accept different data types and there's an integer literal amidst the test implementation.

Expected Behavior

expect_table_aggregation_to_equal_other_table would compare two different DATETIME types and return PASS if they're the same.

Steps To Reproduce

Setup .yml file as follows:

models:
  - name: table
    tests:
          - dbt_expectations.expect_table_aggregation_to_equal_other_table:
              expression: max(updatedat)
              compare_model: ref('legacy_table')

Relevant log output

Database Error in test Database Error in test dbt_expectations_expect_table_aggregation_to_equal_other_table_ods_contacts_max_UpdatedDate___source_intercom_legacy_contacts___max_updated_date_ (models/intercom/__intercom.yml)
  No matching signature for function COALESCE for argument types: DATETIME, INT64. Supported signature: COALESCE([ANY, ...])

Environment

- OS: ghcr.io/dbt-labs/dbt-bigquery
- Python: 
- dbt: 1.7.11
- dbt-expectations: 0.10.3

Which database adapter are you using with dbt?

BigQuery 1.7.7

Note: dbt-expectations currently does not support database adapters other than the ones listed below.

Additional Context

rafael-italiano commented 2 months ago

A workaround to fellows out there who are eager to use this feature, use safe_cast and unix_millis to convert dates, datetimes or timestamp to integers and work from there