[CT-3027] [Bug] Create test cases for each materialization type that end in a SQL comment (`-- like this`)

[dbeatty10]

Is this a new bug in dbt-core?

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

Current Behavior

We don't have functional tests for models ending with a SQL comment (-- like this comment), which has led to bug reports like https://github.com/dbt-labs/dbt-core/issues/6781 and https://github.com/dbt-labs/dbt-bigquery/issues/896.

Snapshots were covered within https://github.com/dbt-labs/dbt-core/pull/6780, but there are other materialization types left undone.

Expected Behavior

As discussed here, we should add a comment to the end of models in integration tests for each of our materialization types + singular and generic tests to catch any regressions in the future.

Steps To Reproduce

See the steps to reproduce in the issues linked above.

Implementations

#### Implementation in adapters
- [ ] dbt-bigquery: https://github.com/dbt-labs/dbt-bigquery/issues/896
- [ ] dbt-postgres
- [ ] dbt-redshift
- [ ] dbt-snowflake
- [ ] dbt-spark

Implementation example

https://github.com/dbt-labs/dbt-core/pull/6780 provides an example of how to test this for snapshots. We'd just need to do something similar for other materialization types.

Just add something like this to the bottom of a simple materialization of each type: https://github.com/dbt-labs/dbt-core/blob/4d3c6d9c7c6bb7de35dda71074650e57c12a5a3b/tests/functional/simple_snapshot/test_comment_ending_snapshot.py#L17

[github-actions[bot]]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.