Hello Team,

dbt version: 1.3.0
elementary version: 0.13.1

I am facing an issue with a dbt model that includes a test. The test specification is as follows:

tests:
  - elementary.table_anomalies:
      table_anomalies:
        - row_count
      severity: error
      timestamp_column: created_at::date

However, the test is failing with the following error message:

15:34:16  Completed with 1 error and 0 warnings:
15:34:16  
15:34:16  Database Error in test elementary_table_anomalies_stg_db_name__table_name_row_count__created_at_date (models/data_lake/staging/h10/product/systems/stg_db_name__table_name.yml)
15:34:16    001003 (42000): SQL compilation error:
15:34:16    syntax error line 8 at position 61 unexpected '::'.
15:34:16    syntax error line 13 at position 19 unexpected 'created_at'.
15:34:16    syntax error line 13 at position 50 unexpected '>='.
15:34:16    syntax error line 36 at position 8 unexpected ')'.

Upon investigation, I found that in the automatically generated dbt query in snowflake, there is a note about an alias with type casting as follows:

with partially_time_filtered_monitored_table as (
    select
        cast(created_at::date as TIMESTAMP) as created_at::date
    from name_dev.dbt_maximv_datalake.stg_db_name__table_name
    where cast(created_at::date as TIMESTAMP) >= 
        dateadd(day, cast(-7 as INT), cast(cast('2024-01-11T00:00:00' as TIMESTAMP) as TIMESTAMP))
)

When I manually removed

::date

from the alias and executed the generated query, the table with the test was created successfully. Additionally, I noticed that when I changed the test from

created_at::date

to

created_at::timestamp

, the generated query had

cast(created_at::date as TIMESTAMP) as created_at::timestamp

. Could you please assist me in understanding and resolving this issue? Thank you.

Hi @Maxim Vengher, this issue was actually resolved in version

0.13.2

of our dbt package 🙂 (you can see it in the release notes here). can you try updating and letting me know if this fixes things for you?

@Ofek Weiss Thank. Problem is resolved

Amazing! glad i cloud help 😄