How to get column data type or mode in dbt using jinja

Question

I'm trying to write a macro in DBT to unpivot a table (I know there are macros available and I can use SQL to do this but I want to write the macro).

Is there a way I can get a column’s data type/mode in dbt? I’m trying to write a macro where if a column is an array it should be treated differently. Something like this:

{%- set cols = adapter.get_columns_in_relation(table) %}
{% for col in cols %}

    {{col.name}} AS 'field_name',

    {% if col.mode != 'REPEATED' %}
        CAST({{ col.column }} AS STRING) AS 'value'
    {% else %}
        ARRAY_TO_STRING({{ col.column }}) AS 'value'
    {% endif %}

{% endfor %}

I looked at this page but I’m not sure what the return type is and there’s something wrong with the code here

Answer 1

According to dbt docs, the Column class has data_type as a property. BigQuery columns have mode as a property which can be used to identify array columns. Once you get the column objects you can simply do:

{%- set cols = adapter.get_columns_in_relation(table) %}
{% for col in cols %}
    
    {% if col.mode.lower() == 'repeated' %}

        ARRAY_TO_STRING({{col.column}}) AS 'value'

    {%else%}

        CAST({{col.column}} AS STRING) AS 'value'

    {%endif%}

{%endfor%}