[state:modified] Introduce tests to capture current state of env-aware configuration and state:modified

[MichelleArk]

Housekeeping

• I am a maintainer of dbt-core

Short description

Currently, there are many scenarios during which state:modified returns either false positives or negatives. While these aren't the way the feature should work ideally, they are documented limitations and we ought to have awareness on how our changes influence this behaviour.

Let's capture the current state of state:modified behaviour with respect to the well-known limitation of environment-aware logic in (hierarchical) configuration to use as a guiding development aid as part of the broader #9562 epic which aims to eliminate the false positives / negatives. My initial spiking leads me to believe that depending on the implementation approach taken to resolve a false positive/negative case, other false positive / negative scenarios may regress further and it will be important to be aware of this during development.

Acceptance criteria

Introduce tests for the following scenarios:

1. environment-aware logic in .sql config method leads to a false positive
2. environment-aware logic in schema.yml config block leads to a false positive
3. environment-aware logic dbt_project.yml does not lead to a false positive or negative, unless (1) or (2) takes precedence
4. combinations of (1,2,3) and their interplay (as partially described in (3))
5. combinations of (1,2,3) where the environment-aware logic lives in a macro
6. combinations of (1,2,3) where the environment-aware logic is determined by a var
7. combinations of (1,2,3) for sources

Additionally - add a comment in the test to the documentation.

Suggested Tests

Test matrix in A/C

Impact to Other Teams

None

Will backports be required?

No

Context

There is a large matrix of test cases to cover here. Ideally a unit test framework provides us with ample testing confidence here, and that should be explored before falling back to functional tests.

TODO: link existing state:modified unit tests

[MichelleArk]

Closed by #10487 and #10675