Getting Redshift syntax errors on volume and column anomaly tests configured without timestamp parameter

[Negashion]

Describe the bug

In package 0.15.2 I am getting some syntax errors when running tests on Redshift:

• When configuring tests without a timestamp I get [42601] ERROR: syntax error at or near "from" The culprit seems to be a misplaced , in dbt_packages/elementary/macros/edr/data_monitoring/monitors_query/column_monitoring_query.sql at the end of line 36: {{ elementary.null_timestamp() }} as start_bucket_in_data,

• When configuring volume or column anomaly tests with dimensions that are boolean I get [42846] ERROR: cannot cast type boolean to character varying The same sql code as above is trying to cast from boolean to varchar, something Redshift isn't liking one bit.

To Reproduce Steps to reproduce the behavior:

1. Create volume or column anomaly tests without timestamp and with boolean field dimensions in Redshift for package version 0.15.2

Environment (please complete the following information):

• Elementary dbt package version: 0.15.2
• dbt version you're using: dbt cloud 1.7
• Data warehouse: Redshift 1.0.67305

[haritamar]

Hi @Negashion ! I can confirm this is a bug. Basically this isn't allowed in Redshift:

select cast(TRUE as varchar)

So we likely need to modify the implementation of the edr_cast_as_string macro for Redshift (and maybe Postgres?), to handle boolean values correctly (may need passing the type of the column externally to the macro to produce different SQL for different data types).

If you'd like by any chance to contribute a fix to this we'd be happy to review / provide guidance.

[Negashion]

Hi haritamar,

Unfortunately I can't spare time on it at the moment, I am in a bit of a crunch, sorry. Just wanted to put the issue on your radar. I would love to give it a proper try once I am a bit free. Actually, I could use some feedback with the workaround I created. I reimplemented the macro in my project dbt/macros folder with an ad-hoc fix based on our column naming convention, we prefix all our boolean fields with "has" or "is" :

{%- macro edr_cast_as_string(column) -%}
     {%if column.startswith('is_') or column.startswith('has_')%}
        cast(decode({{ column }},true,'true',false,'false') as {{ elementary.edr_type_string() }})
    {% else %}
        cast({{ column }} as {{ elementary.edr_type_string() }})
    {%- endif -%}
{%- endmacro -%}

I am not being able to dispatch calls made with the namespace prefix format "elementary.edr_cast_as_string(column)" towards the new macro I declared in my project. It works well if I drop the prefix in the call. I added a dispatch config in my dbt_projects.yml to tackle it but it is not redirecting to my macro properly. Any idea why that is the case?

name: "project_x"

dispatch:
  - macro_namespace: elementary
    search_order: ['project_x', 'elementary']

Regards, Nega

[haritamar]

Hi @Negashion , Sorry for the delay. Yeah I think it may really be because as you mentioned we explicitly call it with the prefix elementary. - so the dispatch configuration is ignored and it takes the implementation from the "elementary" package. So the workaround may require patching the Elementary code and removing the prefix.