search

calogica / dbt-expectations

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

Confusing behavior of macro: dbt_expectations.expect_row_values_to_have_recent_data #212

Open chasemeadows opened 1 year ago

chasemeadows commented 1 year ago

these 2 tests:

https://github.com/calogica/dbt-expectations/blob/main/macros/schema_tests/table_shape/expect_row_values_to_have_recent_data.sql

https://github.com/calogica/dbt-expectations/blob/main/macros/schema_tests/table_shape/expect_grouped_row_values_to_have_recent_data.sql

have unexpected behavior when testing against fields that are date only. For example, in many use cases, these tests may check against a field such as batch_date which would be a date-only field that indicates the batch of data that the batch load corresponds to. This test will compare that date to a timestamp. So for example, if you had a test that runs on t-1 lookback that is orchestrated at 10-27 21:00 UTC, you would be comparing 10-26 21:00 UTC vs 10-26 00:00 UTC, and the test would always fail.

My suggestion is to have some type of behavior that checks if the column_name provided is a date field vs a timestamp and perform the check accordingly.

clausherther commented 1 year ago

Hi @chasemeadows, thanks for opening this issue. Could you please add which platform (Postgres, BigQuery, Snowflake) you're on and which version of dbt-expectations you're using? Thanks!

rud0812 commented 1 year ago

Also experiencing this issue with Snowflake