Fully drop MV on materialization change

[BentsiLeviav]

Describe the bug

When a dbt model is switched from a materialized view to a view and redeployed, the old materialized view is not dropped. Instead, only the MV’s target table is removed. This leaves the MV object in place, causing problems when inserting data, such as:

Failed to insert data into ClickHouse (will be retried). Missing materialized view data is possible due to ClickHouse deduplication: ClickHouse Error Code 48: Method write is not supported by storage View.

Steps to reproduce

1. Create an MV.
2. Redeploy it as a view.
3. The MV does not get dropped, and the error message starts coming in.

Expected behaviour

Both the target table and the MV are supposed to be removed.

[dlouseiro]

We had the exact same issue with one of our deployed models, which was changed from an MV to a view and, when redeploying the model using --full-refresh the MV was not dropped, only the target table was replaced by a view, essentially leaving the instance in a weird state where an MV is targeting a view instead of a table.

The error we received was slightly different, but tracks with Clickhouse trying to insert into a view:

Code: 48. DB::Exception: Method write is not supported by storage View. (NOT_IMPLEMENTED) (version 25.6.2.5971 (official build))

This caused major issues in our application, as this model was a downstream dependency of some crucial source tables and the error above prevented the insert into the source table to succeed (due to the failure in one of the "insert trigger").

[pkotikalapudi]

Doesn't dbt just support creation and modification of underlying physical resources? Isn't deletion a manual task on our end?

[koletzilla]

Doesn't dbt just support creation and modification of underlying physical resources? Isn't deletion a manual task on our end?

Hi @pkotikalapudi! It is supported. The issue lies in our implementation when the DBT connector creates ClickHouse's materialized views. Currently, DBT creates two resources: the materialized view and the target table. The problem is that DBT recreates the target table but leaves the materialized view behind. We also need to remove the materialized view.