[Feature] Add support for unit testing materialized views

### 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

dbt unit tests do not support unit testing models that have `materialized view` materialization: https://docs.getdbt.com/docs/build/unit-tests

Documentation states:

> We currently don't support unit testing models that use the [materialized view](https://docs.getdbt.com/docs/build/materializations#materialized-view) materialization.

We would like to be able to unit test models that have materialized view materializations.

### Describe alternatives you've considered

The status quo, which is to not have unit tests at all. This results in lower code quality since unit tests are a core part of any quality software engineering project.

### Who will this benefit?

We are setting up a dbt project for a streaming database, RisingWave. Materialize is another streaming database gaining in popularity.

Streaming database design commonly uses materialized views, which are always kept up-to-date in a streaming fashion. This is one of the key selling points of these databases.

Thus, dbt unit tests will only be of much more limited use with these database types, unless dbt can support unit testing materialized views.

### Are you interested in contributing this feature?

_No response_

### Anything else?

_No response_