[Bug] Change in Defined Redshift Sort Key Sequence During Execution

[hareesh-anjane]

Is this a new bug in dbt-core?

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

Current Behavior

I am using dbt to create materialized view in Redshift along with distribution and sort keys defined. Upon creation, I noticed that sort key ordering is different than the one provided in config block or yaml file(tried both the config block and yaml file).

Adapter: dbt-redshift 1.8.1

Expected Behavior

config(
        dist="all",
        sort= ["column_1","column_2","column_3","column_4","column_5"]
    )

Expected output in dbt.log file

create materialized view "warehouse"."materialized_view_example"
        backup yes
        diststyle all
        sortkey (column_1,Column_2,column_3,Column_4,Column_5)

Steps To Reproduce

Define the sort keys and dist keys in config block or in model.yml file .

I chose to define them in config block as per dbt docs

config(
        dist="all",
        sort= ["column_1","column_2","column_3","column_4","column_5"]
    )

Refer the dbt.log file for create materialized view line and check the sequencing of sort keys:

create materialized view "warehouse"."materialized_view_example"
        backup yes
        diststyle all
        sortkey (column_5,Column_2,column_3,Column_1,Column_4)

Relevant log output

Environment

- OS: Windows 11
- Python: pyenv
- dbt: dbt-core 1.8.0

Which database adapter are you using with dbt?

redshift

Additional Context

https://docs.getdbt.com/reference/resource-configs/redshift-configs#using-sortkey-and-distkey

Tables in Amazon Redshift have two powerful optimizations to improve query performance: distkeys and sortkeys. Supplying these values as model-level configurations apply the corresponding settings in the generated CREATE TABLE DDL. Note that these settings will have no effect on models set to view or ephemeral models.

dist can have a setting of all, even, auto, or the name of a key.
sort accepts a list of sort keys, for example: ['reporting_day', 'category']. dbt will build the sort key in the same order the fields are supplied.
sort_type can have a setting of interleaved or compound. if no setting is specified, sort_type defaults to compound.