[Core] Caveat for SQL changes within a `materialized_view` model

### Link to the page(s) on docs.getdbt.com requiring updates

A couple potential places:
- https://docs.getdbt.com/reference/resource-configs/on_configuration_change
- https://docs.getdbt.com/docs/build/materializations#configuration-change-monitoring

### Tell us more about this update

Here's a common caveat we've seen that is applicable to materialized views (MVs):

Many people have been surprised that when they alter the SQL for a materialized view, that it does not trigger an update, refresh, or anything else.

Rather, it silently skips to the next model. That's because the SQL is not monitored and our implementation doesn't consider it a configuration change.

https://github.com/dbt-labs/dbt-core/issues/8862 was opened by a user that didn't expect this behavior.

### Reviewers/Stakeholders/SMEs

Adapters Team

### Related GitHub issues

- https://github.com/dbt-labs/dbt-core/issues/8862
- https://github.com/dbt-labs/docs.getdbt.com/pull/5213

### Additional information

_No response_