[Feature] Support `dispatch` for materializations to use implementations defined in installed packages

### Is this your first time submitting a feature request?

- [X] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [X] I have searched the existing issues, and I could not find an existing issue for this feature
- [X] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

This is a way of preserving a more granular version of the previous behavior, which would implicitly (and somewhat surprisingly) use materializations from packages that override builtins (`view`, `table`, `incremental`, `test`, etc).

We are opting for this syntax:

```yml
dispatch:
- type: materialization
# macro_namespace is not needed, because all materializations are global
search_order: ['elementary', 'dbt']
- type: macro
macro_namespace: dbt_utils
search_order: [...]
```

### Considerations

If not specified, the default would remain:
1. Root package first
2. Then builtin implementations within `dbt-core` / adapters

Which is how it works for materializations with the flag, and for dispatched macros.

### Implementation

The relevant method is [`find_materialization_macro_by_name`](https://github.com/dbt-labs/dbt-core/blob/29b83598e3530e99a441e4b6de51f9f9dace781f/core/dbt/contracts/graph/manifest.py#L941-L981).

Michelle spiked a similar capability, though by pulling the "allowlist" from `Project.flags` rather than `Project.dispatch`: https://github.com/dbt-labs/dbt-core/pull/9956/commits/99998a597c75d71f85b85202e31de6cff984249e

### Describe alternatives you've considered

### (1) Not doing this

Users must reimplement materializations one-by-one, by defining them in their root project and calling the implementations in packages.
```sql
{% materialization table, snowflake %}
{{ return(elementary.materialization_table_snowflake()) }}
{% endmaterialization %}

{% materialization incremental, snowflake %}
{{ return(elementary.materialization_incremental_snowflake()) }}
{% endmaterialization %}

-- etc
-- differs by adapter
```

### (2) Bundling with existing macro dispatch

Under the hood, these materializations _are_ macros, and defined within the `'dbt'` namespace. But I like the idea of continuing to keep the two separate, for three reasons:
1. Avoid tying ourselves forever to this implementation details (that materializations are macros)
2. Materializations are only callable from the "global" namespace, whereas all other macros can be called by namespace
3. It's conceivable that users would want different behavior for materializations versus other built-in macros

### (3) Different syntax that wouldn't require evolving the type of `dispatch` config

```
# 'materialization' is just a special macro_namespace
dispatch:
- macro_namespace: materialization
search_order: ['elementary', 'dbt']
```

Edge case: this would not play well with an installed package named `materialization`.

### Who will this benefit?

- Users who have installed the `elementary` package (https://github.com/elementary-data/dbt-data-reliability/issues/703)
- Maintainers of internal "utils" packages for their organizations, who want to change the behavior of table/incremental/etc for all of their colleagues with only a few lines of code

### Are you interested in contributing this feature?

Yes, with the help of the elementarians

### Anything else?

We should do this for dbt Core v1.8.x only. No backports. While we introduced the deprecation warning in dbt-core v1.6 + v1.7, it's still the default behavior in those versions to use implementations defined in packages.

We will document in the [v1.8 upgrade guide](https://docs.getdbt.com/docs/dbt-versions/core-upgrade/upgrading-to-v1.8) and ["legacy behaviors"](https://docs.getdbt.com/reference/global-configs/legacy-behaviors) the recommended sequence of:
- Any users currently seeing the deprecation warning should set the behavior change flag to `False` to preserve existing behavior (regardless of the version they're running on), and to unblock their upgrade to v1.8
- Then, at their leisure, they may set materialization `dispatch`, set the flag to `True` / remove the flag setting, and confirm that they're seeing the same behavior as before