dbt test failing on Azure SQL without columnstore supported service tier

[infused-kim]

Hi,

I just upgraded from dbt 0.19.0 to dbt-core 1.0.0 and dbt-sqlserver 1.0.0.

Most things seemed to work well, but then I tried to run dbt test on a DB that was on the S0 Azure SQL service tier, which doesn't support columnstore indexes (which are supported on S3 and higher).

Even though my tables were built without columnstore indexes, I was getting the following error:

06:57:26  Database Error in test not_null_base_appointments_appointment_id (models/02_base/schema.yml)
06:57:26    ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'COLUMNSTORE' is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database. (40536) (SQLExecDirectW)")

In a previous issue we discussed how to disable columnstore indexes and it was suggested to add it to the dbt_project.yml:

models:
  my_project_name:
    +as_columnstore: False

Unfortunately, this didn't solve the issue, but then I realized that tests have their own config section.

So, to solve the issue you have to also add...

tests:
  my_project_name:
    +as_columnstore: False

I am raising this issue to document the solution in case someone else runs into it.

But perhaps it is worth considering whether even benefit from columnstore indexes and perhaps consider turning them off for tests.

[Elliot2718]

@infused-kim thank you for posting this as I ran into this issue after upgrading to dbt-core 0.21.1 and adding this configuration worked:

tests:
  my_project_name:
    +as_columnstore: False

And I would agree that it is likely unneeded to have columnstore indexes on tables in the dbt_test_audit schemas.

[dataders]

closing as #195 would fix this and there is a current workaround to add the following to one's dbt_project.yml

tests:
  my_project_name:
    +as_columnstore: False