[Feature] Raise a clearer error message for resource types that don't support enforcing contracts

[dbeatty10]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

#9542 describes the current behavior, namely:

dbt should raise a descriptive error message if a user adds a model contract to any of the following:

• Python models.
• ephemeral-materialized SQL models.
• Other resource types, such as sources, seeds, snapshots, and so on.

Put another way, dbt should raise an error for model contracts that don't have all of the following properties:

• resource_type == "model"
• language == "sql"
• materialized in ("incremental", "table", "view", "materialized_view")

Expected Behavior

#9542 (comment) describes the desired behavior.

Steps To Reproduce

See "current behavior" above.

Relevant log output

No response

Environment

- OS:
- Python:
- dbt:

Which database adapter are you using with dbt?

No response

Additional Context

No response