Repository: dbt-labs/dbt-redshift (python)

Issue:
[Bug] Materialized view config change identification not working with multiple sort keys
### Is this a new bug in dbt-redshift?

- [X] I believe this is a new bug in dbt-redshift
- [X] I have searched the existing issues, and I could not find an existing issue for this bug

### Current Behavior

I think this is a continuation of #621 as it's almost the same really but there was a note that says that that issue is 7 months old and to consider opening up another issue.

### Expected Behavior

A config unchanged mv should not be identified as "config changed".

### Steps To Reproduce

1. Make sure to delete the MV first.

```sql
drop materialized view if exists dev.public.mv_double_sort
```

2. Add an mv to the project with more than 1 sort key:

```sql
-- models/mv_double_sort.sql
{{
    config(
        materialized = 'materialized_view',
        dist = 'b',
        sort = ['b', 'a'],
        on_configuration_change = 'fail'
    )
}}

select a, b, c from foo
```

3. Initial build:

```sh
$ dbt --debug build -s my_double_sort

03:08:31  1 of 1 START sql materialized_view model public.mv_double_sort ................. [RUN]
03:08:31  Re-using an available connection from the pool (formerly list_dev_public, now model.my_dbt_project.mv_double_sort)
03:08:31  Began compiling node model.my_dbt_project.mv_double_sort
03:08:31  Writing injected SQL for node "model.my_dbt_project.mv_double_sort"
03:08:31  Began executing node model.my_dbt_project.mv_double_sort
03:08:31  Writing runtime sql for node "model.my_dbt_project.mv_double_sort"
03:08:31  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:31  On model.my_dbt_project.mv_double_sort: BEGIN
03:08:31  Opening a new connection, currently in state closed
03:08:31  Redshift adapter: Establishing connection using ssl with `sslmode` set to 'prefer'.To connect without ssl, set `sslmode` to 'disable'.
03:08:31  Redshift adapter: Connecting to redshift with username/password based auth...
03:08:34  SQL status: SUCCESS in 2.0 seconds
03:08:34  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:34  On model.my_dbt_project.mv_double_sort: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.mv_double_sort"} */

        create materialized view "dev"."public"."mv_double_sort"
        backup yes
        diststyle key
        distkey (b)
        sortkey (b,a)
        auto refresh no
    as (
        select a, b, c from foo
    )
03:08:35  SQL status: SUCCESS in 1.0 seconds
03:08:35  On model.my_dbt_project.mv_double_sort: COMMIT
03:08:35  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:35  On model.my_dbt_project.mv_double_sort: COMMIT
03:08:36  SQL status: SUCCESS in 1.0 seconds
03:08:36  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:36  On model.my_dbt_project.mv_double_sort: BEGIN
03:08:37  SQL status: SUCCESS in 0.0 seconds
03:08:37  Applying DROP to: "dev"."public"."mv_double_sort__dbt_backup"
03:08:37  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:37  On model.my_dbt_project.mv_double_sort: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.mv_double_sort"} */
drop materialized view if exists "dev"."public"."mv_double_sort__dbt_backup"
03:08:37  SQL status: SUCCESS in 0.0 seconds
03:08:37  On model.my_dbt_project.mv_double_sort: COMMIT
03:08:37  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:37  On model.my_dbt_project.mv_double_sort: COMMIT
03:08:38  SQL status: SUCCESS in 1.0 seconds
03:08:38  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:38  On model.my_dbt_project.mv_double_sort: BEGIN
03:08:38  SQL status: SUCCESS in 0.0 seconds
03:08:38  On model.my_dbt_project.mv_double_sort: COMMIT
03:08:38  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:38  On model.my_dbt_project.mv_double_sort: COMMIT
03:08:38  SQL status: SUCCESS in 0.0 seconds
03:08:38  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:38  On model.my_dbt_project.mv_double_sort: BEGIN
03:08:39  SQL status: SUCCESS in 0.0 seconds
03:08:39  Applying DROP to: "dev"."public"."mv_double_sort__dbt_tmp"
03:08:39  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:39  On model.my_dbt_project.mv_double_sort: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.mv_double_sort"} */
drop materialized view if exists "dev"."public"."mv_double_sort__dbt_tmp"
03:08:39  SQL status: SUCCESS in 1.0 seconds
03:08:39  On model.my_dbt_project.mv_double_sort: COMMIT
03:08:39  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:39  On model.my_dbt_project.mv_double_sort: COMMIT
03:08:40  SQL status: SUCCESS in 1.0 seconds
03:08:40  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:08:40  On model.my_dbt_project.mv_double_sort: BEGIN
03:08:40  SQL status: SUCCESS in 0.0 seconds
03:08:40  On model.my_dbt_project.mv_double_sort: ROLLBACK
03:08:40  On model.my_dbt_project.mv_double_sort: Close
03:08:40  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '192109ef-68b1-4a97-a11c-6bfe02667917', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x106393b50>]}
03:08:40  1 of 1 OK created sql materialized_view model public.mv_double_sort ............ [SUCCESS in 8.90s]
```

4. Subsequent build:

```sh
03:09:20  Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'start', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x106007190>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10605fcd0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10607c550>]}
03:09:20  Running with dbt=1.8.1
03:09:20  running dbt with arguments {'printer_width': '80', 'indirect_selection': 'eager', 'write_json': 'True', 'log_cache_events': 'False', 'partial_parse': 'True', 'cache_selected_only': 'False', 'profiles_dir': '/Users/jeremy/.dbt', 'debug': 'True', 'version_check': 'True', 'log_path': '/Users/jeremy/git/dbt-basic/logs', 'fail_fast': 'False', 'warn_error': 'None', 'use_colors': 'True', 'use_experimental_parser': 'False', 'no_print': 'None', 'quiet': 'False', 'warn_error_options': 'WarnErrorOptions(include=[], exclude=[])', 'invocation_command': 'dbt --debug build -s mv_double_sort', 'log_format': 'default', 'introspect': 'True', 'target_path': 'None', 'static_parser': 'True', 'send_anonymous_usage_stats': 'True'}
03:09:20  Redshift adapter: Setting redshift_connector to ERROR
03:09:20  Redshift adapter: Setting redshift_connector.core to ERROR
03:09:20  Sending event: {'category': 'dbt', 'action': 'project_id', 'label': '44212e74-9a36-497e-bd19-b512e1ed7892', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10605fd50>]}
03:09:20  Sending event: {'category': 'dbt', 'action': 'adapter_info', 'label': '44212e74-9a36-497e-bd19-b512e1ed7892', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10434dfd0>]}
03:09:20  Registered adapter: redshift=1.8.0
03:09:20  checksum: ebc509fb14e151ea268e89b60fd5abf912ed99129f87516462718fa2f14f4838, vars: {}, profile: , target: , version: 1.8.1
03:09:21  Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
03:09:21  Partial parsing enabled, no changes found, skipping parsing
03:09:21  Sending event: {'category': 'dbt', 'action': 'load_project', 'label': '44212e74-9a36-497e-bd19-b512e1ed7892', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x106ba48d0>]}
03:09:21  Sending event: {'category': 'dbt', 'action': 'resource_counts', 'label': '44212e74-9a36-497e-bd19-b512e1ed7892', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1070c8150>]}
03:09:21  Found 3 models, 593 macros
03:09:21  
03:09:21  Acquiring new redshift connection 'master'
03:09:21  Acquiring new redshift connection 'list_dev'
03:09:21  Using redshift connection "list_dev"
03:09:21  On list_dev: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "connection_name": "list_dev"} */

    select distinct nspname from pg_namespace
03:09:21  Opening a new connection, currently in state init
03:09:21  Redshift adapter: Establishing connection using ssl with `sslmode` set to 'prefer'.To connect without ssl, set `sslmode` to 'disable'.
03:09:21  Redshift adapter: Connecting to redshift with username/password based auth...
03:09:23  SQL status: SUCCESS in 2.0 seconds
03:09:23  On list_dev: Close
03:09:23  Re-using an available connection from the pool (formerly list_dev, now list_dev_public)
03:09:23  Using redshift connection "list_dev_public"
03:09:23  On list_dev_public: BEGIN
03:09:23  Opening a new connection, currently in state closed
03:09:23  Redshift adapter: Establishing connection using ssl with `sslmode` set to 'prefer'.To connect without ssl, set `sslmode` to 'disable'.
03:09:23  Redshift adapter: Connecting to redshift with username/password based auth...
03:09:25  SQL status: SUCCESS in 2.0 seconds
03:09:25  Using redshift connection "list_dev_public"
03:09:25  On list_dev_public: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "connection_name": "list_dev_public"} */
select
        table_catalog as database,
        table_name as name,
        table_schema as schema,
        'table' as type
    from information_schema.tables
    where table_schema ilike 'public'
    and table_type = 'BASE TABLE'
    union all
    select
      table_catalog as database,
      table_name as name,
      table_schema as schema,
      case
        when view_definition ilike '%create materialized view%'
          then 'materialized_view'
        else 'view'
      end as type
    from information_schema.views
    where table_schema ilike 'public'
03:09:26  SQL status: SUCCESS in 1.0 seconds
03:09:26  On list_dev_public: ROLLBACK
03:09:26  On list_dev_public: Close
03:09:26  Using redshift connection "master"
03:09:26  On master: BEGIN
03:09:26  Opening a new connection, currently in state init
03:09:26  Redshift adapter: Establishing connection using ssl with `sslmode` set to 'prefer'.To connect without ssl, set `sslmode` to 'disable'.
03:09:26  Redshift adapter: Connecting to redshift with username/password based auth...
03:09:29  SQL status: SUCCESS in 2.0 seconds
03:09:29  Using redshift connection "master"
03:09:29  On master: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "connection_name": "master"} */
with
    relation as (
        select
            pg_class.oid as relation_id,
            pg_class.relname as relation_name,
            pg_class.relnamespace as schema_id,
            pg_namespace.nspname as schema_name,
            pg_class.relkind as relation_type
        from pg_class
        join pg_namespace
          on pg_class.relnamespace = pg_namespace.oid
        where pg_namespace.nspname != 'information_schema'
          and pg_namespace.nspname not like 'pg\_%'
    ),
    dependency as (
        select distinct
            coalesce(pg_rewrite.ev_class, pg_depend.objid) as dep_relation_id,
            pg_depend.refobjid as ref_relation_id,
            pg_depend.refclassid as ref_class_id
        from pg_depend
        left join pg_rewrite
          on pg_depend.objid = pg_rewrite.oid
        where coalesce(pg_rewrite.ev_class, pg_depend.objid) != pg_depend.refobjid
    )

select distinct
    dep.schema_name as dependent_schema,
    dep.relation_name as dependent_name,
    ref.schema_name as referenced_schema,
    ref.relation_name as referenced_name
from dependency
join relation ref
    on dependency.ref_relation_id = ref.relation_id
join relation dep
    on dependency.dep_relation_id = dep.relation_id
03:09:29  SQL status: SUCCESS in 1.0 seconds
03:09:29  Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': '44212e74-9a36-497e-bd19-b512e1ed7892', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10743dd10>]}
03:09:29  On master: ROLLBACK
03:09:30  Using redshift connection "master"
03:09:30  On master: BEGIN
03:09:30  SQL status: SUCCESS in 0.0 seconds
03:09:30  On master: COMMIT
03:09:30  Using redshift connection "master"
03:09:30  On master: COMMIT
03:09:31  SQL status: SUCCESS in 1.0 seconds
03:09:31  On master: Close
03:09:31  Concurrency: 1 threads (target='rs')
03:09:31  
03:09:31  Began running node model.my_dbt_project.mv_double_sort
03:09:31  1 of 1 START sql materialized_view model public.mv_double_sort ................. [RUN]
03:09:31  Re-using an available connection from the pool (formerly list_dev_public, now model.my_dbt_project.mv_double_sort)
03:09:31  Began compiling node model.my_dbt_project.mv_double_sort
03:09:31  Writing injected SQL for node "model.my_dbt_project.mv_double_sort"
03:09:31  Began executing node model.my_dbt_project.mv_double_sort
03:09:31  Determining configuration changes on: "dev"."public"."mv_double_sort"
03:09:31  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:09:31  On model.my_dbt_project.mv_double_sort: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.mv_double_sort"} */

    select
            tb.database,
            tb.schema,
            tb.table,
            tb.diststyle,
            tb.sortkey1,
            mv.autorefresh
        from svv_table_info tb
        -- svv_mv_info is queryable by Redshift Serverless, but stv_mv_info is not
        left join svv_mv_info mv
            on mv.database_name = tb.database
            and mv.schema_name = tb.schema
            and mv.name = tb.table
        where tb.table ilike 'mv_double_sort'
        and tb.schema ilike 'public'
        and tb.database ilike 'dev'
03:09:31  Opening a new connection, currently in state closed
03:09:31  Redshift adapter: Establishing connection using ssl with `sslmode` set to 'prefer'.To connect without ssl, set `sslmode` to 'disable'.
03:09:31  Redshift adapter: Connecting to redshift with username/password based auth...
03:09:34  SQL status: SUCCESS in 3.0 seconds
03:09:34  Using redshift connection "model.my_dbt_project.mv_double_sort"
03:09:34  On model.my_dbt_project.mv_double_sort: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.mv_double_sort"} */

    select
            vw.definition
        from pg_views vw
        where vw.viewname = 'mv_double_sort'
        and vw.schemaname = 'public'
        and vw.definition ilike '%create materialized view%'
03:09:34  SQL status: SUCCESS in 1.0 seconds
03:09:34  On model.my_dbt_project.mv_double_sort: Close
03:09:34  FailFast Error in model mv_double_sort (models/mv_double_sort.sql)
  Configuration changes were identified and `on_configuration_change` was set to `fail` for `"dev"."public"."mv_double_sort"`
03:09:34  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '44212e74-9a36-497e-bd19-b512e1ed7892', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x104360e10>]}
03:09:34  1 of 1 ERROR creating sql materialized_view model public.mv_double_sort ........ [ERROR in 3.70s]
03:09:34  Finished running node model.my_dbt_project.mv_double_sort
03:09:34  Using redshift connection "master"
03:09:34  On master: BEGIN
03:09:34  Opening a new connection, currently in state closed
03:09:34  Redshift adapter: Establishing connection using ssl with `sslmode` set to 'prefer'.To connect without ssl, set `sslmode` to 'disable'.
03:09:34  Redshift adapter: Connecting to redshift with username/password based auth...
03:09:37  SQL status: SUCCESS in 2.0 seconds
03:09:37  On master: COMMIT
03:09:37  Using redshift connection "master"
03:09:37  On master: COMMIT
03:09:37  SQL status: SUCCESS in 0.0 seconds
03:09:37  On master: Close
03:09:37  Connection 'master' was properly closed.
03:09:37  Connection 'model.my_dbt_project.mv_double_sort' was properly closed.
03:09:37  
03:09:37  Finished running 1 materialized view model in 0 hours 0 minutes and 16.73 seconds (16.73s).
03:09:37  Command end result
03:09:37  
03:09:37  Completed with 1 error and 0 warnings:
03:09:37  
03:09:37    FailFast Error in model mv_double_sort (models/mv_double_sort.sql)
  Configuration changes were identified and `on_configuration_change` was set to `fail` for `"dev"."public"."mv_double_sort"`
```

### Relevant log output

_No response_

### Environment

```markdown
- OS: macOS
- Python: 3.11
- dbt-core: 1.8.1
- dbt-redshift: 1.8.0
```


### Additional Context

* If I did not use `on_configuration_change = 'fail'` - the outcome would simply be that we would to a drop and create of the mv - as opposed to `refresh` which is what is expected here.
* If the model only had a single sort key:
```sql
-- models/mv_single_sort.sql
{{
    config(
        materialized = 'materialized_view',
        dist = 'b',
        sort = ['b'],
        on_configuration_change = 'fail'
    )
}}

select a, b, c from foo
```
Then there is no issue and the subsequent run refreshes our mv as expected:
```sh
03:16:21  On model.my_dbt_project.mv_single_sort: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.mv_single_sort"} */

        refresh materialized view "dev"."public"."mv_single_sort"
03:16:21  SQL status: SUCCESS in 1.0 seconds
```

Pull request:
Fix config change detection not working for multiple sortkey in materialized views
resolves #838

<!---
  Include the number of the issue addressed by this PR above if applicable.
  PRs for code changes without an associated issue *will not be merged*.
  See CONTRIBUTING.md for more information.

  Include the number of the docs issue that was opened for this PR. If
  this change has no user-facing implications, "N/A" suffices instead. New
  docs tickets can be created by clicking the link above or by going to
  https://github.com/dbt-labs/docs.getdbt.com/issues/new/choose.
-->

### Problem

Having multiple sort keys and without changes between the model and the Materialized View, the change check between model and view ends up returning as a result that it has changes. This is because svv_table_info.sortkey1 is being used instead of use the list of sort key that the Materialized View have

### Solution

We can get the list of SortKeys using the `mv_tbl` related table

### Checklist

- [x] I have read [the contributing guide](https://github.com/dbt-labs/dbt-core/blob/main/CONTRIBUTING.md) and understand what's expected of me
- [x] I have run this code in development and it appears to resolve the stated issue
- [x] This PR includes tests, or tests are not required/relevant for this PR
- [x] This PR has no interface changes (e.g. macros, cli, logs, json artifacts, config files, adapter interface, etc) or this PR has already received feedback and approval from Product or DX


### Steps To Reproduce:
1. Make sure to delete the MV first.
`drop materialized view if exists dev.public.mv_double_sort`

2. Add an mv to the project with more than 1 sort key:
```
-- models/mv_double_sort.sql
{{
    config(
        materialized = 'materialized_view',
        dist = 'b',
        sort = ['b', 'a'],
        on_configuration_change = 'fail'
    )
}}

select a, b, c from foo

```

3. `$ dbt --debug build -s my_double_sort` Should create the materialized view 
4. Run again `$ dbt --debug build -s my_double_sort` and should refresh the materialized view instead of drop and recreate the view

Patch:
diff --git a/.changes/unreleased/Fixes-20240610-104114.yaml b/.changes/unreleased/Fixes-20240610-104114.yaml
new file mode 100644
index 00000000..d220288f
--- /dev/null
+++ b/.changes/unreleased/Fixes-20240610-104114.yaml
@@ -0,0 +1,6 @@
+kind: Fixes
+body: Fix config change detection not working for multiple sortkey in materialized views
+time: 2024-06-10T10:41:14.721411-03:00
+custom:
+    Author: lvitti
+    Issue: "838"
diff --git a/dbt/adapters/redshift/relation_configs/materialized_view.py b/dbt/adapters/redshift/relation_configs/materialized_view.py
index 48c04b55..a01185f2 100644
--- a/dbt/adapters/redshift/relation_configs/materialized_view.py
+++ b/dbt/adapters/redshift/relation_configs/materialized_view.py
@@ -169,6 +169,13 @@ class RedshiftMaterializedViewConfig(RedshiftRelationConfigBase, RelationConfigV
                     "query": agate.Table(
                         agate.Row({"definition": "<query>")}
                     ),
+                    "columns": agate.Table(
+                        agate.Row({
+                            "column": "<column_name>",
+                            "sort_key_position": <int>,
+                            "is_dist_key: any(true, false),
+                        })
+                    ),
                 }
 
                 Additional columns in either value is fine, as long as `sortkey` and `sortstyle` are available.
@@ -199,11 +206,12 @@ class RedshiftMaterializedViewConfig(RedshiftRelationConfigBase, RelationConfigV
                 {"dist": RedshiftDistConfig.parse_relation_results(materialized_view)}
             )
 
-        # TODO: this only shows the first column in the sort key
-        if materialized_view.get("sortkey1"):
-            config_dict.update(
-                {"sort": RedshiftSortConfig.parse_relation_results(materialized_view)}
-            )
+        if columns := relation_results.get("columns"):
+            sort_columns = [row for row in columns.rows if row.get("sort_key_position", 0) > 0]
+            if sort_columns:
+                config_dict.update(
+                    {"sort": RedshiftSortConfig.parse_relation_results(sort_columns)}
+                )
 
         return config_dict
 
diff --git a/dbt/adapters/redshift/relation_configs/sort.py b/dbt/adapters/redshift/relation_configs/sort.py
index be5b3627..f38d5a1e 100644
--- a/dbt/adapters/redshift/relation_configs/sort.py
+++ b/dbt/adapters/redshift/relation_configs/sort.py
@@ -1,6 +1,6 @@
 from dataclasses import dataclass
 from dbt.adapters.contracts.relation import RelationConfig
-from typing import Optional, FrozenSet, Set, Dict, Any, TYPE_CHECKING
+from typing import Optional, Set, Dict, Any, TYPE_CHECKING, Tuple
 
 from dbt.adapters.relation_configs import (
     RelationConfigChange,
@@ -46,7 +46,7 @@ class RedshiftSortConfig(RedshiftRelationConfigBase, RelationConfigValidationMix
     """
 
     sortstyle: Optional[RedshiftSortStyle] = None
-    sortkey: Optional[FrozenSet[str]] = None
+    sortkey: Optional[Tuple[str]] = None
 
     def __post_init__(self):
         # maintains `frozen=True` while allowing for a variable default on `sort_type`
@@ -103,7 +103,7 @@ class RedshiftSortConfig(RedshiftRelationConfigBase, RelationConfigValidationMix
     def from_dict(cls, config_dict) -> Self:
         kwargs_dict = {
             "sortstyle": config_dict.get("sortstyle"),
-            "sortkey": frozenset(column for column in config_dict.get("sortkey", {})),
+            "sortkey": tuple(column for column in config_dict.get("sortkey", {})),
         }
         sort: Self = super().from_dict(kwargs_dict)  # type: ignore
         return sort  # type: ignore
@@ -133,12 +133,12 @@ class RedshiftSortConfig(RedshiftRelationConfigBase, RelationConfigValidationMix
             if isinstance(sortkey, str):
                 sortkey = [sortkey]
 
-            config_dict.update({"sortkey": set(sortkey)})
+            config_dict.update({"sortkey": tuple(sortkey)})
 
         return config_dict
 
     @classmethod
-    def parse_relation_results(cls, relation_results_entry: "agate.Row") -> dict:
+    def parse_relation_results(cls, relation_results_entry: "agate.MappedSequence") -> dict:
         """
         Translate agate objects from the database into a standard dictionary.
 
@@ -147,19 +147,26 @@ class RedshiftSortConfig(RedshiftRelationConfigBase, RelationConfigValidationMix
             Processing of `sortstyle` has been omitted here, which means it's the default (compound).
 
         Args:
-            relation_results_entry: the description of the sortkey and sortstyle from the database in this format:
-
-                agate.Row({
-                    ...,
-                    "sortkey1": "<column_name>",
-                    ...
-                })
+            relation_results_entry: The list of rows that contains the sortkey in this format:
+                [
+                    agate.Row({
+                        ...,
+                        "column": "<column_name>",
+                        "sort_key_position": <int>,
+                        ...
+                    }),
+                ]
 
         Returns: a standard dictionary describing this `RedshiftSortConfig` instance
         """
-        if sortkey := relation_results_entry.get("sortkey1"):
-            return {"sortkey": {sortkey}}
-        return {}
+        sort_config = []
+
+        sorted_columns = sorted(relation_results_entry, key=lambda x: x["sort_key_position"])
+        for column in sorted_columns:
+            if column.get("sort_key_position"):
+                sort_config.append(column.get("column"))
+
+        return {"sortkey": sort_config}
 
 
 @dataclass(frozen=True, eq=True, unsafe_hash=True)
diff --git a/dbt/include/redshift/macros/relations/materialized_view/describe.sql b/dbt/include/redshift/macros/relations/materialized_view/describe.sql
index a56d0756..3f038b9a 100644
--- a/dbt/include/redshift/macros/relations/materialized_view/describe.sql
+++ b/dbt/include/redshift/macros/relations/materialized_view/describe.sql
@@ -24,6 +24,20 @@
     {%- endset %}
     {% set _materialized_view = run_query(_materialized_view_sql) %}
 
+    {%- set _column_descriptor_sql -%}
+        SELECT
+            a.attname as column,
+            a.attisdistkey as is_dist_key,
+            a.attsortkeyord as sort_key_position
+        FROM pg_class c
+        JOIN pg_namespace n ON n.oid = c.relnamespace
+        JOIN pg_attribute a ON a.attrelid = c.oid
+        WHERE
+            n.nspname ilike '{{ relation.schema }}'
+            AND c.relname LIKE 'mv_tbl__{{ relation.identifier }}__%'
+    {%- endset %}
+    {% set _column_descriptor = run_query(_column_descriptor_sql) %}
+
     {%- set _query_sql -%}
         select
             vw.definition
@@ -34,6 +48,10 @@
     {%- endset %}
     {% set _query = run_query(_query_sql) %}
 
-    {% do return({'materialized_view': _materialized_view, 'query': _query}) %}
+    {% do return({
+       'materialized_view': _materialized_view,
+       'query': _query,
+       'columns': _column_descriptor,
+    })%}
 
 {% endmacro %}


Test patch:
diff --git a/tests/unit/test_materialized_view.py b/tests/unit/test_materialized_view.py
index 8e4f6ca3..322b134f 100644
--- a/tests/unit/test_materialized_view.py
+++ b/tests/unit/test_materialized_view.py
@@ -1,5 +1,6 @@
 from unittest.mock import Mock
 
+import agate
 import pytest
 
 from dbt.adapters.redshift.relation_configs import RedshiftMaterializedViewConfig
@@ -53,3 +54,57 @@ def test_redshift_materialized_view_config_throws_expected_exception_with_invali
     )
     with pytest.raises(ValueError):
         config.parse_relation_config(model_node)
+
+
+def test_redshift_materialized_view_parse_relation_results_handles_multiples_sort_key():
+    materialized_view = agate.Table.from_object(
+        [],
+        [
+            "database",
+            "schema",
+            "table",
+            "diststyle",
+            "sortkey1",
+            "autorefresh",
+        ],
+    )
+
+    column_descriptor = agate.Table.from_object(
+        [
+            {
+                "column": "my_column",
+                "is_dist_key": True,
+                "sort_key_position": 1,
+            },
+            {
+                "column": "my_column2",
+                "is_dist_key": True,
+                "sort_key_position": 2,
+            },
+            {
+                "column": "my_column5",
+                "is_dist_key": False,
+                "sort_key_position": 0,
+            },
+        ],
+    )
+
+    query = agate.Table.from_object(
+        [
+            {
+                "definition": "create materialized view my_view as (select 1 as my_column, 'value' as my_column2)"
+            }
+        ]
+    )
+
+    relation_results = {
+        "materialized_view": materialized_view,
+        "columns": column_descriptor,
+        "query": query,
+    }
+
+    config_dict = RedshiftMaterializedViewConfig.parse_relation_results(relation_results)
+
+    assert isinstance(config_dict["sort"], dict)
+    assert config_dict["sort"]["sortkey"][0] == "my_column"
+    assert config_dict["sort"]["sortkey"][1] == "my_column2"
diff --git a/tests/unit/test_relation.py b/tests/unit/test_relation.py
index 4817ab10..0985c62c 100644
--- a/tests/unit/test_relation.py
+++ b/tests/unit/test_relation.py
@@ -1,5 +1,15 @@
+from unittest.mock import Mock
+
+import agate
+import pytest
+
 from dbt.adapters.redshift.relation import RedshiftRelation
-from dbt.adapters.contracts.relation import RelationType
+from dbt.adapters.contracts.relation import (
+    RelationType,
+    RelationConfig,
+)
+
+from dbt.adapters.redshift.relation_configs.sort import RedshiftSortStyle
 
 
 def test_renameable_relation():
@@ -15,3 +25,133 @@ def test_renameable_relation():
             RelationType.Table,
         }
     )
+
+
+@pytest.fixture
+def materialized_view_without_sort_key_from_db():
+    materialized_view = agate.Table.from_object(
+        [
+            {
+                "database": "my_db",
+                "schema": "my_schema",
+                "table": "my_table",
+            }
+        ],
+    )
+
+    column_descriptor = agate.Table.from_object([])
+
+    query = agate.Table.from_object(
+        [
+            {
+                "definition": "create materialized view my_view as (select 1 as my_column, 'value' as my_column2)"
+            }
+        ]
+    )
+
+    relation_results = {
+        "materialized_view": materialized_view,
+        "columns": column_descriptor,
+        "query": query,
+    }
+    return relation_results
+
+
+@pytest.fixture
+def materialized_view_without_sort_key_config():
+    relation_config = Mock(spec=RelationConfig)
+
+    relation_config.database = "my_db"
+    relation_config.identifier = "my_table"
+    relation_config.schema = "my_schema"
+    relation_config.config = Mock()
+    relation_config.config.extra = {}
+    relation_config.config.sort = ""
+    relation_config.compiled_code = (
+        "create materialized view my_view as (select 1 as my_column, 'value' as my_column2)"
+    )
+    return relation_config
+
+
+@pytest.fixture
+def materialized_view_multiple_sort_key_from_db(materialized_view_without_sort_key_from_db):
+    materialized_view_without_sort_key_from_db["columns"] = agate.Table.from_object(
+        [
+            {
+                "column": "my_column",
+                "is_dist_key": True,
+                "sort_key_position": 1,
+            },
+            {
+                "column": "my_column2",
+                "is_dist_key": True,
+                "sort_key_position": 2,
+            },
+        ],
+    )
+    return materialized_view_without_sort_key_from_db
+
+
+@pytest.fixture
+def materialized_view_multiple_sort_key_config(materialized_view_without_sort_key_config):
+    materialized_view_without_sort_key_config.config.extra = {
+        "sort_type": RedshiftSortStyle.compound,
+        "sort": ["my_column", "my_column2"],
+    }
+
+    return materialized_view_without_sort_key_config
+
+
+def test_materialized_view_config_changeset_without_sort_key_empty_changes(
+    materialized_view_without_sort_key_from_db,
+    materialized_view_without_sort_key_config,
+):
+    change_set = RedshiftRelation.materialized_view_config_changeset(
+        materialized_view_without_sort_key_from_db,
+        materialized_view_without_sort_key_config,
+    )
+
+    assert change_set is None
+
+
+def test_materialized_view_config_changeset_multiple_sort_key_without_changes(
+    materialized_view_multiple_sort_key_from_db,
+    materialized_view_multiple_sort_key_config,
+):
+
+    change_set = RedshiftRelation.materialized_view_config_changeset(
+        materialized_view_multiple_sort_key_from_db,
+        materialized_view_multiple_sort_key_config,
+    )
+
+    assert change_set is None
+
+
+def test_materialized_view_config_changeset_multiple_sort_key_with_changes(
+    materialized_view_multiple_sort_key_from_db,
+    materialized_view_multiple_sort_key_config,
+):
+    materialized_view_multiple_sort_key_config.config.extra["sort"].append("my_column3")
+
+    change_set = RedshiftRelation.materialized_view_config_changeset(
+        materialized_view_multiple_sort_key_from_db,
+        materialized_view_multiple_sort_key_config,
+    )
+
+    assert change_set is not None
+    assert change_set.sort.context.sortkey == ("my_column", "my_column2", "my_column3")
+
+
+def test_materialized_view_config_changeset_multiple_sort_key_with_changes_in_order_column(
+    materialized_view_multiple_sort_key_from_db,
+    materialized_view_multiple_sort_key_config,
+):
+    materialized_view_multiple_sort_key_config.config.extra["sort"] = ["my_column2", "my_column"]
+
+    change_set = RedshiftRelation.materialized_view_config_changeset(
+        materialized_view_multiple_sort_key_from_db,
+        materialized_view_multiple_sort_key_config,
+    )
+
+    assert change_set is not None
+    assert change_set.sort.context.sortkey == ("my_column2", "my_column")