[BUG] cast failure in replace_placeholder_with_period_filter on to_timestamp() call

[igorvoltaic]

Describe the bug
cast fails for anything other than varchar in replace_placeholder_with_period_filter on to_timestamp() call

automate-dv/macros/materialisations/period_mat_helpers/replace_placeholder_with_period_filter.sql

Lines 23 to 25 in d56ce03

	TO_TIMESTAMP({{ timestamp_field }}) >= DATE_TRUNC('{{ period }}', TO_TIMESTAMP('{{ start_timestamp }}') + INTERVAL '{{ offset }} {{ period }}')
	AND TO_TIMESTAMP({{ timestamp_field }}) < DATE_TRUNC('{{ period }}', TO_TIMESTAMP('{{ start_timestamp }}') + INTERVAL '{{ offset }} {{ period }}' + INTERVAL '1 {{ period }}'))
	AND (TO_TIMESTAMP({{ timestamp_field }}) >= TO_TIMESTAMP('{{ start_timestamp }}')

Environment

dbt version: 1.8
automate_dv version: latest
Database/Platform: Trino

Expected behavior
cast should work as expected

AB#5589

[azure-boards]

✅ Successfully linked to Azure Boards work item(s):

• Bug 5589

[DVAlexHiggs]

Thanks for this report. At this time, I'm closing this issue due to two reasons:

• We do not officially support Trino at the moment, so you will get unpredictable behaviour on Trino with AutomateDV. Please see our supported platforms here
• Our custom materialisations (which you are using here) are deprecated and no longer receiving updates.

If you wish to continue using the custom materialisation, please see here for how to add support for the correct cast approach yourself, however I strongly recommend against using these materialisations.

Please re-open this if necessary in the future. Thank you!

[igorvoltaic]

Well, that was quick! Thanks for the response. I still believe correct cast should be used piece instead of hardcoding that:
TO_TIMESTAMP({{ timestamp_field }}) -> cast({{ timestamp_field }} as {{ dbt.type_timestamp() }})

[DVAlexHiggs]

Well, that was quick! Thanks for the response. I still believe correct cast should be used piece instead of hardcoding that:
TO_TIMESTAMP({{ timestamp_field }}) -> cast({{ timestamp_field }} as {{ dbt.type_timestamp() }})

In AutomateDV, the default is used by Snowflake and we have platform specific implementations as needed, so the code you linked is correct for the Snowflake version, hence it's hard-coded. We were planning to change the implementation (if it had not been deprecated) to use your suggested approach of delegating the casting to a cross-platform macro.

The current approach of using Snowflake as the default can be confusing. We are going to be changing this in future versions, where the default will raise an unimplemented error/warning instead.