I think it's impossible to do an incremental update in an arbitrary case. Imagine an m-view based on a query that selects top 100 largest purchases during last 30 days on an e-commerce site. Or, worse, a query that selects the largest subtree of followers on a social network site.

Only certain kinds of conditions, such as a rolling window over a timestamp field, seem amenable to efficient incremental updates. What am I missing?

https://youtu.be/iT4k5DCnvPU?si=cZ_TYuynKavksQKG

That's probably beyond the scale level appropriate for a materialized view. For that I'd use something like DBT.