[Feature] Optional Node Selection for --favor-state

[d-cole]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Similar to #9410. Opening a new issue as discussed with @jtcohen6.

When referencing upstreams from a different target, users often prefer explicitly listing upstreams to defer. I suspect this is due to a few reasons:

• Explicitly listing upstreams does not require an understanding of database state in order to know which nodes will be selected.
• The experience is similar to what many users are familiar with - manually swapping out table references in SQL.

The experience of using defer can be improved by having the --favor-state flag optionally support node selection. This would allow users to explicitly list the nodes they wish to favor-state to apply to. This behaviour is illustrated below:

    model_a ----\
                 \       
                  --> model_c 
                 /       
    model_b ----/       

Assume:

Production artifacts exist at: target/prod_artifacts

Dev db state: 
    model_a 
    model_b 

Prod db state:
     model_a 
     model_b 

Current Behaviour

Behaviour when no nodes specified after --favor-state flag (Same as current favor-state behaviour). The condition "Does the reference node exist as a database object in the current environment?" is ignored for all nodes.

dbt run -s model_c --defer --favor-state --defer-state target/prod_artifacts --target dev

model_a (prod)--\
                 \       
                  --> model_c (dev)
                 /       
model_b (prod)--/     

New Behaviour --favor-state List[SelectionSpec]

The condition "Does the reference node exist as a database object in the current environment?" is ignored only for nodes selected by --favor-state.

dbt run -s model_c --defer --favor-state model_b --defer-state target/prod_artifacts --target dev

model_a (dev)--\
                 \       
                  --> model_c (dev)
                 /       
model_b (prod)--/     

Describe alternatives you've considered

• Add a flag --defer-ignore-nodes to select which nodes to ignore the defer behaviour from. This complicates the interface by adding an additional flag. Additionally, it does not remove the mental overhead of understanding the db state to know the node selection.

• Add a separate flag "--ref-from-state" that only accepts an explicit list of nodes. This makes defer even more complicated by adding an additional flag that is similar to --favor-state.

Who will this benefit?

Users of defer who need a mixed environment selection of upstreams. See #9410 (comment) for further discussion.

Are you interested in contributing this feature?

No response

Anything else?

No response