Refactor how we render tests to include dbt unit tests and also data tests

[tatiana]

Extracted from the comment by @dwreeves :
#1377 (review)

A thought as I read this, for another day though:

dbt 1.8+ has a new concept called unit tests, which it differentiates from data tests (formerly just "tests"). dbt's preferred way of running things is: unit tests -> build model -> data tests.

For the TestBehavior.BUILD, we comport with how dbt wants to run things 👍 because the order of operations is resolved automatically by dbt build.

We don't currently handle dbt unit tests for TestBehavior.AFTER_ALL and TestBehavior.AFTER_EACH though. 🤔 A natural way to do this would be to have AFTER_ALL also mean "before all" when it comes to unit tests, and similarly AFTER_EACH means "before each" for unit tests.

Alternatively, these could be decoupled; i.e. "before each, unit test" and "after all, data test," similarly "before all, unit test" and "after each, data test." But for TestBehavior.BUILD, decoupling gets weird. 🤷

No actionable item here other than, potentially, to mention in the docs that TestBehavior.BUILD is currently the only way to run dbt unit tests using Cosmos's automatic graph parsing. Just thinking out loud about the future.

[DanMawdsleyBA]

Just to mention DBT actually recommends running unit tests as part of cicd and not part of the airflow job.

"dbt Labs strongly recommends only running unit tests in development or CI environments. Since the inputs of the unit tests are static, there's no need to use additional compute cycles running them in production. Use them in development for a test-driven approach and CI to ensure changes don't break them.
"

https://docs.getdbt.com/docs/build/unit-tests

In fact we may want to disable them by default?