1.7.2 does not include clustered columnstore indexes on SQL Server 2019 tables

[CamronBorealis]

We upgraded to the latest version of dbt-sqlserver, tables are not being created with clustered columnstore indexes anymore

[jjquist]

Same issue here. Was working in v1.4.*.

[vvvito]

We upgraded to 1.7 and also found this issue, caused our pipelines to grind to a halt trying to process the models which were now effectively heaps.

We developed a workaround - in the interim whilst we wait for the PR resolving this issue is merged.
It's a macro that's based on the original code that was running on 1.4, but we just extracted it from the source and added it to our dbt project.

Warning

Only tested on SQL Server 2016 and 2022. You might have to tweak the query to suit your environment if it's running older SQL version

• Create the below macro

{% macro create_clustered_columnstore_index() -%}
    {%- set cci_name = (this.schema ~ '_' ~ this.identifier ~ '_cci') | replace(".", "") | replace(" ", "") -%}
    {%- set relation_name = this.schema ~ '_' ~ this.identifier -%}
    {%- set full_relation = '[' ~ this.schema ~ '].[' ~ this.identifier ~ ']' -%}
    {%- set as_columnstore = config.get('as_columnstore', default = true) -%}
    {%- set materialized_as = config.get('materialized') -%}
    {% if materialized_as != 'view' and as_columnstore %}
    use [{{ this.database }}];
        -- If there is no existing clustered index or columnstore, then create it.
        IF NOT EXISTS (
            SELECT 1 
            FROM sys.indexes (NOLOCK)
            WHERE object_id = OBJECT_ID( '{{this.schema}}.{{this.identifier}}' ) 
                AND (
                        type in ( 1, 5 )
                    OR  name = '{{cci_name}}'
                )
        )
        BEGIN
            --DROP INDEX IF EXISTS {{full_relation}}.{{cci_name}}
            CREATE CLUSTERED COLUMNSTORE INDEX {{cci_name}} ON {{full_relation}}
        END
    {% endif%}
{% endmacro %}

• within your dbt_project, where you define your folder structure, add a +post-hook key referencing the macro

. . .
models:
  my_project
    +materialized: table
    core:
      +schema: dim
      +post-hook: 
        - "{{ create_clustered_columnstore_index () }}"
        - . . .

[CamronBorealis]

This has been fixed in 1.8.0rc1