Storing test failures fails when having a nvarchar(max) column

[tkirschke]

When enabling "storing test failures", dbt creates tables for each test execution. Additionally it creates a clustered columnstore index on this table.

When having a column with the datatype nvarchar(max), SQL Server throws this error:

('42000', "[42000] [FreeTDS][SQL Server]The statement failed. Column '<col_with_nvarchar(max)>' has a data type that cannot participate in a columnstore index. Omit column '<col_with_nvarchar(max)>'. (35343) (SQLMoreResults)")

In the background, I found that this code is executed:

    USE [database];
    if object_id ('"dbt_test__audit"."not_null_<tablename>_<columnname>_temp_view"','V') is not null
        begin
            drop view "dbt_test__audit"."not_null_<tablename>_<columnname>_temp_view"
        end
-- add columnstore index
               use [database];
  if EXISTS (
        SELECT *
        FROM sys.indexes with (nolock)
        WHERE name = 'dbt_test__audit_not_null_<tablename>_<columnname>_cci'
        AND object_id=object_id('dbt_test__audit_not_null_<tablename>_<columnname>')
    )
  DROP index "dbt_test__audit"."not_null_<tablename>_<columnname>".dbt_test__audit_not_null_<tablename>_<columnname>_cci
  CREATE CLUSTERED COLUMNSTORE INDEX dbt_test__audit_not_null_<tablename>_<columnname>_cci
    ON "dbt_test__audit"."not_null_<tablename>_<columnname>"

Haven't found the part of the dbt-sqlserver code where this CCI is generated. But IMO this index could be deactivated completely, or by any configuration option.