RisingWave: SQLMesh plan unnecessarily always recreates MVs

[xardasos]

When running create_external_sources followed by plan with a new MV, sqlmesh first creates the MV, then drops it and recreates it again unnecessarily. This is a regression compared to 0.197.4 version where this big performance problem does not appear.

How to reproduce

1. Create a RW project with a dummy MV

MODEL (
  name demo_mv,
  kind VIEW (materialized true)
);

SELECT event_id from click_events

2. Manually create a table in a RW database:

CREATE TABLE click_events (
    event_id INT PRIMARY KEY,
    event_type VARCHAR,
    event_value INT,
    event_timestamp TIMESTAMPTZ
);

3. Execute sqlmesh create_external_models
4. Execute sqlmesh plan

Current bug behavior

MV demo_mv is created, then deleted and recreated again.

Log

2025-09-09 13:48:01,229 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage CreateSnapshotRecordsStage (evaluator.py:125)
2025-09-09 13:48:01,484 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage PhysicalLayerSchemaCreationStage (evaluator.py:125)
2025-09-09 13:48:01,485 - ThreadPoolExecutor-1_0 - sqlmesh.core.snapshot.evaluator - INFO - Creating schema 'sqlmesh__default' (evaluator.py:1355)
2025-09-09 13:48:01,485 - ThreadPoolExecutor-1_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: /* SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 / CREATE SCHEMA IF NOT EXISTS "sqlmesh__default" (base.py:2466)
2025-09-09 13:48:01,540 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage BackfillStage (evaluator.py:125)
2025-09-09 13:48:01,543 - ThreadPoolExecutor-2_0 - sqlmesh.core.snapshot.evaluator - INFO - Listing data objects in schema sqlmesh__default (evaluator.py:1458)
2025-09-09 13:48:01,543 - ThreadPoolExecutor-2_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: / SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 / SELECT current_catalog (base.py:2466)
2025-09-09 13:48:01,555 - ThreadPoolExecutor-3_0 - sqlmesh.core.snapshot.evaluator - INFO - Evaluating snapshot SnapshotId<"demo_mv": 281835372> (evaluator.py:701)
2025-09-09 13:48:01,557 - ThreadPoolExecutor-3_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: / SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 / SELECT 1 FROM "information_schema"."tables" WHERE "table_name" = 'demo_mv__827871489' AND "table_schema" = 'sqlmesh__default' (base.py:2466)
2025-09-09 13:48:01,561 - ThreadPoolExecutor-3_0 - sqlmesh.core.snapshot.evaluator - INFO - Creating view 'sqlmesh__default.demo_mv__827871489' (evaluator.py:2358)
2025-09-09 13:48:01,565 - ThreadPoolExecutor-3_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: / SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 / CREATE MATERIALIZED VIEW "sqlmesh__default"."demo_mv__827871489" AS SELECT "click_events"."event_id" AS "event_id" FROM "click_events" AS "click_events" (base.py:2466)
2025-09-09 13:48:02,911 - ThreadPoolExecutor-3_0 - sqlmesh.core.snapshot.evaluator - INFO - Inserting data for snapshot SnapshotId<"demo_mv": 281835372> (evaluator.py:905)
2025-09-09 13:48:02,916 - ThreadPoolExecutor-3_0 - sqlmesh.core.snapshot.evaluator - INFO - Inserting batch (2025-09-08 00:00:00, 2025-09-09 00:00:00) into sqlmesh__default.demo_mv__827871489' (evaluator.py:935)
2025-09-09 13:48:02,917 - ThreadPoolExecutor-3_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: / SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 / SELECT 1 FROM "information_schema"."tables" WHERE "table_name" = 'demo_mv__827871489' AND "table_schema" = 'sqlmesh__default' (base.py:2466)
2025-09-09 13:48:02,920 - ThreadPoolExecutor-3_0 - sqlmesh.core.snapshot.evaluator - INFO - Replacing view 'sqlmesh__default.demo_mv__827871489' (evaluator.py:2322)
2025-09-09 13:48:02,921 - ThreadPoolExecutor-3_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: / SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 / DROP MATERIALIZED VIEW IF EXISTS "sqlmesh__default"."demo_mv__827871489" CASCADE (base.py:2466)
2025-09-09 13:48:03,112 - ThreadPoolExecutor-3_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: / SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 / CREATE MATERIALIZED VIEW "sqlmesh__default"."demo_mv__827871489" AS SELECT "click_events"."event_id" AS "event_id" FROM "click_events" AS "click_events" (base.py:2466)
2025-09-09 13:48:04,579 - ThreadPoolExecutor-3_0 - sqlmesh.core.state_sync.db.facade - INFO - Adding interval (2025-09-08 00:00:00, 2025-09-09 00:00:00) for snapshot SnapshotId<"demo_mv": 281835372> (facade.py:622)
2025-09-09 13:48:04,579 - ThreadPoolExecutor-3_0 - sqlmesh.core.state_sync.db.interval - INFO - Pushing intervals for snapshot SnapshotId<"demo_mv": 281835372> (interval.py:214)
2025-09-09 13:48:04,681 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage EnvironmentRecordUpdateStage (evaluator.py:125)
2025-09-09 13:48:04,682 - MainThread - sqlmesh.core.state_sync.db.facade - INFO - Promoting environment 'prod' (facade.py:165)
2025-09-09 13:48:04,852 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage UnpauseStage (evaluator.py:125)
2025-09-09 13:48:05,028 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage VirtualLayerUpdateStage (evaluator.py:125)
2025-09-09 13:48:05,032 - ThreadPoolExecutor-4_0 - sqlmesh.core.snapshot.evaluator - INFO - Creating schema 'default' (evaluator.py:1355)
2025-09-09 13:48:05,033 - ThreadPoolExecutor-4_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: / SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 / CREATE SCHEMA IF NOT EXISTS "default" (base.py:2466)
2025-09-09 13:48:05,085 - ThreadPoolExecutor-5_0 - sqlmesh.core.snapshot.evaluator - INFO - Updating view 'default.demo_mv' to point at table 'sqlmesh__default.demo_mv__827871489' (evaluator.py:1727)
2025-09-09 13:48:05,086 - ThreadPoolExecutor-5_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: / SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 / DROP VIEW IF EXISTS "default"."demo_mv" CASCADE (base.py:2466)
2025-09-09 13:48:05,089 - ThreadPoolExecutor-5_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: / SQLMESH_PLAN: 366b6344f46249c7a942bef1fc7f13b1 */ CREATE VIEW "default"."demo_mv" AS SELECT * FROM "sqlmesh__default"."demo_mv__827871489" (base.py:2466)
2025-09-09 13:48:05,146 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage FinalizeEnvironmentStage (evaluator.py:125)
2025-09-09 13:48:05,146 - MainThread - sqlmesh.core.state_sync.db.environment - INFO - Finalizing environment 'prod' (environment.py:141)

Expected behavior

The MV demo_mv is created only once. For reference, here is the log from version 0.197.4, where the issue does not appear:

Log

2025-09-09 13:37:18,767 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage CreateSnapshotRecordsStage (evaluator.py:115)
2025-09-09 13:37:18,898 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage PhysicalLayerUpdateStage (evaluator.py:115)
2025-09-09 13:37:18,899 - ThreadPoolExecutor-1_0 - sqlmesh.core.snapshot.evaluator - INFO - Listing data objects in schema sqlmesh__default (evaluator.py:350)
2025-09-09 13:37:18,899 - ThreadPoolExecutor-1_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: SELECT current_catalog (base.py:2237)
2025-09-09 13:37:18,912 - MainThread - sqlmesh.core.snapshot.evaluator - INFO - Creating schema 'sqlmesh__default' (evaluator.py:1143)
2025-09-09 13:37:18,913 - MainThread - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: CREATE SCHEMA IF NOT EXISTS "sqlmesh__default" (base.py:2237)
2025-09-09 13:37:18,916 - ThreadPoolExecutor-2_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: SELECT 1 FROM "information_schema"."tables" WHERE "table_name" = 'demo_mv__3545757078' AND "table_schema" = 'sqlmesh__default' (base.py:2237)
2025-09-09 13:37:18,921 - ThreadPoolExecutor-2_0 - sqlmesh.core.snapshot.evaluator - INFO - Creating view 'sqlmesh__default.demo_mv__3545757078' (evaluator.py:1940)
2025-09-09 13:37:18,926 - ThreadPoolExecutor-2_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: CREATE MATERIALIZED VIEW "sqlmesh__default"."demo_mv__3545757078" AS SELECT "click_events"."event_id" AS "event_id" FROM "click_events" AS "click_events" (base.py:2237)
2025-09-09 13:37:20,301 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage BackfillStage (evaluator.py:115)
2025-09-09 13:37:20,304 - ThreadPoolExecutor-3_0 - sqlmesh.core.snapshot.evaluator - INFO - Evaluating snapshot SnapshotId<"demo_mv": 4095825826> (evaluator.py:636)
2025-09-09 13:37:20,308 - ThreadPoolExecutor-3_0 - sqlmesh.core.snapshot.evaluator - INFO - Inserting batch (2025-09-08 00:00:00, 2025-09-09 00:00:00) into sqlmesh__default.demo_mv__3545757078' (evaluator.py:690)
2025-09-09 13:37:20,312 - ThreadPoolExecutor-3_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: SELECT 1 FROM "information_schema"."tables" WHERE "table_name" = 'demo_mv__3545757078' AND "table_schema" = 'sqlmesh__default' (base.py:2237)
2025-09-09 13:37:20,317 - ThreadPoolExecutor-3_0 - sqlmesh.core.snapshot.evaluator - INFO - Skipping creation of the view 'sqlmesh__default.demo_mv__3545757078' (evaluator.py:1891)
2025-09-09 13:37:20,318 - ThreadPoolExecutor-3_0 - sqlmesh.core.state_sync.db.facade - INFO - Adding interval (2025-09-08 00:00:00, 2025-09-09 00:00:00) for snapshot SnapshotId<"demo_mv": 4095825826> (facade.py:621)
2025-09-09 13:37:20,318 - ThreadPoolExecutor-3_0 - sqlmesh.core.state_sync.db.interval - INFO - Pushing intervals for snapshot SnapshotId<"demo_mv": 4095825826> (interval.py:213)
2025-09-09 13:37:20,414 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage EnvironmentRecordUpdateStage (evaluator.py:115)
2025-09-09 13:37:20,414 - MainThread - sqlmesh.core.state_sync.db.facade - INFO - Promoting environment 'prod' (facade.py:169)
2025-09-09 13:37:20,561 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage UnpauseStage (evaluator.py:115)
2025-09-09 13:37:20,569 - MainThread - sqlmesh.core.state_sync.db.snapshot - INFO - Unpausing snapshot SnapshotId<"demo_mv": 4095825826> (snapshot.py:144)
2025-09-09 13:37:20,570 - MainThread - sqlmesh.core.state_sync.db.snapshot - INFO - Unpausing snapshot SnapshotId<"click_events": 886993094> (snapshot.py:144)
2025-09-09 13:37:20,643 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage VirtualLayerUpdateStage (evaluator.py:115)
2025-09-09 13:37:20,646 - MainThread - sqlmesh.core.snapshot.evaluator - INFO - Creating schema 'default' (evaluator.py:1143)
2025-09-09 13:37:20,646 - MainThread - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: CREATE SCHEMA IF NOT EXISTS "default" (base.py:2237)
2025-09-09 13:37:20,782 - ThreadPoolExecutor-4_0 - sqlmesh.core.snapshot.evaluator - INFO - Updating view 'default.demo_mv' to point at table 'sqlmesh__default.demo_mv__3545757078' (evaluator.py:1450)
2025-09-09 13:37:20,783 - ThreadPoolExecutor-4_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: DROP VIEW IF EXISTS "default"."demo_mv" CASCADE (base.py:2237)
2025-09-09 13:37:20,786 - ThreadPoolExecutor-4_0 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: CREATE VIEW "default"."demo_mv" AS SELECT * FROM "sqlmesh__default"."demo_mv__3545757078" (base.py:2237)
2025-09-09 13:37:20,839 - MainThread - sqlmesh.core.plan.evaluator - INFO - Evaluating plan stage FinalizeEnvironmentStage (evaluator.py:115)
2025-09-09 13:37:20,839 - MainThread - sqlmesh.core.state_sync.db.environment - INFO - Finalizing environment 'prod' (environment.py:139)

Affected versions

0.216.0

Notes

1. Interestingly, it seems that the issue does not appear when step 3. (execute sqlmesh create_external_models) is omitted.
2. Related discussion: Fix(risingwave): Recreate materialized views #5195

[izeigerman]

Hey @xardasos !

I've fixed the redundant view creation in #5337.

However, keep in mind that the materialized view will still be recreated if the change was categorized as Indirect Non-Breaking. This is because views preserve the queries with which they were originally created, which may later contain obsolete table references.

Consider the following DAG: A (table) <- B (materialized view). When these models are deployed for the first time they might get assigned physical tables A__1 and B__1 respectively. However, if I make a change to A, even if it's Non-Breaking change a new table A__2 will be assigned to it. If we don't recreate view B it will continue to point at the old A__1 table.

Please let me know if this explanation makes sense.

[xardasos]

Hey @izeigerman,

Thank you for the fix, it works as a charm!

Your explanation makes prefect sense. I was also concerned about exactly these indirect non-breaking changes and in the current sqlmesh workflow I agree that the recreation is needed (#5195 (comment)).

However, I have been also thinking about a possible improvement to the workflow to avoid downtime associated with the recreation time (which can be very long in case of bigger MVs) when the data is not available in prod.

Suppose we have these A__1 and B__1 from your example in prod env and then we create a new virtual data env test_env where a view test_env.A points at a new MV A__2 (assume we did a non-breaking change to the model A).

Current workflow

1. Preview MV B__2__dev is created.
2. When the changes are promoted to prod, it is not a virtual update - B__1 gets recreated (it points at A__2 after the recreation).

New workflow proposal

1. A regular MV B__2 is created (we calculate the MV version based on both parent data hash and data hash, similarly to the current dev versions).
2. When the changes are promoted to prod, it can be now a simple, almost instantaneous virtual update.

Maybe I am missing something, but I think it would be a big improvement for RisingWave, where the preview MV B__2__dev already contains all the data and is no different from a regular MV (so why not skip the __dev suffix and use the MV while promoting the environment?). Possibly, it could also be beneficial for other engines, although I'm not sure.

What's your take on this?

[xardasos]

I created a separate issue to discuss the optimization proposal #5365.