[Bug] Unit tests + compiled paths: report correct compiled path if the test fails, and don't overwrite input compiled paths with fixtures

[jtcohen6]

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

Two problems:

1. Unit test failure includes link to yaml file where the unit test is defined, rather than the file where I could find the actual compiled code
2. In the process of running the unit test, we overwrite the compiled code for the actual model being unit tested with its

22:43:55  Failure in unit_test my_unit_test (models/my_model.yml)
22:43:55

actual differs from expected:

@@,id,renamed
→ ,1 ,red→blue


22:43:55
22:43:55    compiled Code at models/my_model.yml

Expected Behavior

1. Unit test failure includes the actual compiled path of the unit test query, so I could copy-paste-rerun it if I so chose
2. Don't overwrite the compiled path for the input model(s) with fixture(s)

May be tricky to link what we're running with the definition of it. The dummy nodes and manifest go away at the end of execution.

Steps To Reproduce

-- models/subfolder/model_a.sql
select
  1 as id, 'blue' as color

-- models/subfolder/model_b.sql
select
    id,
    color
from {{ ref('model_a')}}

# models/subfolder/my_model.yml
unit_tests:
  - name: my_unit_test
    model: model_b
    given:
      - input: ref('model_a')
        rows:
          - { id: 1, renamed: 'blue' }
    expect:
      rows:
        - { id: 1, renamed: 'red' }

Run dbt build, and see that:

1. The compiled code is models/subfolder/my_model.yml rather than target/compiled/my_dbt_project/models/my_model.yml/models/my_unit_test.sql
2. dbt has overwritten target/compiled/my_dbt_project/models/model_a.sql with its fixture for the unit test, which I think we should avoid doing if possible:

-- Fixture for model_a
select 
    
    cast(1 as INTEGER)
 as id, 
    
    cast('blue' as character varying(256))
 as renamed

Relevant log output

$ dbt build
22:47:45  Running with dbt=1.8.0-a1
22:47:45  Registered adapter: duckdb=1.7.0 ;)
22:47:46  Found 2 models, 515 macros, 1 unit_test
22:47:46
22:47:46  Concurrency: 1 threads (target='dev')
22:47:46
22:47:46  1 of 3 START sql view model dev.model_a ........................................ [RUN]
22:47:46  1 of 3 OK created sql view model dev.model_a ................................... [OK in 0.08s]
22:47:46  2 of 3 START unit_test model_b::my_unit_test ................................... [RUN]
22:47:46  2 of 3 FAIL 1 model_b::my_unit_test ............................................ [FAIL 1 in 0.38s]
22:47:46  3 of 3 SKIP relation dev.model_b ............................................... [SKIP]
22:47:46
22:47:46  Finished running 2 view models, 1 unit_test in 0 hours 0 minutes and 0.63 seconds (0.63s).
22:47:46
22:47:46  Completed with 1 error and 0 warnings:
22:47:46
22:47:46  Failure in unit_test my_unit_test (models/subfolder/my_model.yml)
22:47:46

actual differs from expected:

@@,id,color
→ ,1 ,red→blue


22:47:46
22:47:46    compiled Code at models/subfolder/my_model.yml
22:47:46
22:47:46  Done. PASS=1 WARN=0 ERROR=1 SKIP=1 TOTAL=3

Environment

- OS: 13.4.1
- Python: 3.10.11
- dbt: 1.8.0a1 (main)

Which database adapter are you using with dbt?

No response

Additional Context

No response