[Epic] state:modified should Actually (only) select the modified resources

[graciegoheen]

We currently call out a lot of caveats to state comparison in our docs.

There are many scenarios where executing dbt list --select state:modified over or under selects the appropriate resources. This leads to:

• confusion "why are these models selected as state:modified, I haven't changed them"
• performance concerns "why am I running 60 models when I only changed 2"
• potentially bad code sneaking it's way into production "i checked my changes using a Slim CI job, but these models weren't run, now production is broken"

While it's unlikely we can cover ever single edge case here, we should improve state:modified so that it Ideally actually only selects the modified resources.

This epic will track the relevant bugs/enhancements related to state:modified.

### p0
- [ ] https://github.com/dbt-labs/dbt-core/issues/10002
- [ ] https://github.com/dbt-labs/dbt-core/issues/9548
- [ ] https://github.com/dbt-labs/dbt-core/issues/9563
- [ ] https://github.com/dbt-labs/dbt-core/issues/9564
- [ ] https://github.com/dbt-labs/dbt-core/issues/10138
- [ ] https://github.com/dbt-labs/dbt-core/issues/9573

### p1
- [ ] https://github.com/dbt-labs/dbt-core/issues/4304
- [ ] https://github.com/dbt-labs/dbt-core/issues/8727

[matt-winkler]

What would be lost by disallowing env-aware logic outside the dbt_project.yml?

[patkearns10]

Lots of examples here: https://discourse.getdbt.com/t/why-are-ci-jobs-state-method-building-more-less-models-nodes-than-expected/7504
An example of env_var logic here , which seems impossible to keep out of state:modified

[graciegoheen]

For those following along, would love to hear your thoughts over in this discussion #10518