[ADAP-953] [Bug] Materialized Views not correctly dropping

[SpiceyC]

Is this a new bug in dbt-redshift?

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

Current Behavior

When running (or building) a materialized_view model a first time creates correctly, running the same view a second time with no changes generates an error:

"cms_mv_test" is not a view

indeed, when looking at the target/compiled code for the second dbt i can see this:

    drop view if exists "<db>"."<schema>"."cms_mv_test" cascade;
    create materialized view "<db>"."<schema>"."cms_mv_test"

the database is correctly returning an error as it expects this first line to read:

    drop materialized view if exists "<db>"."<schema>"."cms_mv_test";

I do notice the files dbt/include/redshift/macros/relations/view/drop.sql and dbt/include/redshift/macros/relations/materialized_view/drop.sql appear correct (to my untrained eye) so presumably this some mapping error? Perhaps in the dbt-core layer?

i used the following config:

{{
  config(
    materialized = 'materialized_view',
    on_configuration_change = 'apply',
    auto_refresh = True,
    schema = 'dbt_datarunner'
  )
}}

but this behaviour was true for all values of on_configuration_change and auto_refresh, and regardless of if i made a change to the model file or not, or presence of the --full-refresh flag.

Expected Behavior

To cite docs here for convenience:

Materialized views are implemented following this "drop through" life cycle:

1. If an object does not exist, create a materialized view
2. If an object exists, other than a materialized view, that object is dropped and replaced with a materialized view
3. If --full-refresh is supplied, replace the materialized view regardless of changes and the on_configuration_change setting
4. If there are no configuration changes, refresh the materialized view
5. At this point there are configuration changes, proceed according to the on_configuration_change setting

https://docs.getdbt.com/docs/build/materializations

is relevance here is step 2, following a second dbt run should complete without error, by running the following sql:

    drop materialized view if exists "<db>"."<schema>"."cms_mv_test";
    create materialized view "<db>"."<schema>"."cms_mv_test"

Steps To Reproduce

1. create basic MV under path models/demo/cms_mv_test.sql:

{{
  config(
    materialized = 'materialized_view',
    on_configuration_change = 'apply',
    auto_refresh = True,
    schema = 'dbt_datarunner'
  )
}}

SELECT
	id
	,name
	,surname
FROM demo_table
WHERE
    --<some filters>
GROUP BY 1, 2, 3

2. run following command:

dbt build --select models/demo/cms_mv_test.sql

3. rerun the command

dbt build --select models/demo/cms_mv_test.sql

Relevant log output

$ dbt build --select models/demo/cms_mv_test.sql --full-refresh
10:29:27  Running with dbt=1.6.6
10:29:27  Registered adapter: redshift=1.6.2
10:29:27  Found 48 models, 52 tests, 44 seeds, 21 sources, 0 exposures, 0 metrics, 549 macros, 0 groups, 0 semantic models
10:29:27  
10:29:32  Concurrency: 4 threads (target='<profile>')
10:29:32  
10:29:32  1 of 1 START sql materialized_view model <schema>.cms_mv_test ............ [RUN]
10:29:33  1 of 1 ERROR creating sql materialized_view model <schema>.cms_mv_test ... [ERROR in 0.84s]
10:29:34  
10:29:34  Finished running 1 materialized_view model in 0 hours 0 minutes and 6.71 seconds (6.71s).
10:29:34  
10:29:34  Completed with 1 error and 0 warnings:
10:29:34  
10:29:34    Database Error in model cms_mv_test (models/demo/cms_mv_test.sql)
  "cms_mv_test" is not a view
  compiled Code at target/run/redshift/models/demo/cms_mv_test.sql
10:29:34

Environment

- OS: MacOS Monterey 12.6
- Python: 3.11.5
- dbt-core: 1.6.6
- dbt-redshift: 1.6.6

Additional Context

Disclaimer: i am aware of the existing issues #621 and #565 i think this is technically different hence why i'm raising it. I won't take issue if this is rolled into those as i expect the required code changes might be the same.

Similarly if this is a user error i'm happy to humbly walk away with a learning on how to work this correctly.

[SpiceyC]

ignore me i believe this is a dupe of CT-3229, sorry for any confusion