calogica/dbt-expectations

[BUG] expect_table_aggregation_to_equal_other_table doesn't work with dates

rafael-italiano opened this issue · 1 comments

Is this a new bug in dbt-expectations?

  • I believe this is a new bug in dbt-expectations
  • I have searched the existing issues, and I could not find an existing issue for this bug

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.

  • Postgres
  • Snowflake
  • BigQuery

Additional Context

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