Thank you for the detailed explanation, this clarifies a lot.

I still have concerns around the APPEND ONCE FLOW pattern, especially in the context of dependent MVs and CDC propagation.

Let me outline two scenarios to explain the issue.

Scenario 1 – No APPEND ONCE flow

Suppose I change the aggregation logic from:

median(value)

to:

avg(value)

This is clearly a logical change, so it should trigger a full refresh of the MV. That part is expected and acceptable.

However, my concern is what happens downstream.

Assume:
- The new values (avg vs median) are numerically very close.
- Only around 2% of rows actually change value.
- But because the MV is fully recomputed, every row is rewritten.

From a CDC perspective, this would appear as massive changes (all rows updated), even if most values are identical.

If I have downstream MVs depending on this MV, would they now:
- Detect massive upstream changes
- And therefore also trigger full refresh instead of incremental refresh?

In other words:

Even if the logical difference is small, does a full recomputation upstream automatically cascade into full recomputation downstream because of rewritten rows?

This is the main architectural concern.

Scenario 2 – Using APPEND ONCE flow

The APPEND ONCE pattern is very clear in streaming use cases, where:
- A streaming table continues processing
- You append backfill data without resetting the checkpoint, basically a UNION operation

However, for MVs (which are declarative and not streams, not sure that an append flow is even allowed here), I am struggling to understand how this avoids recomputation in practice.

Let me walk through a simplified example.

Suppose I change from:

median_value

to:

median_value + 1

Now I try to apply APPEND ONCE flow.

The steps that were described:
1. Create a new MV with new logic
2. Copy and modify data from old MV into the new MV using APPEND ONCE
3. Run refresh to get new data

Questions:
- If I create a new MV with new logic, does it need a new name?
- If the logic changed, would refresh not trigger a full recomputation anyway?
- If I migrate data from the old MV into the new MV (for example apply +1 manually), then run refresh:
- Will Databricks do an incremental refresh on the first run with the changes? (This contradicts "Any change to the query logic triggers a full refresh").
- Or will it still perform a full refresh due to logic change?

How exactly does APPEND ONCE prevent full recomputation in a non-streaming MV?

Core concern:

Even if APPEND ONCE avoids recomputing the table itself, the CDC volume issue remains.

If I:
- Recompute or rewrite a large MV (even with identical values)
|- Then downstream MVs may detect large upstream changes
|- Which could force them into full refresh mode as well.

Is there any way to avoid this? I am trying to understand how to safely evolve aggregation logic in layered MV architectures without triggering cascading full recomputations and massive CDC events.
Any deeper clarification on how APPEND ONCE works internally for MVs (not streaming tables) would be very helpful.

Thank you.