[Feature] Allow node level dependency deprecation warnings

[jeremyyeo]

Is this your first time submitting a feature request?

• [X] I have read the expectations for open source contributors
• [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

At the moment, the deprecation_date warnings only show up at the "project level" - so at the initial compilation phase... this means that users who ignore that-top-bit will be unaware that a model is ref-ing a model that has already been deprecated - especially true in dbt Cloud:

If the system logs are minimized (which it is by default) - then you wouldn't be able to tell at all:

There is a current bug where this "bar depends on deprecated foo" warning is gone from versionless (#10833), so I've reverted back to 1.7 to record the above screenshots.

Describe alternatives you've considered

Turn the warning into an error - not ideal as this would break users runs. The idea is just to provide a node level warning so that it is more obvious that it is now during development.

Who will this benefit?

Users who want a more obvious "soft error" (warning) that their models are using deprecated models.

Are you interested in contributing this feature?

No response

Anything else?

No response