type_string should be varchar

[gerbenoostra]

dbt_utils.type_string() is implemented as

{%- macro type_string() -%}
  {{ return(adapter.dispatch('type_string', 'dbt_utils')()) }}
{%- endmacro -%}

but returns 'string'. When running cast('constant', string) Athena throws: Unknown type: string. You may need to manually clean the data at location ....

When used for typecasts, VARCHAR is a more safe target type.

[gerbenoostra]

Found a workaround (or the only solution?):

Specify a macro in your own project:

-- project/macros/athena.sql
{%- macro athena__type_string() -%}
    varchar
{%- endmacro -%}

Then override the adapter.dispatch from dbt_utils to first look into your project:

-- project/dbt_project.yml

name: 'project_name'

# Overriding the dispatch method to first look in our own package
# As explained in https://docs.getdbt.com/reference/dbt-jinja-functions/dispatch#overriding-package-macros
dispatch:
  - macro_namespace: dbt_utils
    search_order: ['project_name', 'dbt_utils']