[Feature] Better error message for ephemeral table's unit test must state input in sql format

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

When I was erring due to my ephemeral table's unit test NOT in sql format, the error msg I get is

`Not able to get columns for unit test 'int_everest__error_cleaned' from relation `fathom-dev-210618`.`dbt_test_pr_37852`.`int_everest__error_cleaned` because the relation doesn't exist

> in macro get_fixture_sql (macros/unit_test_sql/get_fixture_sql.sql)
> called by model int_everest__error_cleaned (models/intermediate/training/_int_training_models.yml)`

which is super misleading and led me to check correctness of my int_everest__error_cleaned's code. Related issue here -> https://github.com/dbt-labs/dbt-core/issues/11263

### Describe alternatives you've considered

Just straightforward tell me that my unit test input must be in sql format

### Who will this benefit?

_No response_

### Are you interested in contributing this feature?

_No response_

### Anything else?

_No response_

Start with macros/unit_test_sql/get_fixture_sql.sql and the unit test definition in models/intermediate/training/_int_training_models.yml. Trace how the ephemeral table input is handled and compare the current error with the requested SQL-format guidance. Done means an invalid input produces a clear message stating that the unit test input must be in SQL format.

Written by the indexing model from the issue text.