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

Issue:
[ADAP-961] [Bug] Materialized views that depend on other materialized views will fail to be refreshed due to drop not cascading
### 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

If a materialized view (parent_mv) is based on another materialized view, a refresh of the the parent view that requires a full rebuild will fail as the `drop materialized view if exists parent_mv` does not cascade

### Expected Behavior

Drop all dependent materialized views and recreate them

### Steps To Reproduce

```
--parent_mv.sql
{{ config(
    materialized='materialized_view',
    sort=['cust_id'],
    on_configuration_change = 'apply',
)
}}

SELECT
    log_date,
    cust_id,
    cust_country,
    count(request_id) as total_requests
FROM api_logs
GROUP BY 1,2,3

--child_mv.sql
{{ config(
    materialized='materialized_view',
    sort=['cust_id'],
    on_configuration_change = 'apply',
)
}}

SELECT
    log_date,
    cust_id,
    count(request_id) as total_requests
FROM {{ ref('parent_mv') }}
GROUP BY 1,2

```

`dbt run --full-refresh` 


### Relevant log output

```shell
cannot drop materialized view parent_mv because other objects depend on it
```


### Environment

```markdown
- OS:
- Python:
- dbt-core: 1.6.2
- dbt-redshift: 1.6.2
```


### Additional Context

_No response_


Discussion / hints:
Thanks for trying out materialized views and sharing your experiences @jmeiring-sc !

I wasn't able to reproduce this upon my initial attempt. Could you try out the files that I have below and help me see what I need to change to get the same error as you?

`models/my_model.sql`
```sql
{{ config(
    materialized='table',
)
}}

select 1 as id
```

`models/parent_mv.sql`
```sql
{{ config(
    materialized='materialized_view',
    on_configuration_change='apply',
)
}}

select * from {{ ref('my_model') }}
```

`models/child_mv.sql`
```sql
{{ config(
    materialized='materialized_view',
    on_configuration_change='apply',
)
}}

select * from {{ ref('parent_mv') }}
```

```
$ dbt run --full-refresh
01:43:17  Running with dbt=1.6.0
01:43:17  Registered adapter: redshift=1.6.0
01:43:17  Found 3 models, 0 sources, 0 exposures, 0 metrics, 397 macros, 0 groups, 0 semantic models
01:43:17  
01:43:20  Concurrency: 5 threads (target='redshift')
01:43:20  
01:43:20  1 of 3 START sql table model dbt_dbeatty.my_model .............................. [RUN]
01:43:22  1 of 3 OK created sql table model dbt_dbeatty.my_model ......................... [SUCCESS in 1.89s]
01:43:22  2 of 3 START sql materialized_view model dbt_dbeatty.parent_mv ................. [RUN]
01:43:24  2 of 3 OK created sql materialized_view model dbt_dbeatty.parent_mv ............ [SUCCESS in 1.95s]
01:43:24  3 of 3 START sql materialized_view model dbt_dbeatty.child_mv .................. [RUN]
01:43:26  3 of 3 OK created sql materialized_view model dbt_dbeatty.child_mv ............. [SUCCESS in 1.93s]
01:43:26  
01:43:26  Finished running 1 table model, 2 materialized_view models in 0 hours 0 minutes and 9.25 seconds (9.25s).
01:43:27  
01:43:27  Completed successfully
```
I believe that the configuration you have above will work fine as the base table `my_model` will get dropped which will cascade to the materialized view. Here is a setup that will fail however: 

```
CREATE TABLE test.test_table (
    id int
);
INSERT INTO test.test_table VALUES (1);
INSERT INTO test.test_table VALUES (2);
```


```
{{ config(
    materialized='materialized_view',
    on_configuration_change='apply',
)
}}

select * from test.test_table
```

```
{{ config(
    materialized='materialized_view',
    on_configuration_change='apply',
)
}}

select * from {{ ref('parent') }}
```

The first run will succeed, as there are no drops that need to happen. Subsequent runs will fail however:
```
[0m15:29:45 Database Error in model parent (models/test/parent.sql)
cannot drop materialized view test.parent because other objects depend on it
```
 



Thanks @jmeiring-sc, that was _exactly_ the insight we needed!

<details>

<summary>

### Reprex
</summary>

`seeds/my_seed.sql`
```csv
id
1
```

`models/parent_mv.sql`
```sql
{{ config(
    materialized='materialized_view',
    on_configuration_change='apply',
)
}}

select * from {{ ref('my_seed') }}
```

`models/child_mv.sql`
```sql
{{ config(
    materialized='materialized_view',
    on_configuration_change='apply',
)
}}

select * from {{ ref('parent_mv') }}
```

This will work:
```shell
dbt seed
dbt run
```

But then run this to get an error:
```
dbt run --full-refresh
```
or this:
```
dbt run -s parent_mv+ --full-refresh
```

Error output:
```
15:55:45  1 of 2 START sql materialized_view model dbt_dbeatty.parent_mv ................. [RUN]
15:55:46  1 of 2 ERROR creating sql materialized_view model dbt_dbeatty.parent_mv ........ [ERROR in 0.89s]
15:55:46  2 of 2 SKIP relation dbt_dbeatty.child_mv ...................................... [SKIP]
15:55:46  
15:55:46  Finished running 2 materialized_view models in 0 hours 0 minutes and 5.27 seconds (5.27s).
15:55:46  
15:55:46  Completed with 1 error and 0 warnings:
15:55:46  
15:55:46  Database Error in model parent_mv (models/parent_mv.sql)
15:55:46    cannot drop materialized view dbt_dbeatty.parent_mv because other objects depend on it
15:55:46    compiled Code at target/run/foo/models/parent_mv.sql
15:55:46  
15:55:46  Done. PASS=0 WARN=0 ERROR=1 SKIP=1 TOTAL=2
```

</details>
Also facing this issue. Attempted to solve with an on-run-start hook that looks for the `--full-refresh` flag, but I'm not super familiar with using these and it only seems to _kinda_ work.

```
  project:
    model:
      +on-run-start: |
        {% if flags.FULL_REFRESH and this is not none %} DROP MATERIALIZED VIEW {{ this }} CASCADE
        {% endif %}
```
This is noted as a limitation in our [documentation](https://docs.getdbt.com/reference/resource-configs/redshift-configs#limitations). This is because Redshift does not support drop cascade on materialized views which is a prerequisite for lifting this limitation.
Re-opening as offline conversation with the Redshift team indicates we _may_ be able to support this use case
@colin-rogers-dbt any ETA on being able to get this supported?
In conversations with the Redshift team - it looks like the pathway is to update the materialization to use `CASCADE` which  is now supported. We will take a look and update this thread!

Fix patch:
diff --git a/.changes/unreleased/Fixes-20240906-102642.yaml b/.changes/unreleased/Fixes-20240906-102642.yaml
new file mode 100644
index 000000000..622f908e7
--- /dev/null
+++ b/.changes/unreleased/Fixes-20240906-102642.yaml
@@ -0,0 +1,7 @@
+kind: Fixes
+body: Support DROP CASCADE for materialized views; fixes bug that occurs when running
+  dbt on materialized views that reference other materialized views
+time: 2024-09-06T10:26:42.501014-04:00
+custom:
+  Author: mikealfare
+  Issue: "642"
diff --git a/dbt/include/redshift/macros/relations/materialized_view/drop.sql b/dbt/include/redshift/macros/relations/materialized_view/drop.sql
index 76f92e0e9..0db283817 100644
--- a/dbt/include/redshift/macros/relations/materialized_view/drop.sql
+++ b/dbt/include/redshift/macros/relations/materialized_view/drop.sql
@@ -1,3 +1,3 @@
 {% macro redshift__drop_materialized_view(relation) -%}
-    drop materialized view if exists {{ relation }}
+    drop materialized view if exists {{ relation }} cascade
 {%- endmacro %}


Test patch:
diff --git a/tests/functional/__init__.py b/tests/functional/__init__.py
new file mode 100644
index 000000000..30e204d08
--- /dev/null
+++ b/tests/functional/__init__.py
@@ -0,0 +1,1 @@
+# provides namespacing for test discovery
diff --git a/tests/functional/adapter/__init__.py b/tests/functional/adapter/__init__.py
new file mode 100644
index 000000000..30e204d08
--- /dev/null
+++ b/tests/functional/adapter/__init__.py
@@ -0,0 +1,1 @@
+# provides namespacing for test discovery
diff --git a/tests/functional/adapter/materialized_view_tests/__init__.py b/tests/functional/adapter/materialized_view_tests/__init__.py
new file mode 100644
index 000000000..30e204d08
--- /dev/null
+++ b/tests/functional/adapter/materialized_view_tests/__init__.py
@@ -0,0 +1,1 @@
+# provides namespacing for test discovery
diff --git a/tests/functional/adapter/materialized_view_tests/test_drop_cascade.py b/tests/functional/adapter/materialized_view_tests/test_drop_cascade.py
new file mode 100644
index 000000000..3d226a01e
--- /dev/null
+++ b/tests/functional/adapter/materialized_view_tests/test_drop_cascade.py
@@ -0,0 +1,56 @@
+"""
+This test addresses this bug: https://github.com/dbt-labs/dbt-redshift/issues/642
+
+Redshift did not initially support DROP CASCADE for materialized views,
+or at least did not document that they did. Now that they do, we should
+use DROP CASCADE instead of DROP.
+"""
+
+from dbt.tests.util import run_dbt
+import pytest
+
+
+SEED = """
+id
+1
+""".strip()
+
+
+PARENT_MATERIALIZED_VIEW = """
+{{ config(
+    materialized='materialized_view',
+    on_configuration_change='apply',
+) }}
+
+select * from {{ ref('my_seed') }}
+"""
+
+
+CHILD_MATERIALIZED_VIEW = """
+{{ config(
+    materialized='materialized_view',
+    on_configuration_change='apply',
+) }}
+
+select * from {{ ref('parent_mv') }}
+"""
+
+
+@pytest.fixture(scope="class")
+def seeds():
+    return {"my_seed.csv": SEED}
+
+
+@pytest.fixture(scope="class")
+def models():
+    return {
+        "parent_mv.sql": PARENT_MATERIALIZED_VIEW,
+        "child_mv.sql": CHILD_MATERIALIZED_VIEW,
+    }
+
+
+def test_drop_cascade(project):
+    run_dbt(["seed"])
+    run_dbt(["run"])
+    # this originally raised an error when it should not have
+    run_dbt(["run", "--full-refresh"])