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.

[joshmarkovic]

Thanks for the issue. This is the expected interaction between two SQL Server behaviors: dbt-sqlserver builds tables with a clustered columnstore index (CCI) by default (as_columnstore: true), and SQL Server does not permit (n)varchar(max)/LOB columns to participate in a columnstore index. When test failure storage creates an audit table containing such a column (dbt's STRING maps to VARCHAR(MAX)), the CCI build fails.

You can resolve this today by disabling the CCI on the affected resources, which is exactly option 2 from your request. For test failure storage, set it project-wide in dbt_project.yml:

data_tests:
+store_failures: true
+as_columnstore: false

Or per model:

{{ config(materialized="table", as_columnstore=false) }}

@axellpadilla, I believe we can close this.

[axellpadilla]

@joshmarkovic yes, maybe there could be a follow up to #702 where this fails before the engine and suggest to not use columnstore or fix the typing to use it