[Bug] Support complex Array data types for macro get_columns_in_relation

[jdistler]

Is this a new bug?

• I believe this is a new bug
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

If a given column is a complex ARRAY type such as ARRAY(VARCHAR(16777216)), the macro get_columns_in_relation raises an error.

Expected Behavior

We can better handle this case to not raise an error.

Steps To Reproduce

Do a dbt run on a model which calls adapter.get_columns_in_relation() on a table with a column of type ARRAY(VARCHAR(16777216)).

Relevant log output

Could not interpret data_type "ARRAY(VARCHAR(16777216))": could not convert "VARCHAR(16777216" to an integer

Environment

- OS:
- Python: 3.11.4
- dbt-adapters: 1.6.1

Additional Context

Relevant code that can more gracefully handle this case -

https://github.com/dbt-labs/dbt-adapters/blob/v1.6.1/dbt/adapters/base/column.py#L142-L146

[jdistler]

Note the similarities with this other issue, in general this area of the logic is very brittle and should be made more flexible -

dbt-labs/dbt-snowflake#1098

[amychen1776]

Hello @jdistler - are you coming across this on Snowflake?

[jamesmorris23]

Hi all – 

I'm running into this currently while attempting to build an incremental model with vector embeddings for use in a simple RAG pipeline in Snowflake. Is there any update on prioritization of a fix here or workaround in the short term. It's untenable and wasteful to not run models like these in an incremental fashion.

Thanks in advance! :)

[brandonpeebles]

Also seeing this issue with Snowflake. It's blocking us from being able to add data tests to sources which contain any field of either Array or Object type. Even if I add a test to completely different field on the source table, I'll still get the same error:

Could not interpret data_type "ARRAY(VARCHAR(16777216))": could not convert "VARCHAR(16777216" to an integer