[Bug] During deferral, the foreign key constraints ref() is resolving to the wrong node

[jeremyyeo]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

This is a bug in the same vein as #11885 - however it is slightly different in that the ref('other_table') is always resolving to the deferred relation, even IF the other_table is built as part of the selection - aka

-- bar is built in CI schema.
create or replace db.ci.bar as (...)

-- foo's turn to be built in CI schema.
create or replace db.ci.foo (
  fk int references db.prod.bar (id)
) as (
  select * from db.ci.bar
)

^ Observe that:

• The model body ref('bar') resolves correctly to db.ci.bar
• The model foreign key ref('bar') resolves incorrectly to db.prod.bar

Expected Behavior

Expectation:

-- bar is built in CI schema.
create or replace db.ci.bar as (...)

-- foo's turn to be built in CI schema.
create or replace db.ci.foo (
  fk int references db.ci.bar (id) -- this should also resolve to the ci schema's version of bar.
) as (
  select * from db.ci.bar
)

Steps To Reproduce

We will setup 2 profiles so we can test this.

# profiles.yml
sf:
  target: ci
  outputs:
    ci:
      type: snowflake
      schema: ci
      ...
    prod:
      type: snowflake
      schema: prod
      ...

And our dbt project:

# dbt_project.yml
name: analytics
profile: sf
version: "1.0.0"

models:
  analytics:
    +materialized: table

# models/schema.yml
models:
  - name: foo
    config:
      materialized: table
      contract: { enforced: true }
    columns:
      - name: pk
        data_type: int
        constraints:
          - type: primary_key
      - name: fk
        data_type: int
        constraints:
          - type: foreign_key
            to: ref('bar')
            to_columns:
              - id
  - name: bar
    config:
      materialized: table
      contract: { enforced: true }
    columns:
      - name: id
        data_type: int
        constraints:
          - type: primary_key

-- models/bar.sql
select 1 id

-- models/foo.sql
select 1 pk, id as fk from {{ ref('bar') }}

Lets do a production build into the production schema prod (also moving the target folder to do defer)

$ dbt --debug run -t prod && mv target target_old
...
03:42:28  On model.analytics.bar: create or replace transient table db.prod.bar 
  (
    id int primary key
    )
    as (
    select id
    from (
        select 1 id
    ) as model_subq
    )

03:42:29  On model.analytics.foo: create or replace transient table db.prod.foo
  (
    pk int primary key,
    fk int references db.prod.bar (id)
    )
    as (
    select pk, fk
    from (
        select 1 pk, id as fk from db.prod.bar
    ) as model_subq
    )

No issues here.

1. bar built as db.prod.bar.
2. foo selects from db.prod.bar in both it's fk int references ... and the body select ... from.

Let's modify foo.sql:

-- models/foo.sql
select 2 pk, id as fk from {{ ref('bar') }}

Now, we do a "CI run" with deferral:

$ dbt --debug run -s +state:modified+ --defer --state target_old -t ci
...
03:46:34  On model.analytics.bar: create or replace transient table db.ci.bar  
  (
    id int primary key
    )
    as (
    select id
    from (
        select 1 id
    ) as model_subq
    )
...
03:46:35  On model.analytics.foo: create or replace transient table db.ci.foo
  (
    pk int primary key,
    fk int references db.prod.bar (id)
    )
    as (
    select pk, fk
    from (
        select 2 pk, id as fk from db.ci.bar
    ) as model_subq
    )

^ Here is where we get the issue.

1. bar built as db.ci.bar.
2. foo selects from db.ci.bar in the body select ... from; BUT:
3. foo's fk int references .. is to db.prod.bar (this is incorrect).

Relevant log output

Environment

- OS: macOS
- Python: 3.11.9
- dbt:
Core:
  - installed: 1.11.2
  - latest:    1.11.2 - Up to date!

Plugins:
  - snowflake: 1.11.1 - Up to date!

Which database adapter are you using with dbt?

snowflake

Additional Context

For Snowflake, it's not a big issue but for Datawarehouses like Postgres where fk constraints are enforced, this can cause actual errors.